From: Guillaume Roger <guillaume.roger@spilgames.com>
To: pgsql-sql@postgresql.org
Subject: Partition over a sliding date window
Date: Tue, 13 Sep 2011 13:25:39 +0200
Message-ID: <4E6F3DB3.4030905@spilgames.com> (raw)
Hi,
I have a simple table with 3 fields: date, value and username, which can
hold identical rows.
What I am trying to do is to have for each date the count of distinct
username for the 30 previous days.
I was hoping to get this with a partition, as in the following
non-working exemple
SELECT
t.date
, t.value
, COUNT(DISTINCT t.username) OVER (PARTITION date BETWEEN t.date -
INTERVAL '29 days' and t.date)
FROM
table t
GROUP BY
date
, value
, username
;
There are many issues with this query:
- distinct not implemented for window function
- COUNT () OVER is not seen as an aggregate function, I thus need to add
username in the GROUP BY clause, which leads to wrong result
- I am not convinced that the date BETWEEN is valid either, but the
other issues prevent me to check this.
Is there a way to do what I am looking for with partitions, or should I
just give up and use 'usual' sql?
Thanks,
Guillaume
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: guillaume.roger@spilgames.com
Subject: Re: Partition over a sliding date window
In-Reply-To: <4E6F3DB3.4030905@spilgames.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox