SQL Server补全日期间隔数据及数据仓库存储方案咨询

我们可以通过生成日期序列并关联原数据的方式，自动补全每日状态记录。以下是基于SQL的实现示例（以PostgreSQL为例）：

步骤1：生成目标日期范围的全量日期 #

先创建包含起始日到结束日所有日期的临时数据集：

WITH date_range AS (
    SELECT generate_series(
        '2021-01-12'::DATE,
        '2021-01-20'::DATE,
        '1 day'::INTERVAL
    ) AS date
),

步骤2：关联原数据并填充状态 #

通过窗口函数标记每条状态记录的生效结束时间，再与日期序列关联，自动填充中间日期的状态：

item_status_changes AS (
    SELECT 
        ItemID,
        Date,
        Status,
        LEAD(Date) OVER (PARTITION BY ItemID ORDER BY Date) AS next_change_date
    FROM your_table
)
SELECT 
    '001' AS ItemID,
    dr.date::DATE AS Date,
    isc.Status
FROM date_range dr
LEFT JOIN item_status_changes isc 
    ON dr.date >= isc.Date 
    AND (dr.date < isc.next_change_date OR isc.next_change_date IS NULL)
ORDER BY dr.date;

这个查询会自动继承状态变更日之间的所有日期的前序状态，得到你需要的补全结果。

下面对比每日全量记录（补全后）和Valid From/Valid To区间记录两种方案的核心差异：

1. 存储效率 #

• Valid From/Valid To方式：仅存储状态变更节点，存储量极小。比如你的示例中原数据仅3条，补全后变为9条，状态变化越少，两者的存储量差距越明显，长期来看能节省大量存储空间。
• 补全后的全量记录：存储量随时间线性增长，时间跨度越大、状态越稳定，冗余数据越多。

2. 查询效率 #

• 补全后的全量记录：查询单日状态时，直接按ItemID和Date过滤即可，无需计算区间，查询速度极快，适合频繁按单日查询的场景。
• Valid From/Valid To方式：查询单日状态时需判断日期是否落在区间内（date >= valid_from AND date < valid_to），若未建立(ItemID, valid_from, valid_to)复合索引，查询效率会下降；但索引优化后，查询性能可接近全量记录。

3. 维护效率 #

• 补全后的全量记录：状态变更时无需额外操作，但回溯修改历史状态时，需要批量更新多个日期的记录，维护成本高。
• Valid From/Valid To方式：状态变更时仅需新增一条记录，并更新上一条记录的valid_to值；回溯修改也只需调整对应区间的起止日期，维护操作简单。

选择建议 #

• 若业务需频繁查询单日状态且数据量不大，优先选补全后的全量记录。
• 若业务需查询区间内状态变化，或数据时间跨度大、状态稳定，Valid From/Valid To方式在存储和维护上更具优势。

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