制作会话柱状图

1435. 制作会话柱状图

表：Sessions

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| session_id          | int     |
| duration            | int     |
+---------------------+---------+
session_id 是该表主键
duration 是用户访问应用的时间, 以秒为单位

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

写一个解决方案来报告 (bin, total) 。

返回结果 无顺序要求 。

结果格式如下所示。

示例 1：

输入：
Sessions 表：
+-------------+---------------+
| session_id  | duration      |
+-------------+---------------+
| 1           | 30            |
| 2           | 199           |
| 3           | 299           |
| 4           | 580           |
| 5           | 1000          |
+-------------+---------------+
输出：
+--------------+--------------+
| 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 分钟。

原站题解

去查看

上次编辑到这里，代码来自缓存点击恢复默认模板

# Write your MySQL query statement below

mysql 解法, 执行用时: 264 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 16:33:15

select
s1.bin as bin,
ifnull(s2.total, 0) as total
from
(
    select '[0-5>' as bin union
    select '[5-10>' as bin union
    select '[10-15>' as bin union
    select '15 or more' as bin
) as s1
left join
(
    select
    case
    when duration/60<5
    then '[0-5>'
    when duration/60<10
    then '[5-10>'
    when duration/60<15
    then '[10-15>'
    else '15 or more'
    end as bin,
    count(1) as total
    from `Sessions`
    group by bin
) as s2
on s1.bin = s2.bin

mysql 解法, 执行用时: 278 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 16:32:52

# Write your MySQL query statement below
select '[0-5>' as bin, count(*) as total from Sessions where duration/60>=0 and duration/60<5
union
select '[5-10>' as bin, count(*) as total from Sessions where duration/60>=5 and duration/60<10
union
select '[10-15>' as bin, count(*) as total from Sessions where duration/60>=10 and duration/60<15
union
select '15 or more'as bin, count(*) as total from Sessions where duration/60>=15

上一题

下一题

详情