如何提取同一number和version下状态变更各阶段的最新记录？

核心思路是给连续相同的status标记同一个组ID，以此区分非连续的相同状态，再按组聚合保留最新记录。具体步骤如下：

步骤1：排序并标记状态变更点 #

先按number、version分组，再按date降序排序，用LAG()窗口函数获取当前记录的上一条status，如果和当前status不同，标记为新组的起始点。

步骤2：生成连续状态组ID #

通过SUM()窗口函数对变更点进行累计求和，为每个连续相同的status组分配唯一ID。

步骤3：按组聚合保留最新记录 #

最后按number、version、组ID分组，取每组中date最大的记录（即该状态组的最新记录）。

示例SQL代码（以PostgreSQL/MySQL 8.0+为例） #

WITH ranked_data AS (
    SELECT 
        id,
        number,
        version,
        status,
        date,
        -- 标记状态变更：当前status与上一条不同则为1，否则为0
        CASE WHEN LAG(status) OVER (PARTITION BY number, version ORDER BY date DESC) != status THEN 1 ELSE 0 END AS status_change
    FROM your_table
),
grouped_data AS (
    SELECT 
        *,
        -- 累计求和生成连续状态组ID
        SUM(status_change) OVER (PARTITION BY number, version ORDER BY date DESC) AS status_group_id
    FROM ranked_data
)
SELECT 
    number,
    version,
    status,
    MAX(date) AS latest_date,
    -- 若需要保留对应id，可结合MAX(date)关联获取
    FIRST_VALUE(id) OVER (PARTITION BY number, version, status_group_id ORDER BY date DESC) AS latest_id
FROM grouped_data
GROUP BY number, version, status_group_id, status
ORDER BY number, version, latest_date DESC;

关键说明 #

• LAG(status)：用于获取同number、version下，上一条（按date降序）的status值，以此判断是否发生状态变更。
• SUM(status_change) OVER (...)：通过累计变更标记，将连续相同的status归为同一组，彻底解决GROUP BY会合并非连续相同状态的问题。
• FIRST_VALUE(id)：确保获取该状态组最新日期对应的原始id，若不需要id可省略。

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