書いてる理由
- AWSのRedShiftを使う。
- RedShiftがpostgresqlベースで使い方忘れる。
- posgreのPG/psqlの簡単な使い方を残す。
参考
概要
postgresqlでのPG/psqlの書き方とカンマ区切りのレコードの突合で含まれてたら1、そうでなければ0を返すSQLを作る。
テーブルのカラムに可変長の項目を入れたい場合、カラムの型をtext型にしてカンマ区切りとかで格納することがある。
このカンマ区切りの値のうちどれか一つでも含まれていたらその情報をselectでgetしたいとする。
具体的には、以下のtable aとbがあった時に、aのidsの中のどれかがbのidsの中のどれかに含まれているデータだけ取りたいみたいな場合。
table a
| id | ids |
|---|---|
| 1 | 3,4,5 |
| 2 | 6,7,8 |
| 3 | 10,11,12 |
table b
| id | ids |
|---|---|
| 1 | 3,5,9 |
| 2 | 2,15,19 |
| 3 | 10,100,200 |
詳細
idのどれかが入っているかどうかのマッチは、ARRAYとARRAYをARRAY1 && ARRAY2をすると確認できる。
これを使って、table a/bのidsをARRAYにして比較すればよい。
example-db=# select ARRAY[1,2,3] && ARRAY[3,4,5]; ?column? ---------- t (1 row) example-db=# select ARRAY[1,2,3] && ARRAY[7,4,5]; ?column? ---------- f
ファンクションの作成は、CREATE OR REPLACE FUNCTION function_name(args)で定義していく。
今回は引数が(ARRAY, ARRAY)なので、(anyarray, anyarray)で定義。
引数は$1, $2とかで扱えるので、これを $1 && $2で 1 or 0を返却する形で作成。
CREATE OR REPLACE FUNCTION id_is_include(anyarray, anyarray)
RETURNS
int AS
$$
DECLARE
result int;
BEGIN
IF $1 && $2
THEN return 1;
ELSE
return 0;
END IF;
END;
$$ LANGUAGE plpgsql;
あとは、このファンクションを利用して、以下のようにSQLを実行。
select a_table.id, b_table.id, a_table.a_ids, b_table.b_ids, id_is_include(a_table.a_ids, b_table.b_ids) as result from (select id, regexp_split_to_array(ids, ',') as a_ids from a) as a_table cross join (select id, regexp_split_to_array(ids, ',') as b_ids from b) as b_table ;
regexp_split_to_array(column, 'sep')でcolumnをsepで分割したARRAYをgetできるので、これで二つのARRAYを作ってファンクションへぶん投げる。
全ての組み合わせを作りたいので、テーブルa/bをcross joinで取得して実施。
id | id | a_ids | b_ids | result
----+----+------------+--------------+--------
1 | 1 | {3,4,5} | {3,5,9} | 1
1 | 2 | {3,4,5} | {2,15,19} | 0
1 | 3 | {3,4,5} | {10,100,200} | 0
2 | 1 | {6,7,8} | {3,5,9} | 0
2 | 2 | {6,7,8} | {2,15,19} | 0
2 | 3 | {6,7,8} | {10,100,200} | 0
3 | 1 | {10,11,12} | {3,5,9} | 0
3 | 2 | {10,11,12} | {2,15,19} | 0
3 | 3 | {10,11,12} | {10,100,200} | 1
以上！
