カテゴリー別アーカイブ: SELECT

PIVOT、UNPIVOT(SQL Server)

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

夏みかん

知人の方から、家に夏みかんの木があって、たくさん実がなるということで頂きました。その写真です。

さて、米国では、トランプ大統領が就任しましたが、就任直後から大統領令を連発し、大きな話題になるとともに、中にはきわめて大きな反発も起こっていますね。オバマケア見直し、TPPの離脱をはじめ、選挙のときから掲げてきた内容を具体的に実施しているわけですが、大きな波紋を呼んだものとしては、メキシコ国境の壁の建設、そして、中東・アフリカ7カ国からの渡航者の入国禁止(シリアからの難民受け入れの拒否も含む)です。
特に入国禁止については、米国政府内でも批判的な声が上がり、反対するデモも各地で起きていて、かなり混乱した状況ですね。この大統領令は憲法に違反しているということで、差し止めを命じるワシントン州の連邦地裁も現れました。強力な力を持つ大統領令ですが、司法のほうで歯止めをすることが可能だということが今回の内容で確認できました。さすが、民主主義の国!

しかし、おっかない世の中になってきました。これからどうなるのやら? できれば平和裏に進んでほしい。僕もやっぱり平和が好きですから。でもよく考えてみると、うちのかみさんもおっかなかったっけなあ?

 <本日の題材>
PIVOT、UNPIVOT (SQL Server)

以前、複数行のデータを集計して横展開という記事をアップしたことがありましたが、今回はこれと同様のことを、SQL ServerのPIVOTという関係演算子を使って試してみたいと思います。

前回の記事では、部品別の日別の仕入数量のデータが、部品コード、年月日、数量というようなレイアウトでDBに登録されている場合に、部品別に、月を横に並べて仕入数量の月別合計値を表示させるようなケースについて、case文を使いながら各月を横に並べて表示させるようにしていました。
このテーブルのデータを単純に抽出すると以下のようになります。

SELECT * FROM dbo.部品発注表;

blog75_部品発注表

部品別に、月を横に並べて仕入数量の月別合計値を表示させることを、pivot演算子を使って試してみます。

SELECT *
FROM (select 部品コード, substring(年月日,5,2)+'月' as '月', 数量
       from [dbo].[部品発注表]) as B_tab
PIVOT (
   SUM(数量) FOR 月 IN
  ([01月], [02月], [03月], [04月], [05月], [06月], [07月], [08月], [09月], [10月], [11月], [12月])
) as PVTab
ORDER BY PVTab.部品コード;

 blog75_pivot

 上記のSQLでは、FROM の後の集計対象テーブルの後に、PIVOT句を設定し、FOR .. IN で設定した値を列として出力して、それ毎にSUM関数で設定した項目[数量]を集計しています。なお、SUM関数とFOR .. IN で指定されていない「部品コード」でGROUP BYがされていて、それぞれの行が出力されています。最後のORDER BY句はオプションですが、並び順を設定できます。
Excelのピボットと同様のことが、SQLでできるのが確認できました。

また、以前別の記事「横に並んだ項目を縦の行データに変換」で、最初から列として登録されているデータを、複数行の縦のデータに変換して抽出するという件を取り上げましたが、UNPIVOT という関数がこれに該当します。

前回のデータをそのまま利用する場合に、まず、「商品売上」テーブルのデータをそのまま抽出すると、

SELECT * FROM dbo. 商品売上;

blog75_商品売上

 上記データについて、各月ごとのデータを行として表示する場合に、UNPIVOT演算子を使ったSQLは以下のようになります。

SELECT 商品CD, 月, 月売上
FROM
  (SELECT * FROM dbo.商品売上) p
UNPIVOT
  (月売上 FOR 月 IN
     (売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月, 売上10月, 売上11月, 売上12月, 売上1月, 売上2月, 売上3月)
)AS unpvt
ORDER BY unpvt.商品CD desc;

blog75_unpivot

 上記のSQLでは、FROM の後の対象テーブル(最初から横に並んだ項目を持つ)の後に、UNPIVOT句を設定し、FOR .. IN で設定した値を行として出力して、それ毎に月の売上を抽出しています。
