Oracle中重叠促销日期范围拆分及累计折扣计算问题

现有一商品原价为229，存在以下重叠促销活动：

需要拆分为如下格式（重叠期需累计计算折扣：先10%再20%）：

你已通过SQL完成日期拆分，但无法计算Promo_Retail字段，以下是补充完整逻辑的实现：

WITH cte_markers AS (
    -- 提取所有促销的起始、结束+1日期作为区间分割点
    SELECT start_date AS marker_date FROM Table_A
    UNION
    SELECT end_date + 1 AS marker_date FROM Table_A
),
cte_date_ranges AS (
    -- 生成连续无重叠的日期区间
    SELECT 
        marker_date AS start_date,
        LEAD(marker_date) OVER (ORDER BY marker_date) - 1 AS end_date
    FROM cte_markers
    WHERE LEAD(marker_date) OVER (ORDER BY marker_date) IS NOT NULL
),
cte_active_promos AS (
    -- 关联区间与生效的促销，计算累计折扣和说明文本
    SELECT 
        dr.start_date,
        dr.end_date,
        ta.Discount_pecent,
        ta.Original_Retail,
        -- 计算累计折扣后的最终价格（用EXP+SUM+LN替代PRODUCT，兼容多数数据库）
        ta.Original_Retail * EXP(SUM(LN(1 - ta.Discount_pecent / 100.0)) OVER (PARTITION BY dr.start_date, dr.end_date)) AS final_price,
        -- 生成折扣应用的说明链
        STRING_AGG(
            CONCAT(ta.Discount_pecent, '% applied on ', 
                   ROUND(ta.Original_Retail * EXP(SUM(LN(1 - t2.Discount_pecent / 100.0)) OVER (PARTITION BY dr.start_date, dr.end_date ORDER BY t2.Discount_pecent ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)), 1)),
            ' then '
        ) OVER (PARTITION BY dr.start_date, dr.end_date ORDER BY ta.Discount_pecent) AS price_note
    FROM cte_date_ranges dr
    JOIN Table_A ta 
        ON dr.start_date <= ta.End_Date AND dr.end_date >= ta.Start_date
),
cte_final AS (
    -- 聚合区间结果，匹配示例输出格式
    SELECT 
        start_date,
        end_date,
        MAX(Discount_pecent) AS Discount_pecent,
        MAX(Original_Retail) AS Original_Retail,
        CONCAT(ROUND(final_price, 1), 
               CASE WHEN COUNT(*) OVER (PARTITION BY start_date, end_date) > 1 THEN 
                   CONCAT(' (', LAST_VALUE(price_note) OVER (PARTITION BY start_date, end_date), ')') 
               ELSE '' END) AS Promo_Retail
    FROM cte_active_promos
    GROUP BY start_date, end_date, final_price
)
SELECT * FROM cte_final ORDER BY start_date;

关键逻辑说明： #

• 区间分割：通过cte_markers提取所有促销的时间节点，用LEAD生成连续无重叠的日期区间。
• 累计折扣计算：用EXP(SUM(LN(折扣系数)))替代部分数据库不支持的PRODUCT函数，实现折扣的累计相乘。
• 说明文本生成：通过窗口函数的STRING_AGG和LAST_VALUE，拼接出折扣应用的递进说明，匹配示例格式。

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