替代多WITH子查询：Cyclistic共享单车按季节统计SQL优化方案

你可以通过CASE表达式映射月份到季节，再结合GROUP BY实现单次表扫描的聚合，替代原来多次子查询+UNION ALL的冗余写法，代码更简洁且性能更优：

SELECT
    CASE
        WHEN extract(month FROM started_at) BETWEEN 6 AND 8 THEN 'summer'
        WHEN extract(month FROM started_at) BETWEEN 9 AND 11 THEN 'autumn'
        WHEN extract(month FROM started_at) IN (12, 1, 2) THEN 'winter'
        WHEN extract(month FROM started_at) BETWEEN 3 AND 5 THEN 'spring'
    END AS season,
    avg(ride_duration) AS avg_ride_duration,
    max(ride_duration) AS max_ride_duration,
    count(ride_id) AS ride_count,
    mode() WITHIN GROUP (ORDER BY day_of_week) AS mode_day_of_week
FROM trip_data_total
GROUP BY season
ORDER BY ride_count DESC

关键优化点说明： #

• 减少表扫描次数：原写法需要对trip_data_total表扫描4次（每个季节子查询一次），优化后仅扫描1次，大幅提升查询效率，尤其是数据量较大时效果明显。
• 消除代码冗余：将重复的聚合逻辑统一，仅通过CASE表达式区分季节，代码更易维护，后续调整季节划分规则时只需修改CASE部分即可。
• 逻辑一致性：聚合函数（avg、max、count、mode）与原查询完全一致，保证统计结果和原写法相同。

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