PostgreSQL – RANGE Clause Error in Window Function

postgresqlpostgresql-9.3window functions

I'm trying to use postgres' RANGE clause within a window function to dynamically set the window to the past 4 weeks not including the current date. Here is my attempt at it:

SELECT date("aa0"."created_at") AS "Day of Created At",
       "aa0"."user_id" AS "User Id",
       count (*) activities_today,
       count(*) over (PARTITION BY "aa0"."user_id"
                      ORDER BY date("aa0"."created_at") ASC
                      RANGE BETWEEN date("aa0"."created_at" - interval '4 weeks')
                                    AND 
                                    date("aa0"."created_at" - interval '1 day')
                ) active_days_past_4_weeks
FROM "public"."activity_activity" AS "aa0"
GROUP BY date("aa0"."created_at"), "aa0"."user_id" 
ORDER BY "Day of Created At" ASC LIMIT 1000;

However, I get the following error:

ERROR: syntax error at or near ")" Position: 459

If I use ROWS instead of RANGE it works but this is not the correct logic since it's not guaranteed that there will be a row for every day:

SELECT date("aa0"."created_at") AS "Day of Created At",
       "aa0"."user_id" AS "User Id",
       count(*) activities_today,
       count(*) over (PARTITION BY "aa0"."user_id"
                      ORDER BY date("aa0"."created_at") ASC
                      ROWS BETWEEN 28 PRECEEDING AND 1 PRECEEDING
                ) active_days_past_4_weeks
FROM "public"."activity_activity" AS "aa0"
GROUP BY date("aa0"."created_at"), "aa0"."user_id" 
ORDER BY "Day of Created At" ASC LIMIT 1000;

Schema:

CREATE TABLE activity_activity (
  user_id int,
  created_at timestamp
)

Version:

select version()

PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-16ubuntu6) 4.8.2, 64-bit

Best Answer

PostgreSQL doesn't support the RANGE clause yet. So even if you'd got the syntax right, it would've just failed with:

regress=> SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND 42 FOLLOWING);
ERROR:  RANGE FOLLOWING is only supported with UNBOUNDED
LINE 1: SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND 42 F...

To work around this, you can do a left outer join on generate_series(first_day, last_day, INTERVAL '1' DAY) then use a ROWS based window. That way a row exists for every day. It's a whole lot less efficient but unless you feel like getting your hands dirty in PostgreSQL's source code, it's probably the only option.

Related Question