如何用SQL的ROW_NUMBER/RANK函数获取状态变更最新行及统计次数

数据示例 #

需求1：获取每个ID最近一次状态变更为"8"的记录 #

预期结果 #

实现SQL #

WITH status_groups AS (
    SELECT 
        ID,
        Date,
        Status,
        -- 标记状态变化的位置，生成连续状态组的ID
        -- 注意：若Date为字符串类型，需转换为日期类型确保排序正确
        -- MySQL: STR_TO_DATE(Date, '%d/%m/%y')
        -- PostgreSQL: TO_DATE(Date, 'DD/MM/YY')
        -- SQL Server: CONVERT(DATE, Date, 3)
        SUM(CASE WHEN Status = LAG(Status) OVER (PARTITION BY ID ORDER BY STR_TO_DATE(Date, '%d/%m/%y')) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY ID ORDER BY STR_TO_DATE(Date, '%d/%m/%y')) AS group_id
    FROM your_table
),
status_8_groups AS (
    SELECT 
        ID,
        MIN(Date) AS first_enter_date, -- 每组进入状态"8"的初始日期
        Status,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY MIN(STR_TO_DATE(Date, '%d/%m/%y')) DESC) AS rn
    FROM status_groups
    WHERE Status = '8'
    GROUP BY ID, Status, group_id
)
SELECT ID, first_enter_date AS Date, Status
FROM status_8_groups
WHERE rn = 1;

需求2：统计每个ID进入状态"8"的次数 #

预期结果 #

实现SQL #

WITH status_groups AS (
    SELECT 
        ID,
        Status,
        -- 标记状态变化的位置，生成连续状态组的ID
        -- 注意：若Date为字符串类型，需转换为日期类型确保排序正确
        -- MySQL: STR_TO_DATE(Date, '%d/%m/%y')
        -- PostgreSQL: TO_DATE(Date, 'DD/MM/YY')
        -- SQL Server: CONVERT(DATE, Date, 3)
        SUM(CASE WHEN Status = LAG(Status) OVER (PARTITION BY ID ORDER BY STR_TO_DATE(Date, '%d/%m/%y')) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY ID ORDER BY STR_TO_DATE(Date, '%d/%m/%y')) AS group_id
    FROM your_table
)
SELECT 
    ID,
    COUNT(DISTINCT group_id) AS Loops
FROM status_groups
WHERE Status = '8'
GROUP BY ID;

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