在复杂查询中生成有序序列等级 [英] Generating a ordered sequence rank on complex query

问题描述

我正在尝试在查询的输出中创建隐式排名。问题似乎是 row_number（）在计算 worth 之前正在运行。

I am trying to create an implicit rank in the output from the query. The problem seems to be that row_number() is running before worth is calculated.

SELECT 
    firstname,
    lastname,
    personid,
    year,
    (
        SELECT COALESCE(SUM(thevalue),0) 
        FROM assets WHERE personidref = w.personid AND year = w.year
    ) AS assets ,
    (
        SELECT COALESCE(SUM(amount),0) 
        FROM liabilities WHERE personidref = w.personid AND year = w.year
    ) AS liabilities,
    (
        (SELECT COALESCE(SUM(thevalue),0) 
         FROM assets 
         WHERE personidref = w.personid AND year = w.year) 
        - 
        (SELECT COALESCE(SUM(amount),0) 
         FROM liabilities 
         WHERE personidref = w.personid AND year = w.year)
    ) as worth,
    row_number() over(ORDER BY w.worth) as rank 
FROM members w 
WHERE year = 2012 
ORDER BY worth DESC LIMIT 2;

结果是我得到了：

| firstname | lastname | personid | year | assets | liabilities | worth | rank |
+-----------+----------+----------+------+--------+-------------+-------+------+
| foo       | bar      | 234      | 2012 | 30000  | 20          | 29980 | 32   |
| foo2      | bar2     | 5234     | 2012 | 30000  | 100         | 29900 | 69   |

而不是此期望的输出：

| firstname | lastname | personid | year | assets | liabilities | worth | rank |
+-----------+----------+----------+------+--------+-------------+-------+------+
| foo       | bar      | 234      | 2012 | 30000  | 20          | 29980 | 1    |
| foo2      | bar2     | 5234     | 2012 | 30000  | 100         | 29900 | 2    |

是否可以预运行此查询并按排名先进行排序？

Is there a way to pre-run this query and pre-sort by rank first?

推荐答案

没有示例数据很难确定，但是如果您想要排名，请为什么不使用 rank 或 dense_rank ？

Without sample data it's hard to be sure, but if you want a ranking, why not use rank or dense_rank?

rank() over(ORDER BY w.worth) as rank 

或

dense_rank() over(ORDER BY w.worth) as rank 

？

这就是我想您可能正在尝试做的。未经测试，因为没有示例数据或 http://sqlfiddle.com/ 进行测试，但是：

Here's what I think you might be trying to do. Untested, as there's no sample data or http://sqlfiddle.com/ to test with, but:

SELECT
    *,
    dense_rank() OVER (ORDER BY worth)
FROM
(
    SELECT
        *,
        assets - liabilities AS worth
    FROM
    (
        SELECT 
            firstname,
            lastname,
            personid,
            year,
            (
                SELECT COALESCE(SUM(a.thevalue),0) 
                FROM assets a WHERE a.personidref = w.personid AND a.year = w.year
            ) AS assets ,
            (
                SELECT COALESCE(SUM(l.amount),0) 
                FROM liabilities l WHERE l.personidref = w.personid AND l.year = w.year
            ) AS liabilities
        FROM members w
        WHERE year = 2012
    ) AS a_and_l
) AS net_worths
ORDER BY worth DESC
LIMIT 2;

这篇关于在复杂查询中生成有序序列等级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！