欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql按区间分组查询、获取各区间的总数

程序员文章站 2022-05-03 19:17:43
...

数据表如下

mysql按区间分组查询、获取各区间的总数

需求: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

运行结果

mysql按区间分组查询、获取各区间的总数