SQL查询如何为实体无匹配记录的指定日期返回NULL值

核心逻辑 #

这个需求最直接的实现方式分三步：

• 提取原表所有唯一的EntityId集合
• 生成2021年5月到8月所有自然月的月末日期集合
• 两个集合做笛卡尔积得到全量的「EntityId+月末日期」配对，再左关联预处理后的原表数据，无匹配的Value自动返回NULL。如果需要按时间前向填充缺失值，加个窗口函数即可。

代码示例 #

以下为通用SQL写法，可根据你使用的数据库方言微调日期函数：

WITH 
-- 1. 提取所有唯一实体ID
distinct_entities AS (
    SELECT DISTINCT EntityId FROM your_table
),
-- 2. 生成目标区间的所有月末日期
target_month_ends AS (
    SELECT '2021-05-31' AS Date
    UNION ALL SELECT '2021-06-30'
    UNION ALL SELECT '2021-07-31'
    UNION ALL SELECT '2021-08-31'
),
-- 3. 预处理原表：把每条记录的日期转换为当月月末，方便匹配
original_processed AS (
    SELECT 
        EntityId,
        EOMONTH(Date) AS month_end, -- MySQL可替换为LAST_DAY(Date)
        Value
    FROM your_table
)
-- 4. 关联得到结果
SELECT 
    de.EntityId,
    tme.Date,
    -- 如果需要前向填充缺失值，把下面这行替换成注释里的代码即可
    owe.Value
    -- COALESCE(owe.Value, LAST_VALUE(owe.Value) IGNORE NULLS OVER (PARTITION BY de.EntityId ORDER BY tme.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS Value
FROM distinct_entities de
CROSS JOIN target_month_ends tme
LEFT JOIN original_processed owe 
    ON de.EntityId = owe.EntityId 
    AND tme.Date = owe.month_end
ORDER BY de.EntityId, tme.Date

说明 #

你给出的期望输出中EntityId为1的8月值为59，和原表的8月记录值61不一致，应该是需要前向填充的场景，只要把代码中Value字段的计算逻辑替换为注释里的窗口函数写法即可匹配你的期望结果。

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