Oracle SQL基于指定日期的W/A类型订单重叠行标记需求

需求说明 #

扩展原有逻辑，基于指定日期实现以下标记规则：

• W类型订单始终标记为PASS
• A类型订单仅当指定日期落在该A订单的日期区间内，且该日期不与任何W类型订单的区间重叠时标记为PASS，否则标记为NOT PASS

示例表结构 #

CREATE TABLE table_name (brand, type, start_date, end_date) AS
  SELECT 'abc', 'W', DATE '2020-01-01', DATE '2020-06-30' FROM DUAL UNION ALL
  SELECT 'abc', 'W', DATE '2020-07-01', DATE '2020-08-31' FROM DUAL UNION ALL
  SELECT 'abc', 'A', DATE '2019-01-01', DATE '2022-09-30' FROM DUAL UNION ALL
  SELECT 'abc', 'A', DATE '2019-01-01', DATE '2020-03-31' FROM DUAL

修改后的SQL解决方案 #

使用MATCH_RECOGNIZE合并W类型订单的连续日期区间，再基于指定日期判断A类型订单的标记状态：

WITH continuous_w_ranges (brand, start_date, end_date) AS (
  SELECT brand, start_date, end_date
  FROM   (
    SELECT *
    FROM   table_name
    WHERE type = 'W'
  )
  MATCH_RECOGNIZE(
    PARTITION BY brand
    ORDER BY start_date, end_date
    MEASURES
      FIRST(start_date) AS start_date,
      MAX(end_date)     AS end_date
    PATTERN ( continuing_range* next_range )
    DEFINE
      continuing_range AS MAX(end_date) + 1 >= NEXT(start_date) 
  )
)
SELECT 
  brand,
  type,
  start_date,
  end_date,
  CASE
    WHEN type = 'W' THEN 'PASS'
    WHEN type = 'A' THEN 
      CASE 
        WHEN :p_spec_date BETWEEN start_date AND end_date
             AND NOT EXISTS (
               SELECT 1 
               FROM continuous_w_ranges w
               WHERE w.brand = table_name.brand
                     AND :p_spec_date BETWEEN w.start_date AND w.end_date
             ) THEN 'PASS'
        ELSE 'NOT PASS'
      END
  END AS pass_status
FROM table_name
ORDER BY brand, start_date;

注：:p_spec_date为Oracle绑定变量，可替换为具体日期常量（如DATE '2020-01-01'）进行测试

场景验证 #

• 指定日期2020-01-01：W类型全为PASS；所有A类型订单因指定日期落在W区间内，标记为NOT PASS
• 指定日期2019-10-01：指定日期不落在任何W区间，且所有A订单区间包含该日期，所有订单标记为PASS
• 指定日期2021-10-01：W类型全为PASS；第一条A订单区间包含指定日期且不在W区间，标记PASS；第二条A订单区间不包含指定日期，标记NOT PASS

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