Re: Count dates distinct within an interval

Dmitry Tkach wrote:
> 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;