postgresql - 将所有行的前一行结果相乘

Question

如何动态乘以前一行的结果：

row A col_3 = if (row A col_1 <= 1 then 1), else  (1 *  col_2)
row B col_3 = if (row B col_1 <= 1 then 1), else  (row A *  col_2)
row C col_3 = if (row C col_1 <= 1 then 1), else  (row B *  col_2)

我已经在 Postgres 中尝试过这个。这里是

sum (
    case
        when col_1 <= 1 then 1
        else (lag(col_3) over (...)) * col_2 <-- I'm aware you cannot use a lag function within a sum/window function
    end
) over (order by ...) as col_3

注意：我在这里问了一个类似的问题（感谢@Bergi！），但我不确定如何为此目的实现该答案。

---

编辑：

逻辑：

if (previous_interval is null) previous_interval = 1

if (curr_repetition = 1) interval = 1
else if (curr_repetition = 2) interval = 6
else interval = previous_interval * easiness

标签： postgresql