Count dates distinct within an interval
От | Dmitry Tkach |
---|---|
Тема | Count dates distinct within an interval |
Дата | |
Msg-id | 3F1410CB.9020504@openratings.com обсуждение исходный текст |
Ответы |
Re: Count dates distinct within an interval
Re: Count dates distinct within an interval Re: Count dates distinct within an interval |
Список | 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
В списке pgsql-sql по дате отправления: