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

写真は、島根の実家の庭で咲かせているボタンの花です。以前、大根島（島根県松江市）の由志園に行ったときに購入したボタンを、父親が庭に植えていたのですが、今年もきれいに咲いたということで、父も喜んでいました。
また、先祖代々大事に育ててきた樹齢200年ほどの松の木があるのですが、毎年初夏に、新芽を摘む手入れをしているということで、父親が頑張って剪定作業を行っていました。今まであまり意識していなかったのですが、松の木の樹形を整えるのもなかなか大変だということを、改めて感じました。

＜本日の題材＞
バイト数分の表示と数値3桁ごとカンマ区切り表示（SQL Server）

データは日本語や英語、半角数値などが入り混じっている項目について、指定のバイト数のみを表示したいという場合や、数値項目を3桁ごとにカンマ区切りで表示させたい場合などが結構あるかと思いますが、本日は、それについて取り上げてみたいと思います。

例)
項目は、日本語や英語、半角数字などが混在していてバイト数と文字数が一致しない状態だけれども、帳票出力などで、どうしても限られたバイト数分しか表示したくないというケースがあるかと思います。そのときに、指定したバイト数分まで表示するSQLを確認してみます。

まず、今回確認するテーブルを作成し、データを登録します。
CREATE TABLE test_tbl(
ID INT,
NAME NVARCHAR(40),
QTY NUMERIC(12,2)
CONSTRAINT PK_test_tbl PRIMARY KEY (ID));

INSERT INTO test_tbl VALUES(1, 'テスト名称abcd_あいう', 1234567.12);
INSERT INTO test_tbl VALUES(2, 'テスト名称２_10xy', 20046);
INSERT INTO test_tbl VALUES(3, 'テスト名称３_データzyx', 33592.67);
INSERT INTO test_tbl VALUES(4, 'テスト名称4_全角半角混在', 4192.8);

それでは、作成したテーブルの「NAME」列について、15バイト分だけ抽出してみたいのですが、単純に最初の15文字だけをLEFT関数で抽出してみると、以下のようになります。

SELECT
ID,
left(NAME, 15) AS 名称,
QTY AS 数量
FROM test_tbl
ORDER BY ID;

これは、2バイト文字が混じっているので、明らかに15バイトよりも多いバイト数が抽出されます。
次に、convert関数を利用して抽出してみます。

SELECT
ID,
convert(varchar, substring(convert(text, NAME), 1, 15)) AS 名称,
QTY AS数量
FROM test_tbl
ORDER BY ID;

CONVERT関数にて、TEXT型に変換したものをSUBSTRING関数にて指定のバイト数を取り出し、さらに VARCHAR型に変換することで、指定バイト数分の表示ができるということです。
ちなみに、4行目の名称については、15バイト目がマルチバイト文字になるため、SQL Serverのほうでその前の文字までが表示されるかたちにしてくれます。

次に、上記の数量について、3桁ごとにカンマ区切りで表示させてみます。
このとき、数値を money型に変換した後、CONVERT関数の第3引数を1にすることで、3桁区切りの数値になります。

SELECT
ID,
convert(varchar, substring(convert(text, NAME), 1, 15)) AS 名称,
convert(nvarchar, convert(money, QTY), 1) AS 数量
FROM test_tbl
ORDER BY ID;

ただし、CONVERT関数でMONEY型に変換すると、小数点以下は2桁に四捨五入されるとともに、小数点以下2桁は必ず表示されるので、元の項目が整数であったとしても、’.00’が付いて表示されますので、注意が必要です。整数で表示したい場合は、’.00’を空白にREPLACEする必要があります。

今日は以上まで

にほんブログ村

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

自宅から会社に行く途中に道路のそばに咲いているポピーの花です。自転車で通るので、きれいに咲いているときには少し止まって眺めたりするこの頃ですが、若いときは、頭の中は仕事のことか、差し迫ったやるべきことなどでいっぱい（もしくはぼーっとしている？）で、見ていても記憶に残らないという感じだったと思います。妻にあそこにきれいな花が咲いているでしょう？と聞かれても、そうだっけ？という返事をするので、そういう感覚が欠落しているかわいそうな人だとあきれられていたものですが、少しずつ花などの自然も意識するようになってきました。

ちなみに、ポピーは色とりどりの花を咲かせるケシ科の植物の総称で、その実から採れる乳液には、入眠や麻痺の作用があることから、紀元前400年頃のギリシアでは麻酔薬や睡眠導入薬として用いられていたそうです。花言葉も「なぐさめ」「心の平静」「いたわり」「思いやり」などその効能にちなんだものが多くあり、相手をいたわる気持ちや、助けたい気持ちを表現するときによく贈られる花だそうです。

＜本日の題材＞
APPLY（SQL Server）

今回は、APPLYという演算子について取り上げてみます。
以前、テーブル値関数について記事として載せたことがありますが、あるテーブルのデータの値と、その値によって取得されるテーブル値関数の結果を組み合わせて抽出したいという場合に、APPLY という演算子を使用することができるようです。（SQL Server 2005から）
SQLのFROM句の後に、まずテーブルを記載し、「CROSS APLLY」、もしくは「OUTER APPLY」の後側にテーブル値関数を指定するかたちになり、SELECT のほうではテーブルの列とテーブル値関数の結果の列を含めるかたちになります。
私も今まであまり使ったことがなかったのですが、今回、試してみようと思います。

