SQL适配指定日期规则的时间戳过滤及空值处理实现

逻辑拆解 #

你可以把过滤规则拆为两层实现：

• 基础保留规则：要么是2021-04-03之前的行（即TIMESTAMP2为空，无需校验时间），要么是2021-04-03及之后的行（TIMESTAMP2非空），满足TIMESTAMP2晚于TIMESTAMP1的校验
• 额外过滤规则：剔除TIMESTAMP1、TIMESTAMP2同时为NULL的行

原有查询的问题 #

你当前的语句WHERE coalesce(TIMESTAMP1, '1970-01-01') < TIMESTAMP2仅能匹配TIMESTAMP2非空且通过时间校验的行，会把所有2021-04-03之前的行全部误删——这类行的TIMESTAMP2为NULL，和任何值的比较结果都为NULL，不满足WHERE条件。

修正后的查询语句 #

SELECT *
FROM dt
WHERE
  -- 基础规则：4月3日前的行直接保留，或4月3日后的行通过时间校验
  (TIMESTAMP2 IS NULL OR COALESCE(TIMESTAMP1, '1970-01-01') < TIMESTAMP2)
  -- 额外规则：剔除两个时间字段同时为空的行
  AND NOT (TIMESTAMP1 IS NULL AND TIMESTAMP2 IS NULL)

关于coalesce用法的说明 #

你用COALESCE(TIMESTAMP1, '1970-01-01')处理空值的写法是正确的：

• 当TIMESTAMP1为NULL时，会被替换为远早于业务时间的1970年，天然小于任何业务期的TIMESTAMP2，这类行可以直接通过4月3日后的时间校验，符合业务要求
• 仅需注意替换的默认值要早于业务最早的时间（比如你这里最早业务时间是2021年，1970年完全不会和业务时间冲突）

示例数据核对效果 #

对照你给出的输入输出，过滤结果符合预期：

• 不触发额外过滤时，4月2日的3行TIMESTAMP2为空全部保留，和你给出的期望输出一致
• 4月3日第一行：TIMESTAMP1为18:24，TIMESTAMP2为18:22，不满足时间校验，被过滤
• 4月3日第二行：TIMESTAMP1为空，被替换为1970年，小于TIMESTAMP2，保留
• 4月4日行：TIMESTAMP1早于TIMESTAMP2，保留
• 额外过滤生效后，4月2日的002 BB行（TIMESTAMP1、TIMESTAMP2均为空）会被剔除

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