sql - SQL从视频观看日志中计算每次观看的观看时间
问题描述
有一个表格,其中存储了如下视频观看日志的数据。
|user_id| status | time |
-------------------------------------
|user_a |start |2019-06-18 00:00:00|
|user_a |progress|2019-06-18 00:00:05|
|user_a |progress|2019-06-18 00:00:10|
|user_a |complete|2019-06-18 00:00:15|
|user_a |start |2019-06-18 00:10:00|
|user_a |complete|2019-06-18 00:10:05|
|user_b |start |2019-06-18 00:20:00|
|user_b |progress|2019-06-18 00:20:05|
|user_b |progress|2019-06-18 00:20:10|
从上表中,我想计算每个用户观看每个视频的秒数。
图片如下。
|user_id|views_num|time(second) |
|user_a |1 |15 |
|user_a |2 |5 |
|user_b |1 |10 |
每 5 秒记录一次日志。
有没有办法用sql聚合?
我正在使用presto。
解决方案
可以从下面实现预期的输出。
在同一张表上使用子查询作为列表达式
SELECT t.user AS "user_id"
,row_number() OVER (
PARTITION BY t.user ORDER BY TIME
) AS "views_num"
,EXTRACT(EPOCH FROM (COALESCE(t.complete, t.progress) - t.TIME)) AS "time(second)"
FROM (
SELECT *
,(
SELECT min(TIME)
FROM log l2
WHERE l1.user = l2.user
AND l2.STATUS = 'complete'
AND l1.TIME < l2.TIME
) complete
,(
SELECT max(TIME)
FROM log l3
WHERE l1.user = l3.user
AND l3.STATUS = 'progress'
AND l1.TIME < l3.TIME
) progress
FROM log l1
WHERE l1.STATUS = 'start'
) t
输出
| user_id | views_num | time(second) |
| ------- | --------- | ------------ |
| user_a | 1 | 15 |
| user_a | 2 | 5 |
| user_b | 1 | 10 |
在 PrestoDB 中使用 date_diff 而不是EXTRACT(EPOCH())
postgre 中使用的。该演示使用的是 postgre DB。您可以如下更改该行,它应该可以工作。
date_diff('second', COALESCE(t.complete, t.progress),t.TIME) AS "time(second)"
推荐阅读
- node.js - 更改节点支持网站前端使用的脚本文件
- python - Django - 为模板自定义颜色的字符串
- jenkins - 试图让 Jenkins 在 Github 存储库上构建时间。有没有简单的方法来实现这一目标?
- python - 数组上的广播掩码操作
- java - 将第一个“对象”复制到所有“对象”内部
- sql - 如何在 Oracle 的同一列值中对不同模式使用 REPLACE 进行更新?
- angular - AngularFirebase2,Ionic 3:通过firebase返回数据不完全工作
- c# - 如何在不引发异常的情况下使用 Selenium 'Until' 函数?
- java - Intellij IDEA 中的蓝点(圆圈)是什么意思?
- intellij-idea - 如何在 IntelliJ 中创建保护块?