また、FOR .. IN で指定されていない「商品CD」でGROUP BYがされていて、商品CD毎の、各月毎の売上が出力されています。

今日は以上まで

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

外部表(2)(ORACLE)

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

img_0418

写真は、前回も載せましたが、富山の五箇山という世界遺産合掌造り集落に行ったときの写真です。

さて、とうとうブラジル・リオデジャネイロで行われていたパラリンピックも終わりました。オリンピック、パラリンピックとも、多くの感動を私たちに与えて頂いたと、選手の方々に本当に感謝します。
パラリンピックでは金メダルはなかったということですが、過去最高のメダル数を獲得。また、ちょっと話題は古くなりますが、先月のオリンピックでは、体操団体での悲願の金メダル、個人総合2連覇の内村選手の最後の大逆転は圧巻でしたね。伊調馨選手のレスリング4連覇は、女子個人競技で史上初めてのこと。卓球でも男子団体で銀メダル、女子団体も銅メダル、女子バドミントンダブルスの日本初の金メダル、本当に手に汗握って応援しました。柔道も男子は全階級でメダル、男子の陸上4*100メートルリレー銀メダル、競泳陣の活躍、数え上げればきりがないです。

次はいよいよ東京オリンピック、パラリンピック! 4年間という長い努力と訓練の期間、想像を絶する戦いの末に、栄光があるのでしょう。私たちも、道は違いますが、選手の方々に負けないよう、与えられた環境の中で精いっぱい頑張って行かなければと背中を押される気がします。
選手の皆さん、本当にお疲れ様でした。そして、感動をありがとう!

<本日の題材>
Oracleの外部表(2)

前回、ORACLEの外部表についてORACLE_LOADER型を取り上げましたが、今回は、ORACLE_DATAPUMP型というものを試してみたいと思います。ORACLE_LOADER型では、CSVファイルからの読込しかできませんが、ORACLE_DATAPUMP型では、ダンプファイルにデータを書き込んだり、読込んだりすることができます。

例)
商品売上という、商品コード毎、年度毎の各月の売上金額を集計したテーブルがあり、そこに、2014年、2015年、2016年のデータが登録されているとします。
テーブルの定義は以下のようになっています。
DESC 商品売上

desc_%e5%95%86%e5%93%81%e5%a3%b2%e4%b8%8a

データとしてはこんな感じ。

%e5%95%86%e5%93%81%e5%a3%b2%e4%b8%8a%e3%83%87%e3%83%bc%e3%82%bf

 年度毎の件数は、
SELECT 年度, COUNT(*) FROM 商品売上
GROUP BY 年度
ORDER BY 年度;

%e5%b9%b4%e5%ba%a6%e3%81%94%e3%81%a8%e4%bb%b6%e6%95%b0

サンプルデータなので件数は少ないです。
それで、今年度(2016)のデータだけ残して、過去の年度のデータは、テーブルとしては持たないでダンプファイルに保存し、必要なときには外部表としてアクセスするようにしたいと思います。

まず、2014年のデータの外部表を作成し、外部表のダンプファイルにデータを登録します。

CREATE TABLE SYO_URIAGE_2014
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY temp_dir
  LOCATION ('uriage_2014.dmp')
)
AS SELECT * FROM 商品売上
    WHERE 年度 = '2014';

syo_uriage_2014

 次に、同様にして、2015年のデータの外部表を作成します。

CREATE TABLE SYO_URIAGE_2015
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY temp_dir
  LOCATION ('uriage_2015.dmp')
)
AS SELECT * FROM 商品売上
    WHERE 年度 = '2015';

syo_uriage_2015

実際に、ダンプファイルが指定のフォルダに作成されていることを確認してみます。今回ディレクトリに指定した「temp_dir」は、前回のブログで作成したように、「C:\temp」フォルダになりますので、エクスプローラで確認すると、

temp_dir

確かに、「URIAGE_2014.DMP」「URIAGE_2015.DMP」ファイルが作成されていました。

