Use expression in window function that references columns from the current row
2
votes
2
answers
570
views
Suppose I have the following query that uses window function:
SELECT id
, var
, num
, SUM(var * num) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS calc
FROM (VALUES
(1, 0.1, 7),
(2, 0.7, 1),
(3, 0.3, 9),
(4, 0.9, 5),
(5, 0.5, 3)
) AS t(id, var, num)
And the following result:
id | var | num | calc | explanation
1 | 0.1 | 7 | 0.7 | 0.1*7
2 | 0.7 | 1 | 1.4 | 0.1*7 + 0.7*1
3 | 0.3 | 9 | 4.1 | 0.1*7 + 0.7*1 + 0.3*9
4 | 0.9 | 5 | 7.9 | 0.7*1 + 0.3*9 + 0.9*5
5 | 0.5 | 3 | 8.7 | 0.3*9 + 0.9*5 + 0.5*3
Is is possible to reference the var
column _from the outside_ inside the SUM() OVER ()
? For example:
```none
id | var | num | calc | sum of f(r.var, w.var, w.num)
1 | 0.1 | 7 | ... | iif(0.1fiddle][1] where I was able to achieve the result with correlated queries but I want to use window functions.
Asked by Salman Arshad
(461 rep)
Nov 20, 2019, 09:23 AM
Last activity: Sep 8, 2024, 12:03 AM
Last activity: Sep 8, 2024, 12:03 AM