pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCounting Distinct Records
7+ messages / 2 participants
[nested] [flat]
* Counting Distinct Records
@ 2004-11-16 19:45 Thomas F.O'Connell <tfo@sitening.com>
2004-11-16 20:03 ` Re: Counting Distinct Records Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 7+ messages in thread
From: Thomas F.O'Connell @ 2004-11-16 19:45 UTC (permalink / raw)
To: pgsql-sql
I am wondering whether the following two forms of SELECT statements are
logically equivalent:
SELECT COUNT( DISTINCT table.column ) ...
and
SELECT DISTINCT COUNT( * ) ...
If they are the same, then why is the latter query much slower in
postgres when applied to the same FROM and WHERE clauses?
Furthermore, is there a better way of performing this sort of operation
in postgres (or just in SQL in general)?
Thanks!
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Counting Distinct Records
2004-11-16 19:45 Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
@ 2004-11-16 20:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-16 20:09 ` Re: Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 1 reply; 7+ messages in thread
From: Stephan Szabo @ 2004-11-16 20:03 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: pgsql-sql
On Tue, 16 Nov 2004, Thomas F.O'Connell wrote:
> I am wondering whether the following two forms of SELECT statements are
> logically equivalent:
>
> SELECT COUNT( DISTINCT table.column ) ...
>
> and
>
> SELECT DISTINCT COUNT( * ) ...
Not in general.
The former counts how many distinct table.column values there are. The
distinct in the latter would be basically meaningless unless there's a
group by involved.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Counting Distinct Records
2004-11-16 19:45 Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
2004-11-16 20:03 ` Re: Counting Distinct Records Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-11-16 20:09 ` Thomas F.O'Connell <tfo@sitening.com>
2004-11-16 22:34 ` Re: Counting Distinct Records Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 7+ messages in thread
From: Thomas F.O'Connell @ 2004-11-16 20:09 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
Is there another way to accomplish what the former is doing, then?
For practical reasons, I'd like to come up with something better.
For theoretical curiosity, I'd like to know whether there's a way to
combine COUNT and DISTINCT that still allows one to reference * rather
than naming specific columns without grouping.
If I resort to GROUP BY, is there an efficient way of counting all the
groups, or would it just be something like:
SELECT COUNT ( * ) FROM ( SELECT ... GROUP BY ... );
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Nov 16, 2004, at 2:03 PM, Stephan Szabo wrote:
> On Tue, 16 Nov 2004, Thomas F.O'Connell wrote:
>
>> I am wondering whether the following two forms of SELECT statements
>> are
>> logically equivalent:
>>
>> SELECT COUNT( DISTINCT table.column ) ...
>>
>> and
>>
>> SELECT DISTINCT COUNT( * ) ...
>
> Not in general.
>
> The former counts how many distinct table.column values there are. The
> distinct in the latter would be basically meaningless unless there's a
> group by involved.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Counting Distinct Records
2004-11-16 19:45 Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
2004-11-16 20:03 ` Re: Counting Distinct Records Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-16 20:09 ` Re: Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
@ 2004-11-16 22:34 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-16 23:02 ` Re: Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 1 reply; 7+ messages in thread
From: Stephan Szabo @ 2004-11-16 22:34 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: pgsql-sql
On Tue, 16 Nov 2004, Thomas F.O'Connell wrote:
> Is there another way to accomplish what the former is doing, then?
The only thing I can think of is a subselect in from that uses distinct.
select count(*) from (select distinct ...) foo
That also theoretically allows you to use select distinct * inside the
subselect.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Counting Distinct Records
2004-11-16 19:45 Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
2004-11-16 20:03 ` Re: Counting Distinct Records Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-16 20:09 ` Re: Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
2004-11-16 22:34 ` Re: Counting Distinct Records Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-11-16 23:02 ` Thomas F.O'Connell <tfo@sitening.com>
2004-11-17 14:52 ` Re: Counting Distinct Records Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 7+ messages in thread
From: Thomas F.O'Connell @ 2004-11-16 23:02 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
Hmm. I was more interested in using COUNT( * ) than DISTINCT *.
I want a count of all rows, but I want to be able to specify which
columns are distinct.
That's definitely an interesting approach, but testing doesn't show it
to be appreciably faster.
If I do a DISTINCT *, postgres will attempt to guarantee that there are
no duplicate values across all columns rather than a subset of columns?
Is that right?
Anyway, I was just wondering if there were any best practices out there
for counting distinct values in sets of values that might not
themselves be distinct.
Thanks for the tips so far!
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Nov 16, 2004, at 4:34 PM, Stephan Szabo wrote:
> On Tue, 16 Nov 2004, Thomas F.O'Connell wrote:
>
>> Is there another way to accomplish what the former is doing, then?
>
> The only thing I can think of is a subselect in from that uses
> distinct.
> select count(*) from (select distinct ...) foo
>
> That also theoretically allows you to use select distinct * inside the
> subselect.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Counting Distinct Records
2004-11-16 19:45 Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
2004-11-16 20:03 ` Re: Counting Distinct Records Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-16 20:09 ` Re: Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
2004-11-16 22:34 ` Re: Counting Distinct Records Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-16 23:02 ` Re: Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
@ 2004-11-17 14:52 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-17 19:27 ` Re: Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 1 reply; 7+ messages in thread
From: Stephan Szabo @ 2004-11-17 14:52 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: pgsql-sql
On Tue, 16 Nov 2004, Thomas F.O'Connell wrote:
> Hmm. I was more interested in using COUNT( * ) than DISTINCT *.
>
> I want a count of all rows, but I want to be able to specify which
> columns are distinct.
I'm now a bit confused about exactly what you're looking for in the end.
Can you give a short example?
> That's definitely an interesting approach, but testing doesn't show it
> to be appreciably faster.
>
> If I do a DISTINCT *, postgres will attempt to guarantee that there are
> no duplicate values across all columns rather than a subset of columns?
> Is that right?
It guarantees one output row for each distinct set of column values across
all columns.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Counting Distinct Records
2004-11-16 19:45 Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
2004-11-16 20:03 ` Re: Counting Distinct Records Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-16 20:09 ` Re: Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
2004-11-16 22:34 ` Re: Counting Distinct Records Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-16 23:02 ` Re: Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
2004-11-17 14:52 ` Re: Counting Distinct Records Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-11-17 19:27 ` Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 0 replies; 7+ messages in thread
From: Thomas F.O'Connell @ 2004-11-17 19:27 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
The specific problem I'm trying to solve involves a user table with
some history.
Something like this:
create table user_history (
user_id int
event_time_stamp timestamp
);
I'd like to be able to count the distinct user_ids in this table, even
if it were joined to other tables.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Nov 17, 2004, at 8:52 AM, Stephan Szabo wrote:
> On Tue, 16 Nov 2004, Thomas F.O'Connell wrote:
>
>> Hmm. I was more interested in using COUNT( * ) than DISTINCT *.
>>
>> I want a count of all rows, but I want to be able to specify which
>> columns are distinct.
>
> I'm now a bit confused about exactly what you're looking for in the
> end.
> Can you give a short example?
>
>> That's definitely an interesting approach, but testing doesn't show it
>> to be appreciably faster.
>>
>> If I do a DISTINCT *, postgres will attempt to guarantee that there
>> are
>> no duplicate values across all columns rather than a subset of
>> columns?
>> Is that right?
>
> It guarantees one output row for each distinct set of column values
> across
> all columns.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-11-17 19:27 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-16 19:45 Counting Distinct Records Thomas F.O'Connell <tfo@sitening.com>
2004-11-16 20:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-16 20:09 ` Thomas F.O'Connell <tfo@sitening.com>
2004-11-16 22:34 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-16 23:02 ` Thomas F.O'Connell <tfo@sitening.com>
2004-11-17 14:52 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-17 19:27 ` Thomas F.O'Connell <tfo@sitening.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