それぞれ作成した外部表のデータ件数を確認してみます。
SELECT COUNT(*) FROM SYO_URIAGE_2014;

syo_uri_2014_count

SELECT COUNT(*) FROM SYO_URIAGE_2015;

syo_uri_2015_count

元の商品売上テーブルからは、過去の年度のデータは削除し、2016年度のデータのみを残すことにします。

DELETE FROM 商品売上
WHERE 年度 < '2016';

del_%e5%95%86%e5%93%81%e5%a3%b2%e4%b8%8a

 処理の上で、2016年度のデータを見ればよい場合は「商品売上」テーブルに対してSELECTを実施しますが、過去のデータも見たい場合には、外部表を使うことで検索できます。

SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
  FROM 商品売上
 WHERE 商品CD = 'A0001'
UNION
SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
  FROM SYO_URIAGE_2015
 WHERE 商品CD = 'A0001'
UNION
SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
  FROM SYO_URIAGE_2014
 WHERE 商品CD = 'A0001'
ORDER BY 年度;

%e5%a4%96%e9%83%a8%e8%a1%a8_union

 また、上記でユーザ「BLOG」によって作成された外部ファイル(ダンプファイル)を、別のユーザで使用するようなこともできます。

今回、「BLOG2」というユーザを作成し、「temp_dir」をBLOG2ユーザからも読み書きできるようにします。

CREATE USER BLOG2
IDENTIFIED BY< password>    -- パスワードを設定します
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;

GRANT READ, WRITE ON DIRECTORY temp_dir TO BLOG2;

grant_read_write_blog2

作成したユーザー「BLOG2」でログインし、上記で作成した2015年度の商品売上データのダンプファイル(uriage_2015.dmp)を元にBLOG2ユーザ用の外部表「SYO_URIAGE2_2015」を作成してみます。

  CREATE TABLE SYO_URIAGE2_2015
   ("商品CD" VARCHAR2(20 BYTE),
              "年度" VARCHAR2(4 BYTE),
              "売上4月" NUMBER(12,0),
              "売上5月" NUMBER(12,0),
              "売上6月" NUMBER(12,0),
              "売上7月" NUMBER(12,0),
              "売上8月" NUMBER(12,0),
              "売上9月" NUMBER(12,0),
              "売上10月" NUMBER(12,0),
              "売上11月" NUMBER(12,0),
              "売上12月" NUMBER(12,0),
              "売上1月" NUMBER(12,0),
              "売上2月" NUMBER(12,0),
              "売上3月" NUMBER(12,0)
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY TEMP_DIR
      LOCATION
       ( 'uriage_2015.dmp'
       )
    );

 %e5%a4%96%e9%83%a8%e8%a1%a8%e4%bd%9c%e6%88%902

作成した外部表を検索してみます。

SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
 FROM SYO_URIAGE2_2015
ORDER BY 商品CD;

select_%e5%a4%96%e9%83%a8%e8%a1%a82

 データの検索ができました。
上記のように、一度作成した外部ファイル(ダンプファイル)は、別のユーザや別のデータベース環境に移動してそこで使用することも可能なんですね。
さらに、PREPROCESSOR句を使用すると、外部ファイルを圧縮したファイルに対して解凍しながら使用するということも可能なようです。

(※)注意点として、DATAPUMP(expdp)で作成したdmpファイルは外部表のダンプファイルとしては使用できないようです。

 今日は以上まで

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

外部表(ORACLE)

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

IMG_0441

夏休みに、妻の実家に行って来ました。長女だけは仕事の都合がつかず、一緒に行けなかったのが残念でしたが、あとの子供たちを引き連れて行ってきました。また、実家からそれほど遠くはないところに、世界遺産があるということで、そこに行ってきたのが、上の写真です。

