我有一个简单的表格,它是:
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的更新答案,因为它产生了正确的结果。我也想知道如何改进像这样有很多聚合和窗口函数的查询。任何关于这个主题的参考资料都会很有帮助。