【SQL】求每组商品的浏览用户数

一、题目

求每组商品的浏览人数

表order：goods_id, amount
表pv：goods_id,uid

商品分组按照销售总金额排序，分为top10，top10-top20，others

二、解题思路

实现商品销售分组，创建临时表方便运算。

-- 先算出每个商品的总销售金额
-- group by 1 是根据第一列聚合，即goods_id
select goods_id, sum(amount)
from goods
group by 1;

--对商品的总销售金额排序
select
 goods_id,
 row_number() over(partition by goods_id order by amt desc) as rn
from(
 select goods_id, sum(amount) as amt
 from goods
 group by 1;
)

-- 创建临时表，存储商品id和其对应分组
create table if not exists goods_category as
select 
 goods_id,
 case when rn  <=  10 then 'top10'
 	  when rn <= 20 then 'top10-top20'
 	  else 'others' end as 'goods_group'
from(
 select
  goods_id,
  row_number() over(partition by goods_id order by amt desc) as rn
 from(
  select goods_id, sum(amount) as amt
  from goods
  group by 1;
 )t1
)t2;
select
 gc.goods_group, count(distinct pv.uid) as uid
from pv left join
goods_category gc
on pv.goods_id = gc.goods_id
group by 1;