sql - 仅返回 GROUPING SETS 查询中每个组的前 n 个结果

Question

我有一个相当复杂的查询，使用 GROUPING SETS 执行一些聚合，它看起来大致如下:

SELECT 
    column1,
    [... more columns here]
    count(*)
FROM table_a 
GROUP BY GROUPING SETS (
    column1,
    [... more columns here]
)
ORDER BY count DESC

只要每组的结果数量相当少，这种方法通常效果很好。但是我在这个查询中有一些列可以有大量不同的值，这导致这个查询返回了大量的行。

我实际上只对分组集中每个组的最佳结果感兴趣。但是似乎没有明显的方法来限制使用分组集的查询中每组的结果数量，LIMIT 在这种情况下不起作用。

我使用的是 PostgreSQL 9.6，所以我在这里可以使用哪些新功能不受限制。

所以我的查询是这样的:

| column1 | column2 | count |
|---------|---------|-------|
| DE      |         | 32455 |
| US      |         | 3445  |
| FR      |         | 556   |
| GB      |         | 456   |
| RU      |         | 76    |
|         | 12      | 10234 |
|         | 64      | 9805  |
|         | 2       | 6043  |
|         | 98      | 2356  |
|         | 65      | 1023  |
|         | 34      | 501   |

我真正想要的是只返回前 3 个结果的东西:

| column1 | column2 | count |
|---------|---------|-------|
| DE      |         | 32455 |
| US      |         | 3445  |
| FR      |         | 556   |
|         | 12      | 10234 |
|         | 64      | 9805  |
|         | 2       | 6043  |

Answer 1

使用row_number 和分组

select a, b, total
from (
    select 
        a, b, total, 
        row_number() over(
            partition by g 
            order by total desc
        ) as rn
    from (
        select a, b, count(*) as total, grouping ((a),(b)) as g
        from t
        group by grouping sets ((a),(b))
    ) s
) s
where rn <= 3