有店铺销量表purchase:
输出结果如下表:
select month(dt) as month, seller_name, sum(gmv) as totalgmv from purchase where month(dt) in ('5' ,'6') group by month(dt),seller_name2.从临时表c中筛选贡献前50%的店铺,此时还是要注意加上筛选条件
select month, seller_name, percentile(totalgmv, 0.5)as mid
from c group by month(dt) seller_name
3.再求出5,6月每家店铺总的gmv与符合条件的临时表b内联,*表示要求查询的内容 select * select month(dt) as month, seller_name, sum(gmv) as totalgmv
from purchase
where month(dt) in ('5' ,'6')
group by month(dt),seller_name) a join b a.month = b.month and a.seller_name = b.seller_name
where totalgmv > mid
总觉得输出不用concat呢,就是直接dt不就行,最后呈现的结果也不是2019-09呀? Select id, dt, seller_id, t1.seller_name,item_id, gmv from purchase t1 join( select month, seller_name, total_gmv/sum(total_gmv)over(partition by month) percentage_rate, sum(total_gmv)over(partition by month order by total_gmv desc)/sum(total_gmv)over(partition by month) cumulative_rate # 按照月份累积 from( select month(dt) month, seller_name, sum(gmv) total_gmv from purchase where month(dt) in (5, 6) group by month ,seller_name )t2 # 计算按月和店铺汇总的gmv )t3 # 计算累计百分比 on t1.seller_name=t3.seller_name and month(t1.dt)=t3.month where cumulative_rate-percentage_rate <=0.5 #筛选百分比