大数据学习第三天

SQL练习

        前言

       今天找了到sql题，生啃啃出来了，感觉sql除了语法，最重要的还是思路，思路理清楚了，题也就好做了

        

        题目：得出每篇文章在同一时刻最大在看人数

        这道题所要查的表：tb_user_log

                

        建表语句：

DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:11', 0),
  (102, 9001, '2021-11-01 10:00:09', '2021-11-01 10:00:38', 0),
  (103, 9001, '2021-11-01 10:00:28', '2021-11-01 10:00:58', 0),
  (104, 9002, '2021-11-01 11:00:45', '2021-11-01 11:01:11', 0),
  (105, 9001, '2021-11-01 10:00:51', '2021-11-01 10:00:59', 0),
  (106, 9002, '2021-11-01 11:00:55', '2021-11-01 11:01:24', 0),
  (107, 9001, '2021-11-01 10:00:01', '2021-11-01 10:01:50', 0);

        我直接在表里多加了两行数据，结果写题的时候一看时间加错了(汗)，不过对于文章id不等于0的判断还是要有的

        解题思路

        我们先来看一下这道题的题目，每篇文章在同一时刻的最大在看人数，做题要会提取关键字。

         这道题里的关键字，对应这表中数据来看 

                每篇文章：artical_id(文章id) : 9001 、9002

                同一时刻：不同的用户进入时间与离开时间，之间有重合的时间

                最大在看人数：这是最后要输出的数据

        好，分析到这里估计就有人跟我第一次做这道题时一样，开始想怎么对比重合的时间了。

        这种方法也许可行，但肯定要耗费很多的时间和资源。

        我们换个思路想一想：题目要求的是 同一时刻 在看这篇文章的人数，那我们可不可以时间为顺序，有人进入文章时人数+1，有人离开文章时人数+(-1)。这样一想 问题是不是简单的多了？最后只需要把最大的人数得出来，就可以解开这道题了。

        代码实现

        既然思路有了，下面就通过代码把它实现。

        分析一下表tb_user_log ，表中我们需要的字段只有artical_id（文章id）、in_time（进入时间）、out_time（离开时间）。

        我们现在需要实现 有人进入文章时人数+1，有人离开文章时人数+(-1) 。可以使用伪列 f ，

把 in_time的数据后伪列 f 设为1 ; out_time的数据后伪列 f 设为 -1。然后把这两张表用union all并起来。

select 
artical_id,in_time time, 1 f 
from tb_user_log
where artical_id != 0
union all 
select 
artical_id,out_time time, -1 f 
from tb_user_log
where artical_id != 0

        这样就得出了下面的表 起个别名 t1

                  ​​​​​​​        ​​​​​​​        ​​​​​​​       

        下面要实现的就是把 t1 表里的数据按照时间升序排序 

        

  select
    artical_id,Time,f
  from (
    select
      artical_id, in_time Time, 1 f
    from tb_user_log
      where artical_id != 0
    union all
    select
      artical_id, out_time Time, -1 f
    from tb_user_log
    where artical_id != 0
	) t1
  order by Time

        得出下面的表

           ​​​​​​​        ​​​​​​​              

        下面就是要把伪列上的数给加起来了，这里用开窗函数来实现

sum(要求和的字段) over(partition by 要分组的字段 order by 要排序的字段)

  select
    artical_id,
    sum(f) over(partition by artical_id order by Time) max_man
  from (
    select
      artical_id, in_time Time, 1 f
    from tb_user_log
    where artical_id != 0
    union all
    select
      artical_id, out_time Time, -1 f
    from tb_user_log
    where artical_id != 0
	) t1

        得出下面这张表，起个别名 t2

        ​​​​​​​        ​​​​​​​        ​​​​​​​        ​​​​​​​        ​​​​​​​        ​​​​​​​        

        得出t2表后，这题的答案就明显了，只需要给文章id分组，然后再用max()聚合函数得出最大值就ok啦

select
  artical_id,
  MAX(max) max_uv
from (
	select
    artical_id,
    sum(f) over(partition by artical_id order by Time) max
  from (
    select
      artical_id, in_time Time, 1 f
    from tb_user_log
    where artical_id != 0
    union all
    select
      artical_id, out_time Time, -1 f
    from tb_user_log
    where artical_id != 0
	) t1
) t2
group by artical_id

                           输出最终结果：

        ​​​​​​​        ​​​​​​​        ​​​​​​​        ​​​​​​​        ​​​​​​​        ​​​​​​​