0

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.

  • 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. – Salman A Nov 20 '19 at 10:15
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|||||
  • I cannot hard code 1 and 2... the size of window could be anything... even unbounded. – Salman A Nov 20 '19 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 ? – KumarHarsh Nov 20 '19 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.