sql - 格式化带分区的表

Question

在 PostgreSQL 中只获取打开和关闭事件之间的行的最佳方法是什么？

+------------+----+------------+---------------------+
|  event_id  | ID | occurrence |      datetime       |
+------------+----+------------+---------------------+
| 1003603017 | A  | owner_from | 12/16/2016 4:44:16  |
| 1003603017 | A  | owner_to   | 12/16/2016 4:44:38  |
| 1003603017 | A  | owner_from | 12/16/2016 4:44:38  |
| 1003603017 | A  | opened     | 12/16/2016 4:44:39  |
| 1003603017 | B  | owner_from | 12/16/2016 7:36:23  |
| 1003603017 | A  | owner_to   | 12/16/2016 7:36:23  |
| 1003603017 | B  | owner_to   | 12/16/2016 9:00:01  |
| 1003603017 | C  | owner_from | 12/16/2016 9:00:01  |
| 1003603017 | A  | closed     | 12/16/2016 12:00:36 |
| 1003603017 | D  | owner_from | 12/17/2016 4:25:00  |
| 1003603017 | C  | owner_to   | 12/17/2016 4:25:00  |
| 1003603017 | D  | owner_from | 12/17/2016 4:52:02  |
| 1003603017 | D  | owner_to   | 12/17/2016 4:52:02  |
| 1003603017 | D  | opened     | 12/17/2016 4:52:02  |
| 1003603017 | D  | owner_to   | 12/17/2016 8:57:00  |
| 1003603017 | E  | owner_from | 12/17/2016 8:57:00  |
| 1003603017 | D  | closed     | 12/17/2016 12:03:10 |
+------------+----+------------+---------------------+

Answer 1

使用延迟时的 ignore nulls 选项会很容易。这是另一种使用 opened 和 closed 的累积最大 datetime 的方法:

select t.*
from (select t.*,
             max(case when occurrence = 'opened' then datetime end) over (order by datetime) as mr_opened,
             max(case when occurrence = 'closed' then datetime end) over (order by datetime) as mr_closed,
      from t
     ) t
where mr_opened > mr_closed;

注意事项:

• 这不包括最终的关闭。这个要求的问题不清楚。
• 您可能希望按 event_id 进行分区。这个要求的问题不清楚。
• 在较新版本的 Postgres 中，您可以使用 filter 语法代替 case。