如何用SQL JOIN生成每个客户全日期数据并填充缺失字段

需求回顾 #

需要关联两张表生成每个客户对应2017年1-6月所有日期的记录，满足：

• 无订单日期的no_orders设为0
• 无订单日期的city_code沿用该客户上一个有订单记录日期的city_code

现有代码的不足 #

你提供的代码已经完成了全日期+客户的组合生成，以及no_orders的0值填充，但缺少city_code的历史值填充逻辑，以下是完善后的方案：

方案1：支持IGNORE NULLS的数据库（PostgreSQL、SQL Server 2022+等） #

使用LAST_VALUE窗口函数直接获取上一个非空的city_code：

SELECT 
    d.date,
    c.customer,
    COALESCE(t.no_orders, 0) AS no_orders,
    -- 按客户分组，按日期排序，取到当前行为止最后一个非空的city_code
    LAST_VALUE(t.city_code) OVER (
        PARTITION BY c.customer 
        ORDER BY d.date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS city_code
FROM dates d
CROSS JOIN (SELECT DISTINCT customer FROM customers) c
LEFT JOIN customers t
    ON t.date = d.date 
    AND t.customer = c.customer
ORDER BY c.customer, d.date;

方案2：兼容不支持IGNORE NULLS的数据库（MySQL 8.0+、旧版SQL Server等） #

通过分组标记+聚合的方式实现填充：

WITH customer_dates AS (
    -- 生成所有客户+日期的基础关联结果
    SELECT 
        d.date,
        c.customer,
        t.no_orders,
        t.city_code
    FROM dates d
    CROSS JOIN (SELECT DISTINCT customer FROM customers) c
    LEFT JOIN customers t
        ON t.date = d.date 
        AND t.customer = c.customer
),
city_groups AS (
    -- 为每个客户的记录按非空city_code分段
    SELECT 
        date,
        customer,
        COALESCE(no_orders, 0) AS no_orders,
        city_code,
        -- 每遇到非空city_code，分组ID递增，连续空值归为同一组
        SUM(CASE WHEN city_code IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY customer 
            ORDER BY date
        ) AS group_id
    FROM customer_dates
)
-- 取每组内的非空city_code填充所有记录
SELECT 
    date,
    customer,
    no_orders,
    MAX(city_code) OVER (PARTITION BY customer, group_id) AS city_code
FROM city_groups
ORDER BY customer, date;

逻辑说明 #

1. 先通过CROSS JOIN生成所有客户与日期的笛卡尔积，确保每个客户都有全日期的记录
2. 用LEFT JOIN关联订单表，获取原始订单数据
3. 通过窗口函数实现“上一个非空值填充”：
   • 方案1直接用LAST_VALUE + IGNORE NULLS高效获取历史非空城市编码
   • 方案2通过分段分组，用聚合函数取每组内的非空值，兼容更多数据库版本

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