如何通过存储过程在MySQL中实现动态透视创建目标视图？

需求概述 #

现有两张业务表：

• Table1：存储统计维度配置，字段包括id（主键）、name（维度标识）、operations（需执行的聚合操作，逗号分隔）
• Table2：存储原始数值数据，字段包括id、date（统计日期）、table1_id（关联Table1主键）、value（待统计数值）

需要通过存储过程动态创建视图，输出按日期聚合的统计结果，列结构为：自增id、date，以及各维度对应的聚合列（如amin、amax对应维度a的min、max计算值）。

实现步骤 #

1. 创建存储过程 #

以下存储过程会自动读取Table1的配置，动态生成视图创建语句并执行：

DELIMITER //

CREATE PROCEDURE CreateDynamicStatsView()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(50);
    DECLARE v_ops VARCHAR(100);
    DECLARE op VARCHAR(10);
    DECLARE sql_columns TEXT DEFAULT '';
    DECLARE cur CURSOR FOR SELECT id, name, operations FROM Table1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- 遍历Table1配置，拼接聚合列SQL片段
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_id, v_name, v_ops;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 拆分逗号分隔的聚合操作
        SET @op_pos = LOCATE(',', v_ops);
        WHILE @op_pos > 0 DO
            SET op = SUBSTRING(v_ops, 1, @op_pos - 1);
            SET sql_columns = CONCAT(sql_columns, ', ', op, '(CASE WHEN t2.table1_id = ', v_id, ' THEN t2.value END) AS ', v_name, op);
            SET v_ops = SUBSTRING(v_ops, @op_pos + 1);
            SET @op_pos = LOCATE(',', v_ops);
        END WHILE;
        -- 处理最后一个聚合操作
        SET sql_columns = CONCAT(sql_columns, ', ', v_ops, '(CASE WHEN t2.table1_id = ', v_id, ' THEN t2.value END) AS ', v_name, v_ops);
    END LOOP;
    CLOSE cur;

    -- 拼接完整的视图创建SQL
    SET @create_view_sql = CONCAT(
        'CREATE OR REPLACE VIEW stats_view AS ',
        'SELECT ROW_NUMBER() OVER(ORDER BY t2.date) AS id, ',
        't2.date',
        sql_columns,
        ' FROM Table2 t2 ',
        'GROUP BY t2.date'
    );

    -- 执行动态SQL
    PREPARE stmt FROM @create_view_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT '视图stats_view创建成功' AS result;
END //

DELIMITER ;

2. 执行存储过程 #

调用存储过程生成目标视图：

CALL CreateDynamicStatsView();

3. 查询视图验证结果 #

直接查询生成的视图即可得到符合要求的统计数据：

SELECT * FROM stats_view;

输出结果与需求示例完全一致：

id | date    |amin|amax|bmin|bmax|bavg|cavg
-------------------------------------------
1  | 2024-1-1|2.1 |2.2 |2.3 |2.4 |2.35|2.55
2  | 2024-1-2|3.1 |3.2 |3.3 |3.4 |3.35|3.55

关键逻辑说明 #

• 动态列生成：通过游标遍历Table1的配置，拆分每个维度的聚合操作，用CASE WHEN实现按维度筛选数据后聚合，自动拼接出对应统计列
• 自增id生成：使用ROW_NUMBER() OVER(ORDER BY date)生成按日期排序的自增序号
• 视图可更新：采用CREATE OR REPLACE VIEW语法，后续修改Table1的统计配置后，重新调用存储过程即可更新视图结构

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