PostgreSQL：如何在周表中查找具有最大资金的 id

Question

我有下表保存每周锦标赛的虚拟货币：

# select * from pref_money limit 5;
       id       | money |   yw
----------------+-------+---------
 OK32378280203  |   -27 | 2010-44
 OK274037315447 |   -56 | 2010-44
 OK19644992852  |     8 | 2010-44
 OK21807961329  |   114 | 2010-44
 FB1845091917   |   774 | 2010-44
(5 rows)

在列出用户的 PHP 脚本中，我需要知道谁是每周获胜者，以便我可以在他们的名字旁边显示奖牌。所以我正在尝试这个 SQL 语句：

# find the weekly winners
$sth = $db->prepare('select id from pref_money 
    where money in 
    (select max(money) from pref_money group by yw)');

$sth->execute();
while ($row = $sth->fetch(PDO::FETCH_ASSOC))
        @$medals[$row['id']]++;

这大多数情况下有效，但有时当用户碰巧在某周内拥有与获胜者相同的金额（但不是最大金额）时，我会得到误报再过一周。

有谁知道如何更改 SQL 语句，以便真正只选择获胜者 ID？

当我尝试以下操作时，出现错误：

# select id, max(money) from pref_money group by yw;
ERROR:  column "pref_money.id" must appear in the 
  GROUP BY clause or be used in an aggregate function

谢谢！ Alex

更新：PostgreSQL 8.4.5 / CentOS 并且可能有联系:-)

Answer 1

哪个版本的 PostgreSQL？这对于 8.4 中引入的窗口函数来说很容易：

select id, yw from (
    select id,
           yw,
           row_number() over(partition by yw order by money desc) as ranking
    from pref_money
) x
where x.ranking = 1

这在旧版本中几乎可以做到：

select id, yw
from pref_money
     join (select yw, max(money) max_money
           from pref_money
           group by yw) max_money
          on pref_money.yw = max_money.yw and pref_money.money = max_money.max_money

但是请注意，两个用户并列获得最大金钱的几周将在输出中显示两次。如果这确实是您想要的，您也可以使用窗口函数，使用 rank() 而不是 row_number()

Answer 2

假设 PostgreSQL 8.4+，并且在给定的一周内没有人获得获胜者位置 - 这将列出每周获胜者：

WITH sample AS (
  SELECT t.id,
         t.yw,
         ROW_NUMBER() OVER(PARTITION BY t.yw
                               ORDER BY t.money DESC) AS rank
    FROM PREF_MONEY t)
SELECT s.id
  FROM sample s
 WHERE s.rank = 1

如果可以有平局，请使用：

WITH sample AS (
  SELECT t.id,
         t.yw,
         DENSE_RANK() OVER(PARTITION BY t.yw
                               ORDER BY t.money DESC) AS rank
    FROM PREF_MONEY t)
SELECT s.id
  FROM sample s
 WHERE s.rank = 1

我包含了 yw 列，以防万一想要/需要按列过滤。