カテゴリー別アーカイブ: 階層問合せ

WITH句と共通テーブル式(CTE)

ITコーディネータのシュウです。

autumn-leaves_00083 (1)

先日、ITCのポイント取得も兼ねて、≪MCPCモバイルソリューションフェア2014≫のセミナーに参加してきました。
http://www.mcpc-jp.org/fair2014/index.html
モバイル端末を業務で使用するケースが増えてきていますが、それに伴って紛失や盗難による情報漏洩のリスクも高まってきています。紛失した際にすぐに位置情報を確認し、端末をロックしたり、データを消去したり、消去したというレポートを受信できるようなサービスについての紹介のセミナーも数社行っていました。また、端末の電源が落ちていても、独自のBIOSによりリモートから操作することができるようにしているものも出てきていますね。お金目当てで盗難するケースも出てきている中、業務でモバイルを使うためには、このようなサービスが必要な時代であることを感じます。

<本日の題材>
WITH句と共通テーブル式(CTE)

標準SQL規格 SQL99 より、WITH句が導入されて、SELECT文の中で記述するインラインビュー(FROMの後に指定する問合せ)をWITH句で記述し、そのSQL文中に限り繰り返し使用できるようになりました。このWITH句に記述したものを「共通テーブル式」(CTE)といいます。
(OracleではOracle9iから対応、ただし、再帰WITH句はOracle11gR2から対応)
前回の階層構造のデータ表示(Oracle)で例として挙げたSQLの外部結合で使用している部分をWITH句で共通テーブル式を使うかたちにすると、以下のようになります。

WITH E2(EMPNO, ENAME) AS(
SELECT EMPNO, ENAME FROM EMP)   
SELECT
  E1.EMPNO AS 従業員番号
, E1.ENAME AS 従業員名
, LPAD(' ',(LEVEL-1)*2,' ')||E1.JOB AS 職務名
, E1.MGR AS 上司従業員番号
, E2.ENAME AS 上司従業員名
, LEVEL AS 階層LEVEL
  FROM EMP E1
  LEFT OUTER JOIN E2 ON E1.MGR = E2.EMPNO
 START WITH E1.JOB = 'PRESIDENT'
 CONNECT BY PRIOR E1.EMPNO = E1.MGR
 ORDER SIBLINGS BY E1.ENAME;
oracle_kaisou_1_2

※結果は前回と同じになります。また、SQL自体も上から下に順に読めるので、わかりやすくなります。

次に、再帰WITH句についてですが、基本的な書き方は以下のようになります。

WITH 共通テーブル式(CTE)名(column1, column2, ...)
AS (
    /* 元の SELECT文 */
    UNION ALL
    /* CTE名を参照する SELECT文 */
)
SELECT column_x1, column_x2, ... from CTE名;

例として、これもよく挙げられる例ではあると思いますが、1から20までの正の数を順に出力する場合のSQLです。

WITH RECUR_SEISU(val) AS (
SELECT 1 FROM DUAL
UNION ALL
SELECT val+1
FROM RECUR_SEISU
WHERE val+1 <= 20)
SELECT val FROM RECUR_SEISU;
oracle_saiki_seisu
再帰WITH句で、最初にUNION ALLまでのSELECT文を実行して、その結果を使用してUNION ALLの下のSELECT文を実行し、条件を満たすあいだそれを繰り返して処理をしているのが確認できます。

SQL ServerではSQL Server2005から共通テーブル式を使用できるようになっています。
上記のSQLは下記のようになります。

WITH RECUR_SEISU(val) AS (
SELECT 1
UNION ALL
SELECT val+1
FROM RECUR_SEISU
WHERE val+1 <= 20)
SELECT val FROM RECUR_SEISU;
oracle_saiki_seisu2

※結果は同じです。 

ちなみに、MySQLでは、WITH句を使っての共通テーブル式についてはまだ未対応のようです。

今日は以上まで

 

にほんブログ村 IT技術ブログへ
にほんブログ村

階層構造のデータ表示(Oracle)

ITコーディネータのシュウです。

autumn-leaves_00007

