如何用单条SQL查询多日期范围平均配速并保证结果顺序

一、单条查询实现本周&上周配速（保证顺序） #

你的UNION写法存在重复代码、自动去重（可能丢失数据）、无法稳定控制顺序的问题。更简洁高效的方式是按周分组计算，仅需扫描一次表：

PostgreSQL 示例 #

SELECT
  -- 计算每英里平均配速并格式化
  to_char(sum(run_time) / sum(distance), 'mi:ss') AS avg_pace,
  -- 标记周起始日期（用于排序和区分）
  date_trunc('week', run_date) AS week_start
FROM runs
-- 筛选上周和当前周的数据
WHERE run_date >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
  AND run_date < date_trunc('week', CURRENT_DATE) + INTERVAL '1 week'
-- 按周分组
GROUP BY date_trunc('week', run_date)
-- 强制排序：首行对应上周、第二行对应当前周（若要反过来则用DESC）
ORDER BY week_start ASC;

Oracle 示例（适配日期函数） #

SELECT
  to_char(numtodsinterval(sum(extract(second from run_time) + extract(minute from run_time)*60 + extract(hour from run_time)*3600)/sum(distance), 'second'), 'mi:ss') AS avg_pace,
  trunc(run_date, 'IW') AS week_start
FROM runs
WHERE run_date >= trunc(SYSDATE, 'IW') - 7
  AND run_date < trunc(SYSDATE, 'IW') + 7
GROUP BY trunc(run_date, 'IW')
ORDER BY week_start ASC;

二、扩展到未来4周的简化写法 #

如果要计算未来4周的配速，不建议用IN枚举日期范围（灵活性差），更推荐用CTE生成周列表后关联查询，即使某周无跑步数据也能返回结果：

PostgreSQL 生成未来4周配速 #

WITH future_weeks AS (
  -- 生成未来4周的周起始日期（0=下周，3=未来第4周）
  SELECT date_trunc('week', CURRENT_DATE) + INTERVAL '1 week' * n AS week_start
  FROM generate_series(0, 3) n
)
SELECT
  w.week_start,
  -- 无数据时返回默认配速00:00
  COALESCE(to_char(sum(r.run_time)/sum(r.distance), 'mi:ss'), '00:00') AS avg_pace
FROM future_weeks w
-- 关联跑步数据
LEFT JOIN runs r ON date_trunc('week', r.run_date) = w.week_start
GROUP BY w.week_start
ORDER BY w.week_start ASC;

若一定要用IN简化（针对固定周） #

如果是明确指定特定周，IN可以替代多个BETWEEN，写法更紧凑：

SELECT
  to_char(sum(run_time)/sum(distance), 'mi:ss') AS avg_pace,
  date_trunc('week', run_date) AS week_start
FROM runs
WHERE date_trunc('week', run_date) IN (
  date_trunc('week', CURRENT_DATE) + INTERVAL '1 week',
  date_trunc('week', CURRENT_DATE) + INTERVAL '2 weeks',
  date_trunc('week', CURRENT_DATE) + INTERVAL '3 weeks',
  date_trunc('week', CURRENT_DATE) + INTERVAL '4 weeks'
)
GROUP BY date_trunc('week', run_date)
ORDER BY week_start ASC;

关键优化点 #

• 分组查询仅扫描一次表，比UNION系列写法性能更优（数据量大时差异明显）
• 用ORDER BY强制控制结果顺序，避免UNION的无序问题
• 用COALESCE处理无数据的周，保证结果行数稳定

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