SQL 刷题—直播间人气值

需求描述

直播间开播记录表 t_live和直播间观看记录表t_look_log 数据如下：

create table t_live
(
    author_id     integer, --博主
    live_id       integer, --直播间ID
    live_duration integer  --开播时长
);
insert into t_live
values (1, 1, 60),
       (2, 2, 120),
       (3, 3, 50);
 --观看记录表
create table t_look_log
(
    user_id           integer,
    live_id           integer,
    watching_duration integer
);
insert into t_look_log
values (11, 1, 60),
       (12, 1, 30),
       (13, 1, 60),
       (8, 2, 30),
       (9, 1, 50);

需求1：求每个直播间的ACU:平均同时在线人数，观众观看时长总计/某场直播开播时长，无人观看显示0;

需求2：求每个直播间观看时占所有直播间观看时长的比值

需求实现

PostgreSQL 实现

需求1

需求1的计算公式已给出，我们直接汇总观看记录表的观看时长按直播间ID汇总即可，然后拿直播间开播信息表和刚才的汇总信息进行关联即可’，具体实现如下：

select t.author_id, t.live_id, coalesce(round((t1.duration_sum / t.live_duration::numeric), 2), 0) acu
from t_live t
         left join (select live_id, sum(watching_duration) duration_sum from t_look_log group by live_id) t1
                   on t.live_id = t1.live_id
;

需求2

需求2需求先求所有用户的观看时长，然后进行关联即可

select t.live_id,
      concat ( round(coalesce(t1.duration_sum, 0) / sum(coalesce(t1.duration_sum, 0) ) over ()*100,2),'%' ) duration_rate
from t_live t
         left join (select live_id, sum(watching_duration) duration_sum from t_look_log group by live_id) t1
                   on t.live_id = t1.live_id

Hive 实现

需求1

select t.author_id, t.live_id, nvl(round((t1.duration_sum / t.live_duration ), 2), 0) acu
from t_live t
         left join (select live_id, sum(watching_duration) duration_sum from t_look_log group by live_id) t1
                   on t.live_id = t1.live_id

需求2

select t.live_id,
      concat ( round(coalesce(t1.duration_sum, 0) / sum(coalesce(t1.duration_sum, 0) ) over ()*100,2),'%' ) duration_rate
from t_live t
         left join (select live_id, sum(watching_duration) duration_sum from t_look_log group by live_id) t1
                   on t.live_id = t1.live_id;