如何为各report_date补充前15天缺失的install_date记录

需求说明 #

现有存储安装数据的表，字段为report_date、install_date、installs，每个report_date对应的数据快照包含其前15天至后50天的install_date数据。为生成月度报表，要求每个report_date需覆盖当月全量install_date（如report_date为2022-01-27时，需包含2022-01-01至2022-01-31的install_date），缺失的install_date数据需从该report_date前15天的其他report_date中获取，且不能重复已有install_date的记录。

示例源表数据 #

期望结果表 #

问题分析 #

你尝试的SQL存在核心逻辑错误：date = date - INTERVAL '1 DAY'永远不成立，且子查询SELECT install_date FROM table WHERE date = date会返回全表的install_date，无法精准匹配当前report_date的已有数据，达不到去重和补全的目的。

正确SQL实现 #

PostgreSQL版本 #

-- 生成每个report_date对应的当月所有install_date
WITH required_dates AS (
    SELECT 
        t.report_date,
        generate_series(
            date_trunc('month', t.report_date)::date,
            (date_trunc('month', t.report_date) + interval '1 month - 1 day')::date,
            interval '1 day'
        )::date AS required_install_date
    FROM (SELECT DISTINCT report_date FROM your_table) t
),
-- 提取原始表已有数据
existing_data AS (
    SELECT report_date, install_date, installs
    FROM your_table
),
-- 找出缺失数据并从15天内的快照中补全
missing_data AS (
    SELECT 
        rd.report_date,
        rd.required_install_date AS install_date,
        ed.installs
    FROM required_dates rd
    LEFT JOIN existing_data ed1 
        ON rd.report_date = ed1.report_date 
        AND rd.required_install_date = ed1.install_date
    -- 关联前15天的report_date数据，获取对应installs值
    LEFT JOIN existing_data ed 
        ON ed.install_date = rd.required_install_date
        AND ed.report_date BETWEEN rd.report_date - interval '15 days' AND rd.report_date - interval '1 day'
    WHERE ed1.install_date IS NULL
    -- 去重，确保每个缺失日期只补一条数据
    GROUP BY rd.report_date, rd.required_install_date, ed.installs
)
-- 合并原始数据和补全数据
SELECT * FROM existing_data
UNION ALL
SELECT * FROM missing_data
ORDER BY report_date DESC, install_date DESC;

MySQL版本 #

MySQL不支持generate_series，需用递归CTE生成月度日期：

WITH RECURSIVE required_dates AS (
    SELECT 
        t.report_date,
        DATE_FORMAT(t.report_date, '%Y-%m-01') AS required_install_date
    FROM (SELECT DISTINCT report_date FROM your_table) t
    UNION ALL
    SELECT 
        report_date,
        DATE_ADD(required_install_date, INTERVAL 1 DAY)
    FROM required_dates
    WHERE required_install_date < LAST_DAY(report_date)
),
existing_data AS (
    SELECT report_date, install_date, installs
    FROM your_table
),
missing_data AS (
    SELECT 
        rd.report_date,
        rd.required_install_date AS install_date,
        ed.installs
    FROM required_dates rd
    LEFT JOIN existing_data ed1 
        ON rd.report_date = ed1.report_date 
        AND rd.required_install_date = ed1.install_date
    LEFT JOIN existing_data ed 
        ON ed.install_date = rd.required_install_date
        AND ed.report_date BETWEEN DATE_SUB(rd.report_date, INTERVAL 15 DAY) AND DATE_SUB(rd.report_date, INTERVAL 1 DAY)
    WHERE ed1.install_date IS NULL
    GROUP BY rd.report_date, rd.required_install_date, ed.installs
)
SELECT * FROM existing_data
UNION ALL
SELECT * FROM missing_data
ORDER BY report_date DESC, install_date DESC;

逻辑说明 #

1. required_dates：生成每个report_date对应的当月所有日期，确保覆盖月度全量install_date。
2. existing_data：提取原始表中已有的数据记录。
3. missing_data：通过左连接找出当前report_date缺失的install_date，从其前15天的其他快照中获取对应installs值，并用GROUP BY去重避免重复补全。
4. 最后用UNION ALL合并原始数据和补全数据，按需求排序。

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