更有效的方法是选择分区上的array_agg，或者选择array(子查询)

Question

我有这样的数据：

 group_id |         day | amount
----------+-------------+-------
        1 | 15 Nov 2015 |   5.0
        1 | 15 Nov 2015 |   6.0
        1 | 14 Nov 2015 |   3.0
        2 | 17 Nov 2015 |   5.0
        2 | 15 Nov 2015 |   5.0

我想为每个（group_id，day）选择前十个金额。我试着写这样的东西：

Postgres 9.4

select max(x.group_id), max(x.day), max(x.amounts) 
from (select group_id, day, array_agg(amount) over w as amounts,
             row_number() over w as r
      from my_table window w as (partition group_id, day 
                                 order by amount desc)) as x
where x.r<=10 group by x.group_id,x.day

我还想到，我可以写一个更简单的查询：

select a.day, a.group_id, array(select amount 
                                from my_table 
                                where day=a.day and group_id=a.group_id
                                order by amount desc limit 10)
from my_table as a group by a.day, a.group_id

这正是我想要的。这让我想到了一个问题：假设我可以调整第一个例子来得到我想要的，哪个查询会更快？子查询是否比分区慢？

Answer 1

您可能应该使用一个分析函数。

不知道为什么在子查询之外还有MAX、MIN。您的查询似乎不对等。

你对小组前10名的要求应该是：

WITH ranked as (
      SELECT group_id, 
             day,
             row_number() OVER 
                   (partition by group_id, day ORDER BY ammount DESC) rn
      FROM my_table 
) 
SELECT group_id, 
       day,
       array_agg(amount) over (partition by group_id, day ORDER BY rn)
FROM ranked
WHERE rn <=10