sql - 使用另一行数据的 SELECT 查询

Question

我有一个具有以下结构的表 issue:

+----+---------+-------------+------------+
| id | project | new_status  | updated_at |
+----+---------+-------------+------------+
| 1  | 1       | New         | 12:41:18   |
| 1  | 1       | In progress | 12:47:43   |
| 1  | 1       | Resolved    | 17:05:29   |
+----+---------+-------------+------------+

我需要实现一个查询，返回特定项目的每个问题在每个状态下花费的时间，类似这样:

+----+---------+-------------+------------+
| id | project | new_status  | time_diff  |
+----+---------+-------------+------------+
| 1  | 1       | New         | 00:06:25   |
| 1  | 1       | In progress | 04:17:46   |
+----+---------+-------------+------------+

我怎样才能得到这个？最好不要使用特殊的具体数据库功能，即只使用纯 SQL。但如果这很重要 - 我正在使用 PostgreSQL。

Answer 1

我即时编写此查询，因此未对其进行测试:

SELECT id, project, new_status, (updated_at - nextUpdate) AS time_diff
                           --or CAST((updated_at - nextUpdate) AS time) AS time_diff
FROM (
    SELECT *, 
        LEAD(updated_at) OVER (PARTITION BY project ORDER BY updated_at) AS nextUpdate
    FROM yourTable) dt
WHERE nextUpdate IS NOT NULL;

一个相关的答案是this .