跳到主要内容

SQL 视频发布24小时内播放量：时间段筛选+聚合（快手面试题）

一、题目​

已知有两张表：

  • t6_video_publish：视频发布表，记录每个视频的发布信息（video_id, author_id, publish_time）
  • t6_video_play：视频播放日志表，记录每次播放事件（video_id, user_id, play_time）

请统计每个视频发布后24小时内的播放量（即播放时间在 [publish_time, publish_time + 24小时] 区间内的播放次数），按播放量降序排列。

样例数据

t6_video_publish：

+-----------+------------+----------------------+
| video_id  | author_id  |     publish_time     |
+-----------+------------+----------------------+
| V001      | a001       | 2024-06-01 08:00:00  |
| V002      | a002       | 2024-06-01 10:00:00  |
| V003      | a001       | 2024-06-02 12:00:00  |
+-----------+------------+----------------------+

t6_video_play：

+-----------+----------+----------------------+
| video_id  | user_id  |      play_time       |
+-----------+----------+----------------------+
| V001      | u001     | 2024-06-01 08:30:00  |
| V001      | u002     | 2024-06-01 09:00:00  |
| V001      | u003     | 2024-06-02 07:00:00  |
| V001      | u004     | 2024-06-02 10:00:00  |
| V002      | u001     | 2024-06-01 12:00:00  |
| V002      | u002     | 2024-06-02 08:00:00  |
| V003      | u003     | 2024-06-02 13:00:00  |
| V003      | u005     | 2024-06-02 20:00:00  |
| V003      | u001     | 2024-06-03 13:00:00  |
+-----------+----------+----------------------+

二、思路分析​

本题考察的是时间窗口内数据关联统计，属于典型的短视频数据分析场景。核心要点：

  • JOIN + 时间过滤：将视频发布表与播放日志表按 video_id 关联，然后通过 WHERE 条件过滤掉播放时间超出 [发布时间, 发布时间+24小时] 的记录。

  • 时间范围计算：题目要求"严格24小时"，需要保留时分秒，不能用 date_add（它会截断到次日零点）。正确做法是用 unix_timestamp 把时间转为秒数，加 86400 秒（=24小时）后再比较。

  • 注意边界：题目要求"发布后24小时内"，即 play_time >= publish_time AND play_time < publish_time + 24小时。通常左闭右开即可，具体看业务口径。

视频发布后的24小时播放量（又称首日播放量）是快手衡量视频冷启动效果的核心指标之一。

维度评分
题目难度⭐️
题目清晰度⭐️⭐️⭐️⭐️⭐️
业务常见度⭐️⭐️⭐️⭐️⭐️

三、逐步推导​

1. 关联两表并过滤24小时内播放记录​

将发布表和播放表通过video_id JOIN，然后过滤play_time在 [publish_time, publish_time + 1天) 范围内的记录。

执行SQL

select
    p.video_id,
    p.author_id,
    p.publish_time,
    pl.user_id,
    pl.play_time
from t6_video_publish p
inner join t6_video_play pl
    on p.video_id = pl.video_id
where unix_timestamp(pl.play_time) >= unix_timestamp(p.publish_time)
  and unix_timestamp(pl.play_time) < unix_timestamp(p.publish_time) + 86400
order by p.video_id, pl.play_time

执行结果

