如何在CASE中使用BETWEEN运算符处理无起止日的日期计数需求

需求背景 #

原始数据包含起止日期字段（oa.entity_created 为起始日，oa.lagg 为结束日），需要对日期范围做如下调整：

• 起始日：原起始日+1
• 结束日：原结束日-1；若原结束日为blank，则替换为当前日期（current_date）

原始数据 #

oa.entity_created            oa.lagg

  id 1|  start |2022-07-20     end |2022-08-15
  id 2|  start |2022-06-15     end |2022-08-09
  id 3|  start |2022-08-12     end |blank

目标调整后的数据 #

oa.entity_created            oa.lagg

   id 1| start |2022-07-21     end |2022-08-14
   id 2| start |2022-06-16     end |2022-08-08
   id 3| start |2022-08-13     end |blank

尝试的错误语句 #

用户曾尝试以下计数语句，但未得到正确结果：

count(case when (oa.entity_created < cred.entity_created and oa.lagg > cred.entity_created) then 1 end) 

问题 #

如何在CASE语句中使用BETWEEN运算符实现符合日期范围调整要求的计数？

解决方案 #

要实现需求，需先调整原始日期范围，再用BETWEEN判断目标日期是否落在调整后的区间内，具体步骤如下：

1. 日期范围调整逻辑 #

• 调整后的起始日：原起始日加1天（不同SQL语法有差异，标准SQL用INTERVAL '1 day'，MySQL用DATE_ADD函数）
• 调整后的结束日：若原结束日为blank（实际SQL中通常为NULL），则替换为当前日期；否则原结束日减1天

2. 结合CASE与BETWEEN的计数语句 #

标准SQL版本

COUNT(
  CASE 
    WHEN cred.entity_created BETWEEN (oa.entity_created + INTERVAL '1 day') 
                                 AND (COALESCE(oa.lagg, CURRENT_DATE) - INTERVAL '1 day')
    THEN 1 
  END
)

MySQL版本（语法适配）

COUNT(
  CASE 
    WHEN cred.entity_created BETWEEN DATE_ADD(oa.entity_created, INTERVAL 1 DAY) 
                                 AND DATE_SUB(COALESCE(oa.lagg, CURDATE()), INTERVAL 1 DAY)
    THEN 1 
  END
)

关键说明 #

• COALESCE(oa.lagg, CURRENT_DATE)：自动处理结束日为blank的情况，将其替换为当前日期
• BETWEEN ... AND ...：直接判断cred.entity_created是否落在调整后的[起始日+1, 结束日-1]闭区间内，逻辑等价于原语句的> 起始日且< 结束日，但写法更简洁清晰

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