From: | Dmitry Tkach <dmitry(at)openratings(dot)com> |
---|---|
To: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Count dates distinct within an interval |
Date: | 2003-07-15 14:33:47 |
Message-ID: | 3F1410CB.9020504@openratings.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi, everybody!
I was trying to formulate a sql query, that I now think is impossible :-(
I just thought, I'd run it by you before I give up - perhaps, you guys
would have some ideas...
Suppose, I have a table like this
create table test
(
stuff int,
stamp timestamp
);
Now, I want to count the occurences of each value of stuff in the table,
but so that entries within 24 hours from each other count as one...
The closest I could think of is:
select stuff, count (distinct date_trunc ('day', stamp)) from test group
by stuff;
This doesn't do exactly what I need though - for example, if one entry
is one minute before midnight, and the other one is two minutes later,
they'd get counted as 2, and what I am looking for is the way to get
them collapsed into one as long as they are less then 24 hours apart...
Now, I am pretty sure, it is simply impossible to do what I want with
count (distinct...) because my 'equality' is not transitive - for
example, three entries, like
A = 2001 - 01- 01 20:20:00
B = 2001 - 01 - 02 20:19:00
C = 2001 - 01 - 02 20:21:00
Should be counted as *two* (A === B, and B === C, but *not* A === C)...
Also, I could certainly write a simple function, that would get all the
entries in order, and scan through them, counting according to my rules...
But I was hoping to find some way to do this in plain sql though...
Any ideas?
Thanks!
Dima
From | Date | Subject | |
---|---|---|---|
Next Message | greg | 2003-07-15 14:39:09 | Re: max length of sql select statement (long!) |
Previous Message | Henshall, Stuart - TNP Southwest | 2003-07-15 14:31:52 | Re: Cannot insert dup id in pk |