1. 题目描述
背景
某音乐流媒体平台的内容运营团队在做独立音乐人新歌发布效果分析。每当一首新歌在某位歌手的主页首发上线，团队希望分析：首发当日完整听完该新歌的听众（"首发完听用户"），在接下来的 D+1、D+3、D+7 日，是否还在该歌手的任意歌曲下继续产生完整收听（即再次回流听这位歌手）。同时按听众在首发日的收听时段做分层，产出一张歌手 × 时段 × 回流日的矩阵。
表 1：t_song（歌曲表）
- song_id：BIGINT，歌曲 ID
- artist_id：BIGINT，歌手 ID
- song_name：VARCHAR(64)，歌曲名
- duration_sec：INT，歌曲总时长（秒）
- release_time：DATETIME，首发上线时间（2025 年内，含时分秒；每首歌一个）
表 2：t_play（收听流水表）
- play_id：BIGINT，收听记录 ID
- user_id：BIGINT，用户 ID
- song_id：BIGINT，歌曲 ID（关联 t_song.song_id）
- play_start：DATETIME，开始收听时间
- play_sec：INT，实际收听秒数（可能小于 duration_sec）
2. 问题
第一步：识别"首发完听用户"
对每首新歌，首发日 =DATE(release_time)。定位每个歌手首发日当天收听了该歌手该首新歌，且至少有一条 play_sec >= duration_sec（即完整听完一次）的用户。把这些用户记为该歌手的"首发完听用户"。同一用户若在多首新歌的首发日都完听，可重复计入不同歌手的基数。
第二步：按首发日收听时段分层（对每个"首发完听用户 × 歌手"组合单独判定）
取该用户当天对该歌手该新歌的最早一条 play_start，按小时归入：
- morning：HOUR(play_start) ∈ [6, 11]（即 06:00:00–11:59:59）
- afternoon：HOUR(play_start) ∈ [12, 17]
- night：其他所有时间（[18, 23]与[0, 5]都算 night）
第三步：回流判定
回流 = 该用户在 D+N 当天（日历日） 对同一歌手的任意歌曲产生至少一次完整收听（play_sec >= duration_sec）。
- D+1 =DATE_ADD(release_date, INTERVAL 1 DAY)
- D+3 =DATE_ADD(release_date, INTERVAL 3 DAY)
- D+7 =DATE_ADD(release_date, INTERVAL 7 DAY)
- 若同一歌手同日有多首新歌首发（不同 song_id），以各自的 release_date 分别判定用户回流；同一用户对同一歌手可能被多首新歌各自计入基数（允许重复）。
输出字段：
- artist_id（歌手 ID）
- time_slot（时段：morning / afternoon / night）
- base_user_cnt（该歌手该时段的首发完听用户基数，即"用户 × 新歌"组合数）
- d1_rate（D+1 回流率，四舍五入保留 2 位小数）
- d3_rate（D+3 回流率，同上）
- d7_rate（D+7 回流率，同上）
只输出base_user_cnt > 0的行；无需输出空组合。
排序规则（严格按此顺序）：
- d7_rate降序
- base_user_cnt降序
- artist_id升序
- time_slot按固定顺序 morning → afternoon → night（不是字典序）
3. 示例数据表
t_song
| song_id | artist_id | song_name | duration_sec | release_time |
|---|---|---|---|---|
| 1001 | 501 | Midnight Drive | 210 | 2025-04-10 00:00:00 |
| 1002 | 501 | Summer Light | 180 | 2025-04-15 00:00:00 |
| 1003 | 502 | Quiet Street | 240 | 2025-04-10 00:00:00 |
t_play
| play_id | user_id | song_id | play_start | play_sec |
|---|---|---|---|---|
| 1 | 9001 | 1001 | 2025-04-10 08:15:00 | 210 |
| 2 | 9001 | 1002 | 2025-04-11 09:00:00 | 180 |
| 3 | 9001 | 1001 | 2025-04-13 22:00:00 | 210 |
| 4 | 9002 | 1001 | 2025-04-10 14:00:00 | 210 |
| 5 | 9002 | 1001 | 2025-04-10 14:40:00 | 100 |
| 6 | 9002 | 1001 | 2025-04-17 15:00:00 | 210 |
| 7 | 9003 | 1001 | 2025-04-10 23:30:00 | 209 |
| 8 | 9004 | 1001 | 2025-04-10 20:00:00 | 210 |
| 9 | 9004 | 1001 | 2025-04-11 10:00:00 | 150 |
| 10 | 9001 | 1002 | 2025-04-15 10:00:00 | 180 |
| 11 | 9001 | 1001 | 2025-04-16 10:00:00 | 210 |
| 12 | 9001 | 1001 | 2025-04-18 10:00:00 | 210 |
| 13 | 9001 | 1001 | 2025-04-22 10:00:00 | 210 |
| 14 | 9005 | 1003 | 2025-04-10 07:00:00 | 240 |
| 15 | 9005 | 1003 | 2025-04-17 07:00:00 | 240 |
4. 示例数据查询结果表
推导
歌手 501 共有两次新歌首发（1001 日首发于 04-10，1002 首发于 04-15），"首发完听用户"组合（user × song）：
- (9001, 1001)：首发日完听 at 08:15（morning）；D+1 04-11 听 1002 完听 ✓；D+3 04-13 听 1001 完听 ✓；D+7 04-17 无 501 完听记录 ✗
- (9002, 1001)：首发日完听 at 14:00（afternoon）；D+1 04-11 ✗；D+3 04-13 ✗；D+7 04-17 听 1001 完听 ✓
- (9003, 1001)：首发日未完听（209<210），不进基数
- (9004, 1001)：首发日完听 at 20:00（night）；D+1 04-11 play_sec=150<210 ✗；D+3/D+7 无 ✗
- (9001, 1002)：首发日 04-15 完听 at 10:00（morning）；D+1 04-16 听 1001 完听 ✓；D+3 04-18 听 1001 完听 ✓；D+7 04-22 听 1001 完听 ✓
歌手 501 分组：
- morning：(9001,1001)、(9001,1002) → base=2；D+1=1.00；D+3=1.00；D+7=0.50
- afternoon：(9002,1001) → base=1；D+1=0.00；D+3=0.00；D+7=1.00
- night：(9004,1001) → base=1；D+1=0.00；D+3=0.00；D+7=0.00
歌手 502：
- morning：(9005,1003) → base=1；D+1=0.00；D+3=0.00；D+7=1.00
按严格排序规则（d7 降序 → base 降序 → artist_id 升序 → slot 固定序）：
| artist_id | time_slot | base_user_cnt | d1_rate | d3_rate | d7_rate |
|---|---|---|---|---|---|
| 501 | afternoon | 1 | 0.00 | 0.00 | 1.00 |
| 502 | morning | 1 | 0.00 | 0.00 | 1.00 |
| 501 | morning | 2 | 1.00 | 1.00 | 0.50 |
| 501 | night | 1 | 0.00 | 0.00 |
0.00 |
