PostgreSQL, two windowing functions at once

Question

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)

Clodoaldo Neto · Accepted Answer · 2016-08-10 11:20:42Z

3

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

edited Aug 10, 2016 at 11:20

answered Aug 10, 2016 at 7:46

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1697111 Over a year ago

That sounds very scientific. But you get result same as is mine, say incorrect or unwanted. I can get wanted result with two queries where first add row number then in second I ORDER BY rn, mydate. But I though maybe some may do this with a single query. Real data is large and performance is important.

Clodoaldo Neto Over a year ago

@user1697111 Updated

user1697111 Over a year ago

Thanks Colorado. This seems to be solution. Now I have to study it. But result are as expected. I have to note that on my system and pgAdmin III line "set date style = 'dmy';" causes syntax error. I can write dates in eurpoean style without casting "::date" too. Is this query faster than two separate queries or it takes same time?

Clodoaldo Neto Over a year ago

@user1697111 It is faster than two separate queries. It is datestyle without space. Fixed now. That date style setting is necessary for those using the ISO style.

Collectives™ on Stack Overflow

PostgreSQL, two windowing functions at once

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related