如何统计指定年份范围内的记录？基于DATE字段的年份计数需求

需求概述 #

需要统计指定年份（1985、1986、1987）内，被AD_Start_Date和AD_End_Date覆盖的记录数量，每个年份对应一个衍生字段（Year_1985、Year_1986、Year_1987）单独计数。表中AD_Start_Date和AD_End_Date均为DATE类型，测试记录的计数规则如下：

• 记录1：AD_Start_Date='01/30/1980'，AD_End_Date='07/01/1990' → 计入Year_1985、Year_1986、Year_1987
• 记录2：AD_Start_Date='03/30/1985'，AD_End_Date='07/01/1985' → 仅计入Year_1985
• 记录3：AD_Start_Date='02/01/1978'，AD_End_Date='07/01/1990' → 计入Year_1985、Year_1986、Year_1987
• 记录4：AD_Start_Date='05/01/1986'，AD_End_Date='11/30/1987' → 计入Year_1986、Year_1987

现有SQL尝试 #

我写了下面的SQL，但不确定是否能正确实现需求：

SELECT
SUM(CASE WHEN to_char(AD_Start_Date, 'MM/DD/YYYY') <= '12/31/1985' AND 
 to_char(AD_End_Date, 'MM/DD/YYYY') >= '01/01/1985' THEN 1 ELSE 0 END) AS Year_1985

,SUM(CASE WHEN to_char(AD_Start_Date, 'MM/DD/YYYY') <= '12/31/1986' AND 
to_char(AD_End_Date, 'MM/DD/YYYY') >= '01/01/1986' THEN 1 ELSE 0 END) AS Year_1986

,SUM(CASE WHEN to_char(AD_Start_Date, 'MM/DD/YYYY') <= '12/31/1987' AND 
to_char(AD_End_Date, 'MM/DD/YYYY') >= '01/01/1987' THEN 1 ELSE 0 END) AS Year_1987

问题分析与修正 #

现有SQL的隐患 #

把DATE类型转成字符串比较存在风险：如果数据库默认日期格式和你指定的MM/DD/YYYY不一致，字符串比较会直接出错；另外字符串的字典序和日期的时间序并不总是匹配（比如某些格式下'01/01/2020'和'12/31/2019'的字符串比较结果不符合日期逻辑）。应该直接用DATE类型字面量做比较，避免多余的类型转换。

正确的SQL写法 #

核心逻辑是判断记录的日期区间与目标年份的区间是否存在重叠。目标年份的区间是该年1月1日到12月31日，重叠的判定条件为：AD_Start_Date <= 当年最后一天 且 AD_End_Date >= 当年第一天。

优化后的SQL：

SELECT
  SUM(CASE WHEN AD_Start_Date <= DATE '1985-12-31' AND AD_End_Date >= DATE '1985-01-01' THEN 1 ELSE 0 END) AS Year_1985,
  SUM(CASE WHEN AD_Start_Date <= DATE '1986-12-31' AND AD_End_Date >= DATE '1986-01-01' THEN 1 ELSE 0 END) AS Year_1986,
  SUM(CASE WHEN AD_Start_Date <= DATE '1987-12-31' AND AD_End_Date >= DATE '1987-01-01' THEN 1 ELSE 0 END) AS Year_1987
FROM your_table_name; -- 替换成你的实际表名

测试验证 #

用你的测试数据验证结果：

• Year_1985：记录1、2、3符合条件 → 计数3
• Year_1986：记录1、3、4符合条件 → 计数3
• Year_1987：记录1、3、4符合条件 → 计数3
  完全匹配需求的计数规则。

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