ODPS SQL 计算状态变化记录

背景&问题

处理时间序列数据时，每个key可能有多次状态变化记录的情况下，怎样计算得到每个状态持续的时间？

表结构示例：

CREATE TABLE IF NOT EXISTS tmp_key_status
(
    key     STRING
    ,status STRING
    ,date   DATETIME
)
PARTITIONED BY 
(
    ds      STRING
)
LIFECYCLE 1
;

插入示例数据

INSERT OVERWRITE TABLE tmp_key_status PARTITION (ds = '20240301') VALUES
        ("key_1","Stop",DATETIME '2024-01-01 00:00:00')
        ,("key_1","Running",DATETIME '2024-01-02 00:00:00')
        ,("key_1","Stop",DATETIME '2024-01-03 12:00:00')
        ,("key_2","Stop",DATETIME '2024-01-01 00:00:00')
        ,("key_2","Running",DATETIME '2024-01-02 12:00:00')
        ,("key_2","Stop",DATETIME '2024-01-03 00:00:00')
;

计算状态持续时间

SELECT  A.key
        ,A.status
        ,A.date AS start_time
        ,B.date AS end_time
        ,DATEDIFF(B.date,A.date,'ss') AS diff_s
FROM    (
            SELECT  key
                    ,status
                    ,date
                    ,ROW_NUMBER() OVER (PARTITION BY key ORDER BY date ) AS rn
            FROM    tmp_key_status
            WHERE   ds = '20240301'
        ) A
JOIN    (
            SELECT  key
                    ,status
                    ,date
                    ,ROW_NUMBER() OVER (PARTITION BY key ORDER BY date ) AS rn
            FROM    tmp_key_status
            WHERE   ds = '20240301'
        ) B
ON      A.key = B.key
AND     A.rn = B.rn - 1
ORDER BY A.key,A.date
;

解释：

1. 子查询A包含以下内容：

   1. 使用ROW_NUMBER()函数为每组key值指定行号（rn），按date字段顺序排列。

2. 子查询B是与子查询A一模一样的，也是选择相同的字段并为每组key值赋予行号，但它们会被用于匹配下一行的记录。

3. 这两个子查询通过JOIN操作连接在一起，具体条件如下：(核心)

   • A.key = B.key：确保连接的记录来自相同的key。
   • A.rn = B.rn - 1：这会结果在A中的每条记录与B中的"下一条"记录相连接，因为在B中要相应地查找行号比A中当前行号大1的后续行。

4. 一旦这种连接建立后，DATEDIFF(B.date, A.date, 'ss')函数被用来计算A和B表中配对的连续记录之间相隔的秒数。

5. 最后，SELECT语句选择了以下字段来构成结果集：

   • A.key和A.status：当前记录的关键字和状态。

   • A.date AS start_time：表示状态开始的时间。

   • B.date AS end_time：表示状态结束的时间（实际上是下一个状态开始的时间）。

   • diff_s：两个时间点之间相隔的秒数，即状态持续的时间。

6. ORDER BY A.key, A.date语句按照key和开始时间顺序对结果进行排序。