查找前N个最常见的类别,每个类别的前N个最常见的子类别

我正在尝试创建一个将检索的查询:

顶部例如来自汽车列表的3个最受欢迎的品牌 . 对于前3个品牌中的每一个,我想要检索前5个最受欢迎的型号 .

我尝试了排名/分区策略和 distinct ON 策略,但我似乎无法弄清楚如何在两个查询中获得限制 .

以下是一些示例数据:http://sqlfiddle.com/#!15/1e81d5/1

从排名查询中我会得到这样的输出,给定样本数据(顺序不重要):

brand       car_mode    count
'Audi'      'A4'        3
'Audi'      'A1'        3
'Audi'      'Q7'        2
'Audi'      'Q5'        2
'Audi'      'A3'        2
'VW'        'Passat'    3
'VW'        'Beetle'    3
'VW'        'Caravelle' 2
'VW'        'Golf'      2
'VW'        'Fox'       2
'Volvo'     'V70'       3
'Volvo'     'V40'       3
'Volvo'     'S60'       2
'Volvo'     'XC70'      2
'Volvo'     'V50'       2

回答(3)

2 years ago

您可以使用窗口函数:

select brand, car_model, cnt_car
from (select c.*, dense_rank() over (order by cnt_brand, brand) as seqnum_b
      from (select brand, car_model, count(*) as cnt_car,
                   row_number() over (partition by brand order by count(*) desc) as seqnum_bc,
                   sum(count(*)) over (partition by brand) as cnt_brand
            from cars c
            group by brand, car_model
           ) c
     ) c
 where seqnum_bc <= 5 and seqnum_b <= 3
 order by cnt_brand desc, brand, cnt desc;

如果您知道每个品牌(或至少每个顶级品牌)至少有五辆车,那么您可以将查询简化为:

select brand, car_model, cnt_car
from (select brand, car_model, count(*) as cnt_car,
              row_number() over (partition by brand order by count(*) desc) as seqnum_bc,
              sum(count(*)) over (partition by brand) as cnt_brand
      from cars c
      group by brand, car_model
     ) c
where seqnum_bc <= 5 
order by cnt_brand desc, brand, cnt desc
limit 15

2 years ago

你可以尝试使用cte和window函数 row_number()

with cte as
(
select brand,car_model,count(*) as cnt from cars group by brand,car_model
 ) , cte2 as
 (
     select * ,row_number() over(partition by brand order by cnt desc) rn from cte
 )
select brand,car_model,cnt from cte2 where rn<=5

demo link

2 years ago

结果我可以按照评论中的建议使用 LATERAL join . 谢谢 .

SELECT brand, car_model, the_count
FROM
  (
    SELECT brand FROM cars GROUP BY brand ORDER BY COUNT(*) DESC LIMIT 3 
  ) o1
INNER JOIN LATERAL
  (
    SELECT car_model, count(*) as the_count
    FROM cars
    WHERE brand = o1.brand
    GROUP BY brand, car_model
    ORDER BY count(*) DESC LIMIT 5
  ) o2 ON true;

http://sqlfiddle.com/#!15/1e81d5/9