MySQL/MariaDB动态透视表报表：关联表与Metabase适配方案问询

原始数据表 #

假设表名为task_stats，结构及数据如下：

+----+--------+-------+---------+
| id | date   |planned| actual  |
+----+--------+-------+---------+
|  1 |03-04-23| 40    |      15 |
|  2 |03-04-23| 15    |      17 |
|  3 |03-04-23| 60    |      19 |
|  4 |03-04-23| 20    |      20 |
|  1 |04-04-23| 10    |      22 |
|  2 |04-04-23| 15    |      32 |
|  3 |04-04-23| 65    |      50 |
|  4 |04-04-23| 22    |      55 |
|  1 |05-04-23| 18    |      40 |
|  2 |05-04-23| 36    |      65 |
|  3 |05-04-23| 44    |      70 |
|  4 |05-04-23| 47    |      57 |
+----+--------+-------+---------+

目标报表格式 #

需要生成按用户分组、日期为列的交叉表，每个日期下包含Planned和Actual两个子列，且仅显示指定日期范围内有数据的日期：

+---------+--------------+--------------+---------------+
| user_id |    03-04-23  |    04-04-23  |   05-04-23    | 
+---------+--------------+--------------+---------------+
|         |Planned|Actual|Planned|Actual|Planned|Actual |
+---------+--------------+------------------------------+
|      1  |  40   |  15  |   10  |  22  |   18  | 40    |
|      2  |  15   |  17  |   15  |  32  |   36  | 65    |
|      3  |  60   |  19  |   65  |  50  |   44  | 70    |
|      4  |  20   |  20  |   22  |  55  |   47  | 57    |
+---------+--------------+--------------+---------------+

最优实现方案 #

1. 创建带参数的存储过程 #

编写存储过程，接收日期范围参数，动态生成交叉表SQL并执行，同时关联原始数据表获取planned和actual数据：

DELIMITER //
CREATE PROCEDURE GenerateTaskStatsReport(IN start_date DATE, IN end_date DATE)
BEGIN
    SET @sql = NULL;
    
    -- 动态生成每个日期对应的Planned和Actual列
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN DATE(ts.date) = ''', DATE(ts.date), ''' THEN ts.planned END) AS `', DATE_FORMAT(ts.date, '%d-%m-%y'), '_planned`,',
            'MAX(CASE WHEN DATE(ts.date) = ''', DATE(ts.date), ''' THEN ts.actual END) AS `', DATE_FORMAT(ts.date, '%d-%m-%y'), '_actual`'
        )
    ) INTO @sql
    FROM task_stats ts
    WHERE DATE(ts.date) BETWEEN start_date AND end_date;
    
    -- 拼接完整SQL，按user_id分组
    SET @sql = CONCAT(
        'SELECT ts.id AS user_id, ', @sql, ' ',
        'FROM task_stats ts ',
        'WHERE DATE(ts.date) BETWEEN ''', start_date, ''' AND ''', end_date, ''' ',
        'GROUP BY ts.id'
    );
    
    -- 执行动态SQL
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

2. 在Metabase中调用存储过程 #

Metabase支持调用存储过程，步骤如下：

• 进入Metabase，创建新的原生查询
• 使用CALL语句调用存储过程，传入日期参数（支持Metabase变量）：

  CALL GenerateTaskStatsReport({{start_date}}, {{end_date}});
  

• 设置变量类型为日期，用户即可在报表界面选择日期范围

3. 报表格式优化（Metabase内处理） #

存储过程返回的列名是03-04-23_planned、03-04-23_actual，可以在Metabase中：

• 进入报表的可视化设置，调整列标题，将03-04-23_planned改为03-04-23 Planned，03-04-23_actual改为03-04-23 Actual
• 使用Metabase的表格可视化，通过拖拽列排序，模拟目标报表的合并表头效果（Metabase原生不支持合并单元格，但可通过列标题命名实现近似效果）

替代方案：无需存储过程的Metabase自定义SQL（限制：需提前适配日期范围） #

如果不想创建存储过程，可手动编写静态交叉表SQL，但无法动态适配任意日期范围，适合固定周期报表：

SELECT
    id AS user_id,
    MAX(CASE WHEN date = '2023-04-03' THEN planned END) AS `03-04-23 Planned`,
    MAX(CASE WHEN date = '2023-04-03' THEN actual END) AS `03-04-23 Actual`,
    MAX(CASE WHEN date = '2023-04-04' THEN planned END) AS `04-04-23 Planned`,
    MAX(CASE WHEN date = '2023-04-04' THEN actual END) AS `04-04-23 Actual`,
    MAX(CASE WHEN date = '2023-04-05' THEN planned END) AS `05-04-23 Planned`,
    MAX(CASE WHEN date = '2023-04-05' THEN actual END) AS `05-04-23 Actual`
FROM task_stats
WHERE date BETWEEN {{start_date}} AND {{end_date}}
GROUP BY id;

关键注意事项 #

• 确保原始数据表的date字段是日期类型（若为字符串需先转换：STR_TO_DATE(date, '%d-%m-%y')）
• 存储过程中使用DATE_FORMAT统一日期列名格式，匹配目标报表样式
• Metabase调用存储过程时，需确保数据库用户有执行存储过程的权限

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