基于消息日志构建KPI报表：TSQL缺失值处理方案问询

针对你基于PackageFlow表构建月度KPI、解决消息缺失问题的需求，结合每日5万+的数据量，我整理了几个实用的TSQL方案，兼顾准确性和性能：

第一步：明确预期消息集合与时间范围 #

首先得确定每个包裹（Package）每天应该发送哪些消息——如果有专门的消息配置表是最好的，要是没有，咱们可以从现有业务数据中提取去重的消息类型：

-- 1. 获取每个Package对应的预期消息类型（无配置表时用这个）
WITH ExpectedMessages AS (
    SELECT DISTINCT Package, message
    FROM PackageFlow
),
-- 2. 生成月度完整日期范围（这里以2019年1月为例，可按需调整）
DateRange AS (
    -- 递归生成日期，避免依赖系统表master..spt_values
    SELECT CAST('2019-01-01' AS DATE) AS Date_Day
    UNION ALL
    SELECT DATEADD(day, 1, Date_Day)
    FROM DateRange
    WHERE Date_Day < EOMONTH('2019-01-01')
)

第二步：定位缺失的消息 #

通过全量的「Package+日期+预期消息」组合，左连接实际数据，就能找出哪些消息在某天没发送：

-- 接上上面的CTE，找出所有缺失的消息
SELECT 
    em.Package,
    dr.Date_Day,
    em.message AS Missing_Message
FROM ExpectedMessages em
CROSS JOIN DateRange dr
LEFT JOIN PackageFlow pf 
    ON em.Package = pf.Package
    AND em.message = pf.message
    AND CAST(pf.Date_time AS DATE) = dr.Date_Day
WHERE pf.Package IS NULL  -- 左连接后无匹配的就是缺失项
ORDER BY em.Package, dr.Date_Day, em.message;

第三步：计算月度核心KPI #

基于上面的预期和实际数据，咱们可以计算消息完整性率、及时率等关键指标：

消息完整性率（月度） #

WITH ExpectedMessages AS (
    SELECT DISTINCT Package, message
    FROM PackageFlow
),
DateRange AS (
    SELECT CAST('2019-01-01' AS DATE) AS Date_Day
    UNION ALL
    SELECT DATEADD(day, 1, Date_Day)
    FROM DateRange
    WHERE Date_Day < EOMONTH('2019-01-01')
),
TotalExpected AS (
    -- 计算月度内每个Package的总预期消息数
    SELECT 
        Package,
        COUNT(*) AS Total_Expected_Messages
    FROM ExpectedMessages em
    CROSS JOIN DateRange dr
    GROUP BY Package
),
ActualSent AS (
    -- 计算月度内每个Package实际发送的有效消息数（按日期+消息去重）
    SELECT 
        Package,
        COUNT(DISTINCT CAST(Date_time AS DATE) + '_' + message) AS Total_Actual_Messages
    FROM PackageFlow
    WHERE Date_time BETWEEN '2019-01-01' AND EOMONTH('2019-01-01')
    GROUP BY Package
)
SELECT 
    te.Package,
    te.Total_Expected_Messages,
    COALESCE(asn.Total_Actual_Messages, 0) AS Total_Actual_Messages,
    ROUND(CAST(COALESCE(asn.Total_Actual_Messages, 0) AS FLOAT) / te.Total_Expected_Messages * 100, 2) AS Message_Completion_Rate
FROM TotalExpected te
LEFT JOIN ActualSent asn ON te.Package = asn.Package
ORDER BY te.Package;

消息及时率（假设预期发送时间为每日01:00） #

如果有明确的预期发送时间，咱们可以对比实际发送时间计算延迟情况，进而统计及时率：

WITH ExpectedDelivery AS (
    SELECT 
        Package,
        message,
        CAST(Date_time AS DATE) AS Date_Day,
        DATEADD(day, DATEDIFF(day, 0, Date_time), '01:00:00') AS Expected_Time,  -- 每日预期发送时间
        Date_time AS Actual_Time
    FROM PackageFlow
    WHERE Date_time BETWEEN '2019-01-01' AND EOMONTH('2019-01-01')
),
DeliveryStats AS (
    SELECT 
        Package,
        COUNT(*) AS Total_Sent,
        SUM(CASE WHEN DATEDIFF(minute, Expected_Time, Actual_Time) <= 0 THEN 1 ELSE 0 END) AS On_Time_Count
    FROM ExpectedDelivery
    GROUP BY Package
)
SELECT 
    Package,
    Total_Sent,
    On_Time_Count,
    ROUND(CAST(On_Time_Count AS FLOAT) / Total_Sent * 100, 2) AS On_Time_Rate
FROM DeliveryStats
ORDER BY Package;

性能优化建议 #

针对每日5万的量级，月度数据量不小，咱们可以做这些优化：

• 给PackageFlow创建复合索引：CREATE NONCLUSTERED INDEX IX_PackageFlow_Package_Message_Date ON PackageFlow(Package, message, Date_time);，大幅提升连接和过滤效率
• 尽量用专门的消息配置表代替DISTINCT提取预期消息，既准确又避免全表扫描
• 如果月度日期范围固定，可以提前生成日期表，避免每次递归计算

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