ここは世界遺産合掌造り集落といって、岐阜県の白川郷・荻町集落、富山県の五箇山・菅沼集落、五箇山・相倉集落があり、今回行ってみたのは五箇山・菅沼集落です。東海北陸自動車道の五箇山インターチェンジからわりとすぐのところにあり、合掌造りの家屋が9棟くらいの3つの集落では一番小さな集落です。
そこに行くのに、駐車場のあるところからはエレベータで降りて行くのですが、別世界というか、昔にタイムスリップしたような不思議な感覚になりました。屋根がとても高く独特で、両手を合わせたような形になっていることから、合掌造りと呼ばれるようになったと思われるとのことです。
1軒1軒がとても大きかったですね。妻や子供たちも感動しながら、いっぱい写真を撮ってました。
それなりに多くの観光客(外国の方も結構いました)の人も来ていて、結構人気があるんですね。できれば、白川郷や相倉集落など他の集落も見て見たかったのですが、時間も遅くなってしまいそうだったので、菅沼集落だけを見て帰りました。
今度時間があれば、また妻と一緒に行ってみようっと!

IMG_0412_

 <本日の題材>
Oracleの外部表

ORACLEの場合に、テーブルにCSVファイル用のようなデータを読込む処理にSQL*Loaderというツールがあります(前回の投稿の中で、ちょこっと使っています)が、実際のテーブルには読込まずに、あたかもテーブルが存在するかのようにそういった外部ファイルにアクセスすることができるツールとして、外部表というものがあります。今回はそれを取り上げてみたいと思います。

まず、準備として、外部表によって読取りまたは書込みが行われるファイルのデフォルトの位置としてのディレクトリを作成します。以前、UTL_FILEパッケージについて取り上げたときに、ディレクトリの作成を行いましたが、今回も同様に設定します。

構文:
CREATE [OR REPLACE] DIRECTORY <
ディレクトリ名>
 AS ‘<
ディレクトリ・パス>

) SYSDBA権限でログインし、ディレクトリを作成します。

CREATE OR REPLACE DIRECTORY temp_dir
AS 'C:\temp';

create_directory

※このディレクトリは、サーバ上のディレクトリになります。

実行ユーザ「BLOG」にディレクトリに対する読込権限と書き込み権限を付与します。

GRANT READ, WRITE ON DIRECTORY temp_dir TO blog;

grant_read_write

 外部表の作成は、CREATE TABLE...ORGANIZATION EXTERNAL文を使用します。
今まで何度か使用している商品マスタについて、その内容をCSVファイルにしたものを外部表としてアクセスしてみたいと思います。

例)
CREATE TABLE syomst_load
  (syo_cd         VARCHAR2(10),
   syo_name   VARCHAR2(20),
   bnrui             VARCHAR2(20),
   price             NUMBER(10))
ORGANIZATION EXTERNAL
  (TYPE ORACLE_LOADER
   DEFAULT DIRECTORY temp_dir
   ACCESS PARAMETERS
     (RECORDS DELIMITED BY NEWLINE
      FIELDS TERMINATED BY ','
             (syo_cd,
              syo_name,
              bnrui,
              price
             )
     )
   LOCATION ('syomst.txt')
  )
REJECT LIMIT UNLIMITED;

cre_外部表

 上記のSQL文にあるように、temp_dir というディレクトリ(実際は、c:\temp )に、「syomst.txt」 というカンマ区切りの以下のようなCSVファイルを置いておきます。

A0001,チョコレート,お菓子,120
A0002,ビスケット,お菓子,200
A0003,ガム,お菓子,100
A0004,スナック,お菓子,140
B0001,りんご,果物,100
B0002,桃,果物,160
B0003,みかん,果物,80
B0004,梨,果物,120
C0001,キャベツ,野菜,160
C0002,にんじん,野菜,150
C0003,じゃがいも,野菜,100
C0004,玉ねぎ,野菜,150

ここまでの設定で、以下のSQLを実行すると、CSVファイルのデータを、あたかもテーブルが既に存在するかのように表示することができます。

SELECT * FROM syomst_load;

select_外部表

CSVデータの中身を変更してみます。
1行目と2行目のPRICEを130,210と変更して、CSVファイルを保存します。その後、再度先ほどのSQL文を実行します。

SELECT * FROM syomst_load;

select_外部表変更後

結果は、たしかに、1行目の金額が130円、2行目が210円に変わっていることが確認できました。

