自定义区间统计频数

你想知道用户在你的 app 上的访问时长情况。因此决定统计访问时长区间分别为 "[0-5>", "[5-10>", "[10-15>" 和 "15 or more" （单位：分钟）的会话数量，并以此绘制柱状图。

写一个SQL查询来报告（访问时长区间，会话总数）。结果可用任何顺序呈现。

方法一：用union连接 

select '[0-5>' as bin,
       count(*) as total 
from Sessions s
where s.duration/60 >= 0 and s.duration/60 < 5

union
select '[5-10>' as bin,
       count(*) as total 
from Sessions s
where s.duration/60 >= 5 and s.duration/60 < 10

union
select '[10-15>' as bin,
       count(*) as total 
from Sessions s 
where s.duration/60 >= 10 and s.duration/60 < 15

union
select '15 or more' as bin,
       count(*) as total 
from Sessions s 
where s.duration/60 >= 15

方法二：case when ：

select
(case when duration/60 >= 0 and duration/60 < 5 then '[0,5>'
when duration/60 >= 5 and duration/60 < 10 then '[5,10>'
when duration/60 >= 10 and duration/60 < 15 then '[10,15>'
else '15 or more'
end) as bins, count(duration) as total
from Sessions
group by bins
order by bins;

这种会只有三个结果，因为group by会将频数为0的结果不显示，所以需要改进为如下SQL：

含有GROUP BY子句的查询中如何显示COUNT()为0的结果

select t1.bin, ifnull(count(t2.bin),0) as total
from(
    select '[0-5>' as bin union all select '[5-10>' as bin union all
    select '[10-15>' as bin union all select '15 or more' as bin)t1
left join
(select case when duration >= 0 and duration < 300 then '[0-5>'
             when duration >= 300 and duration < 600 then '[5-10>'
             when duration >= 600 and duration < 900 then '[10-15>'
             else '15 or more' end as bin from Sessions)t2
on t1.bin = t2.bin
group by t1.bin