mysql按区间分组查询、获取各区间的总数
程序员文章站
2022-05-03 19:17:43
...
数据表如下
需求:tick_count是次数、user_account是用户标识,user_account可能重复,统计0次,1-3次、4-6次、7-9次、10-12次、13次以上,这几个区间各有多少个用户数
select case
when tc.stick_count = 0 then
'0'
when tc.stick_count > 0 and tc.stick_count <= 3 then
'1to3'
when tc.stick_count > 3 and tc.stick_count<= 6 then
'4to6'
when tc.stick_count > 6 and tc.stick_count <= 9 then
'7to9'
when tc.stick_count > 9 and tc.stick_count <= 12 then
'10to12'
when tc.stick_count > 13 then
'more13'
end stickLevel,
COUNT(DISTINCT user_account) total
from t_stick_detail_hourly tc
group by case
when tc.stick_count = 0 then
'0'
when tc.stick_count > 0 and tc.stick_count <= 3 then
'1to3'
when tc.stick_count > 3 and tc.stick_count<= 6 then
'4to6'
when tc.stick_count > 6 and tc.stick_count <= 9 then
'7to9'
when tc.stick_count > 9 and tc.stick_count <= 12 then
'10to12'
when tc.stick_count > 13 then
'more13'
end
运行结果