表:Sessions
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| session_id | int |
| duration | int |
+---------------------+---------+
session_id 是该表主键
duration 是用户访问应用的时间, 以秒为单位
你想知道用户在你的 app 上的访问时长情况。因此决定统计访问时长区间分别为 "[0-5>", "[5-10>", "[10-15>" 和 "15 or more" (单位:分钟)的会话数量,并以此绘制柱状图。
写一个SQL查询来报告(访问时长区间,会话总数)。结果可用任何顺序呈现。
下方为查询的输出格式:
Sessions 表:
+-------------+---------------+
| session_id | duration |
+-------------+---------------+
| 1 | 30 |
| 2 | 199 |
| 3 | 299 |
| 4 | 580 |
| 5 | 1000 |
+-------------+---------------+
Result 表:
+--------------+--------------+
| bin | total |
+--------------+--------------+
| [0-5> | 3 |
| [5-10> | 1 |
| [10-15> | 0 |
| 15 or more | 1 |
+--------------+--------------+
对于 session_id 1,2 和 3 ,它们的访问时间大于等于 0 分钟且小于 5 分钟。
对于 session_id 4,它的访问时间大于等于 5 分钟且小于 10 分钟。
没有会话的访问时间大于等于 10 分钟且小于 15 分钟。
对于 session_id 5, 它的访问时间大于等于 15 分钟。
解答:这一题一开始我不知道怎么么去构建bin的区间表。后面看来评论才知道有union这个可以跟select配合构建一个空表的结构。后面呢利用case when 把秒数变成分钟数区间的过程呢顺便统计一下个数
最后在用第一个构建的空表去左连接case when构建的表。为什么是左连接而不是右连接呢?:因为我们区间可能会出现没有用户的情况(total为0),但是如果是右连接,这些数据就会被过滤掉。只有左连接才能有效的保留这些数据。盲写一遍不知道对不对
select a.bin as bin,count(b.bin) form
(
select '[0-5>' as bin union select '[5-10>' as bin union select '[10-15>' as union
) as a
left jion
(
select case
when 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
) as b
on a.bin = b.bin
group by a.bin;