2024-11 Bilibili 中等
 需求  表结构  输出示例  参考答案  讨论  举一反三

有一个视频平台，以下是相关表的结构：

  1. bilibili_t100 表（用户登录及会员标识表）

    • usr_id (VARCHAR): 用户ID
    • v_date (DATE): 登录日期（只有日期，没有时间）
    • m_flg (INT): 会员标志（0表示非会员，1表示会员）

  2. bilibili_t20 表（用户观看记录表）

    • usr_id (VARCHAR): 用户ID
    • v_id (VARCHAR): 视频ID
    • v_tm (DATETIME): 观看时间

  3. bilibili_t3 表（视频类型标签表）

    • v_id (VARCHAR): 视频ID
    • v_nm (VARCHAR): 视频名称
    • v_typ (VARCHAR): 视频类型

任务

计算每类视频的观看人数作为分母，看过3类及以上视频的用户数作为分子，计算每类视频的“多类别观看指数”（MCVI），保留两诶小数，并按 MCVI 值降序排序。

考点

  1. 多表连接：连接 bilibili_t20 和 bilibili_t3 表，获取用户的观看记录和视频类型。
  2. 用户筛选：筛选出观看过3类及以上视频的用户。
  3. 聚合计算：计算每类视频的观看人数和观看过3类及以上视频的用户数。
  4. 结果排序：按 MCVI 值降序排序。

泛TGI概念计算 多表连接 inner join distinct count /

bilibili_t100，用户登录及会员标识表

usr_id v_date m_flg
B12 2020-01-03 0
B12 2020-01-05 1
B15 2020-01-03 0
B15 2020-01-05 1

bilibili_t20，用户观看记录表

usr_id v_id v_tm
B1038 A17 2021-02-08 16:23:55
B791 A21 2021-02-11 23:24:24
B1260 A22 2021-02-04 01:20:47
B1116 A22 2021-02-13 19:34:28
B456 A22 2021-02-23 20:09:56

bilibili_t3，视频类型标签表

v_id v_nm v_typ
A01 消失的爱人-惊悚片-本·阿弗莱克 放映厅
A02 每天建模半小时-在家就能养活自己 IT
A03 溜溜哥-我的CTR进化史 汽车
A04 首次唱跳－七夕发糖 舞蹈
A05 古典舞－九儿－无道具 舞蹈

 成为会员，解锁300道大厂数分SQL真题

ID160 红包发送量排名  
简单 inner join count 多表连接 支付宝
ID253 周杰伦的最受欢迎的专辑  
简单 group by inner join distinct count 聚合函数 多表连接 多列分组 音乐偏好分析 QQ音乐
ID254 MJ最受欢迎的歌曲  
简单 group by inner join distinct count 聚合函数 多表连接 音乐偏好分析 QQ音乐
ID257 专辑播放量统计  
简单 group by inner join distinct count 聚合函数 多表连接 音乐偏好分析 QQ音乐
ID331 使用了全场通用优惠券的订单数量  
简单 inner join distinct count = 聚合函数 口径探查 多表连接 优惠券使用场景 拼多多

MySQL 8.0

00:00