Referencing current row in FILTER clause of window function

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_last4daysFROM  (   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)   ) ctJOIN  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:

• Select finishes where athlete didn't finish first for the past 3 events
• PostgreSQL: running count of rows for a query 'by minute'
• PostgreSQL unnest() with element number

I don't think there is any syntax that means "current row" in an expression. The gram.y file for postgres makes a filter clausetake just an a_expr, which is just the normal expression clauses. Thereis nothing specific to window functions or filter clauses in an expression.As far as I can find, the only current row notion in a window clause is for specifying the window frame boundaries. I don't think this gets youwhat you want.

It's possible that you could get some traction from an enclosing query:

http://www.postgresql.org/docs/current/static/sql-expressions.html

When an aggregate expression appears in a subquery (see Section 4.2.11 and Section 9.22), the aggregate is normally evaluated over the rows of the subquery. But an exception occurs if the aggregate's arguments (and filter_clause if any) contain only outer-level variables: the aggregate then belongs to the nearest such outer level, and is evaluated over the rows of that query.

but it's not obvious to me how.