Count cumulative total in Postgresql

by

With larger datasets, window functions are the most efficient way to perform these kinds of queries — the table will be scanned only once, instead of once for each date, like a self-join would do. It also looks a lot simpler. 🙂 PostgreSQL 8.4 and up have support for window functions.

This is what it looks like:

SELECT created_at, sum(count(email)) OVER (ORDER BY created_at)
FROM subscriptions
GROUP BY created_at;

Here OVER creates the window; ORDER BY created_at means that it has to sum up the counts in created_at order.

Edit: If you want to remove duplicate emails within a single day, you can use sum(count(distinct email)). Unfortunately this won’t remove duplicates that cross different dates.

If you want to remove all duplicates, I think the easiest is to use a subquery and DISTINCT ON. This will attribute emails to their earliest date (because I’m sorting by created_at in ascending order, it’ll choose the earliest one):

SELECT created_at, sum(count(email)) OVER (ORDER BY created_at)
FROM (
    SELECT DISTINCT ON (email) created_at, email
    FROM subscriptions ORDER BY email, created_at
) AS subq
GROUP BY created_at;

If you create an index on (email, created_at), this query shouldn’t be too slow either.

(If you want to test, this is how I created the sample dataset)

create table subscriptions as
   select date '2000-04-04' + (i/10000)::int as created_at,
          '[email protected]' || (i%700000)::text as email
   from generate_series(1,1000000) i;
create index on subscriptions (email, created_at);

More Related Contents:

  1. Aggregate columns with additional (distinct) filters
  2. PostgreSQL: running count of rows for a query ‘by minute’
  3. Two SQL LEFT JOINS produce incorrect result
  4. Create a pivot table with PostgreSQL
  5. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  6. Return multiple columns of the same row as JSON array of objects
  7. How to include “zero” / “0” results in COUNT aggregate?
  8. GROUP BY + CASE statement
  9. Best way to count rows by arbitrary time intervals
  10. Selecting data into a Postgres array
  11. Compute percents from SUM() in the same SELECT sql query
  12. Get the distinct sum of a joined table column
  13. Aggregate a single column in query with many columns
  14. Multiple array_agg() calls in a single query
  15. Postgres window function and group by exception
  16. Truncate timestamp to arbitrary intervals
  17. Select first row in each GROUP BY group?
  18. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  19. How to use RETURNING with ON CONFLICT in PostgreSQL?
  20. Optimal way to concatenate/aggregate strings
  21. PostgreSQL DISTINCT ON with different ORDER BY
  22. Delete duplicate rows (don’t delete all duplicate)
  23. subquery in FROM must have an alias
  24. Insert an image in postgresql database
  25. Perform this hours of operation query in PostgreSQL
  26. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  27. How to use an ALIAS in a PostgreSQL ORDER BY clause?
  28. Postgresql Column Not Found, But Shows in Describe
  29. SQL where joined set must contain all values but may contain more
  30. How to detect query which holds the lock in Postgres?

Leave a Comment