function - 在Postgresql中返回每个组的前三个结果(每周前3个)

 CREATE TABLE games
    (
      idg serial NOT NULL,
      nation character(3),
      points integer,
      datag date,
      CONSTRAINT pk_games PRIMARY KEY (idg )
)

idg    nation  points      dateg
1      ita      12      2011-10-10
2      fra       9      2011-10-11
3      ita       4      2011-10-12
4      fra       8      2011-10-11
5      ger      12      2011-10-12
6      aut       6      2011-10-10
7      ita      11      2011-10-17
8      ita      10      2011-10-18
9      fra       9      2011-10-19
10     ger      15      2011-10-19
11     fra      16      2011-10-18

我想显示最大的三个星期总分组我知道我不能使用max（sum（points），所以我做了下一个查询：

select extract(week from datag) as "dateg", nation, sum(points) as "total"
from games
group by dateg, nation
order by dateg asc, total desc limit 3

但这只是前三个总数。我怎样才能做到每周（每个小组前三名的总数，这将是“每周前三名”）呢？有什么想法吗？
在Postgresql 9工作。
提前谢谢。

最佳答案

使用awindow function：

select idg, nation, points, wk, r
from (
    select idg, nation, points, extract(week from datag) as wk,
           row_number() over (partition by extract(week from datag) order by points desc) as r
    from games
) as dt
where r <= 3

根据需要调整选择。如果您想要唯一的排名，可以在分区内添加nation。
如果您想先计算每个国家的每周积分，只需添加另一个派生表并稍微调整列名即可：

select nation, wk, wk_points, rn
from (
    select nation, wk, wk_points,
           row_number() over (partition by wk order by wk_points desc) as rn
    from (
        select nation, extract(week from datag) wk, sum(points) wk_points
        from games
        group by wk, nation
    ) as dt_sum
) as dt
where rn <= 3