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
相关产品推荐
相关产品推荐

