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): 视频类型

任务

找出所有“钢铁直男”用户的观看记录，并按照观看时间排序。“钢铁直男”定义为只观看“IT”或“汽车”类别的视频的用户。

考点

  1. 多表连接：连接 bilibili_t20 和 bilibili_t3 表，获取用户的观看记录和视频类型。
  2. 用户筛选：筛选出只观看“IT”或“汽车”类别的视频的用户。
  3. 聚合计算：使用聚合函数计算满足条件的用户个数。
  4. 结果排序：按观看时间排序。

inner join count group_concat

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 多表连接 支付宝
ID321 9分客户最爱去的Top3餐厅  
中等 group by inner join avg case when row_number distinct count datediff + current_date group_concat <= ntile 聚合函数 RFM模型 美团
ID322 4分及以下客户去的最后一家餐厅  
困难 group by inner join avg case when row_number distinct count datediff + current_date group_concat <= = ntile 聚合函数 窗口函数 RFM模型 美团
ID323 统计每一类RFM用户的数量  
困难 group by inner join avg case when distinct count datediff + concat current_date group_concat ntile 聚合函数 窗口函数 RFM模型 美团
ID324 找出流失风险客户（R=1且F<=2）  
中等 group by inner join avg case when distinct count datediff + concat current_date group_concat <= = ntile 聚合函数 窗口函数 RFM模型 美团

MySQL 8.0

00:00