如何在MySQL中实现带条件过滤的窗口函数完成数据转置

现有test.pivot_cols表，包含my_date、cat_1_id、cat_2_id、my_value四列，需要生成cat_2_id_1_value和cat_2_id_2_value两列，逻辑如下：

• cat_2_id_1_value：如果当前行cat_2_id=1，就取当前的my_value；否则取同cat_1_id下最近的cat_2_id=1的行的my_value。
• cat_2_id_2_value：逻辑和上面一样，对应cat_2_id=2的情况。

之前试过用CASE搭配LAST_VALUE窗口函数，按cat_1_id分区、my_date排序，但没法在窗口函数里加cat_2_id的过滤条件。PostgreSQL的FILTER子句MySQL不支持，而且也不适用于非聚合窗口函数，现在找可行的实现方法。

建表和插入数据的语句：

CREATE TABLE test.pivot_cols (
    pivot_by_cols_id INT AUTO_INCREMENT PRIMARY KEY,
    my_date DATE NOT NULL, 
    cat_1_id INT NOT NULL, 
    cat_2_id INT NOT NULL, 
    my_value INT NOT NULL
);

INSERT INTO `test`.`pivot_cols` (`my_date`, `cat_1_id`, `cat_2_id`, `my_value`) 
VALUES 
('2000-01-01', '1', '1', '1'),
('2000-01-01', '2', '1', '2'),
('2000-01-02', '1', '2', '3'),
('2000-01-02', '2', '2', '4'),
('2000-01-02', '1', '1', '5'),
('2000-01-02', '2', '1', '6');

方案一：MySQL 8.0+ 窗口函数方案 #

利用LAST_VALUE结合IF条件，把不符合cat_2_id条件的行值设为NULL，再通过IGNORE NULLS取最近的非空值，同时限定窗口范围是当前分区从起始到当前行：

SELECT 
    pivot_by_cols_id,
    my_date,
    cat_1_id,
    cat_2_id,
    my_value,
    LAST_VALUE(IF(cat_2_id = 1, my_value, NULL)) IGNORE NULLS 
        OVER (PARTITION BY cat_1_id ORDER BY my_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cat_2_id_1_value,
    LAST_VALUE(IF(cat_2_id = 2, my_value, NULL)) IGNORE NULLS 
        OVER (PARTITION BY cat_1_id ORDER BY my_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cat_2_id_2_value
FROM test.pivot_cols
ORDER BY my_date, cat_1_id, cat_2_id;

逻辑说明 #

• IF(cat_2_id = 1, my_value, NULL)：只保留cat_2_id=1行的my_value，其他行设为NULL
• LAST_VALUE(...) IGNORE NULLS：在窗口范围内取最后一个非空值，也就是同cat_1_id下最近的符合条件的my_value
• 窗口范围ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW保证只取当前行及之前的数据，符合“最近”的要求

方案二：兼容MySQL 5.x的自定义变量方案 #

如果是MySQL 5.x版本，不支持IGNORE NULLS，可以用自定义变量来追踪每个cat_1_id下最近的目标值：

SELECT 
    pivot_by_cols_id,
    my_date,
    cat_1_id,
    cat_2_id,
    my_value,
    @val1 := CASE 
        WHEN cat_1_id = @prev_cat1 THEN 
            CASE WHEN cat_2_id = 1 THEN my_value ELSE @val1 END
        ELSE 
            CASE WHEN cat_2_id = 1 THEN my_value ELSE NULL END
    END AS cat_2_id_1_value,
    @val2 := CASE 
        WHEN cat_1_id = @prev_cat1 THEN 
            CASE WHEN cat_2_id = 2 THEN my_value ELSE @val2 END
        ELSE 
            CASE WHEN cat_2_id = 2 THEN my_value ELSE NULL END
    END AS cat_2_id_2_value,
    @prev_cat1 := cat_1_id
FROM test.pivot_cols,
(SELECT @prev_cat1 := NULL, @val1 := NULL, @val2 := NULL) vars
ORDER BY cat_1_id, my_date;

逻辑说明 #

• 初始化三个变量：@prev_cat1记录上一行的cat_1_id，@val1记录当前cat_1_id下最近的cat_2_id=1的值，@val2同理对应cat_2_id=2
• 按cat_1_id和my_date排序，确保同分类下按时间顺序处理
• 当cat_1_id变化时，重置对应的变量值；否则根据当前行的cat_2_id更新或保留变量值

方案一执行后输出如下：

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