group by hour + distinct
От | Michele Petrazzo - Unipex |
---|---|
Тема | group by hour + distinct |
Дата | |
Msg-id | 4CEF9C66.30100@unipex.it обсуждение исходный текст |
Список | pgsql-sql |
Hi list, Into a table like this: id_user | my_date and some data inside 1 | 2010/11/25 00:01:00 1 | 2010/11/25 00:02:00 1 | 2010/11/25 01:01:00 2 | 2010/11/25 02:01:00 3 | 2010/11/25 02:01:00 3 | 2010/11/25 02:06:00 1 | 2010/11/25 03:01:00 I'm looking for a query that say me, hour per hour, how many unique id_user are inside that range. With the simple data above, I'm looking for: hour | count 0 | 1 1 | 1 2 | 2 3 | 1 Like now, with my tests, I achieve only a hour | count 0 | 2 1 | 1 2 | 3 3 | 1 My real query and data: SELECT count(id_user) from some_table where my_date >= '2010/11/25 00:00:00' and my_date < '2010/11/25 01:00:00'; count ------- 90 (1 row) SELECT distinct(id_user) from some_table where my_date >= '2010/11/25 00:00:00' and my_date < '2010/11/25 01:00:00' order by id_user; Give me 69 rows, that are the real unique id_user that I have and I'm looking for. One of a query that I use without success: SELECT count(distinct some_table.my_date), EXTRACT(hour from my_date) as h from some_table where my_date >= '2010/11/25' and my_date < '2010/11/26' group by h order by h; count | h -------+---- 90 | 0 63 | 1 ... and so on Someone? Thanks, Michele
В списке pgsql-sql по дате отправления: