From: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
To: pgsql-sql@postgresql.org
Subject: Re: group by hour + distinct
Date: Fri, 26 Nov 2010 12:19:06 -0000
Message-ID: <FC442338870D492A930770CD23660616@marktestcr.marktest.pt> (raw)
References: <4CEF9C66.30100@unipex.it>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliveiros.cristina@marktest.pt, michele.petrazzo@unipex.it
Subject: Re: group by hour + distinct
In-Reply-To: <FC442338870D492A930770CD23660616@marktestcr.marktest.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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