如何基于上一行月份值按顺序补全SQL表中缺失的月份名称

补全规则 #

若当前行月份为NULL，则取上一行月份的下一个月填充，例如上一行月份为January（一月），当前空值替换为February（二月）；上一行月份为August（八月），当前空值替换为September（九月）。

原有建表语句 #

CREATE TABLE IF NOT EXISTS missing_months (
    `Cust_id` INT,
    `Month` VARCHAR(9) CHARACTER SET utf8,
    `Sales_value` INT
);
INSERT INTO missing_months VALUES
    (1,'Janurary',224),
    (2,'February',224),
    (3,NULL,239),
    (4,'April',205),
    (5,NULL,218),
    (6,'June',201),
    (7,NULL,205),
    (8,'August',246),
    (9,NULL,218),
    (10,NULL,211),
    (11,'November',223),
    (12,'December',211);

问题现状 #

直接查询表得到的结果存在月份空值：

Cust_id    Month     Sales_value
    1       Janurary    224
    2       February    224
    3       null        239
    4       April       205
    5       null        218
    6       June        201
    7       null        205
    8       August      246
    9       null        218
    10      null        211
    11      November    223
    12      December    211

预期输出 #

所有空值按顺序补全后的结果：

Cust_id  Month       Sales_value
    1       Janurary    224
    2       February    224
    3       March       239
    4       April       205
    5       May         218
    6       June        201
    7       July        205
    8       August      246
    9       September   218
    10      October     211
    11      November    223
    12      December    211

实现SQL（支持MySQL 8.0及以上、PostgreSQL等支持窗口函数的数据库） #

WITH month_mapping AS (
    -- 建立月份名称和数字的映射关系，注意原数据中January拼写为Janurary，此处保持匹配
    SELECT 1 AS month_num, 'Janurary' AS month_name UNION ALL
    SELECT 2, 'February' UNION ALL
    SELECT 3, 'March' UNION ALL
    SELECT 4, 'April' UNION ALL
    SELECT 5, 'May' UNION ALL
    SELECT 6, 'June' UNION ALL
    SELECT 7, 'July' UNION ALL
    SELECT 8, 'August' UNION ALL
    SELECT 9, 'September' UNION ALL
    SELECT 10, 'October' UNION ALL
    SELECT 11, 'November' UNION ALL
    SELECT 12, 'December'
),
marked_data AS (
    SELECT 
        t.*,
        m.month_num,
        -- 统计当前行之前的非空月份数量，用于分组
        COUNT(m.month_num) OVER (ORDER BY Cust_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM missing_months t
    LEFT JOIN month_mapping m ON t.Month = m.month_name
),
fixed_month_num AS (
    SELECT 
        Cust_id,
        Sales_value,
        -- 取分组内的第一个非空月份数字，加上当前行在分组内的偏移量，得到正确月份数字
        FIRST_VALUE(month_num) OVER (PARTITION BY group_id ORDER BY Cust_id) + 
        (ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY Cust_id) - 1) AS correct_month_num
    FROM marked_data
)
SELECT 
    f.Cust_id,
    m.month_name AS Month,
    f.Sales_value
FROM fixed_month_num f
JOIN month_mapping m ON f.correct_month_num = m.month_num
ORDER BY f.Cust_id;

说明 #

如果需要直接更新原表的字段值，可以基于上面的查询结果做UPDATE关联更新即可。

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