我在Redshift中要做一件有趣的事情。说,我有一个这样的表:
index,total,delta
0,3,null
1,5,2
2,10,5
3,11,1
4,null,4
5,null,6
6,null,2
delta
每total
行与上一行的区别在哪里total
?在这里,我的delta
s与我的s来自不同的来源total
,因此可以在delta
不接收更新的total
s的情况下接收s。
如何total
根据delta
s估算s?像这样:
index,total,delta
0,3,null
1,5,2
2,10,5
3,11,1
4,15,4
5,21,6
6,23,2
我在附近的某个地方乱七八糟NVL(total, LAST_VALUE(total IGNORE NULLS) OVER (ORDER BY index ROWS UNBOUNDED PRECEDING) + SUM(delta) OVER (ORDER BY index ROWS UNBOUNDED PRECEDING)
,但这并没有做到-我只想找没有对应SUM
的delta
s total
。
您可以使用:
-- creating subgroups
WITH cte AS (
SELECT *, SUM(total IS NOT NULL::int) OVER(ORDER BY index) s
FROM tab
)
SELECT index,
CASE WHEN total IS NULL
THEN SUM(COALESCE(total,0) + delta) OVER(PARTITION BY s ORDER BY index)
-FIRST_VALUE(delta) OVER(PARTITION BY s ORDER BY index)
-- running total starting from first not null total + delta
-- decreased by first delta
ELSE total
END AS total
,delta
FROM cte
ORDER BY index;
输出:
┌────────┬────────┬───────┐
│ index │ total │ delta │
├────────┼────────┼───────┤
│ 0 │ 3 │ │
│ 1 │ 5 │ 2 │
│ 2 │ 10 │ 5 │
│ 3 │ 11 │ 1 │
│ 4 │ 15 │ 4 │
│ 5 │ 21 │ 6 │
│ 6 │ 23 │ 2 │
└────────┴────────┴───────┘
编辑:
实际上,不需要FIRST_VALUE
:
WITH cte AS (
SELECT *, SUM(total IS NOT NULL::int) OVER(ORDER BY index) s
FROM tab
)
SELECT index,
CASE WHEN total IS NULL
THEN SUM(COALESCE(total,0)
+ CASE WHEN total IS NOT NULL THEN 0 ELSE delta END)
OVER(PARTITION BY s ORDER BY index)
ELSE total
END AS total
,delta
FROM cte
ORDER BY index;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句