pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgroup by hour + distinct
5+ messages / 3 participants
[nested] [flat]
* group by hour + distinct
@ 2010-11-26 11:39 Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
2010-11-26 12:19 ` Re: group by hour + distinct Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 5+ messages in thread
From: Michele Petrazzo - Unipex @ 2010-11-26 11:39 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: group by hour + distinct
2010-11-26 11:39 group by hour + distinct Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
@ 2010-11-26 12:19 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-11-26 12:42 ` Re: group by hour + distinct Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
0 siblings, 1 reply; 5+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2010-11-26 12:19 UTC (permalink / raw)
To: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: group by hour + distinct
2010-11-26 11:39 group by hour + distinct Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
2010-11-26 12:19 ` Re: group by hour + distinct Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
@ 2010-11-26 12:42 ` Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
2010-11-26 13:15 ` Re: group by hour + distinct Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-11-26 16:07 ` Re: group by hour + distinct Lew <noone@lewscanon.com>
0 siblings, 2 replies; 5+ messages in thread
From: Michele Petrazzo - Unipex @ 2010-11-26 12:42 UTC (permalink / raw)
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>; +Cc: 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: group by hour + distinct
2010-11-26 11:39 group by hour + distinct Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
2010-11-26 12:19 ` Re: group by hour + distinct Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-11-26 12:42 ` Re: group by hour + distinct Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
@ 2010-11-26 13:15 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
1 sibling, 0 replies; 5+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2010-11-26 13:15 UTC (permalink / raw)
To: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: group by hour + distinct
2010-11-26 11:39 group by hour + distinct Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
2010-11-26 12:19 ` Re: group by hour + distinct Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-11-26 12:42 ` Re: group by hour + distinct Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
@ 2010-11-26 16:07 ` Lew <noone@lewscanon.com>
1 sibling, 0 replies; 5+ messages in thread
From: Lew @ 2010-11-26 16:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-11-26 16:07 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-26 11:39 group by hour + distinct Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
2010-11-26 12:19 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-11-26 12:42 ` Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
2010-11-26 13:15 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-11-26 16:07 ` Lew <noone@lewscanon.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox