跨年度场景下如何用SQL计算上一季度交易总和？

核心问题分析 #

原来的SQL仅通过季度数值对比（QUARTER(date) = 目标季度-1），当目标季度是Q1时，目标季度-1等于0，无法匹配上一年Q4的季度值4，且完全没考虑年份差异，导致跨年场景失效。

解决方案一：通过年份+季度组合值匹配 #

将年份和季度转换为一个可计算的数值（如年份*4 + 季度），通过对比该数值是否等于目标日期的对应值减1，来精准定位上一季度的交易数据：

SUM(CASE 
    WHEN YEAR(date)*4 + QUARTER(date) = YEAR('2022-05-31')*4 + QUARTER('2022-05-31') - 1
    THEN amount 
    ELSE 0 
END) as prev_q_sum

逻辑说明 #

• 对于目标日期2022-05-31（Q2），组合值为2022*4+2=8090，减1后是8089，对应2022Q1的组合值2022*4+1=8089
• 对于目标日期2022-01-31（Q1），组合值为2022*4+1=8089，减1后是8088，对应2021Q4的组合值2021*4+4=8088
• 无论是否跨年，组合值的对比都能精准匹配上一季度的数据

解决方案二：通过上一季度日期区间筛选 #

先计算目标日期对应的上一季度的起止日期，再筛选交易日期落在该区间内的数据求和，这种方法更直观，且如果date字段有索引，性能更优：

PostgreSQL 示例 #

SUM(CASE 
    WHEN date >= DATE_TRUNC('quarter', '2022-05-31'::DATE) - INTERVAL '3 months'
     AND date < DATE_TRUNC('quarter', '2022-05-31'::DATE)
    THEN amount 
    ELSE 0 
END) as prev_q_sum

MySQL 8.0+ 示例 #

SUM(CASE 
    WHEN date >= DATE_TRUNC('QUARTER', '2022-05-31') - INTERVAL 3 MONTH
     AND date < DATE_TRUNC('QUARTER', '2022-05-31')
    THEN amount 
    ELSE 0 
END) as prev_q_sum

逻辑说明 #

• DATE_TRUNC('quarter', 目标日期)会返回目标日期所在季度的第一天（如2022-05-31返回2022-04-01）
• 减去3个月得到上一季度的第一天（2022-01-01），用< 当前季度第一天来确保只包含上一季度的所有日期

注意事项 #

• 如果date字段是字符串类型，需先通过TO_DATE(date, '对应格式')转换为日期类型，避免类型不匹配错误
• 不同SQL方言的日期函数可能略有差异（如Oracle用ADD_MONTHS、TRUNC），可根据使用的数据库调整函数写法

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