SQL 从视频观看日志中计算每次观看的观看时间

Question

有一个表格，其中存储了如下视频观看日志的数据。

|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聚合？

我正在使用普雷斯托。

Answer 1

预期的输出可以从下面实现。

使用子查询作为同一表上的列表达式

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而不是postgre中使用的EXTRACT(EPOCH())。该演示使用的是 postgre DB。您可以按如下所示更改该行，它应该可以工作。

date_diff('second', COALESCE(t.complete, t.progress),t.TIME) AS "time(second)"

PrestoSQL date_diff