HiveSQL刷题

41、同时在线人数问题

现有各直播间的用户访问记录表（live_events）如下，表中每行数据表达的信息为，一个用户何时进入了一个直播间，又在何时离开了该直播间。
user_id
(用户id)live_id
(直播间id)in_datetime
(进入直播间的时间)out_datetime
(离开直播间的时间)10012021-12-1 19:30:002021-12-1 19:53:0010022021-12-1 21:01:002021-12-1 22:00:0010112021-12-1 19:05:002021-12-1 20:55:00
现要求统计各直播间最大同时在线人数，期望结果如下：
live_id
<int>
(直播id)max_user_count
<int>
(最大人数)142332

select live_id,
       max(sum) max_user_count
from (select *,
             sum(user_change) over (
                 partition by
                     live_id
                 order by
                     time1
                 ) sum
      from (select user_id,
                   live_id,
                   in_datetime time1,
                   1           user_change
            from live_events
            union all
            select user_id,
                   live_id,
                   out_datetime time1,
                   -1           user_change
            from live_events) t1) t2
group by live_id;

42、会话划分问题

现有页面浏览记录表（page_view_events）如下，表中有每个用户的每次页面访问记录。
user_idpage_idview_timestamp100home1659950435100good_search1659950446100good_list1659950457100home1659950541100good_detail1659950552100cart1659950563101home1659950435101good_search1659950446101good_list1659950457101home1659950541101good_detail1659950552101cart1659950563102home1659950435102good_search1659950446102good_list1659950457103home1659950541103good_detail1659950552103cart1659950563
规定若同一用户的相邻两次访问记录时间间隔小于60s，则认为两次浏览记录属于同一会话。现有如下需求，为属于同一会话的访问记录增加一个相同的会话id字段，会话id格式为"user_id-number"，其中number从1开始，用于区分同一用户的不同会话，期望结果如下：
user_id
<int>
(用户id)page_id
<string>
(页面id)view_timestamp
<bigint>
(浏览时间戳)session_id
<string>
(会话id)100home1659950435100-1100good_search1659950446100-1100good_list1659950457100-1100home1659950541100-2100good_detail1659950552100-2100cart1659950563100-2101home1659950435101-1101good_search1659950446101-1101good_list1659950457101-1101home1659950541101-2101good_detail1659950552101-2101cart1659950563101-2102home1659950435102-1102good_search1659950446102-1102good_list1659950457102-1103home1659950541103-1103good_detail1659950552103-1

select user_id,
       page_id,
       view_timestamp,
       concat(
               user_id,
               '-',
               sum(flag) over (
                   partition by
                       user_id
                   order by
                       view_timestamp
                   )
           ) session_id
from (select *,
             `if`(view_timestamp - lag < 60, 0, 1) flag
      from (select *,
                   lag(view_timestamp, 1, 0) over (
                       partition by
                           user_id
                       order by
                           view_timestamp
                       ) lag
            from page_view_events) t1) t2;

43、间断连续登录用户问题

现有各用户的登录记录表（login_events）如下，表中每行数据表达的信息是一个用户何时登录了平台。
user_idlogin_datetime1002021-12-01 19:00:001002021-12-01 19:30:001002021-12-02 21:01:00
现要求统计各用户最长的连续登录天数，间断一天也算作连续，例如：一个用户在1,3,5,6登录，则视为连续6天登录。期望结果如下：
user_id
<int>
(用户id)max_day_count
<int>
(最大连续天数)10031016102310431051

select user_id,
       max(datediff) max_day_count
from (select user_id,
             sum,
             datediff(max(login_date), min(login_date)) + 1 datediff
      from (select *,
                   sum(flag) over (
                       partition by
                           user_id
                       order by
                           login_date
                       ) sum
            from (select *,
                         `if`(datediff(login_date, laglogin_date) > 2, 1, 0) flag
                  from (select *,
                               lag(login_date, 1, '1970-01-01') over (
                                   partition by
                                       user_id
                                   order by
                                       login_date
                                   ) laglogin_date
                        from (select user_id,
                                     date_format(login_datetime, 'yyyy-MM-dd') login_date
                              from login_events
                              group by user_id,
                                       date_format(login_datetime, 'yyyy-MM-dd')) t1) t2) t3) t4
      group by user_id,
               sum) t5
group by user_id;

44、日期交叉问题

现有各品牌优惠周期表（promotion_info）如下，其记录了每个品牌的每个优惠活动的周期，其中同一品牌的不同优惠活动的周期可能会有交叉。
promotion_idbrandstart_dateend_date1oppo2021-06-052021-06-092oppo2021-06-112021-06-213vivo2021-06-052021-06-15
现要求统计每个品牌的优惠总天数，若某个品牌在同一天有多个优惠活动，则只按一天计算。期望结果如下：
brand
<string>
(品牌)promotion_day_count
<int>
(优惠天数)vivo17oppo16redmi22huawei22

