sql - 在 Redshift 中按类别选择 n 个最大计数

Question

我想在表格中为每组选择 X 个最常见的配对。让我们考虑下表:

+-------------+-----------+
| identifier  |    city   |
+-------------+-----------+
| AB          |  Seattle  |
| AC          |  Seattle  |
| AC          |  Seattle  |
| AB          |  Seattle  |
| AD          |  Seattle  |
| AB          |  Chicago  |
| AB          |  Chicago  |
| AD          |  Chicago  |
| AD          |  Chicago  |
| BC          |  Chicago  |
+-------------+-----------+

• 西雅图，AB 出现 2 次
• 西雅图，AC 发生 2 次
• 西雅图，AD 发生 1 次
• 芝加哥，AB 出现 2 次
• 芝加哥，AD 发生 2 次
• 芝加哥，BC 发生 1 次

如果我想为每个城市选择 2 个最常见的，结果应该是:

+-------------+-----------+
| identifier  |    city   |
+-------------+-----------+
| AB          |  Seattle  |
| AC          |  Seattle  |
| AB          |  Chicago  |
| AD          |  Chicago  |
+-------------+-----------+

感谢任何帮助。谢谢，本尼

Answer 1

您可以在行号中使用count来排序每个城市组合出现的次数，并选择前两个。

select city,identifier 
from (
select city,identifier
,row_number() over(partition by city order by count(*) desc,identifier) as rnum_cnt
from tbl
group by city,identifier
) t
where rnum_cnt<=2