如何仅通过start_date字段查询指定日期范围内的有效折扣数据

由于数据表仅存start_date字段，默认业务规则为单条折扣的生效结束日期为下一条折扣起始日期的前一天，最后一条无后续记录的折扣默认设置最大可用日期作为结束时间即可。

实现步骤 #

• 第一步：使用窗口函数LEAD()为每条折扣记录计算对应的生效结束日期end_date
• 第二步：按需求匹配符合条件的discount_nr，满足任意一条规则即可命中：
  1. 折扣生效区间与2020-07-01至2020-07-15存在重叠（即该时间段内处于生效状态）
  2. 折扣的起始生效日期恰好为2020-07-14

参考SQL（PostgreSQL环境） #

WITH discount_with_end AS (
    SELECT 
        discount_nr,
        start_date,
        -- 取下一条记录的start_date减1天作为当前记录的end_date，最后一条记录默认用9999-12-31作为结束日期
        COALESCE(LEAD(start_date) OVER (ORDER BY start_date) - INTERVAL '1 day', '9999-12-31'::DATE) AS end_date
    FROM 你的实际表名
)
SELECT discount_nr
FROM discount_with_end
WHERE 
    -- 条件1：生效区间和目标查询范围重叠
    (start_date <= '2020-07-15' AND end_date >= '2020-07-01')
    -- 条件2：7月14日当日生效
    OR start_date = '2020-07-14';

示例数据执行结果 #

针对你给出的测试数据，执行上述SQL后返回的符合要求的discount_nr结果为：1、2、3、4

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