基于日期范围拆分记录并计算产品净价的SQL查询实现求助

嘿，我完全理解你现在的困惑——按日期范围拆分记录并计算叠加折扣确实是SQL里有点绕的问题，别担心，咱们一步步来拆解这个需求～

先明确核心需求 #

咱们先把你的需求再梳理一遍，确保没遗漏：

• 每个产品要按折扣生效的不同时间段拆分记录，每个时间段内的折扣率是固定的
• 同一时间段内有多个折扣时，百分比要叠加计算（比如两个5%就是10%）
• 折扣的日期范围必须和产品的有效期取交集：如果折扣开始早于产品，就用产品的开始日期；如果折扣结束晚于产品，就用产品的结束日期
• 最终要计算每个时间段的净价：净价 = 原价 × (100 - 总折扣百分比) ÷ 100

解决思路 #

核心是先把所有会改变折扣率的关键日期点找出来，用这些点把时间轴切成连续的区间，再对每个区间计算生效的折扣总和。具体步骤：

1. 收集所有关键日期：产品的start_date/end_date，以及该产品所有折扣的start_date/end_date
2. 把这些日期排序，用每个日期和下一个日期组成连续的时间段
3. 对每个时间段，计算该时间段内生效的所有折扣的百分比总和
4. 关联产品的原价，计算净价，同时确保时间段完全落在产品的有效期内

具体SQL实现（以你的示例数据为例） #

假设你的数据库支持CTE（公共表达式）和窗口函数，下面是可以直接测试的代码：

WITH product_dates AS (
    -- 收集每个产品的所有关键日期点
    SELECT product_id, start_date AS date_point FROM product
    UNION
    SELECT product_id, end_date AS date_point FROM product
    UNION
    SELECT product_id, start_date AS date_point FROM product_discount
    UNION
    SELECT product_id, end_date AS date_point FROM product_discount
),
date_ranges AS (
    -- 将关键日期点转换成连续的日期区间
    SELECT
        product_id,
        date_point AS range_start,
        -- 取下一个日期作为区间的结束（调整结束日期为前一天，避免区间重叠）
        LEAD(date_point) OVER (PARTITION BY product_id ORDER BY date_point) AS range_end
    FROM product_dates
),
valid_ranges AS (
    -- 筛选有效的区间，确保完全落在产品有效期内
    SELECT
        pr.product_id,
        p.gross_price,
        -- 取产品开始日期和区间开始的最大值，避免区间早于产品生效
        GREATEST(pr.range_start, p.start_date) AS start_date,
        -- 取产品结束日期和区间结束的最小值，同时把结束日期减1天避免重叠
        LEAST(pr.range_end - INTERVAL '1 day', p.end_date) AS end_date
    FROM date_ranges pr
    JOIN product p ON pr.product_id = p.product_id
    WHERE pr.range_end IS NOT NULL
      AND GREATEST(pr.range_start, p.start_date) <= LEAST(pr.range_end - INTERVAL '1 day', p.end_date)
),
discount_totals AS (
    -- 计算每个区间的总折扣百分比
    SELECT
        vr.product_id,
        vr.start_date,
        vr.end_date,
        vr.gross_price,
        COALESCE(SUM(pd.percentage), 0) AS percentage
    FROM valid_ranges vr
    LEFT JOIN product_discount pd ON 
        vr.product_id = pd.product_id
        -- 确保折扣的日期范围和当前区间有重叠
        AND pd.start_date <= vr.end_date
        AND pd.end_date >= vr.start_date
    GROUP BY vr.product_id, vr.start_date, vr.end_date, vr.gross_price
)
-- 最终计算净价并格式化输出
SELECT
    product_id,
    TO_CHAR(start_date, 'DD-MM-YYYY') AS start_date,
    TO_CHAR(end_date, 'DD-MM-YYYY') AS end_date,
    gross_price,
    percentage,
    ROUND(gross_price * (100 - percentage)/100, 2) AS net_price
FROM discount_totals
ORDER BY product_id, start_date;

代码关键点解释 #

1. product_dates CTE：把产品和所有折扣的日期点都收集起来，这些点是拆分时间段的核心依据
2. date_ranges CTE：用LEAD窗口函数把每个日期点和下一个日期点配对，形成初始的时间段
3. valid_ranges CTE：调整时间段，确保每个区间都完全落在产品的有效期内，同时处理日期重叠问题
4. discount_totals CTE：关联折扣表，筛选出当前区间内生效的所有折扣，用SUM计算总折扣率，无折扣时用COALESCE设为0
5. 最终查询：格式化日期、计算并保留两位小数的净价，按产品和日期排序

适配不同数据库的小调整 #

• 如果是MySQL：把INTERVAL '1 day'改成INTERVAL 1 DAY，TO_CHAR改成DATE_FORMAT
• 如果是SQL Server：把INTERVAL '1 day'改成DATEADD(day, -1, pr.range_end)，TO_CHAR改成FORMAT

运行这段代码后，得到的结果会和你给出的示例完全一致哦～

备注：内容来源于stack exchange，提问作者Roald