Обсуждение: group by hour + distinct
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
Howdy, Michelle, If you write something like this, SELECT hour , COUNT(id_user) as count FROM ( SELECT EXTRACT(hour from my_date) as hour, id_user FROM michelle GROUP BY EXTRACT(hour from my_date),id_user )subquery GROUP BY hour for each hour it will count the number of distinct user_id's there are . If I understood correctly what you need... Can you please test it and see if it is OK for your needs? With me, it worked on the sample data you provided Best, Oliver ----- Original Message ----- From: "Michele Petrazzo - Unipex" <michele.petrazzo@unipex.it> To: <pgsql-sql@postgresql.org> Sent: Friday, November 26, 2010 11:39 AM Subject: [SQL] group by hour + distinct > 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 > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
Oliveiros d'Azevedo Cristina ha scritto: > Howdy, Michelle, > Ciao > for each hour it will count the number of distinct user_id's there > are . If I understood correctly what you need... > > Can you please test it and see if it is OK for your needs? With me, > it worked on the sample data you provided > Yes! Simply perfect! > Best, Oliver > Thanks, Michele P.s. Have you some references about the "subquery" keyword? I found only the word subquery as "use" (for example: select a from b where id in (select id from table)), but not as sql command.
> > Ciao > * Olá! >> for each hour it will count the number of distinct user_id's there >> are . If I understood correctly what you need... >> >> Can you please test it and see if it is OK for your needs? With me, >> it worked on the sample data you provided >> > > Yes! Simply perfect! > * Great to hear it worked ! >> > > Thanks, > Michele * Don't mention it ;-) > > P.s. Have you some references about the "subquery" keyword? I found only > the word subquery as "use" (for example: select a from b where id in > (select id from table)), but not as sql command. > > * I don't know subquery as an SQL keyword too. In the query I gave you the "subquery" thing was just an alias. Such kind of things are mandatory. If I try to omit it, I get this error ERROR: subquery in FROM must have an alias HINT: For example, FROM (SELECT ...) [AS] foo. ********** Erro ********** ERROR: subquery in FROM must have an alias Estado de SQL:42601 Sugestão:For example, FROM (SELECT ...) [AS] foo. So, even though the "subquery" alias was not used anywhere else, I believe it had to be there, otherwise the SQL parser would complain Best, Oliver
Michele Petrazzo - Unipex wrote: > P.s. Have you some references about the "subquery" keyword? I found only > the word subquery as "use" (for example: select a from b where id in > (select id from table)), but not as sql [sic] command. "subquery" is not an SQL keyword. <http://www.postgresql.org/docs/9.0/interactive/sql-keywords-appendix.html> Nor is it a command all by itself. Reading the documentation might help you: <http://www.postgresql.org/docs/9.0/interactive/queries-table-expressions.html#QUERIES-SUBQUERIES> <http://www.postgresql.org/docs/9.0/interactive/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES> <http://www.postgresql.org/docs/9.0/interactive/functions-subquery.html> Or try: <http://lmgtfy.com/?q=SQL+subquery> -- Lew