上記は、カンマ区切りのCSVファイルでしたが、当然固定長のファイルでも可能です。
また今回は、CREATE TABLE のときの外部表の型として、ORACLE_LOADER型を使用しましたが、もう一つ、ORACLE_DATAPUMP型というのもあります。ORACLE_LOADER型では、CSVファイルからの読込しかできませんが、ORACLE_DATAPUMP型では、ダンプファイルを使用しての処理が可能となります。これについては、また機会があれば、試してみたいと思います。

今日は以上まで

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

EXISTS句

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

IMG_0574_3

じゃーん。また、ブルートレイン「北斗星」の写真を載せてみました。さぞかし、北斗星のファンか、鉄道マニアと思われるかもしれませんが、実はそういう訳ではありません。
帰宅途中の駅で、たくさんのマニアの方が写真を撮っていたので、ついつられて一緒になって写真を撮ってしまいました。私って、優柔不断というか、つられ易いタイプですよね。
ただ、そんなにマニアでもない私ですが、今年中にブルートレインがなくなってしまうという噂が真実だとすると、本当に残念に思ってしまうのでした。

<本日の題材>
EXISTS句

今日は、EXISTS句について取り上げてみます。
EXISTS句は、通常は相関副問合せ(親の問い合わせで処理された各レコードを副問い合わせにおいて評価する)のかたちで使用することが多いと思います。副問合せにおいて、結果行が1行以上存在すればTRUE、存在しなければFALSEを返し、TRUEの場合の行が結果として抽出されることになります。

記述の方法は以下のようになります。

SELECT 列名1, 列名2, … , 列名n
 FROM 表名1
 WHERE EXISTS
 (SELECT ‘x’ FROM 表名2                                    -- ※
   WHERE 表名1.列名x = 表名2.列名x
       AND 他抽出条件)

※副問合せの中で SELECT ‘x’ のように固定値を使用しているのは、行がもどされるかどうかのみが判定されればよく、戻る列の値が何であっても問題はないこと、また定数を指定することで「存在有無の確認」であることをより明確に表現するという意味で、ORACLEなどでは慣習的に’x’などを指定することが多いようです。(SELECT * などでも問題はありません)

例として、以前使用したことのある、商品マスタと、売上テーブルを使用します。
まず、商品マスタのデータは以下:

SELECT * FROM 商品マスタ
 ORDER BY 商品CD;

exists_商品マスタ表示

売上テーブルの「2014/11」のレコードが以下の場合:

SELECT *
  FROM 売上テーブル
 WHERE TO_CHAR(受注日,'YYYY-MM') = '2014-11'
 ORDER BY 受注番号;

exists_150119受注

商品マスタのデータで、「2014/11」に受注のあった商品の一覧を抽出する場合、

SELECT 商品CD, 商品名
  FROM 商品マスタ
WHERE EXISTS (SELECT 'x'
                FROM 売上テーブル
               WHERE 商品CD = 商品マスタ.商品CD
                 AND TO_CHAR(受注日,'YYYY-MM') = '2014-11')
 ORDER BY 商品CD;

exists_1411受注のあった商品
2014/11」に1回でも受注のあった商品が抽出されました。

逆に、「商品マスタのデータで、「2014/11」に一度も受注のなかった商品の一覧を抽出する場合、NOT EXISTS を使用します。

SELECT 商品CD, 商品名
  FROM 商品マスタ
WHERE NOT EXISTS
(SELECT 'x'
    FROM 売上テーブル
 WHERE 商品CD = 商品マスタ.商品CD
        AND TO_CHAR(受注日,'YYYY-MM') = '2014-11')
ORDER BY 商品CD;

 not_exists_1411受注のなかった商品

SQL Serverでも同様です。
「2014/11」に1回でも受注のあった商品を抽出する場合:

SELECT商品CD,商品名
  FROM dbo.商品マスタ
WHERE EXISTS
(SELECT 'x'
    FROM dbo.売上テーブル
 WHERE 商品CD = 商品マスタ.商品CD
       AND SUBSTRING(CONVERT(VARCHAR, 受注日,112),1,6) = '201411')                   --※2
 ORDER BY 商品CD;

