CODEWITHSUNDEEP
postgresqlsumcumulative-sum
user39950
user39950

Reputation: 473

Postgres continuous cumulative count

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

Upvotes: 0

Views: 1073

Answers (1)

klin
klin

Reputation: 121604

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)

Upvotes: 3

Related Questions