+-----------+------------+----------------------+----------+----------------------+
| video_id  | author_id  |     publish_time     | user_id  |      play_time       |
+-----------+------------+----------------------+----------+----------------------+
| V001      | a001       | 2024-06-01 08:00:00  | u001     | 2024-06-01 08:30:00  |
| V001      | a001       | 2024-06-01 08:00:00  | u002     | 2024-06-01 09:00:00  |
| V001      | a001       | 2024-06-01 08:00:00  | u003     | 2024-06-02 07:00:00  |
| V002      | a002       | 2024-06-01 10:00:00  | u001     | 2024-06-01 12:00:00  |
| V002      | a002       | 2024-06-01 10:00:00  | u002     | 2024-06-02 08:00:00  |
| V003      | a001       | 2024-06-02 12:00:00  | u003     | 2024-06-02 13:00:00  |
| V003      | a001       | 2024-06-02 12:00:00  | u005     | 2024-06-02 20:00:00  |
+-----------+------------+----------------------+----------+----------------------+
7 rows selected (0.677 seconds)(https://www.dwsql.com)

严格24小时窗口 [publish_time, publish_time + 24h)：V001 的 u003（06-02 07:00，距发布 23 小时）仍算在内，而 u004（06-02 10:00，距发布 26 小时）被排除；V002 的 u002（06-02 08:00，距发布 22 小时）算在内；V003 的 u001（06-03 13:00，距发布 25 小时）被排除。

2. 按视频聚合统计24小时内播放量​

在过滤后的结果上按video_id GROUP BY，计数得到每个视频的24小时内播放量。

执行SQL

select
    p.video_id,
    p.author_id,
    p.publish_time,
    count(pl.user_id) as play_cnt_24h
from t6_video_publish p
left join t6_video_play pl
    on p.video_id = pl.video_id
    and unix_timestamp(pl.play_time) >= unix_timestamp(p.publish_time)
    and unix_timestamp(pl.play_time) < unix_timestamp(p.publish_time) + 86400
group by p.video_id, p.author_id, p.publish_time
order by play_cnt_24h desc

执行结果

+-----------+------------+----------------------+---------------+
| video_id  | author_id  |     publish_time     | play_cnt_24h  |
+-----------+------------+----------------------+---------------+
| V001      | a001       | 2024-06-01 08:00:00  | 3             |
| V002      | a002       | 2024-06-01 10:00:00  | 2             |
| V003      | a001       | 2024-06-02 12:00:00  | 2             |
+-----------+------------+----------------------+---------------+
3 rows selected (0.733 seconds)(https://www.dwsql.com)

使用LEFT JOIN保证了即使某个视频在24小时内没有任何播放记录，也会输出一行（play_cnt_24h=0），不会漏掉数据。

四、常见坑点​

坑1：date_add 会截断时间，不能用于「严格24小时」

date_add(publish_time, 1) 返回日期类型，会丢掉时分秒——date_add('2024-06-01 08:00:00', 1) 结果是 2024-06-02 00:00:00，把「24小时」错算成了「次日零点」，导致 06-02 00:00~08:00 之间本该算在内的播放被漏掉。要精确到秒，必须用 unix_timestamp 转秒数再 + 86400 比较。

坑2：LEFT JOIN 与 INNER JOIN 的选择

如果用 INNER JOIN，在窗口内没有任何播放的视频会直接被过滤掉、不出现在结果里，导致统计漏掉这类视频。题目要求统计"每个视频"的播放量，应使用 LEFT JOIN 保留发布表中的全部视频，无播放时 count(pl.user_id) 得到 0。

坑3：24小时边界处理（左闭右开）

"发布后24小时内"的区间是左闭右开 [publish_time, publish_time + 24h)：play_time >= publish_time 排除发布前的脏数据，play_time < 上界 而不是 <=，恰好落在 publish_time + 24h 边界上的播放不计入。上界用 <= 会多算一条恰好踩线的记录。

五、知识点总结​

考点说明
JOIN + 时间过滤两表按 video_id 关联后，用 WHERE / ON 过滤时间窗口
unix_timestamp 秒数计算时间转秒数后 + 86400 精确表达「24小时」，避免 date_add 截断时分秒
时间窗口边界左闭右开 [start, end)，边界上的记录用 < 排除
LEFT JOIN 保留主表保证无播放的视频也输出一行，count 结果为 0

六、建表语句和数据插入​

点击展开 DDL & DML
--建表语句
create table if not exists t6_video_publish (
    video_id string comment '视频ID',
    author_id string comment '作者ID',
    publish_time string comment '发布时间'
);

create table if not exists t6_video_play (
    video_id string comment '视频ID',
    user_id string comment '播放用户ID',
    play_time string comment '播放时间'
);

--数据插入
insert into t6_video_publish(video_id, author_id, publish_time) values
('V001', 'a001', '2024-06-01 08:00:00'),
('V002', 'a002', '2024-06-01 10:00:00'),
('V003', 'a001', '2024-06-02 12:00:00');

insert into t6_video_play(video_id, user_id, play_time) values
('V001', 'u001', '2024-06-01 08:30:00'),
('V001', 'u002', '2024-06-01 09:00:00'),
('V001', 'u003', '2024-06-02 07:00:00'),
('V001', 'u004', '2024-06-02 10:00:00'),
('V002', 'u001', '2024-06-01 12:00:00'),
('V002', 'u002', '2024-06-02 08:00:00'),
('V003', 'u003', '2024-06-02 13:00:00'),
('V003', 'u005', '2024-06-02 20:00:00'),
('V003', 'u001', '2024-06-03 13:00:00');
📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

微信公众号二维码
💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题

交流微信二维码

你可能还想看

相关题目
01. 直播间最高同时在线人数02. 两人一定认识的组合数03. 视频完播率统计