select brand,
       sum(day_count) promotion_day_count
from (select *,
             case
                 when start_date > maxend_date then datediff(end_date, start_date) + 1
                 when end_date > maxend_date then datediff(end_date, maxend_date)
                 else 0
                 end day_count
      from (select *,
                   nvl(
                           max(end_date) over (
                               partition by
                                   brand
                               order by
                                   start_date rows between unbounded preceding
                                   and 1 preceding
                               ),
                           '1970-01-01'
                       ) maxend_date
            from promotion_info) t1) t2
group by brand;

45、复购率问题

现有电商订单表（order_detail）如下。
order_id
(订单id)user_id
(用户id)product_id
(商品id)price
（售价）cnt
（数量）order_date
（下单时间）111500012022-01-01213550012022-01-023173522022-02-01422380032022-03-03
注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率

此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数

近90天指包含最大日期（以订单详情表(order_detail)中最后的日期）在内的近90天。结果中复购率保留2位小数，并按复购率倒序、商品ID升序排序。

期望结果如下：
product_id
<int>
(商品id)crp
<decimal(16,2)>
(复购率)31.0091.0080.5050.3370.2510.0020.0060.00

select product_id,
       cast(count(`if`(user_count >= 2, 1, null)) / count(1) as decimal(16, 2)) cpr
from (select product_id,
             user_id,
             count(1) user_count
      from (select *,
                   max(order_date) over () max_order_date
            from order_detail) t1
      where order_date >= date_sub(max_order_date, 90)
      group by product_id, user_id) t2
group by product_id
order by cpr desc, product_id;

46、出勤率问题

现有用户出勤表（user_login）如下。
user_id
(用户id)course_id
(课程id)login_in
（登录时间）login_out
（登出时间）112022-06-02 09:08:242022-06-02 10:09:36112022-06-02 11:07:242022-06-02 11:44:21122022-06-02 13:50:242022-06-02 14:21:50222022-06-02 13:50:102022-06-02 15:30:20
课程报名表（course_apply）如下。
course_id
(课程id)course_name
(课程名称)user_id
(用户id)1java[1,2,3,4,5,6]2大数据[1,2,3,6]3前端[2,3,4,5]
注：出勤率指用户看直播时间超过40分钟，求出每个课程的出勤率（结果保留两位小数）。

期望结果如下：
course_id
<int>
(课程id)adr
<decimal(16,2)>
(出勤率)10.3320.5030.25

select t3.course_id,
       cast(user_count / size(ca.user_id) as decimal(16, 2)) adr
from (select course_id,
             count(1) user_count
      from (select course_id,
                   user_id,
                   sum(time1) sum_time
            from (select user_id, course_id, unix_timestamp(login_out) - unix_timestamp(login_in) time1
                  from user_login) t1
            group by course_id, user_id) t2
      where sum_time > 40 * 60
      group by course_id) t3
         join course_apply ca
              on ca.course_id = t3.course_id;

47、打车问题

现有用户下单表（get_car_record）如下。
uid
(用户id)city
(城市)event_time
（下单时间）end_time
(结束时间:取消或者接单)order_id
(订单id)107北京2021-09-20 11:00:002021-09-20 11:00:309017108北京2021-09-20 21:00:002021-09-20 21:00:409008108北京2021-09-20 18:59:302021-09-20 19:01:009018102北京2021-09-21 08:59:002021-09-21 09:01:009002
司机订单信息表（get_car_order）如下。
order_id
(课程id)uid
(用户id)driver_id
(用户id)order_time
(接单时间)start_time
(开始时间)finish_time
(结束时间)fare
(费用)grade
(评分)90171072132021-09-20 11:00:302021-09-20 11:02:102021-09-20 11:31:0038590081082042021-09-20 21:00:402021-09-20 21:03:002021-09-20 21:31:0038490181082142021-09-20 19:01:002021-09-20 19:04:502021-09-20 19:21:00385
统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。全部以event_time-开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留2位小数，平均调度时间仅计算完成了的订单，结果按叫车量升序排序。

