CODEWITHSUNDEEP
postgresqlwindow-functions
user1697111
user1697111

Reputation: 199

PostgreSQL, two windowing functions at once

I have typical table with data, say mytemptable. 

DROP TABLE IF EXISTS mytemptable; 
CREATE TEMP TABLE mytemptable 
       (mydate date, somedoc text, inqty int, outqty int); 
INSERT INTO mytemptable (mydate, somedoc, inqty, outqty) 
VALUES ('01.01.2016.', '123-13-24', 3, 0), 
       ('04.01.2016.', '15-19-44',  2, 0), 
       ('06.02.2016.', '15-25-21',  0, 1), 
       ('04.01.2016.', '21-133-12', 0, 1), 
       ('04.01.2016.', '215-11-51', 0, 2), 
       ('05.01.2016.', '11-181-01', 0, 1), 
       ('05.02.2016.', '151-80-8',  4, 0), 
       ('04.01.2016.', '215-11-51', 0, 2), 
       ('07.02.2016.', '34-02-02',  0, 2); 

SELECT row_number() OVER(ORDER BY mydate) AS rn, 
       mydate, somedoc, inqty, outqty, 
       SUM(inqty-outqty) OVER(ORDER BY mydate) AS csum 
  FROM mytemptable 
 ORDER BY mydate;

In my SELECT query I try to order result by date and add row numbers 'rn' and cumulative (passing) sum 'csum'. Of course unsuccessfully.
I believe this is because I use two windowing functions in query which conflicts in some way.

How to properly make this query to be fast, well ordered and to get proper result in 'csum' column (3, 5, 4, 2, 0, -1, 3, 2, 0)

Upvotes: 2

Views: 188

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

Since there is an ordering tie at 2016-04-01 the result for those rows will be the total accumulated sum. If you want it to be different use untie columns in the order by.

From the manual:

There is another important concept associated with window functions: for each row, there is a set of rows within its partition called its window frame. Many (but not all) window functions act only on the rows of the window frame, rather than of the whole partition. By default, if ORDER BY is supplied then the frame consists of all rows from the start of the partition up through the current row, plus any following rows that are equal to the current row according to the ORDER BY clause. When ORDER BY is omitted the default frame consists of all rows in the partition

Without an untieing column you can use the generated row number in an outer query:

set datestyle = 'dmy';
with mytemptable (mydate, somedoc, inqty, outqty) as (
    values
    ('01-01-2016'::date, '123-13-24', 3, 0), 
    ('04-01-2016', '15-19-44',  2, 0), 
    ('06-02-2016', '15-25-21',  0, 1), 
    ('04-01-2016', '21-133-12', 0, 1), 
    ('04-01-2016', '215-11-51', 0, 2), 
    ('05-01-2016', '11-181-01', 0, 1), 
    ('05-02-2016', '151-80-8',  4, 0), 
    ('04-01-2016', '215-11-51', 0, 2), 
    ('07-02-2016', '34-02-02',  0, 2)
)
select *, sum(inqty-outqty) over(order by mydate, rn) as csum
from (
    select
        row_number() over(order by mydate) as rn, 
        mydate, somedoc, inqty, outqty
    from mytemptable
) s
order by mydate;
 rn |   mydate   |  somedoc  | inqty | outqty | csum 
----+------------+-----------+-------+--------+------
  1 | 2016-01-01 | 123-13-24 |     3 |      0 |    3
  2 | 2016-04-01 | 15-19-44  |     2 |      0 |    5
  3 | 2016-04-01 | 21-133-12 |     0 |      1 |    4
  4 | 2016-04-01 | 215-11-51 |     0 |      2 |    2
  5 | 2016-04-01 | 215-11-51 |     0 |      2 |    0
  6 | 2016-05-01 | 11-181-01 |     0 |      1 |   -1
  7 | 2016-05-02 | 151-80-8  |     4 |      0 |    3
  8 | 2016-06-02 | 15-25-21  |     0 |      1 |    2
  9 | 2016-07-02 | 34-02-02  |     0 |      2 |    0

Upvotes: 3

Related Questions