You need to enable JavaScript to run this app. MySQL如何实现列转行及按日期分组统计24小时列总和
优惠活动
大模型
产品
解决方案
定价
更多

MySQL如何实现列转行及按日期分组统计24小时列总和

实现方法说明

针对你的需求,根据是否需要显式生成列转行后的纵向数据集,有两种可直接落地的实现方案:

方案1:直接按列求和计算日度总和(性能最优)

如果你的目标只是拿到每个自然日的全时段数值总和,不需要生成逐小时的纵向明细,完全不需要做列转行操作,直接通过列加法计算即可,这也是对现有表结构性能最友好的写法。
因为表自带(logdate, logtime)的联合唯一索引,该查询可以走覆盖索引完成计算,无额外回表开销。实现SQL如下:

SELECT
  logdate,
  SUM(
    IFNULL(`0h`, 0) +
    IFNULL(`1h`, 0) +
    IFNULL(`2h`, 0) +
    IFNULL(`3h`, 0) +
    IFNULL(`4h`, 0) +
    IFNULL(`5h`, 0) +
    IFNULL(`6h`, 0) +
    IFNULL(`7h`, 0) +
    IFNULL(`8h`, 0) +
    IFNULL(`9h`, 0) +
    IFNULL(`10h`, 0) +
    IFNULL(`11h`, 0) +
    IFNULL(`12h`, 0) +
    IFNULL(`13h`, 0) +
    IFNULL(`14h`, 0) +
    IFNULL(`15h`, 0) +
    IFNULL(`16h`, 0) +
    IFNULL(`17h`, 0) +
    IFNULL(`18h`, 0) +
    IFNULL(`19h`, 0) +
    IFNULL(`20h`, 0) +
    IFNULL(`21h`, 0) +
    IFNULL(`22h`, 0) +
    IFNULL(`23h`, 0)
  ) AS daily_total_value
FROM history_kw_main
GROUP BY logdate;

方案2:标准列转行后再汇总(适配需要逐小时明细的场景)

如果你确实需要完成列转行操作——也就是把横向存储的0h到23h列,转成每行单独对应一个小时维度、存储对应数值的纵向表结构,再基于这个结构做日度汇总,可以用全版本兼容的UNION ALL写法实现,不需要依赖高版本MySQL的特殊函数:

SELECT
  logdate,
  SUM(kw_value) AS daily_total_value
FROM (
  SELECT logdate, '0h' AS hour_type, IFNULL(`0h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '1h' AS hour_type, IFNULL(`1h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '2h' AS hour_type, IFNULL(`2h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '3h' AS hour_type, IFNULL(`3h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '4h' AS hour_type, IFNULL(`4h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '5h' AS hour_type, IFNULL(`5h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '6h' AS hour_type, IFNULL(`6h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '7h' AS hour_type, IFNULL(`7h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '8h' AS hour_type, IFNULL(`8h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '9h' AS hour_type, IFNULL(`9h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '10h' AS hour_type, IFNULL(`10h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '11h' AS hour_type, IFNULL(`11h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '12h' AS hour_type, IFNULL(`12h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '13h' AS hour_type, IFNULL(`13h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '14h' AS hour_type, IFNULL(`14h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '15h' AS hour_type, IFNULL(`15h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '16h' AS hour_type, IFNULL(`16h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '17h' AS hour_type, IFNULL(`17h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '18h' AS hour_type, IFNULL(`18h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '19h' AS hour_type, IFNULL(`19h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '20h' AS hour_type, IFNULL(`20h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '21h' AS hour_type, IFNULL(`21h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '22h' AS hour_type, IFNULL(`22h`, 0) AS kw_value FROM history_kw_main
  UNION ALL
  SELECT logdate, '23h' AS hour_type, IFNULL(`23h`, 0) AS kw_value FROM history_kw_main
) AS unpivoted_data
GROUP BY logdate;

注意事项

  • 所有0h到23h的列名因为是数字开头,必须用反引号包裹,否则会触发SQL语法错误
  • 用IFNULL将列的NULL值转换为0,避免单个列的NULL值导致整段求和结果为NULL
  • 不要错误使用SUM(\0h`)`这类仅对单个小时列做跨logtime求和的写法,这类写法会遗漏其他23个小时列的数值,无法得到当日全时段的总和
  • 如果使用MySQL 8.0及以上版本,也可以通过JSON_TABLE函数简化列转行的代码量,但UNION ALL的写法对所有MySQL版本兼容,大表执行稳定性更高

内容的提问来源于stack exchange,提问作者Devbreaker

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.18 01:45:04