如何查询产品ID对应的开单时间及关联关单时间

现有数据表 #

ID  产品ID   状态    时间
---------------------------------------
 1      1       开启    27/01/2025 6:30
 2      1       关闭    27/01/2025 7:30
 3      2       开启    27/01/2025 7:35
 4      3       开启    27/01/2025 7:45
 5      4       开启    27/01/2025 8:45
 6      1       开启    27/01/2025 8:55
 7      2       关闭    28/01/2025 7:30
 8      4       关闭    28/01/2025 8:30
 9      1       关闭    28/01/2025 8:30
10      1       开启    28/01/2025 9:30
11      3       关闭    28/01/2025 9:35
12      4       开启    28/01/2025 9:45

需求 #

查询指定产品ID对应的开启时间及其关联的关闭时间，期望输出格式如下：

产品ID   开启时间            关闭时间
-----------------------------------------------
   1        27/01/2025 6:30     27/01/2025 7:30
   1        27/01/2025 8:55     28/01/2025 8:30
   1        28/01/2025 9:30 
   2        27/01/2025 7:35     28/01/2025 7:30
   3        27/01/2025 7:45     28/01/2025 9:35
   4        27/01/2025 8:45     28/01/2025 8:30
   4        28/01/2025 9:45 

解决方案 #

使用窗口函数LEAD()可以高效匹配每个开启记录后对应的第一条关闭记录，SQL语句如下：

SELECT
    ProductId AS 产品ID,
    Time AS 开启时间,
    CASE WHEN next_status = 'Closed' THEN next_time ELSE NULL END AS 关闭时间
FROM (
    SELECT
        ProductId,
        Status,
        Time,
        LEAD(Status) OVER (PARTITION BY ProductId ORDER BY Time) AS next_status,
        LEAD(Time) OVER (PARTITION BY ProductId ORDER BY Time) AS next_time
    FROM your_table_name  -- 替换为你的实际表名
) t
WHERE Status = 'Open'
ORDER BY 产品ID, 开启时间;

逻辑说明 #

1. 内层查询通过LEAD()函数，按产品ID分组、时间升序排序，获取每条记录的下一条记录的状态和时间；
2. 外层筛选出所有状态为Open（开启）的记录，判断下一条记录是否为Closed（关闭），是则取对应时间作为关闭时间，否则留空；
3. 最终按产品ID和开启时间排序，得到符合要求的结果。

内容的提问来源于stack exchange，提问作者Govardhana