🐘

PostgreSQLの配列(ARRAY)型のカラムを、配列内の順序付きで行に変換する

2023/06/01に公開

PostgreSQL

tech

PostgreSQLの配列(ARRAY)型のカラムを、配列内の順序付きで行に変換したくて試したことをまとめておきます。

やりたいこと

下記のような配列型のカラムを持つテーブルがあり、

CREATE TABLE table1 AS 
SELECT 
  *
FROM
(
  VALUES
    (1, ARRAY['a', 'b']),
    (3, ARRAY['a', 'b', 'b', 'a']),
    (2, ARRAY['z', 'y', 'x'])
) AS t (id, names)
;

sample=# \d table1
               Table "public.table1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
 names  | text[]  |           |          |

sample=# SELECT * FROM table1;
 id |   names
----+-----------
  1 | {a,b}
  3 | {a,b,b,a}
  2 | {z,y,x}
(3 rows)

配列型のカラムを行に変換したうえで、配列内の順序(1からの通番)も付けます。
先ほどのテーブルのデータを下記のような形にすることになります。
(orderカラムが配列内での順序)

 id |   names   | name | order
----+-----------+------+-------
  1 | {a,b}     | a    |     1
  1 | {a,b}     | b    |     2
  2 | {z,y,x}   | z    |     1
  2 | {z,y,x}   | y    |     2
  2 | {z,y,x}   | x    |     3
  3 | {a,b,b,a} | a    |     1
  3 | {a,b,b,a} | b    |     2
  3 | {a,b,b,a} | b    |     3
  3 | {a,b,b,a} | a    |     4

試したこと

実際試したことを書いていきます。
結論だけ知りたい方は、「unnest + generate_subscripts」をご確認ください。

unnest + row_number

行に変換するだけならばunnestを使うだけで終わりなのですが、順序を付けようとなると思いつくのはWindows関数のrow_numberです。

9.22. ウィンドウ関数

とりあえず、全体で通番を振ってみます。
unnestしたものにrow_number() OVER()使って全体に対して通番を振る形です。

SELECT
  *
  , row_number() OVER() AS order
FROM (
  SELECT
    *
    , unnest(names) AS name
  FROM
    table1
) temp
;

 id |   names   | name | order
----+-----------+------+-------
  1 | {a,b}     | a    |     1
  1 | {a,b}     | b    |     2
  3 | {a,b,b,a} | a    |     3
  3 | {a,b,b,a} | b    |     4
  3 | {a,b,b,a} | b    |     5
  3 | {a,b,b,a} | a    |     6
  2 | {z,y,x}   | z    |     7
  2 | {z,y,x}   | y    |     8
  2 | {z,y,x}   | x    |     9
(9 rows)

配列の順序は崩れずに全体としての通番となっています。ただ、ORDER BYを指定していないので、この順番になることが保証されると考えてよいのかわかりませんでした。
unnestは配列の順番に展開されることは保証されている(ドキュメント上にも記載)のですが、その後のrow_number() OVER()を行うクエリで保証されるかがわかりません。
(実行計画的にもSortが発生しないので大丈夫そうな気はするが、保証されるものなのか判断できない)

なお、OVER句でPARTITION BY指定したら、各配列毎の順番になるのでは！？と思いましたが、これだと順序が狂う場合がありました。PARTITION BYを行うことでSortが発生するためのようです。

SELECT
  *
  , row_number() OVER(PARTITION BY id) AS order
FROM (
  SELECT
    *
    , unnest(names) AS name
  FROM
    table1
) temp
;

 id |   names   | name | order
----+-----------+------+-------
  1 | {a,b}     | a    |     1
  1 | {a,b}     | b    |     2
  2 | {z,y,x}   | x    |     1
  2 | {z,y,x}   | z    |     2
  2 | {z,y,x}   | y    |     3
  3 | {a,b,b,a} | a    |     1
  3 | {a,b,b,a} | a    |     2
  3 | {a,b,b,a} | b    |     3
  3 | {a,b,b,a} | b    |     4
(9 rows)

全体に対してrow_number() OVER()で振った後に、さらにrow_number() OVER(PARTITION BY id ORDER BY order)のような形で、期待する結果を作ることはできました。

SELECT
  id
  , names
  , name
  , row_number() OVER(PARTITION BY id ORDER BY overall_order) AS order
FROM (
  SELECT
    *
    , row_number() OVER() AS overall_order
  FROM (
    SELECT
      *
      , unnest(names) AS name
    FROM
      table1
  ) temp1
) temp2
;

 id |   names   | name | order
----+-----------+------+-------
  1 | {a,b}     | a    |     1
  1 | {a,b}     | b    |     2
  2 | {z,y,x}   | z    |     1
  2 | {z,y,x}   | y    |     2
  2 | {z,y,x}   | x    |     3
  3 | {a,b,b,a} | a    |     1
  3 | {a,b,b,a} | b    |     2
  3 | {a,b,b,a} | b    |     3
  3 | {a,b,b,a} | a    |     4
(9 rows)

ただ、結局のところrow_number() OVER()のところで順序が保証されるかわからないので、他の方法を探します。

再帰SQLで配列を先頭から辿る

unnestからいったん離れて別の方法ということで、再帰SQLで考えてみました。

再帰SQLで、配列を先頭からなめていきます。

WITH RECURSIVE previous AS(
  -- 配列の先頭を起点に(PostgreSQLの配列のインデックスは1から)
  SELECT
    *
    , names[1] AS name
    , 1 AS order 
  FROM
    table1
  UNION ALL
  SELECT
    table1.*
    , table1.names[previous.order + 1] AS name
    , previous.order + 1 AS order 
  FROM
    previous
    INNER JOIN table1
      USING(id)
  WHERE
    cardinality(table1.names) > previous.order
)
SELECT
  *
FROM
  previous
ORDER BY
  previous.id
  , previous.order
;

 id |   names   | name | order
----+-----------+------+-------
  1 | {a,b}     | a    |     1
  1 | {a,b}     | b    |     2
  2 | {z,y,x}   | z    |     1
  2 | {z,y,x}   | y    |     2
  2 | {z,y,x}   | x    |     3
  3 | {a,b,b,a} | a    |     1
  3 | {a,b,b,a} | b    |     2
  3 | {a,b,b,a} | b    |     3
  3 | {a,b,b,a} | a    |     4
(9 rows)

意図した通りの結果を得ることが出来ました。

unnest + generate_subscripts

配列型のカラムと同じ数の集合を1からの連番で作れれば、もっと簡単に出来るのではと思って、generate_seriesを調べていたところ、同じページでgenerate_subscriptsという「指定した配列の有効な添え字を構成する連番を生成する」関数を見つけました。今回の用途にぴったりです。

9.25. 集合を返す関数

SELECT
  *
  , unnest(names) AS name
  , generate_subscripts(names, 1) AS order
FROM
  table1
;

 id |   names   | name | order
----+-----------+------+-------
  1 | {a,b}     | a    |     1
  1 | {a,b}     | b    |     2
  3 | {a,b,b,a} | a    |     1
  3 | {a,b,b,a} | b    |     2
  3 | {a,b,b,a} | b    |     3
  3 | {a,b,b,a} | a    |     4
  2 | {z,y,x}   | z    |     1
  2 | {z,y,x}   | y    |     2
  2 | {z,y,x}   | x    |     3
(9 rows)

これが一番簡単でわかりやすい方法でした。