running sum with window function

Question

I have the following data in a table:

col1 --- 1 2 5 9 10

I want to update col2 in the table with the running sum of the difference between col1 and the previous value of col1 minus 1

col2 = col2.prev + col1 - col1.prev - 1

The result would be:

col1 | col2 -------------- 1 | 0 2 | 0 5 | 2 9 | 5 10 | 5

I tried using a window function:

SELECT sum(col1 - lag(col1) OVER (ORDER BY col1) - 1) AS col2 FROM table1

But this is not allowed - ERROR: aggregate function calls cannot contain window function calls

Is there another way I can accomplish this? I know I could easily write a function to loop through the rows but I get the impression from what I've read that this method is not efficient and discouraged in most cases. Please correct me if I have the wrong impression.

can you confirm the output in COL2 should be- 00255 OR 00355 — mkRabbani
– mkRabbani, Commented May 14, 2019 at 6:36
it is 00255 - since 0+5-2-1=2, 2+9-5-1=5, 5+10-9-1=5, and so on... — iamthebull
– iamthebull, Commented May 14, 2019 at 6:44

Haleemur Ali · Accepted Answer · 2019-05-14 07:35:48Z

ERROR: aggregate function calls cannot contain window function calls

This error message is displayed because it is not permitted to apply an aggregate function on a column generated through a windowed expression. Nor is it permitted to apply a second windowed expression. The solution is to simply wrap the result in a cte & apply the second windowed expression in a subsequent select statement.

WITH mytable(col1) AS ( VALUES (1), (2), (5), (9), (10) ) , lagdiff AS ( SELECT col1 , COALESCE(col1 - lag(col1) OVER (ORDER BY col1) - 1, 0) col2_ FROM mytable ) SELECT col1 , SUM(col2_) OVER (ORDER BY col1) col2 FROM lagdiff

Produces Output:

 col1 | col2 ------+------ 1 | 0 2 | 0 5 | 2 9 | 5 10 | 5 (5 rows)

cte is an initialism for common table expression, which is a sub query that sits ahead of the main query rather than directly in the from clause.

Collectives™ on Stack Overflow

running sum with window function

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related