时间戳

首页 > 解决方案 > 组中的 SQL 上一个/下一个不相等的日期时间

sql - 组中的 SQL 上一个/下一个不相等的日期时间

问题描述

我有一个由以下人员创建的表：

CREATE TABLE #table1
(
    category INT,
    event_id VARCHAR(MAX),
    event_time DATETIME
)

INSERT INTO #table1
VALUES
(1, 'event1', '2021-01-12 19:00:00'),
(1, 'event2', '2021-01-12 19:00:00'),
(2, 'event3', '2021-01-12 19:00:00'),
(2, 'event4', '2021-01-12 19:30:00'),
(2, 'event5', '2021-01-12 22:30:00'),
(2, 'event6', '2021-01-12 22:30:00'),
(1, 'event7', '2021-01-12 19:30:00'),
(1, 'event8', '2021-01-12 19:30:00'),
(2, 'event9', '2021-01-12 22:30:00'),
(1, 'event10', '2021-01-12 20:00:00')

结果是：

category    event_id    event_time
------------------------------------------------
1           event1      2021-01-12 19:00:00.000
1           event2      2021-01-12 19:00:00.000
2           event3      2021-01-12 19:00:00.000
2           event4      2021-01-12 19:30:00.000
2           event5      2021-01-12 22:30:00.000
2           event6      2021-01-12 22:30:00.000
1           event7      2021-01-12 19:30:00.000
1           event8      2021-01-12 19:30:00.000
2           event9      2021-01-12 22:30:00.000
1           event10     2021-01-12 20:00:00.000

我想添加两个新列：time_of_next_event_in_group和time_of_previous_event_in_group（我认为非常不言自明）。

我曾尝试使用LAG/来执行此操作LEAD，但当组中的前一个事件具有相同的event_time. 为清楚起见，我们需要不同的下一个/上一个事件的时间event_time。预期的结果是：

category    event_id    event_time                  time_of_previous_event_in_group time_of_next_event_in_group
-----------------------------------------------------------------------------------------------------------------------
1           event1      2021-01-12 19:00:00.000     NULL                            2021-01-12 19:30:00.000
1           event2      2021-01-12 19:00:00.000     NULL                            2021-01-12 19:30:00.000
2           event3      2021-01-12 19:00:00.000     NULL                            2021-01-12 19:30:00.000
2           event4      2021-01-12 19:30:00.000     2021-01-12 19:00:00.000         2021-01-12 22:30:00.000
2           event5      2021-01-12 22:30:00.000     2021-01-12 19:30:00.000         NULL
2           event6      2021-01-12 22:30:00.000     2021-01-12 19:30:00.000         NULL
1           event7      2021-01-12 19:30:00.000     2021-01-12 19:00:00.000         2021-01-12 20:00:00.000
1           event8      2021-01-12 19:30:00.000     2021-01-12 19:00:00.000         2021-01-12 20:00:00.000
2           event9      2021-01-12 22:30:00.000     2021-01-12 19:30:00.000         NULL
1           event10     2021-01-12 20:00:00.000     2021-01-12 19:30:00.000         NULL

标签： sqlsql-serverwindow-functions

解决方案

我认为下一个查询可能会有所帮助：

SELECT 
    t.category,
    t.event_id,
    t.event_time,
    MAX(b.event_time) AS time_of_previous_event_in_group,
    MIN(a.event_time) AS time_of_next_event_in_group
FROM table1 t
LEFT JOIN table1 b ON t.category = b.category AND t.event_time > b.event_time
LEFT JOIN table1 a ON t.category = a.category AND t.event_time < a.event_time
GROUP BY t.category, t.event_id, t.event_time
ORDER BY LEN(t.event_id), t.event_id;

在这里尝试 SQL

+==========+==========+=========================+=================================+=============================+
| category | event_id | event_time              | time_of_previous_event_in_group | time_of_next_event_in_group |
+==========+==========+=========================+=================================+=============================+
| 1        | event1   | 2021-01-12 19:00:00.000 | (null)                          | 2021-01-12 19:30:00.000     |
+----------+----------+-------------------------+---------------------------------+-----------------------------+
| 1        | event2   | 2021-01-12 19:00:00.000 | (null)                          | 2021-01-12 19:30:00.000     |
+----------+----------+-------------------------+---------------------------------+-----------------------------+
| 2        | event3   | 2021-01-12 19:00:00.000 | (null)                          | 2021-01-12 19:30:00.000     |
+----------+----------+-------------------------+---------------------------------+-----------------------------+
| 2        | event4   | 2021-01-12 19:30:00.000 | 2021-01-12 19:00:00.000         | 2021-01-12 22:30:00.000     |
+----------+----------+-------------------------+---------------------------------+-----------------------------+
| 2        | event5   | 2021-01-12 22:30:00.000 | 2021-01-12 19:30:00.000         | (null)                      |
+----------+----------+-------------------------+---------------------------------+-----------------------------+
| 2        | event6   | 2021-01-12 22:30:00.000 | 2021-01-12 19:30:00.000         | (null)                      |
+----------+----------+-------------------------+---------------------------------+-----------------------------+
| 1        | event7   | 2021-01-12 19:30:00.000 | 2021-01-12 19:00:00.000         | 2021-01-12 20:00:00.000     |
+----------+----------+-------------------------+---------------------------------+-----------------------------+
| 1        | event8   | 2021-01-12 19:30:00.000 | 2021-01-12 19:00:00.000         | 2021-01-12 20:00:00.000     |
+----------+----------+-------------------------+---------------------------------+-----------------------------+
| 2        | event9   | 2021-01-12 22:30:00.000 | 2021-01-12 19:30:00.000         | (null)                      |
+----------+----------+-------------------------+---------------------------------+-----------------------------+
| 1        | event10  | 2021-01-12 20:00:00.000 | 2021-01-12 19:30:00.000         | (null)                      |
+----------+----------+-------------------------+---------------------------------+-----------------------------+

推荐阅读