Play With SQL Part 02

Goal
RDB で親子関係(parent_id – child_id)を表す many-to-many なテーブルがあった際に、SQL を用いて次の表のように parent_id をキーにして child_id を横方向に左詰めで展開したい。
子供の最大数は固定。親によって子供の数は異なる。

# サンプルテーブルの作成

CREATE TABLE junction(
   parent_id int
  ,child_id  int
);
表が作成されました。

INSERT INTO junction VALUES(1, 1);
INSERT INTO junction VALUES(1, 2);
INSERT INTO junction VALUES(1, 3);
INSERT INTO junction VALUES(2, 2);
INSERT INTO junction VALUES(3, 1);
INSERT INTO junction VALUES(3, 3);

  SELECT *
    FROM junction
ORDER BY parent_id, child_id;

 PARENT_ID   CHILD_ID
---------- ----------
         1          1
         1          2
         1          3
         2          2
         3          1
         3          3

6行が選択されました。

Discussion

# 案1(ホスト言語で頑張る)

parent_id ごとに child_id の一覧を取得し enum で番号を振るというありきたりなもの。
ただこれでは安直過ぎてつまらない。

# 案2(SQLで頑張る)

SQL に閉じてうまく列方向に展開する。
しばらく自力で考えてもうまい方法が見つからなかったので Google で検索。
すると “User’s Forum for DB2 Japan” でぴったりな回答を見つけた。

方針としては

  1. まず parent_id:child_id で row_number を採番し
  2. parent_id で group by して row_number を case で振り分けて、対応する child_id を表示するというもの

SQL (Oracle  dialect)としては次のようになる。

WITH foo AS (
SELECT j.* , row_number() over(PARTITION BY parent_id ORDER BY child_id) rn
           FROM junction j
)
  SELECT parent_id
       , max(CASE rn WHEN 1 THEN child_id ELSE NULL END) child_id1
       , max(CASE rn WHEN 2 THEN child_id ELSE NULL END) child_id2
       , max(CASE rn WHEN 3 THEN child_id ELSE NULL END) child_id3
    FROM foo
GROUP BY parent_id;

 PARENT_ID  CHILD_ID1  CHILD_ID2  CHILD_ID2
---------- ---------- ---------- ----------
         1          1          2          3
         2          3
         3          1          2

Memo

  • SQL 一発できれいに決めることができて気分がよい
  • DB2は実務では使ったことがないものの、 RDB の調べ物をする際に、ノイズが非常に少なく、有益な情報に当たりやすい
Advertisements
Tagged with: , , ,
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
%d bloggers like this: