PostgreSQL中的条件累积平均值(Conditional Cumulative Average in PostgreSQL)

 我有一个简单的表，它是： 
 
    DROP TABLE IF EXISTS running_averages;

    CREATE TABLE running_averages
    (
            avg_id          SERIAL NOT NULL PRIMARY KEY,
            num1             integer,
            num2             integer   DEFAULT 0     

    );

    INSERT INTO running_averages(num1, num2)
    SELECT 100, 100 UNION ALL
    SELECT 200, 175 UNION ALL
    SELECT -400, NULL UNION ALL
    SELECT 300, 200 UNION ALL
    SELECT -100, NULL;
 
 在上表中，如果列“num1”是负值，则应使用前一行的累积平均值更新“num2”列。 我目前的查询是： 
 
    SELECT *,
            num1 * num2 AS current_total,
            SUM(num1 * num2) OVER(order by avg_id) AS cumulative_sum,
            SUM(num1)  OVER(order by avg_id) AS culmulative_num1,

            CASE WHEN num1 > 0 THEN
            SUM(num1 * num2) OVER(order by avg_id) 
            / 
            SUM(num1)  OVER(order by avg_id) 
            ELSE
            0
            END AS cumulative_average
    FROM running_averages;
 
 结果： 
 
avg_id  num1  num2    current_total cumulative_sum   cumulative_num1 cumulative_average
1       100   100     10,000        10,000           100             100
2       200   175     35,000        45,000           300             150
3       -400          NULL          45,00            -100            0
4       300   200     60,000        105,000          200             525
5       -100          NULL          105,000          100               0
 
 如果当前行的num1列是负数，我无法弄清楚如何带上一行的累积平均值 。 而不是上述，预期输出应为： 
 
avg_id  num1  num2    current_total cumulative_sum   cumulative_num1 cumulative_average
1       100   100     10,000        10,000           100             100
2       200   175     35,000        45,000           300             150
3       -400  150     -60,000       -15,00           -100            150
4       300   200     60,000        45,000           200             225
5       -100  225     -22,500       22,500           100             225
 
 在这种情况下，如何获取最后一行的列的值？ 
 
 编辑： 
 
 我编辑了上面的SQL脚本。 我非常喜欢Gordon Linoff的回答。 但是根据脚本的变化，遗憾的是会产生错误的结果： 
 
avg_id  num1  num2    new_num2
1       100   100     100
2       200   175     175
3       -400  150     150 (Correct)
4       300   200     200
5       -100  225     50  (Incorrect)
 
 编辑2 
 
 我还测试了Multisync的答案，它也产生了错误的结果： 
 
avg_id  num1  num2              current_total cumulative_sum   cumulative_num1 cumulative_average
1       100   100               10,000        10,000           100             100
2       200   175               35,000        45,000           300             150
3       -400  150 (Correct)     -60,000       -15,00           -100            150
4       300   200               60,000        45,000           200             225
5       -100  175 (Incorrect)   -17,500       27,500           100             275
 
 编辑3 
 
 我接受了Multisync的更新答案，因为它产生了正确的结果。 我还想知道如何改进这样的查询，我们有很多聚合和窗口函数。 有关此主题的任何参考都会有所帮助。 

I have a simple table, which is:
 
    DROP TABLE IF EXISTS running_averages;

    CREATE TABLE running_averages
    (
            avg_id          SERIAL NOT NULL PRIMARY KEY,
            num1             integer,
            num2             integer   DEFAULT 0     

    );

    INSERT INTO running_averages(num1, num2)
    SELECT 100, 100 UNION ALL
    SELECT 200, 175 UNION ALL
    SELECT -400, NULL UNION ALL
    SELECT 300, 200 UNION ALL
    SELECT -100, NULL;
 
In the above table the "num2" column should be updated with the cumulative average of the previous row if the column "num1" is a negative value. My current query is :
 
    SELECT *,
            num1 * num2 AS current_total,
            SUM(num1 * num2) OVER(order by avg_id) AS cumulative_sum,
            SUM(num1)  OVER(order by avg_id) AS culmulative_num1,

            CASE WHEN num1 > 0 THEN
            SUM(num1 * num2) OVER(order by avg_id) 
            / 
            SUM(num1)  OVER(order by avg_id) 
            ELSE
            0
            END AS cumulative_average
    FROM running_averages;
 
The result:
 
avg_id  num1  num2    current_total cumulative_sum   cumulative_num1 cumulative_average
1       100   100     10,000        10,000           100             100
2       200   175     35,000        45,000           300             150
3       -400          NULL          45,00            -100            0
4       300   200     60,000        105,000          200             525
5       -100          NULL          105,000          100               0
 
I cannot figure out the way to bring the cumulative average of the previous row if the current row's num1 column is a negative number. Instead of the above, the expected output should be :
 
avg_id  num1  num2    current_total cumulative_sum   cumulative_num1 cumulative_average
1       100   100     10,000        10,000           100             100
2       200   175     35,000        45,000           300             150
3       -400  150     -60,000       -15,00           -100            150
4       300   200     60,000        45,000           200             225
5       -100  225     -22,500       22,500           100             225
 
How can I get the value of the last row's column in this case?
 
Edit:
 
I edited the SQL Script above. I quite like the approach of Gordon Linoff's answer. But it sadly produces incorrect result as per the script change:
 
avg_id  num1  num2    new_num2
1       100   100     100
2       200   175     175
3       -400  150     150 (Correct)
4       300   200     200
5       -100  225     50  (Incorrect)
 
Edit 2
 
I also tested answer of Multisync, it also produces wrong result:
 
avg_id  num1  num2              current_total cumulative_sum   cumulative_num1 cumulative_average
1       100   100               10,000        10,000           100             100
2       200   175               35,000        45,000           300             150
3       -400  150 (Correct)     -60,000       -15,00           -100            150
4       300   200               60,000        45,000           200             225
5       -100  175 (Incorrect)   -17,500       27,500           100             275
 
Edit 3
 
I have accepted the updated answer of Multisync, since it produces correct results. I would also like to know on how I can improve queries like this where we have a lot of aggregate and window functions. Any reference on this topic will be helpful.

原文：https://stackoverflow.com/questions/26961586