基于Tabibitosan方法统计Oracle SQL任务最近连续脱轨周数问题

嘿，我来帮你搞定这个问题！你选Tabibitosan方法的思路完全正确，但当前代码的问题是没有聚焦在「最新的连续脱轨组」上，反而把所有历史上的脱轨周数都累加了。咱们一步步修正：

问题根源分析 #

你现有的代码会为每个ID的所有脱轨段（比如ID=1的WEEK1和WEEK3-5是两个独立的脱轨段）分别分组，最后SUM的时候会把所有段的脱轨周数加起来，这就是为什么ID=1返回了4而不是3。我们需要的是只统计从最新周开始往前数的连续脱轨周数，直到遇到第一个「在轨（Y）」的记录为止。

修正方案一：优化Tabibitosan分组 #

我们先通过分组识别出每个ID的所有连续段，然后只保留最新的那个段，统计其脱轨周数：

WITH HIST_TBL AS (
    SELECT 
        ID,
        CASE ON_TRACK WHEN 'N' THEN 1 ELSE 0 END AS OFF_TRACK,
        WEEK
    FROM SOURCE_HISTORICAL_TBL
),
GROUPED AS (
    SELECT 
        ID,
        OFF_TRACK,
        WEEK,
        -- 用Tabibitosan方法生成分组ID：相同连续状态的记录会分到同一组
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY WEEK DESC) 
        - ROW_NUMBER() OVER (PARTITION BY ID, OFF_TRACK ORDER BY WEEK DESC) AS GRP
    FROM HIST_TBL
),
-- 找到每个ID最新的分组（对应最近一周的记录所属的组）
LATEST_SEGMENT AS (
    SELECT ID, GRP, OFF_TRACK
    FROM GROUPED
    WHERE (ID, WEEK) IN (SELECT ID, MAX(WEEK) FROM SOURCE_HISTORICAL_TBL GROUP BY ID)
)
-- 统计最新分组的脱轨周数，如果最新组是在轨状态则返回0
SELECT 
    g.ID,
    CASE WHEN ls.OFF_TRACK = 1 THEN COUNT(g.OFF_TRACK) ELSE 0 END AS WKS_OFF_TRACK
FROM GROUPED g
JOIN LATEST_SEGMENT ls ON g.ID = ls.ID AND g.GRP = ls.GRP
GROUP BY g.ID, ls.OFF_TRACK
ORDER BY g.ID;

修正方案二：更简洁的累计分组法 #

这个方法更直观，通过累计计数标记连续段，直接锁定最新的脱轨段：

WITH ORDERED_DATA AS (
    SELECT 
        ID,
        ON_TRACK,
        WEEK,
        -- 从最新周开始，每遇到一次在轨（Y）就增加分组ID，最新的连续段分组ID为0
        SUM(CASE WHEN ON_TRACK = 'Y' THEN 1 ELSE 0 END) OVER (
            PARTITION BY ID 
            ORDER BY WEEK DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS SEGMENT_ID
    FROM SOURCE_HISTORICAL_TBL
)
SELECT 
    ID,
    -- 只统计最新段（SEGMENT_ID=0）中的脱轨周数
    COUNT(CASE WHEN ON_TRACK = 'N' AND SEGMENT_ID = 0 THEN 1 END) AS WKS_OFF_TRACK
FROM ORDERED_DATA
GROUP BY ID
ORDER BY ID;

验证结果 #

运行上面任意一段代码，都会得到你期望的结果：

ID WKS_OFF_TRACK
1  3
2  1
3  0

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