2

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:

id | var | num | calc | sum of f(r.var, w.var, w.num) 1 | 0.1 | 7 | ... | iif(0.1<=0.1,0.1,0.1)*7 2 | 0.7 | 1 | ... | iif(0.7<=0.1,0.7,0.1)*7 + iif(0.7<=0.7,0.7,0.7)*1 3 | 0.3 | 9 | ... | iif(0.3<=0.1,0.3,0.1)*7 + iif(0.3<=0.7,0.3,0.7)*1 + iif(0.3<=0.3,0.3,0.3)*9 4 | 0.9 | 5 | ... | iif(0.9<=0.7,0.9,0.7)*1 + iif(0.9<=0.3,0.9,0.3)*9 + iif(0.9<=0.9,0.9,0.9)*5 5 | 0.5 | 3 | ... | iif(0.5<=0.3,0.5,0.3)*9 + iif(0.5<=0.9,0.5,0.9)*5 + iif(0.5<=0.5,0.5,0.5)*3

Basically I want to calculate running sum of an expression that references var column in the current row and var column in the current window. iif and multiplication is just an example.

The solution should work in SQL Server but generic answer would be best.

Here is a db<>fiddle where I was able to achieve the result with correlated queries but I want to use window functions.

Improve this question
3
  • It could be arbitrary number of rows e.g. 100 or there could be no ROWS BETWEEN at all. The values do not matter, one can post the output that proves that SUM was calculated for each row in window based on the rows inside and outside the window. Commented Nov 20, 2019 at 10:15
  • This is interesting problem. Definitely not easy when the window size is arbitrary and not just 3 rows. Commented Nov 25, 2022 at 0:07
  • Despite you calling it a "running sum" it is in no sense a running sum because you aren't just adding something to the previous total Commented Aug 28, 2023 at 3:24

2 Answers 2

Reset to default
1

I can't think of a way to solve this using window functions, so here is a method that uses a triangular self-join.

Tested in dbfiddle.uk

WITH tt (id, var, num, rn) AS ( SELECT id, var, num, ROW_NUMBER() OVER (ORDER BY id) AS rn 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) ) SELECT tt.id, tt.var, tt.num, SUM( LEAST(tt.var, ti.var) * ti.num ) AS calc -- SUM( CASE WHEN tt.var < ti.var THEN tt.var ELSE ti.var END -- * ti.num ) AS calc FROM tt JOIN tt AS ti ON ti.rn >= tt.rn -2 -- BETWEEN 2 PRECEDING AND ti.rn <= tt.rn +0 -- AND CURRENT ROW GROUP BY tt.rn, tt.id, tt.var, tt.num ;

The LEAST() function is available only in SQL Server 2022, so for older versions, the CASE expression should be used instead.

Improve this answer
0

Is this what you are looking for ?

create table #t(id int, vars decimal(3,1), num int) insert into #t VALUES (1, 0.1, 7), (2, 0.7, 1), (3, 0.3, 9), (4, 0.9, 5), (5, 0.5, 3) ;With CTE as ( select t.id ,t.vars ,t.num ,isnull(lag(vars ,1) OVER (ORDER BY id ),0) AS vars1 ,isnull(lag(vars ,2) OVER (ORDER BY id ),0) AS vars2 ,isnull(lag(num ,1) OVER (ORDER BY id ),0) AS num1 ,isnull(lag(num ,2) OVER (ORDER BY id ),0) AS num2 FROM #t t ) select * ,iif(vars<=0.1,vars,vars2)*num2 + iif(vars<=vars1,vars,vars1)*num1 + iif(vars<=vars,vars,vars)*num from CTE drop table #t
Improve this answer
2
  • I cannot hard code 1 and 2... the size of window could be anything... even unbounded. Commented Nov 20, 2019 at 13:02
  • @SalmanA, do you mean to say my output is correct ?You should explain the biz. reuirement for size of window or unbounded.How do I understand size of window ? Commented Nov 20, 2019 at 16:55

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.