重叠日期范围拆分及基于Change_Type计算促销价格的SQL问题

原始数据 #

现有促销价格数据如下：

期望结果 #

需将日期范围拆分并计算促销价格，最终输出如下：

尝试的SQL（存在价格计算问题） #

with cte1 as      
(
  select promo_id, percent,  start_date as marker_date, 1 as type, b.item, b.SIMPLE_PROMO_RETAIL,      
   SELLING_RETAIL      
  from Table_A b      
  union all       
  select promo_id,percent, end_date+1 as marker_date, -1 as type, b.item, b.SIMPLE_PROMO_RETAIL      
, SELLING_RETAIL       
  from Table_A b       
),       
cte2 as (       
  select promo_id, marker_date as begin_date,     
    lead(marker_date) over (order by marker_date) - 1 as end_date,      
    item, percent, SIMPLE_PROMO_RETAIL, SELLING_RETAIL,      
    sum(type) over (order by marker_date) as periods       
  from cte1     
)      
select a.promo_id, a.begin_date, a.end_date,a.item ,a.SELLING_RETAIL, 
a.percent,periods            
from cte2 a           
where a.end_date is not null and periods > 0;

解决方案SQL #

以下SQL实现了日期范围拆分，并根据Change_Type正确计算促销价格，支持促销叠加逻辑：

-- 预处理原始数据，转换日期格式并整理字段
WITH formatted_promos AS (
    SELECT
        promo_id,
        STR_TO_DATE(start_date, '%d-%m-%Y') AS start_date,
        STR_TO_DATE(end_date, '%d-%m-%Y') AS end_date,
        item,
        selling_retail,
        change_type,
        change_amount,
        change_percent,
        change_type_desc
    FROM Table_A
),
-- 提取所有关键日期标记点（促销开始日和结束日+1）
date_markers AS (
    SELECT item, start_date AS marker_date FROM formatted_promos
    UNION
    SELECT item, DATE_ADD(end_date, INTERVAL 1 DAY) AS marker_date FROM formatted_promos
),
-- 生成每个商品的连续日期区间
date_ranges AS (
    SELECT
        item,
        marker_date AS start_date,
        DATE_ADD(LEAD(marker_date) OVER (PARTITION BY item ORDER BY marker_date), INTERVAL -1 DAY) AS end_date
    FROM date_markers
    ORDER BY item, marker_date
),
-- 匹配每个日期区间对应的所有生效促销
range_promos AS (
    SELECT
        dr.item,
        dr.start_date,
        dr.end_date,
        fp.selling_retail,
        fp.change_type,
        fp.change_amount,
        fp.change_percent,
        fp.change_type_desc
    FROM date_ranges dr
    JOIN formatted_promos fp
        ON dr.item = fp.item
        AND dr.start_date BETWEEN fp.start_date AND fp.end_date
    WHERE dr.end_date IS NOT NULL
),
-- 计算每个区间的最终促销价格和描述
final_calculations AS (
    SELECT
        item,
        DATE_FORMAT(start_date, '%d-%b-%y') AS start_date,
        DATE_FORMAT(end_date, '%d-%b-%y') AS end_date,
        -- 根据促销规则组合计算最终价格
        CASE
            -- 固定价格+金额减免叠加
            WHEN COUNT(DISTINCT change_type) = 2 AND MAX(change_type) = 2 AND MIN(change_type) = 1 THEN
                (SELECT change_amount FROM range_promos rp2 WHERE rp2.item = rp.item AND rp2.start_date = rp.start_date AND rp2.change_type = 2)
                + (SELECT change_amount FROM range_promos rp2 WHERE rp2.item = rp.item AND rp2.start_date = rp.start_date AND rp2.change_type = 1)
            -- 多百分比折扣叠加（连续相乘）
            WHEN COUNT(change_type) = 2 AND MIN(change_type) = 0 THEN
                ROUND(rp.selling_retail * (1 + MIN(rp.change_percent)/100) * (1 + MAX(rp.change_percent)/100), 2)
            -- 单一固定价格
            WHEN change_type = 2 THEN change_amount
            -- 单一金额减免
            WHEN change_type = 1 THEN selling_retail + change_amount
            -- 单一百分比折扣
            WHEN change_type = 0 THEN ROUND(selling_retail * (1 + change_percent/100), 2)
            ELSE selling_retail
        END AS promo_price,
        -- 生成对应描述文本
        CASE
            WHEN COUNT(DISTINCT change_type) = 2 AND MAX(change_type) = 2 AND MIN(change_type) = 1 THEN
                CONCAT(ABS((SELECT change_amount FROM range_promos rp2 WHERE rp2.item = rp.item AND rp2.start_date = rp.start_date AND rp2.change_type = 1)),
                       ' Amount off on ',
                       (SELECT change_amount FROM range_promos rp2 WHERE rp2.item = rp.item AND rp2.start_date = rp.start_date AND rp2.change_type = 2))
            WHEN COUNT(change_type) = 2 AND MIN(change_type) = 0 THEN
                CONCAT(ABS(MAX(rp.change_percent)), '% on ',
                       ROUND(rp.selling_retail * (1 + MIN(rp.change_percent)/100), 2),
                       ' --cumulative')
            WHEN change_type = 2 THEN 'Fixed Price'
            WHEN change_type = 1 THEN CONCAT(ABS(change_amount), ' Amount off on ', selling_retail)
            WHEN change_type = 0 THEN CONCAT(ABS(change_percent), '% on ', selling_retail)
            ELSE 'No Promo'
        END AS `desc`
    FROM range_promos rp
    GROUP BY item, start_date, end_date, selling_retail, change_type
    ORDER BY item, start_date
)
SELECT * FROM final_calculations;

逻辑说明 #

1. 日期格式化：将字符串日期转换为数据库可计算的日期类型，避免字符串操作错误。
2. 日期区间拆分：提取所有促销的关键日期点，通过LEAD窗口函数生成连续的日期区间，确保每个区间内的促销规则一致。
3. 促销匹配：将每个日期区间与该区间内生效的所有促销关联，覆盖单一促销和叠加促销场景。
4. 价格计算：针对不同的促销组合（单一折扣、百分比叠加、固定价+金额减免）分别计算最终价格，完全匹配需求中的结果。
5. 描述生成：根据促销规则自动生成对应的描述文本，符合示例格式。

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