mysql kudu impala中 多层分组 先统计用户数量 再区间统计和的数量

需求

统计用户记录中的金币数量再根据用户进行金币数区间统计

sql

– kudu跑不了这个sql但是理论上这个可行
select elt(interval(aa.count,0, 10000, 20000, 30000), ‘1/less10000’, ‘2/1000to20000’, ‘3/20000to30000’, ‘4/more30000’) as level, count(1) analysis
from (select sum(ap.parameter) count ,up.did did from db_user.prize up left join admin.prize ap on up.prize_id=ap.id group by did) aa
group by elt(interval(aa.count,0, 10000, 20000, 30000), ‘1/less10000’, ‘2/1000to20000’, ‘3/20000to30000’, ‘4/more30000’);
– 红包币数量排序
select sum(ap.parameter) count ,up.did did from db_user.prize up left join admin.prize ap on up.prize_id=ap.id group by did order by count desc;

– 最后用这个统计的区间红包币数值
select
case
when aa.count<1000 then ‘1 0-1000’
when aa.count<5000 then ‘2 1000-5000’
when aa.count<10000 then ‘3 5000-10000’
when aa.count<15000 then ‘4 10000-15000’
when aa.count<20000 then ‘5 15000-20000’
when aa.count<25000 then ‘6 20000-25000’
else ‘7 大于25000’ end level,
count(1) analysis
from (select sum(ap.parameter) count ,up.did did from db_user.prize up left join admin.prize ap on up.prize_id=ap.id group by did) aa
group by level;

参考

mysql 区间分组_mysql 按区间group by