注：不同时段定义：早高峰 [07:00:00 , 09:00:00)、工作时间 [09:00:00 , 17:00:00）、晚高峰 [17:00:00 ,20:00:00）、休息时间 [20:00:00 , 07:00:00） 时间区间左闭右开（即7:00:00算作早高峰，而9:00:00不算做早高峰）

从开始打车到司机接单为等待接单时间，从司机接单到上车为调度时间

期望结果如下：
period
<string>
(时段)get_car_num
<int>
(叫车量)wait_time
<decimal(16,2)>
(等待时长)dispatch_time
<decimal(16,2)>
(调度时长)工作时间10.501.67休息时间10.672.33晚高峰32.067.28早高峰42.218.00

select period,
       count(1)                                                  get_car_num,
       cast(avg(end_time - event_time) / 60 as decimal(16, 2))   wait_time,
       cast(avg(start_time - order_time) / 60 as decimal(16, 2)) dispatch_time
from (select unix_timestamp(event_time) event_time,
             unix_timestamp(end_time)   end_time,
             unix_timestamp(order_time) order_time,
             unix_timestamp(start_time) start_time,
             case
                 when hour(event_time) between 7 and 8 then '早高峰'
                 when hour(event_time) between 9 and 16 then '工作时间'
                 when hour(event_time) between 17 and 19 then '晚高峰'
                 else '休息时间'
                 end                    period
      from get_car_record gcr
               left join get_car_order gco
                         on gcr.order_id = gco.order_id
      where `dayofweek`(event_time) between 2 and 6) t1
group by period;

48、排列问题

现有球队表（team）如下。
team_name
(球队名称)湖人骑士灰熊勇士
拿到所有球队比赛的组合 每个队只比一次

期望结果如下：
team_name_1
<string>
(队名)team_name_2
<string>
(队名)勇士湖人湖人骑士灰熊骑士勇士骑士湖人灰熊勇士灰熊

select t1.team_name team_name_1, t2.team_name team_name_2
from team t1
         join team t2
where t1.team_name < t2.team_name;

49、视频热度问题

现有用户视频表（user_video_log）如下。
uid
(球队名称)video_id
(视频id)start_time
(开始时间)end_time
(结束时间)if_like
(是否点赞)if_retweet
(是否喜欢)comment_id
(评论id)10120012021-09-24 10:00:002021-09-24 10:00:2010null10520022021-09-25 11:00:002021-09-25 11:00:3001null10220022021-09-25 11:00:002021-09-25 11:00:3011null10120022021-09-26 11:00:002021-09-26 11:00:3001null
视频信息表(video_info) 如下：
video_id
(视频id)author
(作者id)tag
(标签)duration
(视频时长)2001901旅游302002901旅游602003902影视902004902美女90
找出近一个月发布的视频中热度最高的top3视频。

注：热度=(a视频完播率+b点赞数+c评论数+d转发数)*新鲜度；

新鲜度=1/(最近无播放天数+1)；

当前配置的参数a,b,c,d分别为100、5、3、2。

最近播放日期以 end_time-结束观看时间 为准，假设为T，则最近一个月按 [T-29, T] 闭区间统计。

当天日期使用视频中最大的end_time

结果中热度保留为整数，并按热度降序排序。

期望结果如下：
video_id
<int>
(视频id)heat
<decimal(16,2)>
(热度)200280.36200120.33

select video_id,
       cast(ceil((100 * wb + 5 * dz + 3 * pl + 2 * zf) * 1) as decimal(16, 1)) heat
from (select video_id,
             sum(wanbo) / count(1) wb,
             sum(if_like)          dz,
             count(comment_id)     pl,
             sum(if_retweet)       zf,
             min(datediff_time)    zj
      from (select uvl.video_id,
                   if_like,
                   comment_id,
                   if_retweet,
                   datediff(max(end_time) over (), end_time)                                     datediff_time,
                   `if`(unix_timestamp(end_time) - unix_timestamp(start_time) >= duration, 1, 0) wanbo
            from user_video_log uvl
                     join video_info vi
                          on uvl.video_id = vi.video_id) t1
      group by video_id) t2;

50、员工在职人数问题

现有用户表（emp）如下。
id
(员工id)en_dt
(入职日期)start_time
(离职日期)10012020-01-02null10022020-01-022020-03-0510032020-02-022020-02-1510042020-02-122020-03-08
日历表(cal) 如下：
dt
(日期)2020-01-012020-01-022020-01-032020-01-04
统计2020年每个月实际在职员工数量(只统计2020-03-31之前)，如果1个月在职天数只有1天，数量计算方式：1/当月天数。

如果一个月只有一天的话，只算30分之1个人

期望结果如下：
mnt
<int>
(月份)ps
<decimal(16,2)>
(在职人数)11.9423.6232.23

with t1 as (select id,
                   en_dt,
                   nvl(le_dt, '2020-03-31') le_dt,
                   month(en_dt) + pos       mon
            from emp
                     lateral view posexplode(split(repeat('a', month(nvl(le_dt, '2020-03-31')) - month(en_dt)),
                                                   'a')) tbl as pos, val),
     t2 as (select month(dt) mon,
                   max(dt)   max_date,
                   min(dt)   min_date
            from cal
            group by month(dt))
select mon                              mth,
       cast(sum(zai) as decimal(16, 2)) ps
from (select t1.mon,
             (datediff(`if`(le_dt > max_date, max_date, le_dt), `if`(en_dt > min_date, en_dt, min_date)) + 1) /
             (datediff(max_date, min_date) + 1) zai
      from t1
               join t2
                    on t1.mon = t2.mon) t3
group by mon;