SQL Server中基于多列规则推断状态列值的实现方法问询

嘿，我来帮你搞定这个Status列的计算需求！根据你给出的规则，咱们可以通过生成连续日期序列、关联原始数据，再结合窗口函数或OUTER APPLY来实现，下面一步步拆解：

核心思路 #

要满足你的规则，关键是补全缺失的日期，并为每个缺失日期找到最近的有效状态记录，最后按规则判断Status值：

1. 生成从表中最小日期到最大日期的所有连续日期，覆盖无数据的日期；
2. 将连续日期与原始表关联，为每个日期匹配对应数据（无数据则为NULL）；
3. 为每个无数据的日期获取最近的有效StatusCode；
4. 通过CASE表达式按规则计算Status。

具体实现代码 #

假设你的原始表名为MachineData，字段分别是[Day]（日期类型）、RunSpeed（数值型）、StatusCode（数值型）。

方法1：使用递归CTE + LAST_VALUE窗口函数 #

WITH DateRange AS (
    -- 递归生成连续日期序列
    SELECT MIN([Day]) AS DateVal
    FROM MachineData
    UNION ALL
    SELECT DATEADD(DAY, 1, DateVal)
    FROM DateRange
    WHERE DateVal < (SELECT MAX([Day]) FROM MachineData)
),
JoinedData AS (
    SELECT
        dr.DateVal AS [Day],
        md.RunSpeed,
        md.StatusCode,
        -- 获取当前日期及之前最后一个非空的StatusCode
        LAST_VALUE(md.StatusCode) OVER (
            ORDER BY dr.DateVal
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS LastValidStatusCode
    FROM DateRange dr
    LEFT JOIN MachineData md ON dr.DateVal = md.[Day]
)
-- 按规则计算Status列
SELECT
    [Day],
    RunSpeed,
    StatusCode,
    CASE
        -- 规则1：当日RunSpeed>0，Status为TRUE
        WHEN RunSpeed > 0 THEN 'TRUE'
        -- 规则2：当日无数据，取最近有效StatusCode判断
        WHEN RunSpeed IS NULL THEN 
            CASE WHEN LastValidStatusCode = 0 THEN 'TRUE' ELSE 'FALSE' END
        -- 处理RunSpeed=0的情况（如示例中13日）
        ELSE CASE WHEN StatusCode = 0 THEN 'TRUE' ELSE 'FALSE' END
    END AS Status
FROM JoinedData
ORDER BY [Day]
-- 如果日期范围超过100天，需要取消递归次数限制
OPTION (MAXRECURSION 0);

方法2：使用递归CTE + OUTER APPLY（更直观） #

如果你觉得窗口函数不太好理解，用OUTER APPLY直接查询最近的有效记录会更直观：

WITH DateRange AS (
    SELECT MIN([Day]) AS DateVal
    FROM MachineData
    UNION ALL
    SELECT DATEADD(DAY, 1, DateVal)
    FROM DateRange
    WHERE DateVal < (SELECT MAX([Day]) FROM MachineData)
)
SELECT
    dr.DateVal AS [Day],
    md.RunSpeed,
    md.StatusCode,
    CASE
        WHEN md.RunSpeed > 0 THEN 'TRUE'
        WHEN md.RunSpeed IS NULL THEN 
            CASE WHEN last_record.StatusCode = 0 THEN 'TRUE' ELSE 'FALSE' END
        ELSE CASE WHEN md.StatusCode = 0 THEN 'TRUE' ELSE 'FALSE' END
    END AS Status
FROM DateRange dr
LEFT JOIN MachineData md ON dr.DateVal = md.[Day]
-- 为每个日期获取最近的有效记录
OUTER APPLY (
    SELECT TOP 1 StatusCode
    FROM MachineData
    WHERE [Day] <= dr.DateVal
    ORDER BY [Day] DESC
) last_record
ORDER BY dr.DateVal
OPTION (MAXRECURSION 0);

代码说明 #

• DateRange CTE：用递归方式生成连续日期，确保覆盖所有需要判断的日期（包括无数据的日期）；
• LAST_VALUE/OUTER APPLY：两种方式都是为了给无数据的日期找到最近的有效StatusCode；
• CASE表达式：严格按照你给出的规则判断Status值，同时覆盖了RunSpeed=0的特殊情况（如示例中的13日）。

注意事项 #

• 如果你的[Day]字段不是日期类型，需要先转换为DATE类型，比如CAST([Day] AS DATE)；
• 递归CTE默认最大递归次数是100，若日期范围超过100天，必须加上OPTION (MAXRECURSION 0)；
• 两种方法都能完美匹配你给出的示例，比如8-Jan-14和9-Jan-14会取7日的StatusCode=0，因此Status为TRUE；13日RunSpeed=0且StatusCode=20，Status为FALSE。

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