MySQL实现按周按人聚合任务耗时的动态交叉表(CROSS TAB)方案咨询

实现思路 #

• 由于周列随查询日期范围动态变化，无法写死CASE判断，必须使用动态SQL拼接的方式生成查询语句
• 分两步执行：先查询当前日期范围内所有的周起始日期，拼接每个周对应的聚合判断逻辑，再组装成完整的交叉表查询语句执行

具体实现代码 #

-- 1. 自定义查询日期范围，仅需修改这里即可适配不同统计周期
SET @start_date = '2021-09-01';
SET @end_date = '2021-11-10';

-- 2. 调整GROUP_CONCAT长度限制，避免周数过多时拼接内容被截断
SET SESSION group_concat_max_len = 1000000;

-- 3. 动态生成每个周对应的CASE聚合语句
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN DATE_FORMAT(adddate(activity_date, INTERVAL 1-DAYOFWEEK(activity_date) DAY), ''%m/%d/%Y'') = ''',
      DATE_FORMAT(week_start, '%m/%d/%Y'),
      ''' THEN min_spent ELSE 0 END) AS `minutes_week_',
      DATE_FORMAT(week_start, '%m/%d/%Y'), '`'
    )
  ) INTO @sql
FROM (
  -- 先查出指定日期范围内所有的周起始日期
  SELECT DISTINCT adddate(activity_date, INTERVAL 1-DAYOFWEEK(activity_date) DAY) AS week_start
  FROM time_spent
  WHERE activity_date BETWEEN @start_date AND @end_date
) AS weeks;

-- 4. 组装完整的交叉表查询语句
SET @full_sql = CONCAT(
  'SELECT id, ', @sql, ' 
  FROM time_spent
  WHERE activity_date BETWEEN ''', @start_date, ''' AND ''', @end_date, '''
  GROUP BY id
  ORDER BY id'
);

-- 5. 执行动态SQL获取结果
PREPARE stmt FROM @full_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

补充说明 #

• 上述实现完全兼容原有SQL的周计算逻辑（周日为一周起始，对应WEEK函数第二个参数为0的规则），如果需要调整周起始规则，统一修改周计算逻辑即可
• 其他数据库对应方案：
  • PostgreSQL：结合crosstab函数 + 动态类型转换实现
  • SQL Server：使用PIVOT关键字 + 动态SQL拼接
  • Oracle：12c及以上版本支持PIVOT动态列查询，可直接用动态SQL实现

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