postgresql - 在复杂查询上生成有序序列排名

Question

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

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    |

有没有办法预运行此查询并按排名优先进行预排序？

Answer 1

将 row_number() 加倍怎么样？

with enchilada as (
SELECT firstname,lastname,personid,year,(SELECT COALESCE(SUM(thevalue),0)
FROM assets WHERE personidref = w.personid) 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 )
select row_number() over (order by rank) as new_rank, * from enchilada;