exists_1411受注のあった商品_sqlsv

※2:SQL Server 2012からは、FORMAT関数を使っても可能

「2014/11」に1度も受注のなかった商品を抽出する場合(FORMAT関数を使う):

SELECT商品CD,商品名
  FROM dbo.商品マスタ
WHERE NOT EXISTS
(SELECT 'x'
     FROM dbo.売上テーブル
  WHERE 商品CD = 商品マスタ.商品CD
         AND FORMAT(受注日,'yyyyMM') = '201411')
 ORDER BY 商品CD;

not_exists_1411受注のなかった商品sqlsv

 結果はORACLEと同様になりました。

今日は以上まで

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

再帰SQL

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

autumn-leaves_00121

紅葉が好きなので、今回も紅葉の写真をアップしました。皆さんはどうですか?
先日、ITコーディネータのイノベーション経営セミナーに参加した時に、あるコンピュータメーカ:F社の方が、ハッカソン、アイデアソンについての取り組みを説明をされていました。そこではさくらで東北地方を元気にしようということで、いろんな立場の違う方たちが短期集中的に意見を出し合って、アイデアを作り上げたという内容でした。確かにある会社だけで考えるよりは、素晴らしいアイデアが作れそうな気がしますね。画期的な取り組みだと感じました。
ファシリテーションを如何にうまくやれるかとか、実際にサービスに結び付けるところまで持って行けるのかとか、いろいろやる上では課題もありそうにも思えますが、今後はこういう「共創」ということもより増えて行きそうですね。

<本日の題材>

再帰SQL

前々回前回の続きで、階層構造のデータの表示を、再帰SQLを使用して行うという内容を取り上げたいと思います。階層構造のデータとして、部署データを使います。

CREATE TABLE 部署マスタ(
  部署NO    VARCHAR2(5)
, 部署名    VARCHAR2(40)
, 親部署NO  VARCHAR2(5)
, CONSTRAINT PK_部署マスタ PRIMARY KEY (部署NO));

INSERT INTO 部署マスタ VALUES('10000', '人事総務部', null);
INSERT INTO 部署マスタ VALUES('20000', '経理部', null);
INSERT INTO 部署マスタ VALUES('30000', '開発企画部', null);
INSERT INTO 部署マスタ VALUES('40000', '営業部', null);
INSERT INTO 部署マスタ VALUES('11000', '人事課', '10000');
INSERT INTO 部署マスタ VALUES('12000', '総務課', '10000');
INSERT INTO 部署マスタ VALUES('13000', '教育課', '10000');
INSERT INTO 部署マスタ VALUES('21000', '経理課', '20000');
INSERT INTO 部署マスタ VALUES('31000', '企画課', '30000');
INSERT INTO 部署マスタ VALUES('32000', '開発課', '30000');
INSERT INTO 部署マスタ VALUES('33000', 'システム課', '30000');
INSERT INTO 部署マスタ VALUES('41000', '第一営業課', '40000');
INSERT INTO 部署マスタ VALUES('42000', '第二営業課', '40000');
INSERT INTO 部署マスタ VALUES('32100', '基礎研究グループ', '32000');
INSERT INTO 部署マスタ VALUES('32200', '商品開発グループ', '32000');

この部署マスタの階層情報を、START WITH句、及びCONNECT BY句を使用したSQLで検索すると、以下のようになります。

SELECT 部署NO, 部署名, 親部署NO,
  LEVEL, SYS_CONNECT_BY_PATH(部署名,',') AS 経路
  FROM 部署マスタ
 START WITH 親部署NO IS NULL
 CONNECT BY PRIOR 部署NO = 親部署NO
 ORDER SIBLINGS BY 部署NO;

結果は以下です。
oracle_kaisou_busyo

これを、再帰WITH句を使った再帰SQLで表現すると以下のようになります。

