有店铺销量表purchase:
输出结果如下表:
With temp as ( Select substr(dt, 1, 7) as ym, seller_id, seller_name, sum(gmv) as tot_gmv, rank() over (partition by substr(dt, 1, 7) order by sum(gmv) desc) as rk From purchase Group by 1, 2, 3 Where substr(dt, 1, 7) in (’2019-05’, ‘2019-06’) ) , n_sellers as ( Select substr(dt, 1, 7) as ym, count(distinct seller_id) as cnt From purchase Group by 1 ) Select t.ym as year_month, t.seller_id, t.seller_name From temp t Left join n_seller n On t.ym = n.ym Where t.rk <= 0.5*n.cnt
# 参考答案有点复杂,主要是通过percentile函数取出前50%的gmv具体是多少,在多用一个字查询查出哪些店铺gmv超过50%的店家gmv (select p.seller_name month(p.dt) as month, sum(p.gmv) as total_gmv, from purchase p where month(p.dt) in (5,6) group by p.seller_id, month(p.dt)) cjoin
(select a.seller_name, a.month, percentile(a.total_gmv,0.5) as mid
from
(select p.seller_name month(p.dt) as month,
sum(p.gmv) as total_gmv,
from purchase p
where month(p.dt) in (5,6)
group by p.seller_id, month(p.dt)) a
group by a.month, a.seller_name) b on c.seller_name=b.seller_name and c.month=b.month
where c.total_gmv>b.mid
select seller_id ,seller_name ,sale_month ,sum(gmv) from ( select *,month(dt) as sale_month from purchase ) as p where sale_month in ('05','06') group by seller_id,seller_name,sale_month having sum(gmv)/(select sum(gmv) from purchase as p1 where p.sale_month=month(dt))>0.5;