SQL:计算 Postgres 中引用其自身行的新列

Question

假设我有下表，其中 percent_leave 是前一时期离开的人在当前时期离开的百分比:

| Period | percent_leave  |
----------------------
| 1      | 0.05           |
| 2      | 0.05           | 
| 3      | 0.05           |
| 4      | 0.05           | 

我想计算一个新列，其中包含该时期结束时剩余的人数百分比。例如，如果我们从 100 人开始，第一期有 5 人离开，因此我们剩下 95 人。95 人中的 5% 会在第二期离开，这就剩下 90.25 人，依此类推。然后表格看起来像:

| Period | percent_leave  | percent_remaining
-----------------------------------------
| 1      | 0.05           | 0.95
| 2      | 0.05           | 0.9025
| 3      | 0.05           | 0.857375
| 4      | 0.05           | 0.81450625

如您所见，percent_remaining 中新行的计算引用了 percent_remaining 中的前一行。通常我会将这些原始数据导出到 Excel 中并在那里进行计算，但我想在 SQL 中自动执行此任务，因此我需要弄清楚如何在 Postgres 中执行此操作。

有什么想法吗？

Answer 1

您可以使用累加和来执行此操作。 . .和一些算术:

select t.*,
       exp(sum(ln(1-percent_leave)) over (order by period))
from t;

这实际上是将 product() 实现为一个窗口函数。这假定 percent_leave 始终小于 1 且大于或等于 0。