将最近事件关联至每日拆分数据的SQL实现方案咨询

针对你的需求，我们可以通过合并原始事件与每日初始记录，结合窗口函数实现高效的数据填充，避免游标带来的性能问题。以下是具体步骤和SQL实现：

核心思路 #

1. 合并数据集：将原始机器事件视图与你生成的每日0点空记录合并，形成完整的时间线。
2. 填充状态（STATE）：利用分组窗口函数，为每日0点记录填充其之前最近的机器状态。
3. 计算时长（DURATION）：通过LEAD函数获取下一条记录的时间，根据是否存在当日事件计算时长（到下一个事件或当日结束）。
4. 补全前后状态字段：重新计算所有记录的上一/下一状态及时间戳，确保报表的完整性。

完整SQL实现 #

WITH m_in_scope AS (
    -- 获取范围内的所有机器ID
    SELECT DISTINCT MACHINE FROM V_Machine_State_Window
),
daily_records AS (
    -- 生成每日0点空记录，排除已有0点事件的情况
    SELECT 
        d.Day AS LOCAL_DATETIME,
        m.MACHINE,
        NULL AS STATE,
        NULL AS STATE_TEXT,
        d.Day AS STATE_DATE,
        CAST('00:00' AS TIME) AS STATE_TIME
    FROM Day d
    CROSS JOIN m_in_scope m
    WHERE d.Day > DATEADD(day, -400, GETDATE())
      AND d.Day < GETDATE()
      AND NOT EXISTS (
          SELECT 1 FROM MACHINE_STATE_TABLE t
          WHERE t.MACHINE = m.MACHINE
            AND CAST(t.LOCAL_DATETIME AS DATE) = d.Day
            AND CAST(t.LOCAL_DATETIME AS TIME) = CAST('00:00' AS TIME)
      )
),
combined_data AS (
    -- 合并原始事件与每日记录，标记每日记录
    SELECT 
        LOCAL_DATETIME,
        MACHINE,
        STATE,
        STATE_TEXT,
        STATE_DATE,
        STATE_TIME,
        0 AS is_daily_record
    FROM V_Machine_State_Window
    UNION ALL
    SELECT 
        LOCAL_DATETIME,
        MACHINE,
        STATE,
        STATE_TEXT,
        STATE_DATE,
        STATE_TIME,
        1 AS is_daily_record
    FROM daily_records
),
grouped_data AS (
    -- 为每条记录分配状态组ID：非空状态开启新组，空记录继承上一组ID
    SELECT 
        *,
        SUM(CASE WHEN STATE IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY MACHINE 
            ORDER BY LOCAL_DATETIME 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS state_group_id
    FROM combined_data
),
filled_state AS (
    -- 填充每日记录的状态，并获取下一条记录时间
    SELECT 
        LOCAL_DATETIME,
        MACHINE,
        MAX(STATE) OVER (PARTITION BY MACHINE, state_group_id) AS STATE,
        MAX(STATE_TEXT) OVER (PARTITION BY MACHINE, state_group_id) AS STATE_TEXT,
        STATE_DATE,
        STATE_TIME,
        is_daily_record,
        LEAD(LOCAL_DATETIME) OVER (PARTITION BY MACHINE ORDER BY LOCAL_DATETIME) AS next_datetime
    FROM grouped_data
),
final_data AS (
    -- 计算时长及前后状态字段
    SELECT 
        LOCAL_DATETIME,
        MACHINE,
        STATE,
        -- 计算时长：每日记录需判断是否有当日事件，否则到当日结束
        DATEDIFF(SECOND, LOCAL_DATETIME, 
            CASE 
                WHEN is_daily_record = 1 AND next_datetime = DATEADD(DAY, 1, LOCAL_DATETIME) 
                    THEN DATEADD(SECOND, -1, next_datetime)
                ELSE next_datetime
            END
        ) AS DURATION,
        STATE_TEXT,
        STATE_DATE,
        STATE_TIME,
        -- 上一状态相关字段
        LAG(STATE) OVER (PARTITION BY MACHINE ORDER BY LOCAL_DATETIME) AS PREV_STATE,
        LAG(STATE_TEXT) OVER (PARTITION BY MACHINE ORDER BY LOCAL_DATETIME) AS PREV_STATE_TEXT,
        LAG(LOCAL_DATETIME) OVER (PARTITION BY MACHINE ORDER BY LOCAL_DATETIME) AS PREV_STATE_DATESTAMP,
        -- 下一状态相关字段
        LEAD(STATE) OVER (PARTITION BY MACHINE ORDER BY LOCAL_DATETIME) AS NEXT_STATE,
        LEAD(STATE_TEXT) OVER (PARTITION BY MACHINE ORDER BY LOCAL_DATETIME) AS NEXT_STATE_TEXT,
        LEAD(LOCAL_DATETIME) OVER (PARTITION BY MACHINE ORDER BY LOCAL_DATETIME) AS NEXT_STATE_DATESTAMP
    FROM filled_state
)
-- 输出最终结果，可根据需求筛选每日记录或全部记录
SELECT 
    LOCAL_DATETIME,
    MACHINE,
    STATE,
    DURATION,
    STATE_TEXT,
    STATE_DATE,
    STATE_TIME,
    PREV_STATE,
    PREV_STATE_TEXT,
    PREV_STATE_DATESTAMP,
    NEXT_STATE,
    NEXT_STATE_TEXT,
    NEXT_STATE_DATESTAMP
FROM final_data
ORDER BY MACHINE, LOCAL_DATETIME;

关键优化点 #

1. 避免重复记录：在生成每日记录时，通过NOT EXISTS排除已有0点事件的机器日期，减少冗余数据。
2. 高效状态填充：利用SUM窗口函数生成状态组ID，结合MAX窗口函数实现空记录的状态向后填充，无需游标。
3. 时长计算逻辑：通过LEAD获取下一条记录时间，自动判断是到下一个事件还是当日结束，逻辑简洁高效。
4. 索引优化建议：为MACHINE_STATE_TABLE创建索引IX_MACHINE_STATE_TABLE_MACHINE_DATETIME，包含STATE和STATE_TEXT字段，大幅提升窗口函数的执行效率：

   CREATE NONCLUSTERED INDEX IX_MACHINE_STATE_TABLE_MACHINE_DATETIME
   ON MACHINE_STATE_TABLE (MACHINE, LOCAL_DATETIME)
   INCLUDE (STATE, STATE_TEXT);
   

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