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 数据准备

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)

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

 

第二步:求对应时点处累计人数,及对应时段

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的结果,获取最终结果

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

 

3 小结

如果觉得本文对你有帮助,那么不妨也可以选择去看看我的博客专栏 ,部分内容如下:
数字化建设通关指南
专栏 原价99,现在活动价39.9,十一国庆后将上升至59.9,最后一波需要的赶紧冲,最终按照阶梯式增长,直到恢复原价。

主要内容:
(1)SQL进阶实战技巧
可以参考如下教程,具体链接如下

SQL很简单,可你却写不好?也许这才是SQL最好的教程

上面链接中的文章及技巧会不定期更新。

(2)数仓建模实战技巧和个人心得
       1)新人入职新公司后应如何快速了解业务?

       2)以业务视角看宽表化建设?

       3)  维度建模 or 关系型建模?

       4)业务模型与数据模型有什么区别?业务阶段的模型该如何建设?

       5)业务指标体系该如何建设?指标体系该如何维护?指标平台应如何建设?指标体系                           该由谁来搭建?

       6)如何优雅设计DWS层?DWS层模型好坏该如何评价?

       7)指标发生异常,该如何排查?应从哪些方面入手寻找问题点?

       8) 数据架构的选择,mpp or hadoop?

       9)数仓团队应如何体现自己的业务价值,讲好数据故事?

       10)BI与大数据有什么关系?BI与信息化、数字化之间有什么关系?BI与报表之间的关                          系?

       11)数据部门如何与业务部门沟通,并规划指引业务需求?

文章不限于以上内容,有新的想法也会及时更新到该专栏。

具体专栏链接如下:

 ​​​​​​数字化建设通关指南_莫叫石榴姐的博客-****博客

 

上一篇:springboot工程中使用tcp协议


下一篇:late-chunk,尝试拯救硬切分的缺点。有序chunk,产生条理性response。