问题的答案

问题的历史

运行乘积的需求源于定量金融中的复利计算，在概率理论中用于链式事件可能性，以及在工程中进行累积故障率分析。与无处不在的 SUM() 或 AVG() 聚合不同，ANSI SQL 历史上缺乏一个原生的 PRODUCT() 窗口函数，迫使从业者自1990年代早期以来设计变通方案。早期的解决方案依赖递归公共表表达式，但在大型数据集上表现出性能限制。对数变换方法作为一种基于集合的替代方案出现，但引入了关于零和负数处理的复杂性，这仍然是当今常见的面试主题。

问题

计算运行乘积需要将分区开始到当前行的所有值相乘。数学挑战在于乘法不像加法那样是幂等的，并且在大型序列中浮点溢出很快发生。在 ANSI SQL 中，缺少内置聚合意味着开发人员必须使用递归公共表表达式——逐行处理并否定基于集合的优化——或者应用对数恒等式，将乘积转换为使用 EXP(SUM(LN(x))) 的和。然而，对数方法在处理非正数（零或负数）时失败惨重，因此需要一个强大的符号跟踪机制和零检测逻辑以保持数学准确性。

解决方案

一种混合方法结合了窗口函数以获得基于集合的性能和条件逻辑以处理边缘情况。首先，将每个数字分解为其绝对值和符号（1、-1 或 0）。在窗口上使用 SUM() 来计算绝对值的对数，然后进行指数运算。单独跟踪累积符号乘积，使用 CASE 表达式适当地翻转符号，并使用运行标志在任何前值为零时将结果清零。这样可以保持 ANSI SQL 合规，同时实现 O(n log n) 复杂性。

WITH decomposed AS (
  SELECT 
    id,
    grp,
    val,
    CASE 
      WHEN val = 0 THEN 0
      WHEN val < 0 THEN -1
      ELSE 1 
    END AS sign_factor,
    CASE 
      WHEN val = 0 THEN NULL
      ELSE LN(ABS(val)) 
    END AS log_val
  FROM measurements
),
running_calc AS (
  SELECT 
    id,
    grp,
    val,
    MIN(CASE WHEN val = 0 THEN 0 ELSE 1 END) 
      OVER (PARTITION BY grp ORDER BY id) AS has_no_zero,
    CASE 
      WHEN SUM(CASE WHEN sign_factor = -1 THEN 1 ELSE 0 END) 
        OVER (PARTITION BY grp ORDER BY id) % 2 = 0 
      THEN 1 
      ELSE -1 
    END AS running_sign,
    SUM(log_val) 
      OVER (PARTITION BY grp ORDER BY id) AS sum_log
  FROM decomposed
)
SELECT 
  id,
  grp,
  val,
  CASE 
    WHEN has_no_zero = 0 THEN 0
    ELSE running_sign * EXP(sum_log)
  END AS running_product
FROM running_calc;

生活中的情况

一家零售银行需要计算连续风险调整对投资组合估值的累积影响，其中每天的乘数取决于存储在 ANSI SQL 表中的市场波动系数。挑战在于处理 "市场冻结" 天（零乘数）和负向修正（反转），而不将数百万行导出到 Python，因为合规部门要求在数据库中维护完整的数据线索以供审计使用。

第一种方法考虑使用 Pandas 将数据提取到应用程序服务器，该库提供简单的 .cumprod() 功能和丰富的调试工具。然而，这引入了网络延迟和提取窗口期间的一致性风险，违反了实时合规报告的要求，并在数据传输期间造成潜在的安全漏洞。

第二个解决方案使用递归公共表表达式逐行迭代，用递归成员的自连接将前一个结果与当前值相乘。虽然在数学上简单和准确，但这迫使单线程执行，并在分区超过一万行时导致堆栈深度错误，使其不适合银行十年来跨越数百万交易的历史数据集。

第三个解决方案实现了带有显式符号跟踪和零检测的对数窗口函数方法，允许 RDBMS 优化器使用并行排序-合并操作和索引。这使得在不到三秒的时间内完成对五千万条记录的计算，尽管这需要仔细处理浮点边缘情况和符号跟踪逻辑，这增加了对于初级开发人员的维护复杂性。

该方法因其基于集合的效率和严格遵守 ANSI SQL 标准而被选中，确保在 PostgreSQL、Oracle 和 DB2 平台间的可移植性而无需代码更改。银行优先考虑亚秒级响应时间和数据一致性而非实现复杂性，因为风险部门要求在市场波动激增期间对复合调整的即时可见性。

结果使银行能够部署一个实时风险仪表板，准确反映包括全额冲销（零）和修正（负数）在内的复合调整。监管审计人员批准了该方法，因为它在数据库层中维护了完整的数据线索，消除了与外部统计软件包相关的黑箱风险，并确保了合规审查的可重现性。

候选人常常忽视的内容

当运行乘积超过浮点最大可表示值时，您如何确保数值稳定性？

候选人经常建议使用 DOUBLE PRECISION，而没有考虑对数缩放或对数基变换。在 ANSI SQL 中，您可以使用自然对数 LN() 和 EXP() 转换计算，但对于极大的产品，您应该通过除以一个常数因子进行归一化，或使用 LOG() 以 10 为基数单独跟踪数量级。更稳健的做法是在对数空间中存储结果（分贝或对数点），而不是转换回线性尺度，在最终检索时只需进行指数运算即可，防止溢出，代价是需要在用户呈现时才应用 EXP()。

分区内行的顺序如何影响运行乘积的精度， ANSI SQL 又是如何解决关联浮点漂移的？

浮点乘法因舍入误差而不是严格关联； (a * b) * c 在处理非标准数字或规模相差悬殊的值时可能产生与 a * (b * c) 略有不同的结果。由于 ANSI SQL 窗口函数通过 ORDER BY 子句保证了确定性排序，但不保证特定的关联分组，因此漂移在每个查询计划中是确定的，但可能因 RDBMS 优化而有所不同。为减轻此问题，候选人应提到在计算之前将其转换为 DECIMAL 或 NUMERIC 类型，并具有显式精度，尽管这以性能换取准确性，或者实施 Kahan 求和适应，用于乘法序列。

在计算概率值（如乘以许多小概率，如 0.001）时，如何修改方法以考虑到零下溢出问题？

完全在对数概率空间中工作可以防止下溢。在每一行将对数和回归回线性尺度而不是在每一行中都进行指数化，只需将结果保持为对数的和（负数代表小概率）。当需要比较或阈值时，在对数空间中进行比较，利用如下性质：LOG(a) > LOG(b) 则 a > b。仅在最终向用户展示时应用 EXP()，确保乘以数百个小概率不会因浮点限制而归零，这对于 ANSI SQL 环境中的机器学习评分模型至关重要。

阐明计算有序分区的运行乘积的方法，同时正确处理零交叉和负值，而无需诉诸于过程逻辑。

问题的答案

生活中的情况

候选人常常忽视的内容