Oracle SQL技术求助：基于促销日期拆分生效与终止日期记录

测试表DDL #

首先创建测试表并插入示例数据：

CREATE TABLE test_promo (
    id NUMBER,
    eff DATE,
    disc DATE,
    promo_prep DATE,
    promostart DATE
);

INSERT INTO test_promo VALUES (1, DATE '2024-01-01', DATE '2024-12-31', DATE '2024-06-01', DATE '2024-07-01');
INSERT INTO test_promo VALUES (2, DATE '2024-02-01', DATE '2024-11-30', DATE '2024-08-01', DATE '2024-09-01');
INSERT INTO test_promo VALUES (3, DATE '2024-03-01', DATE '2024-10-31', DATE '2024-05-01', DATE '2024-04-01');

解决方案SQL #

使用UNION ALL结合条件判断实现区间拆分，同时保留不符合拆分规则的原始记录：

WITH split_records AS (
    -- 生成拆分后的第一条记录：将原disc替换为promo_prep
    SELECT 
        id,
        eff AS eff,
        promo_prep AS disc,
        promo_prep,
        promostart
    FROM test_promo
    WHERE promo_prep BETWEEN eff AND disc 
      AND promostart BETWEEN eff AND disc

    UNION ALL

    -- 生成拆分后的第二条记录：以promo_prep为新eff，保留原disc
    SELECT 
        id,
        promo_prep AS eff,
        disc AS disc,
        promo_prep,
        promostart
    FROM test_promo
    WHERE promo_prep BETWEEN eff AND disc 
      AND promostart BETWEEN eff AND disc

    UNION ALL

    -- 保留不需要拆分的原始记录
    SELECT 
        id,
        eff,
        disc,
        promo_prep,
        promostart
    FROM test_promo
    WHERE NOT (promo_prep BETWEEN eff AND disc AND promostart BETWEEN eff AND disc)
)
SELECT id, eff, disc
FROM split_records
ORDER BY id, eff;

逻辑说明 #

• 拆分触发条件：仅当promo_prep处于原eff与disc区间内，且promostart也落在该区间内时，才执行拆分
• 拆分规则：
  1. 第一条拆分记录：生效日期保留原eff，终止日期替换为promo_prep
  2. 第二条拆分记录：生效日期设为promo_prep，终止日期保留原disc
• 非拆分场景：不符合拆分条件的记录直接保留原始eff与disc值

示例输出 #

内容的提问来源于stack exchange，提问作者B-Rad