生成缺失/不完整值报告：补全缺失采集记录的SQL方案咨询

核心思路 #

先生成所有必须存在的采集时机清单，再与现有数据关联，补全缺失的记录并独立判断采集状态，彻底规避原Collection_Status字段不可靠的问题，确保每个案例都有entry、exit（以及必要的review）记录。

SQL实现步骤 #

1. 生成所有应有的采集节点（CTE） #

用公共表表达式（CTE）列出每个Product ID需要覆盖的所有采集时机：

• 固定的entry节点（入院当天）
• 固定的exit节点（出院当天）
• 若入院到出院时长超过90天，额外生成review节点（入院后第90天）

WITH required_occasions AS (
    -- 生成entry节点
    SELECT
        "Product ID",
        "Entry date_time",
        "Exit_date_time",
        'entry' AS "Collection_Occasion",
        "Entry date_time" AS collection_datetime
    FROM your_table
    GROUP BY "Product ID", "Entry date_time", "Exit_date_time"
    UNION ALL
    -- 生成exit节点
    SELECT
        "Product ID",
        "Entry date_time",
        "Exit_date_time",
        'exit' AS "Collection_Occasion",
        "Exit_date_time" AS collection_datetime
    FROM your_table
    GROUP BY "Product ID", "Entry date_time", "Exit_date_time"
    UNION ALL
    -- 生成符合条件的review节点
    SELECT
        "Product ID",
        "Entry date_time",
        "Exit_date_time",
        'review' AS "Collection_Occasion",
        "Entry date_time" + INTERVAL '90 days' AS collection_datetime
    FROM your_table
    WHERE "Exit_date_time" - "Entry date_time" > INTERVAL '90 days'
    GROUP BY "Product ID", "Entry date_time", "Exit_date_time"
)

2. 关联现有数据，补全缺失记录并标记状态 #

将CTE生成的采集节点与原表左连接，通过是否存在有效采集数据（H/H+非空）判断状态，自动补全缺失的exit等记录：

SELECT
    ro."Product ID",
    COALESCE(t."Product Measure - H", NULL) AS "Product Measure - H",
    COALESCE(t."Product Measure - H+", NULL) AS "Product Measure - H+",
    ro."Entry date_time",
    ro."Exit_date_time",
    -- 独立判断采集状态，不依赖原字段
    CASE
        WHEN t."Product ID" IS NOT NULL AND (t."Product Measure - H" IS NOT NULL OR t."Product Measure - H+" IS NOT NULL) THEN 'complete'
        ELSE 'incomplete'
    END AS "Collection_Status",
    ro."Collection_Occasion"
FROM required_occasions ro
LEFT JOIN your_table t
    ON ro."Product ID" = t."Product ID"
    AND ro."Collection_Occasion" = t."Collection_Occasion"
ORDER BY ro."Product ID", ro."Collection_Occasion"

3. 可选：将结果存入新表 #

如果需要持久化报告数据，用CREATE TABLE AS替代临时查询：

CREATE TABLE automated_report_data AS
WITH required_occasions AS (
    -- 此处复制第一步的CTE代码
)
SELECT
    -- 此处复制第二步的SELECT代码

关键逻辑说明 #

• 补全缺失exit记录：通过CTE强制生成每个案例的exit节点，左连接后无对应原记录时，H/H+字段为NULL，状态自动标记为incomplete，完全匹配需求。
• 脱离原状态字段依赖：完全通过H/H+是否有有效值判断采集状态，彻底规避原Collection_Status的不可靠问题。
• 自动生成review节点：仅当入院到出院时长超90天时才生成review记录，符合业务规则。

适配调整提示 #

• 日期函数需匹配数据库类型：MySQL用DATE_ADD("Entry date_time", INTERVAL 90 DAY)，SQL Server用DATEADD(day,90,"Entry date_time")，请自行替换。
• 多记录去重：若原表存在同一Product ID同一采集时机的多条记录，可在左连接前用窗口函数ROW_NUMBER()取最新/最优记录。

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