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

> -----Original Message-----
> From: sunpeng [mailto:bluevaley@gmail.com]
> Sent: Thursday, October 14, 2010 7:34 PM
> To: pgsql-general@postgresql.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