基于间断数据按日期和状态统计对象数量的技术问询

问题背景 #

需要按日期统计处于特定状态的对象（如设备、任务、账单等）数量，但状态记录是间断的——仅在状态变更时才有记录。直接用GROUP BY无法得到正确结果，因为某一天的状态数量需要继承历史未变更的对象状态。例如3月2日仅一条新记录，但正确统计需要包含3月1日未变更的对象状态。

示例数据 #

以下是最小化复现问题的SQL代码：

SET NOCOUNT ON
-->>-- minimum problem to solve  -- count by status for a specific day
IF OBJECT_ID('tempdb..#d') IS NOT NULL DROP TABLE #d
CREATE TABLE #d (ndx SMALLINT IDENTITY(1,1), id TINYINT, dt DATE, status CHAR(10) )

INSERT INTO #d (id, dt, status)
VALUES
  ( 1, '20230301' , 'on'        )
, ( 3, '20230301' , 'off'       )
, ( 2, '20230302' , 'on'        )
, ( 3, '20230303' , 'off'       )
, ( 3, '20230305' , 'on'        )
, ( 1, '20230308' , 'off'       )
, ( 2, '20230308' , 'off'       )
, ( 1, '20230310' , 'off'       )
, ( 2, '20230311' , 'off'       )
, ( 1, '20230312' , 'off'       )
, ( 3, '20230312' , 'off'       )
, ( 2, '20230313' , 'on'        )
, ( 1, '20230314' , 'on'        )
, ( 3, '20230314' , 'off'       )
, ( 3, '20230316' , 'off'       )
, ( 2, '20230320' , 'on'        )
, ( 1, '20230321' , 'off'       )

SELECT * FROM #d d ORDER BY id, dt

IF OBJECT_ID('tempdb..#c') IS NOT NULL DROP TABLE #c
CREATE TABLE #c ( calendardt DATE )
INSERT INTO #c(calendardt)
VALUES
  ('2023-03-01 '), ('2023-03-02 '), ('2023-03-03 '), ('2023-03-04 '), ('2023-03-05 ')
, ('2023-03-06 '), ('2023-03-07 '), ('2023-03-08 '), ('2023-03-09 '), ('2023-03-10 ')
, ('2023-03-11 '), ('2023-03-12 '), ('2023-03-13 '), ('2023-03-14 '), ('2023-03-15 ')
, ('2023-03-16 '), ('2023-03-17 '), ('2023-03-18 '), ('2023-03-19 '), ('2023-03-20 ')
, ('2023-03-21 '), ('2023-03-22 '), ('2023-03-23 '), ('2023-03-24 '), ('2023-03-25 ')

SELECT * FROM #c UNION ALL SELECT * FROM #c ORDER BY calendardt

SELECT *
FROM #c c
LEFT JOIN #d d ON d.dt = c.calendardt
ORDER BY c.calendardt, d.id

预期结果 #

calendardt  [status]    [count]
2023-03-01  on          1
2023-03-01  off         1
2023-03-02  on          2
2023-03-02  off         1
2023-03-03  on          2
2023-03-03  off         1
2023-03-04  on          2
2023-03-04  off         1
2023-03-05  on          3
2023-03-05  off         0
2023-03-06  on          3
2023-03-06  off         0
2023-03-07  on          3
2023-03-07  off         0
2023-03-08  on          1
2023-03-08  off         2
2023-03-09  on          1
2023-03-09  off         2
2023-03-10  on          1
2023-03-10  off         2
2023-03-11  on          1
2023-03-11  off         2
2023-03-12  on          1
2023-03-12  off         2
2023-03-13  on          0
2023-03-13  off         3
2023-03-14  on          0
2023-03-14  off         3
2023-03-15  on          0
2023-03-15  off         3
2023-03-16  on          0
2023-03-16  off         3

解决方案 #

核心思路是先为每个对象的每条状态记录计算其有效结束日期（即下一次状态变更的前一天），然后将这些状态有效期与日历表关联，最后按日期和状态统计数量。

完整SQL代码 #

SET NOCOUNT ON;

-- 第一步：为每条状态记录计算有效结束日期基准
WITH StatusPeriods AS (
    SELECT 
        id,
        dt AS start_dt,
        status,
        -- 获取下一次状态变更日期，若无则用日历表最大日期
        LEAD(dt, 1, (SELECT MAX(calendardt) FROM #c)) OVER (PARTITION BY id ORDER BY dt) AS next_status_dt
    FROM #d
),
-- 第二步：生成状态的有效区间（start_dt 到 next_status_dt - 1）
StatusRanges AS (
    SELECT 
        id,
        start_dt,
        DATEADD(DAY, -1, next_status_dt) AS end_dt,
        status
    FROM StatusPeriods
),
-- 第三步：关联日历表与状态区间，匹配每个日期对应的对象状态
DailyStatus AS (
    SELECT 
        c.calendardt,
        sr.status
    FROM #c c
    CROSS JOIN (SELECT DISTINCT id FROM #d) obj
    LEFT JOIN StatusRanges sr 
        ON obj.id = sr.id 
        AND c.calendardt BETWEEN sr.start_dt AND sr.end_dt
    -- 筛选到预期结果的日期范围（可按需调整）
    WHERE c.calendardt <= '2023-03-16'
)
-- 第四步：按日期和状态统计数量，确保所有状态都显示
SELECT 
    calendardt,
    s.status,
    COUNT(CASE WHEN ds.status = s.status THEN 1 END) AS [count]
FROM DailyStatus ds
CROSS JOIN (SELECT DISTINCT status FROM #d) s
GROUP BY calendardt, s.status
ORDER BY calendardt, s.status;

代码说明 #

• StatusPeriods：用LEAD()窗口函数获取每个对象的下一次状态变更时间，确定当前状态的截止基准。
• StatusRanges：将截止基准转换为当前状态的最后有效日期（下一次变更前一天）。
• DailyStatus：将每个对象与日历表交叉关联，匹配该日期下对象的有效状态。
• 最后通过交叉所有状态值，确保即使某状态在当天无对应对象，也能显示数量为0的记录。

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