例）
使用するテーブルは、以前ブログで使ったことのある、日本の都道府県、山、川、湖などの大きさや高さや長さなどをデータにしたランキングのデータを使ってみたいと思います。
今回使用するテーブル（「ランキング」「ランキング区分」）の定義は、下記のような設定。

データを抽出してみると、
「ランキング区分」テーブルは、

「ランキング」テーブルは、

今回使用するテーブル値関数は、「区分NO」の値からランキングテーブルのデータを抽出する簡単なものです。定義は以下：

CREATE FUNCTION [dbo].[ufn_ランク上位取得]
(
         @p区分NO DECIMAL(4,0)
) RETURNS @ランク上位 TABLE (
         区分名 VARCHAR(40),
         ランキング DECIMAL(3,0),
         名称 VARCHAR(40),
         数値 DECIMAL(8,1)
)
AS
BEGIN
         INSERT @ランク上位
         SELECT
                   K.区分名
                  ,RANK() OVER (ORDER BY R.数値 DESC)
                  ,R.名称
                  ,R.数値
         FROM dbo.ランキング R
         JOIN dbo.ランキング区分 K ON R.区分NO = K.区分NO
         WHERE K.区分NO = @p区分NO;
 
        RETURN;
END;

このテーブル値関数にパラメータを直接指定して抽出すると、

SELECT * FROM dbo.ufn_ランク上位取得(1)
ORDER BY ランキング;

これを、「ランキング区分」テーブルの「区分NO」をテーブル値関数にセットして、「区分NO」毎にランキング情報を抽出するように、APPLY句を使用すると、以下のように抽出できます。

SELECT K.区分NO, K.区分名, K.単位, R.ランキング, R.名称, R.数値
  FROM dbo.ランキング区分 K
  CROSS APPLY dbo.ufn_ランク上位取得(K.区分NO) AS R
ORDER BY K.区分NO, R.ランキング;

ここで、「ランキング区分」テーブルにあっても、テーブル値関数で結果のないものは抽出されないのですが、外部結合のように、上記のようなデータも左側のランキング区分の情報は表示し、テーブル値関数側の結果は null で表示したい場合には、「OUTER APPLY」演算子を使用します。

SELECT K.区分NO, K.区分名, K.単位, R.ランキング, R.名称, R.数値
  FROM dbo.ランキング区分 K
  OUTER APPLY dbo.ufn_ランク上位取得(K.区分NO) AS R
ORDER BY K.区分NO, R.ランキング;

 上記のように、「区分NO」=2, 3 のテーブル値関数では抽出されないデータも表示されることが確認できます。

ちなみに、APPLY演算子の右側は必ずテーブル値関数である必要はなく、上記の内容は、以下のようなSQLでも抽出できます。（CROSS APPLYの例）

SELECT K.区分NO, K.区分名, K.単位, RK.ランキング, RK.名称, RK.数値
  FROM dbo.ランキング区分 K
  CROSS APPLY
   (SELECT
                   K.区分名
                  ,RANK() OVER (ORDER BY R.数値 DESC) AS ランキング
                  ,R.名称
                  ,R.数値
         FROM dbo.ランキング R
         WHERE R.区分NO = K.区分NO) AS RK
ORDER BY K.区分NO, RK.ランキング;

今まではあまり使ったことはなかったのですが、今後は、使えるときもあるのではないかと思います。

今日は以上まで

にほんブログ村

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

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

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

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

 ＜本日の題材＞
PIVOT、UNPIVOT （SQL Server）

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

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

SELECT * FROM dbo.部品発注表;

部品別に、月を横に並べて仕入数量の月別合計値を表示させることを、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.部品コード;

 

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

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

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

SELECT * FROM dbo. 商品売上;

 上記データについて、各月ごとのデータを行として表示する場合に、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;

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

今日は以上まで

にほんブログ村

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

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

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

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

＜本日の題材＞
Oracleの外部表（２）

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

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

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

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

サンプルデータなので件数は少ないです。
それで、今年度（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';

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

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

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

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

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

SELECT COUNT(*) FROM SYO_URIAGE_2015;

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

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

 処理の上で、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 年度;

 また、上記でユーザ「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;

作成したユーザー「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'
       )
    );

 

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

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

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

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

 今日は以上まで

にほんブログ村

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

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

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

 ＜本日の題材＞
Oracleの外部表

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

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

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

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

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

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

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

GRANT READ, WRITE ON DIRECTORY temp_dir TO blog;

 外部表の作成は、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;

 上記の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;

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

SELECT * FROM syomst_load;

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

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

今日は以上まで

にほんブログ村

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

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

＜本日の題材＞
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;

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

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

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

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

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;

 

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;

※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;

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

今日は以上まで

にほんブログ村

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

先日、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;

※結果は前回と同じになります。また、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;

再帰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;

※結果は同じです。 

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

今日は以上まで

 

にほんブログ村

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

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

＜本日の題材＞
階層構造のデータ表示（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;


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;

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

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

今日は以上まで

にほんブログ村

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

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

それで、1年以上前からイノベーションが創出できる次世代高度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 受注年月;

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

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.受注年月;

 

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

今日は以上まで

にほんブログ村