スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

階層化されたデータを扱う

階層化されたデータをMySQLで扱うオレンジニュースからの技術ネタ)より

リレーショナルデータベースは階層化したデータ用に開発されなかったと考えたと思います。リレーショナルデータベースのテーブルは階層化されておらず(XMLのように)単に平たいリストです。階層化されたデータは親と子供の関係を持っており、リレーショナル データベースのテーブルでは自然に表すことができません。

とあるが、若干異論がある。RDBでも階層構造を格納することはできる。ただそれを階層構造として表示するのが厄介なだけである。

例えば、グループがサブグループを含んでいる場合、

グループ1
 グループ2
  グループ3
 グループ4
グループ5
 グループ6

これをXMLで表現すると綺麗にこのとおりになるが、RDBだと、

グループ1 0
グループ2 1
グループ3 2
グループ4 1
グループ5 0
グループ6 5

という形で、親へのポインタを別列に持つことになり、これゆえ平べったいと言われるのだろう。
だからといって、XMLのようにツリー構造であることが常によいとは限らない。この階層が無限に連なった場合、全グループを並列に並べようとしても下層まで追っていかないといけない。一方、RDBならその列を見るだけですべて取り出せる。この場合は表形式の方がシンプルだ。

ただRDBの場合、表からツリー構造を表示するとなると厄介だ。

先のリンクページでは、MySQLでどのようにするかが述べられている。

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3,
t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';


このような感じで、JOINをレベル数分だけ使う方法(Adjacency Listモデル)が示されている。しかしこの場合、あらかじめレベルがわかっていないとできないし、数十レベルになったとき筆者が述べているとおりパフォーマンスの問題が出てこないか心配になる。

そして別の方法が示されている。「ネストしたセット・モデル」と書かれていたが、これはツリー構造を一筆書きで追っていって、そのノードの右と左に番号を振っていくやり方で、右番号と、左番号が各ノードに付与される。そのメリットが延々と書かれていた(全部追っていくのは大変orz)。
どうやって先の方法と同じ階層表示ができるのかは原文の方に書いてあった。

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;


私はMySQLでこの階層問題の処理をしたことがあるが、ツリー構造の作成は、プログラム上で行った。
1.まずルートノードを探す(複数の場合あり)
2.その各ノードを親に持つ子ノードを探し子ノードリストに加える。
3.2を再帰的に繰り返す。またその際深さレベルもノードの属性にセットする。
という感じだった。

Oracleでは、SQLの抽出結果を階層構造で表示するテクニックという便利なものがある。

残念ながらMySQLにはないと思う。
こんな感じだ。

GRID GRNM PGRID
-- --------- ---
0 管理者 0
1 グループ名1 0
2 グループ名2 1
3 グループ名3 1
4 グループ名4 1
5 グループ名5 2
6 グループ名6 2
7 グループ名7 2
8 グループ名8 3
9 グループ名9 3
10 グループ名10 5

こういうテーブルM_GROUPがあったとする。
そこで下記のSQLを実行する。

select
  grid, level,
  lpad(' ',2*(level)) || grnm grnm
from
   m_group
start with grnm = '管理者'
connect by prior grid = pgrid;

ERROR:
ORA-01436: ユーザー・データでCONNECT BYのループが発生しました。

Oops!!!! 管理者のGRIDが0で親GRIDも0なのでループだといって怒られてしまった。
なので、
update m_group set pgrid=null where grid=0;

GRID GRNM PGRID
-- --------- ---
0 管理者
1 グループ名1 0
2 グループ名2 1
3 グループ名3 1
4 グループ名4 1
5 グループ名5 2
6 グループ名6 2
7 グループ名7 2
8 グループ名8 3
9 グループ名9 3
10 グループ名10 5

として、これで再度先のSQLを実行。

GRID      LEVEL GRNM
-- ------ ------------
   0          1   管理者
   1          2     グループ名1
   2          3       グループ名2
   5          4         グループ名5
  10          5           グループ名10
   6          4         グループ名6
   7          4         グループ名7
   3          3       グループ名3
   8          4         グループ名8
   9          4         グループ名9
   4          3       グループ名4

今度は綺麗に行った。何て便利なんだろう。MySQLでやったときのような小ざかしいプログラムを書く必要はなかった。

とはいえ、もしこれを使わず、Stored Procedureで処理したらどうなるだろうかと思った。方法としては、テーブルをごそっと持ってきて、先に述べたような手順でプログラム上で処理するか、あるいはSQLを使ってとなるが、これはあまり綺麗にできそうな気がしない。
どちらにせよ再帰関数が必要だが、ストアドで再帰的な関数は使えるだろうか。私の知る限りのPL/SQLでは原始的過ぎて、ちょっと面倒だ。ストアドの中でSQLを使わないなら、プログラムの中でやった方がずっと楽な気がする。

関連記事
スポンサーサイト

コメント

非公開コメント

プロフィール

dayan

Author:dayan
小職は、SE(システムエンジニア)を専門としておりますが、技術的な情報を中心に、それ以外に経済関連の日記、たわいもない日記も載せていきます。
[公式HPもよろしく!]

天気予報

-天気予報コム- -FC2-
リンク
ブロとも申請フォーム

この人とブロともになる

カテゴリー
最近の記事
ブログ内検索
最近のコメント
最近のトラックバック
RSSフィード
月別アーカイブ


上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。