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
You would probably be able to speed the following up using immutable funtions to aid the query, or just a function to do it. However I think this does what you asked in a query. I've put a script at the end. hth, - Stuart -- s is the stuff to group by -- dt is the datetime thing create table Q ( s int4, dt timestamptz);
truncate Q;
INSERT INTO Q (s,dt) VALUES (1,'2003/01/01'::timestamptz); INSERT INTO Q (s,dt) VALUES (1,'2003/01/02 04:00'::timestamptz); INSERT INTO Q (s,dt) VALUES (1,'2003/01/01 08:00'::timestamptz); INSERT INTO Q (s,dt) VALUES (1,'2003/01/02'::timestamptz); INSERT INTO Q (s,dt) VALUES (1,'2003/01/05 23:00'::timestamptz); INSERT INTO Q (s,dt) VALUES (1,'2003/01/06 22:00'::timestamptz); INSERT INTO Q (s,dt) VALUES (1,'2003/01/07 05:00'::timestamptz); INSERT INTO Q (s,dt) VALUES (3,'2003/01/01'::timestamptz); INSERT INTO Q (s,dt) VALUES (4,'2003/01/01'::timestamptz); INSERT INTO Q (s,dt) VALUES (4,'2003/01/02 05:00'::timestamptz); INSERT INTO Q (s,dt) VALUES (4,'2003/01/03 04:00'::timestamptz);
SELECT COUNT(*),s FROM Q WHERE dt IN (SELECT min(R.dt) FROM Q AS R WHERE Q.s=R.s)
OR dt IN (SELECT min(P.dt) FROM Q AS P WHERE P.s=Q.s AND
P.dt>=date_trunc('day',Q.dt)-
CASE WHEN
(SELECT max(dt)::time FROM Q AS T WHERE Q.s=T.s AND T.dt<Q.dt AND NOT EXISTS
(SELECT * FROM Q AS U WHERE T.dt-'1 day'::interval<U.dt AND T.dt>U.dt AND U.s=Q.s))
>Q.dt::time THEN '1 day'::interval
ELSE
'0 day'::interval
END
+(SELECT max(dt)::time FROM Q AS T WHERE Q.s=T.s AND T.dt<Q.dt AND NOT EXISTS
(SELECT * FROM Q AS U WHERE T.dt-'1 day'::interval<U.dt AND T.dt>U.dt AND U.s=Q.s)))
GROUP BY s;
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
