SQL Server连续区间问题：处理含NULL的ignition字段数据

嘿，我明白你现在遇到的问题——手里的数据集里ignition字段有不少NULL值，需要做连续区间的处理对吧？结合你给的样本数据，我整理了两种最常见场景的解决方案，都是SQL Server里实用的技巧：

场景1：把NULL值填充为最近的非NULL ignition值 #

这应该是最常见的需求了，就是让每个NULL值“继承”前面最近的有效ignition状态。如果你的SQL Server是2022及以上版本，直接用LAST_VALUE()加IGNORE NULLS就能搞定，非常简洁：

SELECT 
    id,
    date,
    status,
    LAST_VALUE(ignition IGNORE NULLS) OVER (
        PARTITION BY id 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS filled_ignition
FROM your_table_name;

关键点解释： #

• PARTITION BY id：确保只在同一个设备的记录里找最近值，不会跨设备混淆
• ORDER BY date：严格按时间顺序处理，保证取的是最近的历史有效状态
• IGNORE NULLS：专门跳过NULL值，直接取前面第一个非NULL的ignition值

如果你的SQL Server版本低于2022，不支持IGNORE NULLS，那用这个兼容方案也能实现：

WITH ranked_data AS (
    SELECT 
        *,
        -- 给每个非NULL的ignition打个分组标记，NULL值会继承前面的分组ID
        SUM(CASE WHEN ignition IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY id ORDER BY date
        ) AS group_id
    FROM your_table_name
)
SELECT 
    id,
    date,
    status,
    -- 取当前分组里的第一个非NULL值，也就是最近的有效状态
    MAX(ignition) OVER (PARTITION BY id, group_id) AS filled_ignition
FROM ranked_data;

场景2：统计连续的ignition状态区间（起止时间+记录数） #

如果你的需求是要找出每个连续状态的时间段，比如“Acc Off从几点到几点，有几条记录”，那可以在填充NULL的基础上再做一步分组：

WITH filled_data AS (
    -- 先完成NULL值的向前填充，这里用2022+版本的写法，低版本替换成上面的兼容CTE即可
    SELECT 
        id,
        date,
        status,
        LAST_VALUE(ignition IGNORE NULLS) OVER (
            PARTITION BY id 
            ORDER BY date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS filled_ignition
    FROM your_table_name
),
interval_markers AS (
    SELECT 
        *,
        -- 当当前状态和前一条不一样时，生成一个新的区间ID
        SUM(CASE WHEN prev_ignition = filled_ignition THEN 0 ELSE 1 END) OVER (
            PARTITION BY id ORDER BY date
        ) AS interval_id
    FROM (
        SELECT 
            *,
            LAG(filled_ignition) OVER (PARTITION BY id ORDER BY date) AS prev_ignition
        FROM filled_data
    ) t
)
SELECT 
    id,
    filled_ignition AS ignition_status,
    MIN(date) AS start_time,
    MAX(date) AS end_time,
    COUNT(*) AS record_count
FROM interval_markers
GROUP BY id, interval_id, filled_ignition
ORDER BY id, start_time;

逻辑拆解： #

1. 先通过filled_data把所有NULL值填充好
2. 再用interval_markers标记状态变化的节点——每遇到状态和前一条不同的情况，就给一个新的区间ID
3. 最后按ID和区间ID分组，就能得到每个连续状态的起止时间和记录数量

用你的样本数据测试效果 #

拿你给的几条记录来跑场景1，输出会是这样：

场景2的输出则会得到两个清晰的连续区间：

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