SQL进阶技巧：统计各时段观看直播的人数

0 需求描述

1 数据准备

2 问题分析

3 小结

如果觉得本文对你有帮助，那么不妨也可以选择去看看我的博客专栏，部分内容如下：

数字化建设通关指南

专栏原价99，现在活动价39.9，十一国庆后将上升至59.9，最后一波需要的赶紧冲，最终按照阶梯式增长，直到恢复原价。

0 需求描述

现在有一张A表，里面包含当天用户观看直播的记录，需求：统计每个时间段内对应的观看直播的人数

表A

user_id	start_time	end_time
1001	2024-06-06 12:05:00	2024-06-06 13:10:00
1002	2024-06-06 11:05:00	2024-06-06 13:10:00

1 数据准备

sql">create table a as
    (select stack(
                    2,
                    1001, '2024-06-06 12:05:00', '2024-06-06 13:10:00',
                    1002, '2024-06-06 11:05:00', '2024-06-06 13:10:00'
            ) as (user_id, start_time, end_time));

2 问题分析

本题主要考察拉链表流水化的思维方式，即如何将拉链表转换成对应的时间明细表

具体步骤如下：

第一步：行转列，标记在线、下线状态

状态标记：st时间记为 1，et时间记为-1 （et时表示下播，取-1）

sql">select user_id, start_time dt, 1 status
 from a
 union all
 select user_id, end_time dt, -1 status
 from a

第二步：求对应时点处累计人数，及对应时段

sql">select user_id
      ,dt start_time
      ,sum(status) over(order by dt) acc_cnt
      ,lead(dt) over(order by dt) end_time
from
(select user_id, start_time dt, 1 status
 from a
 union all
 select user_id, end_time dt, -1 status
 from a) t

第三步：筛选出累计人数大于0的结果，获取最终结果

sql">select user_id
     , start_time
     , end_time
     , acc_cnt
from (select user_id
           , dt                             start_time
           , sum(status) over (order by dt) acc_cnt
           , lead(dt) over (order by dt)    end_time
      from (select user_id, start_time dt, 1 status
            from a
            union all
            select user_id, end_time dt, -1 status
            from a) t) t