Message-ID: <4E6F3DB3.4030905@spilgames.com>
Date: Tue, 13 Sep 2011 13:25:39 +0200
From: Guillaume Roger <guillaume.roger@spilgames.com>
Organization: Spilgames
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:6.0.2) Gecko/20110906 Thunderbird/6.0.2
MIME-Version: 1.0
To: <pgsql-sql@postgresql.org>
Subject: Partition over a sliding date window
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: 7bit

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