如何使用SQL不借助循环或游标实现多比例叠加的价格计算

核心思路 #

调价本质是按生效时间顺序对涨幅系数做累积乘法，直接用SQL内置的聚合逻辑/窗口函数就能实现，完全不需要循环、游标。

步骤1：整理调价规则 #

首先把调价规则存为表（临时用也可以直接用CTE生成），涨幅统一存为系数：比如上调10%存为1.1，上调5%存为1.05

-- 示例调价规则表
CREATE TABLE price_adjustments (
    adjust_date DATE PRIMARY KEY,
    adjust_rate DECIMAL(5,4) NOT NULL
);

INSERT INTO price_adjustments (adjust_date, adjust_rate) VALUES
('2021-01-01', 1.10),
('2021-02-01', 1.05),
('2021-03-01', 1.02);

步骤2：计算各调价节点的对应价格 #

主流数据库（MySQL8.0+、PostgreSQL、SQL Server等）通用写法，用对数转换实现累积乘积（SQL没有内置乘积聚合函数，利用ln(a*b) = ln(a)+ln(b)的数学特性，求和后再取指数还原）

SELECT 
    adjust_date,
    1000 * EXP(SUM(LN(adjust_rate)) OVER (ORDER BY adjust_date)) AS current_price
FROM price_adjustments
ORDER BY adjust_date;

PostgreSQL可以直接用内置的乘积聚合函数简化：1000 * product(adjust_rate) OVER (ORDER BY adjust_date)

执行后结果和要求完全匹配：

• 2021-01-01：1100
• 2021-02-01：1155
• 2021-03-01：1178.1

步骤3：计算任意日期对应的价格 #

如果要查某个日期（比如2021-02-10）对应的价格，过滤所有生效时间早等于目标日期的调价规则做聚合即可：

SELECT 
    1000 * EXP(SUM(LN(adjust_rate))) AS target_price
FROM price_adjustments
WHERE adjust_date <= '2021-02-10';

如果要给业务表批量匹配价格，比如给订单表orders的每笔订单按下单日期算对应基准价：

SELECT 
    o.order_id,
    o.order_date,
    1000 * EXP(SUM(LN(pa.adjust_rate))) AS order_base_price
FROM orders o
LEFT JOIN price_adjustments pa ON pa.adjust_date <= o.order_date
GROUP BY o.order_id, o.order_date;

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