如何简化Bixi数据集按年月统计日均出行量的SQL查询

简化方案 #

你原有的写法通过两个独立子查询分别统计2016、2017年数据再做关联，存在重复扫描表、扩展性差的问题，可以根据你需要的输出格式选择对应简化写法：

1. 按「年-月」单行输出（匹配统计每个年月组合日均出行的核心需求）

不需要做任何表关联，单次聚合即可完成计算，写法最简洁：

SELECT
  YEAR(start_date) AS trip_year,
  MONTH(start_date) AS trip_month,
  ROUND(COUNT(*) / COUNT(DISTINCT DATE(start_date)), 0) AS avg_daily_trips
FROM trips
GROUP BY trip_year, trip_month
ORDER BY trip_year, trip_month;

2. 保留原写法同年份数据横向并排的展示效果

如果需要把不同年份的同月份数据放在同一行对比，用条件聚合替代多子查询JOIN，仅需扫描一次表，性能更好，后续新增年份统计也更方便：

SELECT
  MONTH(start_date) AS trip_month,
  ROUND(
    SUM(CASE WHEN YEAR(start_date) = 2016 THEN 1 ELSE 0 END)
    / COUNT(DISTINCT CASE WHEN YEAR(start_date) = 2016 THEN DATE(start_date) END),
  0) AS avg_daily_trips_2016,
  ROUND(
    SUM(CASE WHEN YEAR(start_date) = 2017 THEN 1 ELSE 0 END)
    / COUNT(DISTINCT CASE WHEN YEAR(start_date) = 2017 THEN DATE(start_date) END),
  0) AS avg_daily_trips_2017
FROM trips
WHERE YEAR(start_date) IN (2016, 2017)
GROUP BY trip_month
ORDER BY trip_month;

优化说明 #

• 修正了原写法中COUNT(DISTINCT DAY(start_date))的逻辑漏洞：DAY()仅返回日期中的「日」部分，跨月的同一天（如5月1日、6月1日）会被识别为同一个值，改用DATE(start_date)去重可以准确统计有出行记录的自然日数量
• 仅对trips表做一次扫描，数据量较大时性能远高于多次子查询+JOIN的写法
• 后续需要新增其他年份的统计时，只需要新增一个条件聚合的字段即可，不需要调整关联逻辑，维护成本更低

补充：如果业务要求计算日均时，当月无出行记录的自然日也要计入分母（即除以当月实际总天数，而非有出行的天数），可以把分母替换为对应数据库获取当月天数的函数，例如MySQL中可以用DAY(LAST_DAY(start_date))直接获取当月总天数。

内容的提问来源于stack exchange，提问作者Alfredo Di Massimo