ずんだの表見出し++問題 SQL 編

注意: 本記事のSQLはPostgreSQLの次のバージョンで動作確認を行っています: psql (PostgreSQL) 10.5 (Ubuntu 10.5-0ubuntu0.18.04)

元ネタ: ずんだの表見出し++問題 Ruby 編

計算資源が乏しかった時代の名残なのか、正規化という考え方を知らなかった人が設計したのか、正確なところはわからないのですが、交差テーブルを使わずに、デリミタ区切りのリストを使うテーブル設計がなされていることがあります。なおDB&SQL設計の名著『SQLアンチパターン』では、このパターンを「ジェイウォーク(信号無視)」として、なるべく避けることを推奨しています。洋の東西を超えて、やらかしがちなテーブル設計なのかもしれません。

create table columns (
  no    integer not null primary key,
  cells text 
);

insert into columns values (1, '0|col1|col2|col3');
insert into columns values (2, '1|col1|col2|col3');
insert into columns values (3, '2|col1|col2|col3');
insert into columns values (4, '3|col1|col2|col3');
insert into columns values (5, '4|col1|col2|col3');

select * from columns;

 no |      cells
----+------------------
  1 | 0|col1|col2|col3
  2 | 1|col1|col2|col3
  3 | 2|col1|col2|col3
  4 | 3|col1|col2|col3
  5 | 4|col1|col2|col3
(5 rows)

テーブルがこういう設計になっていると、UPDATEはもちろんのことSELECTでもつらい場合があるので、許されるならば正規化されたVIEWを作成したいところです。方法はいくつかあると思いますが、標準的なSQLに準拠するとなると、再帰クエリと文字列関数を組み合わせるのがよさそうです。

create or replace view cells (no, index, cell) as (
  with recursive t (no, index, head, tail) as (
      select
        no,
        0,
        null,
        cells || '|'
      from columns
    union all
      select
        no,
        index + 1,
        substring(tail from 1 for position('|' in tail) - 1),
        substring(tail from position('|' in tail) + 1)
      from t
      where length(tail) > 0
  )
  select no, index, head from t where index > 0
);

作成したcellsというVIEWの中身を見てみると、正規化されていることがわかります。

select * from cells order by no, index;

 no | index | cell
----+-------+------
  1 |     1 | 0
  1 |     2 | col1
  1 |     3 | col2
  1 |     4 | col3
  2 |     1 | 1
  2 |     2 | col1
  2 |     3 | col2
  2 |     4 | col3
  3 |     1 | 2
  3 |     2 | col1
  3 |     3 | col2
  3 |     4 | col3
  4 |     1 | 3
  4 |     2 | col1
  4 |     3 | col2
  4 |     4 | col3
  5 |     1 | 4
  5 |     2 | col1
  5 |     3 | col2
  5 |     4 | col3
(20 rows)

あとは次のようなクエリで「ずんだの表見出し++問題」を解くことができるはずです。

select
  string_agg(
    case when index = 1 then (cell::integer + 1)::text else cell end,
    '|'
  )
from cells
group by no
order by no;

結果は次の通りです。

    string_agg
------------------
 1|col1|col2|col3
 2|col1|col2|col3
 3|col1|col2|col3
 4|col1|col2|col3
 5|col1|col2|col3
(5 rows)

---

ここからは完全に余談(´・ω・｀) PostgreSQLでは珍しく配列がサポートされています。cellsというVIEWを作る際、上記の例では文字列関数を複雑に組み合わせていましたが、配列を利用するとかなり直感的に書き直すことができます。

create or replace view cells (no, index, cell) as (
  with recursive t (no, index, head, tail) as (
      select 
        no,
        0,
        null,
        string_to_array(cells, '|')
      from columns
    union all
      select
        no,
        index + 1,
        tail[1],
        tail[2 : array_length(tail, 1)]
      from t
      where array_length(tail, 1) > 0
  )
  select no, index, head from t where index > 0
);

こうして作られたVIEWの中身を確認すると、文字列関数を組み合わせていたものと同じ結果が得られます。

select * from cells order by no, index;

 no | index | cell
----+-------+------
  1 |     1 | 0
  1 |     2 | col1
  1 |     3 | col2
  1 |     4 | col3
  2 |     1 | 1
  2 |     2 | col1
  2 |     3 | col2
  2 |     4 | col3
  3 |     1 | 2
  3 |     2 | col1
  3 |     3 | col2
  3 |     4 | col3
  4 |     1 | 3
  4 |     2 | col1
  4 |     3 | col2
  4 |     4 | col3
  5 |     1 | 4
  5 |     2 | col1
  5 |     3 | col2
  5 |     4 | col3
(20 rows)

そもそも配列を利用すればVIEWなどを使わずとも「ずんだの表見出し++問題」を解くことができます。

with t (no, cells) as (
  select
    no,
    string_to_array(cells, '|')
  from columns
)
select
  array_to_string((cells[1]::integer + 1)::text || cells[2 : array_length(cells, 1)], '|')
from t
order by no;

この実行結果が以下。配列を使わない場合とまったく同じ結果が得られていることが分かります。

 array_to_string
------------------
 1|col1|col2|col3
 2|col1|col2|col3
 3|col1|col2|col3
 4|col1|col2|col3
 5|col1|col2|col3
(5 rows)