Postgres continuous cumulative count

Question

assuming table a with data

| id | timestamp   |  
|----|-------------|
| 1  | 12345677677 |  
| 2  | 12345677677 |  
| 3  | 12346600000 |

I nee a query which returns the cumulative count at a given point in time

| timestamp   | count| 
|-------------|------|
| 12345677677 | 2    | 
| 12346600000 | 3    |

so not the count() group by timestamp, but the count() group by timestamp + previous counts.

Thanks to @klin this works perfectly; However with the window function, I have a problem getting the distinct count(*) per day together with the cumulative sum in ONE query.

SELECT date_trunc('day', date_created) AS date, 
       sum, 
       count(*) 
FROM   addon_user, 
       (SELECT DISTINCT ON(date_trunc('day', date_created)) 
       date_trunc('day', date_created), 
       count(*) 
       OVER ( 
         ORDER BY date_trunc('day', date_created)) AS sum 
        FROM   addon_user
) AS sub 

GROUP  BY date, 
          sum 
ORDER  BY date ASC

returns the Cartesian product:

timestamp  | count |sum
-------------+-------+---
12345677677 |     2|2
12345677677 |     2|5
12346600000 |     3|2
12346600000 |     3|5

while I need the data in form of

timestamp  | count |sum
-------------+-------+---
12345677677 |     2|2
12346600000 |     3|5

klin · Accepted Answer · 2017-11-13 07:45:23Z

3

Use count() as a window function with the order by clause. There is a nice feature in Postgres, distinct on to eliminate duplicate rows:

select distinct on(timestamp) timestamp, count(*) over (order by timestamp)
from my_table;

  timestamp  | count 
-------------+-------
 12345677677 |     2
 12346600000 |     3
(2 rows)

answered Nov 13, 2017 at 7:45

klin

123k15 gold badges240 silver badges262 bronze badges

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

3 Comments

user39950 Over a year ago

Thank you, but now I get the cartesian product with count and sum, see my edited question :)

user330315 Over a year ago

@user39950: of course you get a cartesian product because you are not joining the addon_user table and your derived table (sub-query).

klin Over a year ago

@user39950 - When you use two tables in the FROM clause without a join condition you always get a cartesian product. It's not clear to me why did you place the query in the FROM clause and where the column sum come from. Maybe you should ask a new question with full example input data and expected result.

Collectives™ on Stack Overflow

Postgres continuous cumulative count

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related