如何简化PostgreSQL查询以计算跑步平均配速？

可以通过单次聚合查询完成所有计算，避免重复扫描表和多层嵌套子查询，大幅简化语句同时提升效率：

SELECT TO_CHAR(
         MAKE_INTERVAL(mins => SUM(EXTRACT(EPOCH FROM run_time)/60) / SUM(distance)),
         'MI:SS'
       ) AS avg_pace_per_mile
FROM runs
WHERE run_date BETWEEN '2023-12-04' AND '2023-12-05';

逻辑说明： #

• 一次聚合计算核心值
  • SUM(EXTRACT(EPOCH FROM run_time)/60)：将所有跑步时间转换为总分钟数（EXTRACT(EPOCH FROM run_time)获取总秒数，除以60转为分钟）
  • SUM(distance)：统计指定日期范围内的总英里数
• 计算并格式化平均配速
  • 用总分钟数除以总英里数得到每英里的平均分钟数，通过MAKE_INTERVAL转换为时间间隔类型
  • 最后用TO_CHAR格式化为mm:ss格式的字符串

结果验证： #

代入示例数据计算：

• 总距离：10+10=20英里
• 总时间：95+101=196分钟
• 平均配速：196/20=9.8分钟 = 9分钟48秒，与预期的09:48完全一致

注：若run_time为字符串类型，需先转换为TIME类型（如run_time::TIME）再执行上述计算。

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