如何用历史日期数据填充当前日期缺失的block_date记录？

核心逻辑 #

要搞定这个需求，拆解成三步就行：

1. 先给每个date列出它对应的所有目标block_date——也就是这个date往前推30天里的所有block_date；
2. 给每个block_date整理好它在各个date的installs记录，按日期从新到旧排好序；
3. 把第一步的组合和第二步的记录关联起来，每个(date, block_date)对取最近30天里最新的installs值，没有的话就自动回溯前几天的。

具体SQL代码（以MySQL为例，其他数据库微调日期函数即可） #

假设你的表叫install_data，代码如下：

-- 第一步：生成所有需要的date和block_date组合
WITH date_block_combinations AS (
    SELECT 
        d.date,
        b.block_date
    FROM (
        -- 先把表中所有不重复的date拿出来
        SELECT DISTINCT date FROM install_data
    ) d
    CROSS JOIN (
        -- 筛选出当前date往前30天内的所有block_date
        SELECT DISTINCT block_date 
        FROM install_data
        WHERE block_date >= DATE_SUB(d.date, INTERVAL 30 DAY)
        AND block_date <= d.date
    ) b
),
-- 第二步：给每个block_date的记录按日期倒序编号，最新的排第1
block_date_history AS (
    SELECT
        date,
        block_date,
        installs,
        ROW_NUMBER() OVER (PARTITION BY block_date ORDER BY date DESC) AS rn
    FROM install_data
)
-- 第三步：关联组合和历史记录，取每个(date, block_date)对应的最新有效installs
SELECT
    dc.date,
    dc.block_date,
    bh.installs
FROM date_block_combinations dc
LEFT JOIN block_date_history bh 
    ON dc.block_date = bh.block_date
    AND bh.date >= DATE_SUB(dc.date, INTERVAL 30 DAY)
    AND bh.date <= dc.date
WHERE bh.rn = 1 -- 只取最近的那条记录
ORDER BY dc.date, dc.block_date;

代码说明 #

• date_block_combinations：这部分是把每个date和它30天范围内的所有block_date一一配对，保证不会漏掉任何需要的组合。
• block_date_history：给每个block_date的记录按日期从新到旧编号，这样编号1的就是这个block_date最近的一条数据，方便后续快速取值。
• 最后关联的时候，会自动匹配每个(date, block_date)对在30天内的最新installs，如果当前date没有数据，就自动取前一天的，以此类推。

小提示 #

• 不同数据库的日期函数不一样：比如PostgreSQL用date - interval '30 days'，SQL Server用DATEADD(day, -30, d.date)，根据你用的数据库调整就行。
• 如果某个block_date在30天内完全没数据，结果里的installs会是NULL，要是需要默认值，加个COALESCE(bh.installs, 0)之类的就行。

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