Referencing current row in FILTER clause of window function

by

You are not actually aggregating rows, so the new aggregate FILTER clause is not the right tool. A window function is more like it, a problem remains, however: the frame definition of a window cannot depend on values of the current row. It can only count a given number of rows preceding or following with the ROWS clause.

To make that work, aggregate counts per day and LEFT JOIN to a full set of days in range. Then you can apply a window function:

SELECT t.*, ct.ct_last4days
FROM  (
   SELECT *, sum(ct) OVER (ORDER BY dt ROWS 3 PRECEDING) AS ct_last4days
   FROM  (
      SELECT generate_series(min(dt), max(dt), interval '1 day')::date AS dt
      FROM   tbl t1
      ) d
   LEFT   JOIN (SELECT dt, count(*) AS ct FROM tbl GROUP BY 1) t USING (dt)
   ) ct
JOIN  tbl t USING (dt);

Omitting ORDER BY dt in the widow frame definition usually works, since the order is carried over from generate_series() in the subquery. But there are no guarantees in the SQL standard without explicit ORDER BY and it might break in more complex queries.

SQL Fiddle.

Related:

More Related Contents:

  1. PostgreSQL unnest() with element number
  2. Dynamic alternative to pivot with CASE and GROUP BY
  3. PostgreSQL: running count of rows for a query ‘by minute’
  4. Calculating Cumulative Sum in PostgreSQL
  5. GROUP BY and aggregate sequential numeric values
  6. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  7. Get the distinct sum of a joined table column
  8. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  9. Window Functions or Common Table Expressions: count previous rows within range
  10. Postgres window function and group by exception
  11. How to convert PostgreSQL 9.4’s jsonb type to float
  12. How to group timestamps into islands (based on arbitrary gap)?
  13. Operator does not exist: json = json
  14. How to use a ring data structure in window functions
  15. Conditional lead/lag function PostgreSQL?
  16. Aggregate columns with additional (distinct) filters
  17. Computed / calculated / virtual / derived columns in PostgreSQL
  18. Insert data in 3 tables at a time using Postgres
  19. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  20. sql ORDER BY multiple values in specific order?
  21. PostgreSQL: Which Datatype should be used for Currency?
  22. Finding similar strings with PostgreSQL quickly
  23. NOT NULL constraint over a set of columns
  24. postgresql – sql – count of `true` values
  25. Optimize groupwise maximum query
  26. Use email address as primary key?
  27. Creating temporary tables in SQL
  28. PostgreSQL where all in array
  29. Get execution time of PostgreSQL query
  30. Conditional SQL count

Leave a Comment