SQL如何实现按天/按小时计算平均值 解决时分秒粒度统计问题

问题根因 #

聚合结果粒度不符合预期，是因为分组时直接使用了精确到分/秒的原始时间字段，数据库会按每个独立的精确时间值拆分分组，和你需要的天、小时统计粒度不匹配。

修复方案 #

先把时间字段截断到你要统计的目标粒度，再用截断后的时间字段做分组、计算平均值即可。
以下是主流数据库的参考写法，替换成自己业务里的表名、时间字段名、需要求平均的指标字段名即可使用：

按天维度聚合 #

• MySQL

SELECT
  DATE(your_time_col) AS stat_date,
  AVG(your_metric_col) AS avg_value
FROM your_table
GROUP BY stat_date;

• PostgreSQL

SELECT
  DATE_TRUNC('day', your_time_col) AS stat_date,
  AVG(your_metric_col) AS avg_value
FROM your_table
GROUP BY stat_date;

• ClickHouse

SELECT
  toDate(your_time_col) AS stat_date,
  AVG(your_metric_col) AS avg_value
FROM your_table
GROUP BY stat_date;

按小时维度聚合 #

• MySQL

SELECT
  DATE_FORMAT(your_time_col, '%Y-%m-%d %H:00:00') AS stat_hour,
  AVG(your_metric_col) AS avg_value
FROM your_table
GROUP BY stat_hour;

• PostgreSQL

SELECT
  DATE_TRUNC('hour', your_time_col) AS stat_hour,
  AVG(your_metric_col) AS avg_value
FROM your_table
GROUP BY stat_hour;

• ClickHouse

SELECT
  toStartOfHour(your_time_col) AS stat_hour,
  AVG(your_metric_col) AS avg_value
FROM your_table
GROUP BY stat_hour;

校验要点 #

• 写完后先抽查截断生成的时间字段，确认按天统计的时间全部对齐到当日0点，按小时统计的时间全部对齐到整小时0分0秒
• 确认求平均的字段没有做多余的聚合嵌套，避免重复计算

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