PostgreSQLで配列を使いこなす
配列型、テーブル定義にはあんま使いたくないけど
複雑なクエリ書くときは便利～。
以下のようなテーブル「hoge」について考えます。
| type | code |
| 1 | a |
| 1 | b |
| 2 | c |
| 2 | a |
| 3 | b |
| 3 | c |
実テーブル作るのめんどいからとりあえず一時テーブル。
CREATE TEMPORARY TABLE "hoge" AS SELECT 1 AS "type", 'a' AS "code" UNION SELECT 1 AS "type", 'b' AS "code" UNION SELECT 2 AS "type", 'c' AS "code" UNION SELECT 2 AS "type", 'a' AS "code" UNION SELECT 3 AS "type", 'b' AS "code" UNION SELECT 3 AS "type", 'c' AS "code";
GROUP BYを利用して、特定カラムを配列にまとめる
typeごとにcodeを配列にまとめたもの、を表示するにはこう。
SELECT "type" ,ARRAY_AGG("code") AS "code_list" FROM "hoge" GROUP BY "type"
結果はこんな形。code_listの部分が文字列配列。
| type | code_list |
| 1 | {a,b} |
| 2 | {c,a} |
| 3 | {b,c} |
配列に対する条件で絞り込みを行う
「codeに○○を含む(or含まない)typeの一覧」
を求めるクエリを何パターンか作ってみました。
SELECT "type" FROM ( SELECT "type", ARRAY_AGG("code") AS "code_list" FROM "hoge" GROUP BY "type" ) AS "tmp_hoge" WHERE 【ここに条件文】
以下、↑のWHEREの部分のみ抜粋。
テストデータが少なかったのでサンプルはちょっと微妙ですが。
「a」を含む
WHERE "code_list" && ARRAY['a'] -- 1,2
WHERE 'a' = ANY("code_list") -- 1,2
どちらの書き方でもOK。
これはもちろん「SELECT DISTINCT "type" FROM "hoge" WHERE "code" = 'a'」でもOK。
「a」または「b」を含む
WHERE "code_list" && ARRAY['a', 'b'] -- 1,2,3
これも「SELECT DISTINCT "type" FROM "hoge" WHERE "code" IN ('a', 'b')」でもOK。
「a」と「b」の両方を含む
WHERE "code_list" @> ARRAY['a', 'b'] -- 1
この辺から、配列を使わない代替SQLが地味にめんどくさくなるやつ。めんどくさいから書かない。
「a」と「b」の両方のみを含む
WHERE "code_list" @> ARRAY['a', 'b'] AND "code_list" <@ ARRAY['a', 'b'] -- 1
もしかしたらもっとスマートな方法もあるかもしれない。
順序も含めて完全一致かどうかは単純にイコールによる比較でよいので、
並べ替えてから配列化しておくとか。
「a」を含まない
WHERE NOT("code_list" && ARRAY['a']) -- 3
「a」を含む、の逆なのでNOTするだけ。
「a」も「b」も含まない
WHERE NOT("code_list" && ARRAY['a', 'b']) -- (0件)
「a」または「b」を含む、の逆なので（以下略）
「a」と「b」の両方を含むものを除く
WHERE NOT("code_list" @> ARRAY['a', 'b']) -- 2,3
「a」と「b」の両方を含む、の逆（ｒｙ