ITコーディネータ関連の情報です。
ITCA(ITコーディネータ協会)のホームページには、IT活用を考えている中小企業の皆様へと題して(経済産業省からのご案内ということで)、IT導入時に利用可能な予算・融資制度の紹介やIT導入を検討するにあたって、IT活用に成功した企業の事例をいろいろと紹介しているサイトが載せてあります。
http://www.itc.or.jp/kakushinit/
その中の一つに、中小企業・小規模事業者の未来をサポートするサイト「ミラサポ」というものが昨年開設され、国や公的機関の支援情報・支援施策等いろいろな情報を提供していますね。
https://www.mirasapo.jp/index.html
また、ITコーディネータによる支援事例も下記のサイトにあります。
http://wwk.itc.or.jp/itkeiei/
ITCAの宣伝のようになってしまいましたが、ちょっと紹介まで

<本日の題材>
階層構造のデータ表示(Oracle)

今回は、時折社内でも話題になる階層構造のデータ表示について取り上げてみます。
Oracleでは、階層構造のデータを表示するのに便利な START WITH句、CONNECT BY句というのが用意されています。よく、例として上げられるのが、SCOTTユーザのテーブル「emp」表で、このテーブルには各従業員の上司の従業員番号が項目としてあり、それを使って上司と部下の関係を階層構造で表示する例を見てみます。例えば下記のようなSQLを使います。

SELECT
E1.EMPNO AS 従業員番号
, E1.ENAME AS 従業員名
, LPAD(' ',(LEVEL-1)*2,' ')||E1.JOB AS 職務名
, E1.MGR AS 上司従業員番号
, E2.ENAME AS 上司従業員名
, LEVEL AS 階層LEVEL
FROM EMP E1
LEFT OUTER JOIN (SELECT EMPNO, ENAME FROM EMP) E2
ON E1.MGR = E2.EMPNO
START WITH E1.JOB = 'PRESIDENT'
CONNECT BY PRIOR E1.EMPNO = E1.MGR
ORDER SIBLINGS BY E1.ENAME;
oracle_kaisou_1_2

START WITH句で、E1.JOB='PRESIDENT' (社長) を起点(ルート)として階層表示し、CONNECT BY句で親子関係を条件付けしています。PRIOR E1.EMPNO = E1.MGR で親のレコードのEMPNO = 子のレコードのMGR という条件になります。
また、LEVEL は階層レベルを意味していて、職務名のところの頭に階層LEVEL*2ずつの空白を付けて階層が分かりやすくしています。
ORDER SIBLINGS BY句は、階層問い合わせの場合に、CONNECT BYの結果の階層順序を壊さずソートして表示するためのものになります。

START WITH句で、ルートとなるのが JOB='PRESIDENT' という情報がわからない場合は、MGRがNULL(上司の従業員番号がない)のメンバーが階層のSTARTということから、下記のように設定することもできます。

SELECT
E1.EMPNO AS 従業員番号
, E1.ENAME AS 従業員名
, LPAD(' ',(LEVEL-1)*2,' ')||E1.JOB AS 職務名
, E1.MGR AS 上司従業員番号
, E2.ENAME AS 上司従業員名
, LEVEL AS 階層LEVEL
FROM EMP E1
LEFT OUTER JOIN (SELECT EMPNO, ENAME FROM EMP) E2
ON E1.MGR = E2.EMPNO
START WITH E1.EMPNO =
(SELECT EMPNO FROM EMP WHERE MGR IS NULL)
CONNECT BY PRIOR E1.EMPNO = E1.MGR
ORDER SIBLINGS BY E1.ENAME;

※結果は同じです。
また、SYS_CONNECT_BY_PATH(column, char) 句を使うと、ルートからノードへの列(column)の値の経路を、char で指定した文字で区切って返すかたちになります。

SELECT
EMPNO AS 従業員番号
, SYS_CONNECT_BY_PATH (JOB,'/') AS 職務パス
, ENAME AS 従業員名
, CONNECT_BY_ROOT ENAME AS 最終上司名
FROM EMP
START WITH JOB = 'PRESIDENT'
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME;

oracle_kaisou_3

職務パスということで、社長からの階層を区切り文字「/」を使って表示しています。
※CONNECT_BY_ROOT を使用すると、階層のルートの値(この場合は、一番の上司)を表示することができます。(階層のルートが複数あるような場合はそれぞれのルートの値が取れることになります)
ちなみに、上記の内容は、Oracleの場合に限定した内容になります。

次回からは、他のデータベースでも可能な方法として、再帰SQLについて、及びそれを使って階層構造を表示する方法について取り上げてみたいと思います。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村