sql - 使用 row_number 反转对查询进行排名

Question

我有一个查询，我在其中对返回的行进行排名 ( related question)。我希望能够做的是反转排名，并相应地设置 new_rank。假设我有 999 个条目。

例如，如果我有这个查询:

with persondata as (
   SELECT firstname,
          lastname,
          year,
          personid,
        ((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 DESC) as new_rank,
         visible
   FROM members w 
   WHERE year = 2014 
   AND visible = 1 
   ORDER BY worth ASC LIMIT 5
) 
select row_number() over (order by worth DESC) as rank,
       * 
from persondata

这正确地返回了所需顺序的底部 5，如下所示:

 rank | firstname |     lastname      |  worth   | new_rank
------+-----------+-------------------+----------+----------
    1 | Peter     | Griffin           |  -520000 |      145 
    2 | Steve     | Moffat            |  -530000 |      519 
    3 | Gregory   | Peck              |  -540000 |      131 
    4 | Ruben     | Mumbles           |  -550000 |      130 
    5 | Ricky     | Swiss             |  -560000 |      120

有没有办法合并 row_count 并减去当前行数？或者设置一个计数器临时使用，设置行数然后递减？

最终期望的结果:

 rank | firstname |     lastname      |  worth   | new_rank 
------+-----------+-------------------+----------+----------
  997 | Peter     | Griffin           |  -520000 |      145 
  996 | Steve     | Moffat            |  -530000 |      519 
  997 | Gregory   | Peck              |  -540000 |      131 
  998 | Ruben     | Mumbles           |  -550000 |      130 
  999 | Ricky     | Swiss             |  -560000 |      120 

我还没有找到关于使用 row_number 实现的任何信息。

Answer 1

问题

难怪您会被这个查询卡住。您的格式和命名约定是混淆的手段(现在已经编辑了格式)。

• 您根据 new_rank 计算 members.worth，然后计算一个新的 worth(重新使用相同的名称!)和一个基于它的 rank，这非常困惑。

  • 不要重复使用相同的名称“worth”。使用单独的、有意义的标识符。
  • 为什么要将基于旧值的排名称为“new_rank”？

• 为什么一开始要使用 row_number() 而不是 rank() 来计算“排名”？

• 为什么要为表 w 使用别名 members ？

• 您的 ORDER BY worth 使用新计算的 worth ，而不是 members.worth 。你知道吗？构建这样的陷阱是非常不明智的，除非您确实想要混淆您的代码。

• 如果您想要两个排名，则不能在 CTE (Common Table Expression) 中使用 LIMIT 5。要获得相对排名，您必须为所有 相关行计算新的 worth。但是，不清楚哪些行实际上与您的排名相关。 members 中的所有行？只是那些通过了您的 WHERE 条件的？

• 最后，如何打破平局是不确定的。如果计算出的第 3 到第 7 个最高的 worth 相同，您期望得到什么结果？

解决方案

考虑 SELECT 查询中的事件序列:

• How will the order of operations in this query affect my results?

我根本不会在这里使用 CTE。它不需要并且可能比子查询慢。汇总加入前每个人的 Assets 和负债总和。这样，您就可以避免行数相乘:

• Two SQL LEFT JOINS produce incorrect result

我使用 calc_worth 和 calc_rank 而不是您的输出列 worth 和 rank 来稍微理清命名。

SELECT count(*) OVER ()     -- AS total_ct
       + 1                  -- counter off-by-1 error
       - rank() OVER (ORDER BY COALESCE(a.asset_sum, 0)
                             - COALESCE(l.liab_sum, 0)) AS calc_rank
     , m.firstname, m.lastname
     , COALESCE(a.asset_sum, 0) - COALESCE(l.liab_sum, 0) AS calc_worth
     , rank() OVER (ORDER BY m.worth DESC) AS rank
FROM  members m
LEFT  JOIN (
   SELECT personidref, sum(thevalue) AS asset_sum
   FROM   assets
   WHERE  year = 2014
   GROUP  BY 1
   ) a ON a.personidref = m.personid
LEFT JOIN (
   SELECT personidref, sum(amount) AS liab_sum
   FROM   liabilities
   WHERE  year = 2014
   ) l ON l.personidref = m.personid
WHERE  m.year = 2014 
AND    m.visible = 1 
ORDER  BY calc_worth DESC
LIMIT  5;

如果您想反转结果中的排序顺序，请将其包装在子查询中并在外部查询中再次排序。