sql - 获取覆盖组的 N 条记录的客户数量

Question

我在 cust_id 级别有一个唯一的表，并且具有以下内容：

CustID  Product Country Region
1   A   US  NA
2   A   US  NA
3   A   US  NA
4   A   US  NA
5   A   US  NA
6   B   US  NA
7   B   US  NA
8   B   US  NA
9   B   US  NA
10  C   US  NA
11  C   US  NA
12  C   US  NA
13  C   US  NA
14  D   US  NA
15  D   US  NA
16  D   US  NA
17  E   US  NA
18  E   US  NA
19  A   UK  EMEA
20  A   UK  EMEA
21  A   UK  EMEA
22  A   UK  EMEA
23  A   UK  EMEA
24  B   UK  EMEA
25  B   UK  EMEA
26  B   UK  EMEA
27  B   UK  EMEA
28  C   UK  EMEA
29  C   UK  EMEA
30  C   UK  EMEA
31  C   UK  EMEA
32  D   UK  EMEA
33  D   UK  EMEA
34  D   UK  EMEA
35  E   UK  EMEA
36  E   UK  EMEA

我正在寻找一个 SQL 查询，它将返回一个国家和地区使用的前 4(N) 种产品以及使用每种产品的客户数量。输出将如下所示：

Region  Country Product Count ( Distinct ( cust_id ))
NA  US  A   5
NA  US  B   4
NA  US  C   4
NA  US  D   3
UK  EMEA    A   4
UK  EMEA    B   4
UK  EMEA    C   4
UK  EMEA    D   3

请帮忙。谢谢！

标签： sql

Answer 1

您可以为此使用带有窗口函数的聚合：

select rcp.*
from (select region, country, product, count(*) as cnt,
             row_number() over (partition by region, country order by count(*) desc) as seqnum
      from t
      group by region, country, product
     ) rcp
where seqnum <= 4