SQLアンチパターンのジェイウォークを縦持ちテーブルに変換する

概要

SQLアンチパターンの1つであるジェイウォークと遭遇して、
一回のSQLで縦持ちのテーブルに変換できたのでその方法のメモです。

前提条件

• PostgreSQL 11.1

テーブル

元テーブル

下記のようなテーブルがあります。

id	value
1	apple,orange,banana
2	apple
3	orange,banana

valueにはカンマ区切りで値が入っています。
テーブル名は「jaywalk_table」とします。

取得したい形

id	branch_number	splited_value
1	1	apple
1	2	orange
1	3	banana
2	1	apple
3	1	orange
3	2	banana

カンマ区切りの値に対してそれぞれを1レコードとして枝番号をつけます。

SQL

実行したSQLは下記です。

SELECT
    id,
    ROW_NUMBER() OVER(PARTITION BY id) AS branch_number,
    splited_value
FROM
(
    SELECT
        id,
        regexp_split_to_table(value,',') AS splited_value
    FROM
        jaywalk_table
) AS temp

解説

regexp_split_to_table

regexp_split_to_table関数は指定した区切り文字で区切って、区切られた値をそれぞれレコードに変換します。
例えば、下記のSQLを実行すると・・・

SELECT regexp_split_to_table('apple,orange,banana', ',')

下記のような結果が返ってきます。

regexp_split_to_table
apple
orange
banana

この関数を使えばジェイウォークは解消されます。

ROW_NUMBER

ROW_NUMBER関数はWindow関数の一つです。
Window関数はグループごとに計算を行い、計算された結果を各レコードに返します。

今回でいくとOVER(PARTITION BY id)はidごとにグルーピングすることを表し、
ROW_NUMBER()でそのグルーピング内で行数を返してねという感じの流れです。

まとめ

集合を返す関数とWindow関数を組み合わせると良い感じにジェイウォークを解消できますね。