WITH REC_BUSYO(部署NO, 部署名, 親部署NO, 階層LEVEL, 経路) AS(
SELECT 部署NO, 部署名, 親部署NO, 1, 部署名
  FROM 部署マスタ
 WHERE 親部署NO IS NULL
UNION ALL
SELECT b.部署NO, b.部署名, b.親部署NO,
  a.階層LEVEL+1, a.経路 || ',' || b.部署名
  FROM REC_BUSYO a, 部署マスタ b
 WHERE a.部署NO = b.親部署NO)
SEARCH DEPTH FIRST BY 部署NO SET Sortkey
SELECT * FROM REC_BUSYO;

結果は以下です。
oracle_kaisou_busyo_saiki

WHERE のa.部署NO = b.親部署NOで、PRIOR 部署NO = 親部署NO の部分を代わりに表し、SEARCH DEPTH FIRST BYのSERCH句を使って深さ優先探索(探索対象となる木の最初のノードから、目的のノードが見つかるか子のないノードに行き着くまで、深く伸びていく探索)順にソートすることで、ORDER SIBLINGS BYの階層順序を壊さずにソートすることを代用しています。

SQL Serverでも同様のことを再帰SQLで試してみました。

WITH REC_BUSYO(部署NO,部署名,親部署NO,階層LEVEL,経路) AS(
SELECT
  部署NO, 部署名 ,親部署NO
, 1,  CAST(部署名 AS NVARCHAR(4000)) AS 経路
  FROM 部署マスタ
 WHERE 親部署NO IS NULL
UNION ALL
SELECT
  b.部署NO, b.部署名, b.親部署NO
, a.階層LEVEL+1, a.経路 + N',' + b.部署名
  FROM REC_BUSYO a, 部署マスタ b
 WHERE a.部署NO = b.親部署NO)
SELECT * FROM REC_BUSYO
ORDER BY 部署NO;
sqlserver_kaisou_busyo_saiki

※経路のところで、CASTでNVARCHAR(4000) に変換しないでそのままで実行すると、
メッセージ240、レベル16、状態1、行1
再帰クエリ"REC_BUSYO" の列"経路" で、アンカーの型と再帰部分の型が一致していません。
のようなエラーが出ました。

今日は以上まで

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

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技術ブログへ
にほんブログ村

SELECTのCASE句について

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

1402143552101

たまにはITコーディネータについての情報もあったほうがいいかと思い、ITコーディネータ協会のHPから気づいた点を報告します。結構HPもリニューアルしていますね。

それで、1年以上前からイノベーションが創出できる次世代高度IT人材の育成について、独立行政法人情報処理推進機構(IPA)と共同で呼びかけ「IT融合人材育成連絡会」を立ち上げていました。そして「IT融合人材」の具体的な育成と組織のあり方についての検討成果についての最終報告書がホームページで公開されているようです。
http://www.itc.or.jp/news/inv20140325.html

イノベーションを創出できる人材を、あるプロセスに従うことで効果的に育成できれば?日本の将来にとっても本当に大きいことですよね。頑張れニッポン!

<本日の題材>
SELECTのCASE句について

今回は、最近システムを作り込むときに確認した内容で、SELECT文で複数の条件でのそれぞれの件数の結果を1つのSQLで実施する際に、CASE句を利用したケースについて記載します。(ORACLEです)

例)あるテーブル(受注実績テーブルとします)のデータで、あるフラグ(判定フラグとします)に「1」が立っているものと「0」のもの、及び全体の合計件数を抽出する。

SELECT
受注年月
, COUNT(CASE 判定フラグ WHEN '1' THEN 1 ELSE NULL END) AS 判定有
, COUNT(CASE 判定フラグ WHEN '0' THEN 1 ELSE NULL END) AS 判定無
, COUNT(*) 合計件数
FROM 受注実績
GROUP BY 受注年月
ORDER BY 受注年月;

oracle_case_1

他の方法としては、インラインビューを使用して下記のように行うこともできます。

