Re: how to write an optimized sql with two same subsql?

> -----Original Message-----
> From: sunpeng [mailto:bluevaley(at)gmail(dot)com]
> Sent: Thursday, October 14, 2010 7:34 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: how to write an optimized sql with two same subsql?
>
> We have a table A:
> CREATE TABLE A(
> uid integer,
> groupid integer
> )
> Now we use this subsql to get each group's count:
> SELECT count(*) as count
> FROM A
> GROUP BY groupid
> ORDER BY groupid
>
> Then we try to find the group pair with following conditions:
> SELECT c.groupid as groupid1,d.groupid as groupid2 FROM
> subsql as c, subsql as d WHERE d.groupid > c.groupid
> and d.count > c.count;
>
> Does that mean subsql will be executed twice? or how to write
> the optimized sql?
>

Is that what you want:

WITH gr_counts AS (
SELECT groupid, COUNT(*) AS CNT
FROM A
GROUP BY groupid)
SELECT C.groupid AS groupid1, D.groupid AS groupid2
FROM gr_counts C, gr_counts D
WHERE D.groupid > C.groupid
AND D.count > C.count;

This will execute:

SELECT groupid, COUNT(*) AS CNT
FROM A
GROUP BY groupid

only once.

Regards,
Igor Neyman