补全销售表中各客户缺失月份的销售数据需求（填充0值）

刚好碰到过类似的需求，这是典型的时间序列补全场景——我们需要为每个客户生成从首次购买到指定截止日期（这里是2020-07-01）的完整月份序列，再关联原销售数据填充缺失值为0。下面以MySQL为例给出具体实现：

方法1：使用递归CTE（MySQL 8.0+及其他支持CTE的数据库） #

递归CTE是最简洁的方式，先获取每个客户的首次购买时间，再递归生成每个客户的月度序列，最后左连接原表补0：

WITH customer_first_purchase AS (
    -- 第一步：获取每个客户的首次购买日期
    SELECT CustomerID, MIN(TransactionDate) AS FirstPurchaseDate
    FROM sales_table
    GROUP BY CustomerID
),
date_series AS (
    -- 第二步：递归生成每个客户从首次购买到2020-07-01的所有月份
    SELECT 
        CustomerID,
        FirstPurchaseDate AS TransactionDate
    FROM customer_first_purchase
    UNION ALL
    SELECT 
        ds.CustomerID,
        DATE_ADD(ds.TransactionDate, INTERVAL 1 MONTH) AS TransactionDate
    FROM date_series ds
    JOIN customer_first_purchase cfp ON ds.CustomerID = cfp.CustomerID
    WHERE DATE_ADD(ds.TransactionDate, INTERVAL 1 MONTH) <= '2020-07-01'
)
-- 第三步：左连接原表，用COALESCE将缺失的Quantity替换为0
SELECT 
    ds.TransactionDate,
    ds.CustomerID,
    COALESCE(st.Quantity, 0) AS Quantity
FROM date_series ds
LEFT JOIN sales_table st 
    ON ds.CustomerID = st.CustomerID 
    AND ds.TransactionDate = st.TransactionDate
ORDER BY ds.CustomerID, ds.TransactionDate;

方法2：兼容MySQL 5.x（无CTE支持） #

如果你的数据库版本不支持CTE，可以用临时表生成日期范围，再关联客户信息：

-- 1. 创建临时表存储2020-01至2020-07的所有月份
CREATE TEMPORARY TABLE date_range (
    month_date DATE
);

INSERT INTO date_range VALUES
('2020-01-01'), ('2020-02-01'), ('2020-03-01'),
('2020-04-01'), ('2020-05-01'), ('2020-06-01'), ('2020-07-01');

-- 2. 获取每个客户的首次购买日期并存储到临时表
CREATE TEMPORARY TABLE customer_first_purchase AS
SELECT CustomerID, MIN(TransactionDate) AS FirstPurchaseDate
FROM sales_table
GROUP BY CustomerID;

-- 3. 生成客户+有效月份的组合，左连接原表补0
SELECT 
    dr.month_date AS TransactionDate,
    cfp.CustomerID,
    COALESCE(st.Quantity, 0) AS Quantity
FROM customer_first_purchase cfp
CROSS JOIN date_range dr
LEFT JOIN sales_table st 
    ON cfp.CustomerID = st.CustomerID 
    AND dr.month_date = st.TransactionDate
WHERE dr.month_date >= cfp.FirstPurchaseDate -- 过滤掉首次购买前的月份
ORDER BY cfp.CustomerID, dr.month_date;

注意事项 #

• 不同数据库的日期函数略有差异：比如PostgreSQL用INTERVAL '1 month'，SQL Server用DATEADD(month, 1, ds.TransactionDate)，核心逻辑不变；
• 如果截止日期不是固定的2020-07-01，可以替换为MAX(TransactionDate)从原表动态获取最新日期。

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