SELECT
受注実績1.年月
, 受注実績1.件数 判定有
, 受注実績2.件数 判定無
, 受注実績3.件数 合計件数
FROM
(SELECT 受注年月, COUNT(*) 件数 FROM 受注実績
WHERE 判定フラグ = '1'
GROUP BY 受注年月) 受注実績1
, (SELECT 受注年月, COUNT(*) 件数 FROM 受注実績
WHERE 判定フラグ = '0'
GROUP BY 受注年月) 受注実績2
, (SELECT 受注年月, COUNT(*) 件数 FROM 受注実績
GROUP BY 受注年月) 受注実績3
WHERE 受注実績1.受注年月 = 受注実績2.受注年月
AND 受注実績1.受注年月 = 受注実績3.受注年月
ORDER BY 受注実績1.受注年月;

oracle_case_2

 

でも、CASE句を使って抽出したほうが簡単ですね。
CASE文はいろいろなところで使用できますが、1つの例としてあげました。

今日は以上まで

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

SELECTのTOP N分析(Oracle)

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

公園_4

サッカーワールドカップは、日本は十分力を出し切れず、一次予選敗退。本当に残念でした。最後のコロンビア戦は結構頑張っていたし、前半最後に追いついて、これはやってくれるんじゃないかと思ったのですが、カウンターを見事に決められ、終わってみれば完敗でした。アジアのチームはどこも1勝もできず、世界の壁はやはり厚いです。

また、忙しかったお客様の開発案件も、いよいよ運用開始が近くなってきました。まだ気が抜けません。

<本日の題材>
SELECTのTOP N分析について

前回、Microsoft SQL ServerのSELECTでのTOP句について見ましたが、今回はこれと同様のことをOracleで行う場合について考えてみます。Oracleの場合には、ROW_NUMBER関数を使うと同様なことが可能です。

例)今日の受注テーブルから受注番号順に先頭5件を抽出する。 SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY 受注NO) JNO, 受注NO, 受注日, 顧客番号, 決済金額 FROM 受注テーブル)
WHERE JNO <=5 ORDER BY JNO;

Oracle_row_number1

また、ROW_NUMBER関数を使用せずに、インラインビューを使用した問い合わせでも可能です。

SELECT * FROM (
SELECT * FROM 受注テーブル
ORDER BY 受注NO
)
WHERE ROWNUM <= 5
ORDER BY 受注NO;

Oracle_インラインビュー1

顧客番号順に並べた最初の5件ということにしたい場合には、以下のようになります。

SELECT * FROM (
SELECT * FROM 受注テーブル
ORDER BY 顧客番号
)
WHERE ROWNUM <= 5
ORDER BY 顧客番号;

Oracle_インラインビュー2

OracleにはTOP句はないですが、上記のような方法で同様のことができますね。
※ちなみに、SQL ServerでもROW_NUMBER関数は使えます。

今日は以上まで

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

SELECT TOP句について

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

1402143571315

いよいよサッカーワールドカップが始まりました。
今回は、逆転勝ちの試合も結構多いようですね。スペインがオランダに5対1で敗れた衝撃的な試合もありました。日本も初戦のコートジボアール戦は逆転負けしてしまいましたが、2戦目のギリシャ戦は、是非勝利を飾ってほしいものです。


<本日の題材>
SELECTのTOP句について

Microsoft SQL Serverの場合には、SELECTでのクエリの結果の行数を指定した行数、または割合(何パーセントか)に制限するTOP句という便利なものがあります。

今回はこれについて見てみたいと思います。

例)今日の受注テーブルから先頭5件を抽出する。
SELECT TOP 5 * FROM 受注テーブル
WHERE 受注日 = CONVERT(CHAR(8) , GETDATE(), 112);

sqlserver_9回目_1

例)先頭の5%を抽出するという場合は以下:
SELECT TOP 5 percent * FROM 受注テーブル
WHERE 受注日 = CONVERT(CHAR(8) , GETDATE(), 112);

これを、さらに、顧客番号順に並べた最初の5件を表示したいという場合には、以下のようになります。

SELECT TOP 5 * FROM 受注テーブル
WHERE 受注日 = CONVERT(CHAR(8) , GETDATE(), 112)
ORDER BY 顧客番号;

sqlserver_9回目_2

結構便利に使えます。
次回は、これをOracleで表現する場合を題材にする予定です。

今日は以上まで

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