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 (9004, 1001)：首发日完听 at 20:00（night）；D+1 04-11 play_sec=150 (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
示例1
输入
CREATE TABLE t_song (
song_id BIGINT,
artist_id BIGINT,
song_name VARCHAR(64),
duration_sec INT,
release_time DATETIME
);
CREATE TABLE t_play (
play_id BIGINT,
user_id BIGINT,
song_id BIGINT,
play_start DATETIME,
play_sec INT
);
INSERT INTO t_song VALUES
(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');
INSERT INTO t_play VALUES
(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);
输出
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
加载中...