pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddetermining how many products are in how many categories
2+ messages / 2 participants
[nested] [flat]
* determining how many products are in how many categories
@ 2004-02-08 17:09 David Garamond <lists@zara.6.isreserved.com>
0 siblings, 1 reply; 2+ messages in thread
From: David Garamond @ 2004-02-08 17:09 UTC (permalink / raw)
To: pgsql-sql
# product table (simplified):
create table p (
id char(22) not null primary key,
name text,
desc text
);
# product category table (simpl.):
create table pc (
id char(22) not null primary key,
name text,
desc text
);
# table that maps products into categories:
create table p_pc (
id char(22) not null primary key,
pid char(22) not null references p(id),
pcid char(22) not null references pc(id)
);
create index i_ppc_pid on p_pc(pid);
create index i_ppc_pcid on p_pc(pcid);
create unique index i_ppc_pid_pcid on p_pc(pid, pcid);
There are about 50k rows in p, 40k in pc, and 140k in p_pc. Most
products are only placed in 1 category, some in 2, fewer in 3, and fewer
stills in 4, 5, 6 categories.
I want to know how many products are placed in 1 category, how many in
2, and so on. Here's my query:
select count(pid),num_cat from (
select pid,count(cid) as num_cat
from ppc group by pid) as f
group by num_cat;
A sample output (4000 products are categorized in 5 different places,
4998 in 4, and so on):
count | num_cat
-------+---------
4000 | 5
4998 | 4
7502 | 3
10001 | 2
17499 | 1
(5 rows)
However, this query is taking about 2.5 minutes. Any idea on how to make
it faster?
--
dave
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: determining how many products are in how many categories
@ 2004-02-11 23:43 Greg Patnude <gpatnude@hotmail.com>
parent: David Garamond <lists@zara.6.isreserved.com>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Patnude @ 2004-02-11 23:43 UTC (permalink / raw)
To: pgsql-sql
You might be better off with something more like this:
SELECT COUNT(P.*) AS p_count, P.p_product_category_id FROM p_product P
WHERE P.p_product_category_id IN (SELECT DISTINCT id FROM
p_product_category) GROUP BY P.p_product_category_id;
obviously tailored to YOUR schema... not mine...
--
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762
"David Garamond" <lists@zara.6.isreserved.com> wrote in message
news:40266D61.3070304@zara.6.isreserved.com...
> # product table (simplified):
> create table p (
> id char(22) not null primary key,
> name text,
> desc text
> );
>
> # product category table (simpl.):
> create table pc (
> id char(22) not null primary key,
> name text,
> desc text
> );
>
> # table that maps products into categories:
> create table p_pc (
> id char(22) not null primary key,
> pid char(22) not null references p(id),
> pcid char(22) not null references pc(id)
> );
> create index i_ppc_pid on p_pc(pid);
> create index i_ppc_pcid on p_pc(pcid);
> create unique index i_ppc_pid_pcid on p_pc(pid, pcid);
>
> There are about 50k rows in p, 40k in pc, and 140k in p_pc. Most
> products are only placed in 1 category, some in 2, fewer in 3, and fewer
> stills in 4, 5, 6 categories.
>
> I want to know how many products are placed in 1 category, how many in
> 2, and so on. Here's my query:
>
> select count(pid),num_cat from (
> select pid,count(cid) as num_cat
> from ppc group by pid) as f
> group by num_cat;
>
> A sample output (4000 products are categorized in 5 different places,
> 4998 in 4, and so on):
>
> count | num_cat
> -------+---------
> 4000 | 5
> 4998 | 4
> 7502 | 3
> 10001 | 2
> 17499 | 1
> (5 rows)
>
> However, this query is taking about 2.5 minutes. Any idea on how to make
> it faster?
>
> --
> dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-02-11 23:43 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-08 17:09 determining how many products are in how many categories David Garamond <lists@zara.6.isreserved.com>
2004-02-11 23:43 ` Greg Patnude <gpatnude@hotmail.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