PostgreSQLを使用した案件にてサブクエリを最大5つ使用するSQLを作成していたが条件項目が多くレスポンスでよい結果がでないこともあり、そこでWITH句を使用することにした。

WITH句

PostgreSQLのドキュメントには以下のように書かれています。
https://www.postgresql.jp/document/9.5/html/queries-with.html

WITHは、より大規模な問い合わせで使用される補助文を記述する方法を提供します。 これらの文は共通テーブル式(Common Table Expressions)またはCTEとよく呼ばれるものであり、1つの問い合わせのために存在する一時テーブルを定義すると考えることができます。 WITH句内の補助文はそれぞれSELECT、INSERT、UPDATEまたはDELETEを取ることができます。 そしてWITH句自身は、これもSELECT、INSERT、UPDATEまたはDELETEを取ることができる主文に付与されます。

簡単にいうとサブクエリに名前を付けて使用する感じになりますね。

レスポンスの計測結果ですが
サブクエリ2個の場合で1.5倍高速に、
サブクエリ5個の場合で8.2倍高速になる結果となりました。

全てのクエリでレスポンスが向上するものを保証するものではありませんので

以下のSQL例が、今回改修したSQLのおおまかな例です。

 a1 AS (
    SELECT 
        z.id2 
        SUM(a.yy) yy_count
    FROM (
        SELECT 
            id,
            COUNT(xx) yy
        FROM table1
        WHERE
            aaa = '001'
            AND bbb = '002'
            AND ccc = '003'
        GROUP BY id
        ) a
    INNER JOIN
        table2 AS z
        ON z.id = a.id
    GROUP BY 
        z.id2
),
a2 (
    SELECT 
        z.id2 
        SUM(a.yy) yy_count
    FROM (
        SELECT 
            id,
            COUNT(xx) yy
        FROM table1
        WHERE
            aaa = '001'
            AND bbb = '012'
            AND ccc = '013'
            AND ddd = '014'
        GROUP BY id
        ) a
    INNER JOIN
        table2 AS z
        ON z.id = a.id
    GROUP BY 
        z.id2
)
SELECT
    b.*
FROM
    main_table AS b
LEFT OUTER JOIN a1
    ON a1.id2 = b.id2
LEFT OUTER JOIN a2
    ON a2.id2 = b.id2
WHERE
    b.eee = '0101'
ORDER BY
    b.id

WITH句が使用できるRDBMS


https://www.postgresql.jp/document/9.5/html/queries-with.html

http://www.oracle.com/technetwork/jp/articles/otnj-sql-image7-1525406-ja.html#a
MS SQL Server
https://msdn.microsoft.com/ja-jp/library/ms175972.aspx

5.7まではWITH句はサポートされていないようです。SQL-99で規格化されているものなんですけどねぇ
MySQLは使えないけど、MariaDBは使えるようです(SQL-99 Complete ってサイトに載ってます)。
8 ではWITH句がサポートされているらしいのですが未確認。

MariaDB
https://mariadb.com/kb/en/mariadb/with/
Amazon Redshift
http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_WITH_clause.html