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。您可以按下面的方式更改行，它应该可以工作。

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

比较