优化累积价格计算递归查询：寻求高性能实现方案

核心问题分析 #

你的定价逻辑是按顺序叠加计算税费/利润（即((成本*税率1)*税率2)*...），原递归CTE因为逐行迭代计算，数据量大时性能极差。窗口函数尝试失败，大概率是没抓住「累积乘积」的数学本质，或是排序/分区规则未和原逻辑对齐。

优化方案 #

1. 用累积乘积窗口函数替代递归 #

叠加乘法的本质是初始成本 × 所有前置税率的乘积，因此可以用EXP(SUM(LN(rate)))将乘法转成加法计算累积乘积，配合窗口函数一次性完成计算，时间复杂度从递归的O(n²)降到O(n log n)。

优化后的查询示例：

SELECT
  pv.id AS product_variant_id,
  pv.source_country,
  pr.rule_type,
  pr.rule_order,
  -- 计算累积乘积：成本 × 从第一条到当前规则的所有税率乘积
  ROUND(
    pv.cost * EXP(SUM(LN(pr.rate)) OVER (
      PARTITION BY pv.id, pv.source_country
      ORDER BY pr.rule_order
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )),
    2
  ) AS final_price
FROM product_variants pv
JOIN pricing_rules pr ON pv.source_country = pr.country
ORDER BY pv.id, pr.rule_order;

注意事项：

• 确保pr.rate始终大于0（定价场景下税率/利润率应为正数），否则LN()会报错；若存在折扣率<1的规则，只要rate>0就不影响计算。
• 用ROUND()控制小数精度，避免浮点数运算的微小误差。

2. 适配物化视图 #

直接用上述查询创建物化视图，窗口函数的计算效率远高于递归，刷新成本极低：

CREATE MATERIALIZED VIEW pricing_engine_mv AS
SELECT
  pv.id AS product_variant_id,
  pv.source_country,
  pr.rule_type,
  pr.rule_order,
  ROUND(
    pv.cost * EXP(SUM(LN(pr.rate)) OVER (
      PARTITION BY pv.id, pv.source_country
      ORDER BY pr.rule_order
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )),
    2
  ) AS final_price
FROM product_variants pv
JOIN pricing_rules pr ON pv.source_country = pr.country;

-- 添加索引加速查询
CREATE INDEX idx_mv_variant_country ON pricing_engine_mv(product_variant_id, source_country);

如果需要增量刷新，可以基于product_variants和pricing_rules的更新时间戳，仅刷新变动的分区。

3. 适配单产品查询 #

单产品场景下，直接在优化后的查询或物化视图中添加WHERE条件即可，窗口函数会自动仅计算目标产品的分区，性能远优于递归：

-- 直接查询SQL
SELECT *
FROM (
  SELECT
    pv.id AS product_variant_id,
    pv.source_country,
    pr.rule_type,
    pr.rule_order,
    ROUND(
      pv.cost * EXP(SUM(LN(pr.rate)) OVER (
        PARTITION BY pv.id, pv.source_country
        ORDER BY pr.rule_order
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      )),
      2
    ) AS final_price
  FROM product_variants pv
  JOIN pricing_rules pr ON pv.source_country = pr.country
) AS pricing
WHERE product_variant_id = '目标产品ID';

-- 或查询物化视图（更快）
SELECT * FROM pricing_engine_mv WHERE product_variant_id = '目标产品ID';

性能验证 #

原递归CTE会多次扫描表并逐行迭代，而窗口函数仅需一次表扫描+排序操作，数据量越大，性能差距越明显。对比原查询计划和优化后的计划，会发现递归的Recursive Union开销完全消失，取而代之的是高效的WindowAgg操作。

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