Sample Header Ad - 728x90

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