如何用SQL精准查询有效变为Covered状态的首次记录？

问题 #

需要编写SQL查询，找出状态首次有效变为Covered的记录。有效情况分为两种：

• 从Available变为Covered，且后续状态再也没有切换回Available；
• 初始状态即为Covered（无前置Available状态，且后续也未回到Available）。

现有两个示例数据集：

示例数据集1 #

此数据集的目标记录是ID=12（ID=10虽变为Covered，但后续又变回Available，因此无效）

示例数据集2 #

此数据集的目标记录是ID=10

尝试的SQL语句无法正确返回第一个数据集的目标记录，请求正确解决方案：

select *
from loads_fieldupdates 
where theField = 'loadStatus'
and loadid = 261574
and (
        /* most common scenario */
        (previousFieldVal LIKE 'Available%' AND theFieldVal LIKE 'Covered%')
        OR
        /* case where load was duped so started as covered but not went back to available */
        (previousFieldVal LIKE 'Covered%' AND theFieldVal NOT LIKE 'Available%')
)
order by dateCreated desc

解决方案 #

核心逻辑是确认某个变为Covered的节点之后，再也没有出现回到Available的状态，同时锁定首次有效切换的记录。以下是通用的实现方案：

最终SQL查询 #

WITH status_track AS (
    SELECT 
        *,
        -- 标记当前记录之后是否出现过回到Available的状态
        MAX(CASE WHEN theFieldVal LIKE 'Available%' THEN 1 ELSE 0 END) OVER (
            PARTITION BY loadid 
            ORDER BY dateCreated DESC 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS has_subsequent_available
    FROM loads_fieldupdates
    WHERE theField = 'loadStatus'
      AND loadid = 261574
),
valid_covered_records AS (
    SELECT 
        *,
        -- 按时间排序标记首次有效记录
        ROW_NUMBER() OVER (
            PARTITION BY loadid 
            ORDER BY dateCreated ASC
        ) AS valid_rank
    FROM status_track
    WHERE 
        -- 第一种有效情况：从Available切到Covered且后续无Available
        (previousFieldVal LIKE 'Available%' AND theFieldVal LIKE 'Covered%' AND has_subsequent_available = 0)
        OR
        (
            -- 第二种有效情况：初始状态为Covered
            previousFieldVal LIKE 'Covered%' 
            AND has_subsequent_available = 0
            -- 额外验证没有更早的Available状态
            AND NOT EXISTS (
                SELECT 1 
                FROM loads_fieldupdates s2
                WHERE s2.loadid = status_track.loadid
                  AND s2.theField = 'loadStatus'
                  AND s2.dateCreated < status_track.dateCreated
                  AND s2.theFieldVal LIKE 'Available%'
            )
        )
)
SELECT *
FROM valid_covered_records
WHERE valid_rank = 1;

逻辑说明 #

1. status_track CTE：通过窗口函数从当前记录向后遍历，标记该loadid后续是否存在回到Available的状态；
2. valid_covered_records CTE：筛选符合两种有效规则的记录，并按时间顺序给有效记录排名；
3. 最终取排名为1的记录，就是首次有效变为Covered的目标结果。

示例验证 #

• 示例1中：ID=12的记录后续无Available状态，会被选中；ID=10后续有ID=11的Available状态，直接被排除；
• 示例2中：ID=10的前置状态是Covered，且没有更早的Available记录，后续也无Available状态，会被选中。

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