首页 文章

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

提问于
浏览 次
0

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

顶部例如来自汽车列表的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
sql postgresql

3 回答

  • 1

    您可以使用窗口函数：

    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
    回复于
  • 0

    你可以尝试使用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

    回复于
  • 0

    结果我可以按照评论中的建议使用 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

    回复于

相关问题