ITコーディネータのシュウです。
近くの神社に息子と寄ってみました。三が日は毎年人が多いので、ずらして行ったところ、あまり人も多くなくて、すぐにお詣りできました。
ラッキー！
新年あけましておめでとうございます。
旧年中は、大変お世話になりました。
つたない文章ながら、少しずつアクセス数も増えてきて、大変うれしく思っております。
今年も、へたりながらも頑張りますので、よろしくお願いします。
今日は以上まで
にほんブログ村
あけましておめでとうございます
にほんブログ村
ＩＴＣ シュウちゃん のすべての投稿
にほんブログ村
NULLのソート順
ITコーディネータのシュウです。
あれ！ 青空だけ。雲一つない晴天でした。
わかりにく写真ですいません(＾＾；)
先日、大寒波で北海道や東北、北陸などの日本海側では、猛吹雪や相当な雪も積もったようです。一方、私の住む埼玉では、そのことが信じられないような晴れやかな空でした。私も日本海側で育ったため、冬はどんよりとした空と雪のイメージを持っているのですが、関東は違いますね。同じ日本なのにねえ。私は、勿論、すっきりと晴れ渡る関東の気候が好きです（雪で苦しむ人たちには申し訳ないですが）。
そして、今日もとても良い天気です。今日は、会社の納会の日なのですが、今年もいろいろありました。心を今日の空のように全てクリアして、来年の出発に備えていきたいと思います。来年もよろしくお願いします。
＜本日の題材＞
NULLのソート順
先日、NULLを題材にしましたが、本日も、もう少しNULLについて取り上げてみたいと思います。
以前にブログで取り上げた内容のサンプルでも、ソート順において、NULL値の結果がORACLEとSQL Serverで異なる場合があって、同じにならないものかと考えてしまうケースがありましたが、ORACLEでは、ソート順でのNULL値の位置を制御することができますね。
前回の社員マスタをサンプルで見てみます。
SELECT * FROM 社員マスタ
ORDER BY 歩合給, 社員番号;
歩合給がNULLの方は、一番下のほうに出ています。これは、ORACLEではソートにおいてはNULLは無限大と同じ扱いとなるためです。
逆に、SQL Serverでは、同じSQLでもNULLは一番上に表示されます。
SQL Serverでの結果は以下：
ORACLEとは反対です。
ORACLEでは、ORDER BY句で NULLS FIRST、NULLS LAST（昇順のデフォルト）というキーワードを追加することでNULLの位置を制御することができます。
上記の例では、
SELECT * FROM 社員マスタ
ORDER BY 歩合給 NULLS FIRST, 社員番号;
SQL Serverの結果と同様に、NULLを先頭に表示させることができます。
逆に、SQL Serverの場合には、上記のような設定ができないため、少し工夫が必要になります。よくある例としては、以下のように対象の列の値が NULL の場合には「1」、NULLでない場合には「0」を返して、返された値を昇順にするか降順にするかで切り替えるという方法です。
SELECT *
FROM 社員マスタ
ORDER BY CASE WHEN 歩合給 IS NULL THEN 1 ELSE 0 END
, 歩合給, 社員番号;
NULLデータを一番下に持ってくることができました。
今日は以上まで
にほんブログ村
NULLについて
ITコーディネータのシュウです。
会社からの帰宅途中で撮った札幌行き寝台特急「北斗星」の写真です。北斗星はブルートレインとも言われるので、通常は青色だと思いますが、写真はカシオペア色の牽引機ですね。
見かけるたびに、一度は乗ってみたいと思っていたのですが、来年中には北斗星が廃止される方針というニュースが流れています。残念ですね。
自分も、田舎（北海道ではないですが）から東京に初めて大学受験に来たとき、寝台特急で来たことを今も覚えています。
一度乗ってみたかったな～！
＜本日の題材＞
NULLについて
NULLは、データが存在しない（空である）ということを意味しますが、データを扱う上で、NULLというのはときに非常にやっかいな存在ですね。（NULLは「不明」という言葉で表現する場合もあります）誤って使用すると、データが正しく抽出されないということを何度も経験した方もいらっしゃると思います。
今日は、NULLを扱う際の注意点について取り上げたいと思います。
以下のような社員マスタを使用します。歩合給のデータがある人もいれば、NULLの人もいる状態です。
SELECT * FROM 社員マスタ
ORDER BY 社員番号;
ここで、「固定給」+「歩合給」がその人の給与となるので、給与が300000以上の方を出す場合に、以下のように計算してしまうと、
SELECT * FROM 社員マスタ
WHERE 固定給+歩合給 > 300000
ORDER BY 社員番号;
となってしまい、求めたい人数より少ない結果が出ました。固定給が 300000 以上だけれども、歩合給が NULLの方が抽出されていません。
試しに、歩合給がNULLの方を抽出するのに、=NULLとした場合、
SELECT * FROM 社員マスタ
WHERE歩合給 = NULL;
結果は1件も抽出されません。
これは、NULLに対しては、以下のような比較を行う式は全て unknown と評価されてしまい、trueとならないために、結果が抽出されてこないのです。
項目 = NULL
項目> NULL
項目< NULL
項目<> NULL
NULL = NULL
さらに、項目+NULL、項目-NULL、項目*NULL、項目/NULL など、NULLを含む算術演算はすべて結果も NULL になります。そのため、最初の例の、給与+歩合給の場合、歩合給が NULL のデータは足し算の結果も NULL となり、給与+歩合給>300000 という式は unknown となって、抽出結果に出て来なかったということです。
それでは、NULLのものを抽出するためにはどうするかというと、
「IS NULL」という句を使います。
SELECT * FROM 社員マスタ
WHERE 歩合給 IS NULL;
反対に、NULLでないものを抽出するのは、「IS NOT NULL」を使います。
SELECT * FROM 社員マスタ
WHERE 歩合給 IS NOT NULL;
結果としては、全て拠点がシカゴの人です。
それでは、最初の例に戻って、固定給+歩合給が300000以上の方を抽出したい場合、Oracleであれば、NVL関数などを使用して、NULLであれば指定した値を戻すという関数をよく使います。
NULLの場合は0とみなせば、この比較は成立するので、
NVL(歩合給,0) と記述して
SELECT * FROM 社員マスタ
WHERE 固定給+NVL(歩合給,0) > 300000
ORDER BY 社員番号;
求める結果が出ました。
ちなみに、SQL Serverの場合には、NVL関数ではなく、ISNULL関数を使います。
SELECT * FROM 社員マスタ
WHERE 固定給+ISNULL(歩合給,0) > 300000
ORDER BY 社員番号;
算術演算ということで、+, - *, / などを上げましたが、SUMやAVGなどの集計関数でも同様に影響があります。例えば、各拠点毎の、
固定給+歩合給の合計・平均などを求めたい場合に、NULLがあると通常のSQLでは正しい結果が出ません。
SELECT 拠点, SUM(固定給) 固定給計, SUM(歩合給) 歩合給計
, SUM(固定給+歩合給) 給与計, AVG(固定給+歩合給) 平均給与
, COUNT(歩合給) 歩合給人数, COUNT(*) 人数
FROM 社員マスタ
GROUP BY 拠点
ORDER BY 拠点;
固定給+歩合給 のSUM、AVGを計算している箇所の結果が正しくありません。正しい値を取得するためには、先ほどの NVL関数を使用して、以下のようになります。
SELECT 拠点, SUM(固定給) 固定給計
, SUM(NVL(歩合給,0)) 歩合給計
, SUM(固定給+NVL(歩合給,0)) 給与計
, AVG(固定給+NVL(歩合給,0)) 平均給与
, COUNT(歩合給) 歩合給人数, COUNT(*) 人数
FROM 社員マスタ
GROUP BY 拠点
ORDER BY 拠点;
先ほどと結果が変わりました。
SQL Serverでは以下のようになります。
SELECT 拠点, SUM(固定給) 固定給計
, SUM(ISNULL(歩合給,0)) 歩合給計
, SUM(固定給+ISNULL(歩合給,0)) 給与計
, AVG(固定給+ISNULL(歩合給,0)) 平均給与
, COUNT(歩合給) 歩合給人数, COUNT(*) 人数
FROM 社員マスタ
GROUP BY 拠点
ORDER BY 拠点;
にほんブログ村
集計関数（小計、合計）
ITコーディネータのシュウです。
ショッピングセンターの外の広場が飾りつけられているのを、娘が以前夕方に撮った写真です。こういう飾りがあるだけで雰囲気は変わりますよね。これから、クリスマスに向けてもっといろいろな場所できれいな飾りが見れるようになるのではないかと楽しみです。
＜本日の題材＞
集計関数（小計、合計）
システムの帳票を出力する場合に、よく小計や合計を出力するケースがありますよね。これをSQL側で行いたいということも多々あるかと思います。今回は、GROUP BY句で集計を行う際に、小計や合計を出す方法について、取り上げてみたいと思います。
商品マスタのデータと、売上テーブルのデータが以下のような場合の、受注日ごと、及び商品の分類ごとの集計を出す場合を考えてみます。
SELECT * FROM 商品マスタ
ORDER BY 商品CD;
SELECT * FROM 売上テーブル
ORDER BY 受注番号;
受注日ごと、及び商品の分類ごとの集計を抽出します。（Oracleの場合）
SELECT A.受注日, B.分類
, SUM(A.商品数量) AS 数量, SUM(A.売上金額) 売上金額
FROM 売上テーブル A
JOIN 商品マスタ B ON A.商品CD = B.商品CD
GROUP BY A.受注日, B.分類
ORDER BY A.受注日, B.分類;
次に、受注日ごと、及び商品の分類ごとの集計に、小計、合計を出すように、ROLLUP句を使用してみます。
SELECT A.受注日, B.分類
, SUM(A.商品数量) AS 数量, SUM(A.売上金額) 売上金額
FROM 売上テーブル A
JOIN 商品マスタ B ON A.商品CD = B.商品CD
GROUP BY ROLLUP(A.受注日, B.分類)
ORDER BY A.受注日, B.分類;
ここで、小計、合計のときに NULL の表示になるのが嫌な場合には、以下のようなかたちで表現を変更することもできます。
SELECT
DECODE(GROUPING(A.受注日), 1, '総合計', A.受注日) AS 受注日
, DECODE(GROUPING(A.受注日), 1, ' ', DECODE(GROUPING(B.分類), 1, '1日分小計', B.分類)) AS 分類
, SUM(A.商品数量) AS 数量
, SUM(A.売上金額) AS 売上金額
FROM 売上テーブル A
JOIN 商品マスタ B ON A.商品CD = B.商品CD
GROUP BY ROLLUP(A.受注日, B.分類)
ORDER BY A.受注日, B.分類;
※GROUPING関数は、ROLLUPやCUBEの操作の場合に、小計や合計を表すNULLの場合に「1」を戻し、その他は「0」を戻すので、それを使って表示を変更しています。受注日のところの合計のところは、NULLではなく「総合計」、分類のところは、NULLではなく「1日分小計」としました。（総合計のところの分類は今回は空白にしました）
また、CUBE句を使用すると、指定された列のすべての組合せについての小計を作成します。
小計や合計のときの表現を考慮して
SELECT
DECODE(GROUPING(A.受注日), 1, '総合計', A.受注日) AS 受注日
, CASE WHEN GROUPING(B.分類) = 0 THEN B.分類
WHEN GROUPING(A.受注日) = 1 THEN ' '
WHEN GROUPING(B.分類) = 1 THEN '1日分小計'
END AS 分類
, SUM(A.商品数量) AS 数量
, SUM(A.売上金額) AS 売上金額
FROM 売上テーブル A
JOIN 商品マスタ B ON A.商品CD = B.商品CD
GROUP BY CUBE(A.受注日, B.分類)
ORDER BY A.受注日, B.分類;
最後の総合計のところで、商品の分類ごとのデータが抽出されているのが ROLLUP句の場合と異なります。
●SQL Serverの場合
通常の集計について：
SELECT A.受注日, B.分類, SUM(A.商品数量)数量, SUM(A.売上金額)売上金額
FROM 売上テーブル A
JOIN 商品マスタ B ON A.商品CD = B.商品CD
GROUP BY A.受注日, B.分類
ORDER BY A.受注日, B.分類;
ROLLUPを使った例は以下（こちらも総合計、小計の表現を設定したもの）：
SELECT
CASE WHEN GROUPING(A.受注日) = 1 THEN '総合計'
ELSE CAST(A.受注日 AS VARCHAR)
END AS 受注日
, CASE WHEN GROUPING(B.分類) = 0 THEN B.分類
WHEN GROUPING(A.受注日) = 1 THEN ' '
WHEN GROUPING(B.分類) = 1 THEN '1日分小計'
END AS 分類
, SUM(A.商品数量)数量
, SUM(A.売上金額)売上金額
FROM 売上テーブル A
JOIN 商品マスタ B ON A.商品CD = B.商品CD
GROUP BY A.受注日, B.分類 WITH ROLLUP
ORDER BY 受注日, GROUPING(B.分類), B.分類;
※ROLLUP句は、GROP BY Col1, Col2,.. WITH ROLLUP となります。
CUBEを使った例は以下（こちらも総合計、小計の表現を設定したもの）：
SELECT
CASE WHEN GROUPING(A.受注日) = 1 THEN '総合計'
ELSE CAST(A.受注日 AS VARCHAR)
END AS 受注日
, CASE WHEN GROUPING(B.分類) = 0 THEN B.分類
WHEN GROUPING(A.受注日) = 1 THEN ' '
WHEN GROUPING(B.分類) = 1 THEN '1日分小計'
END AS 分類
, SUM(A.商品数量)数量
, SUM(A.売上金額)売上金額
FROM 売上テーブル A
JOIN 商品マスタ B ON A.商品CD = B.商品CD
GROUP BY A.受注日, B.分類 WITH CUBE
ORDER BY 受注日, GROUPING(B.分類), B.分類;
※CUBE句は、GROP BY Col1, Col2,.. WITH CUBE となります。
Oracleのときと同様な結果が出ました。
ちなみに、MySQLの場合も ROLLUP・CUBE句は、SQL Serverと同じく
GROP BY Col1, Col2,.. WITH ROLLUP
GROP BY Col1, Col2,.. WITH CUBE
となります。Oracleが独自のようですね。
今日は以上まで
にほんブログ村
再帰SQL
ITコーディネータのシュウです。
紅葉が好きなので、今回も紅葉の写真をアップしました。皆さんはどうですか？
先日、ＩＴコーディネータのイノベーション経営セミナーに参加した時に、あるコンピュータメーカ：Ｆ社の方が、ハッカソン、アイデアソンについての取り組みを説明をされていました。そこではさくらで東北地方を元気にしようということで、いろんな立場の違う方たちが短期集中的に意見を出し合って、アイデアを作り上げたという内容でした。確かにある会社だけで考えるよりは、素晴らしいアイデアが作れそうな気がしますね。画期的な取り組みだと感じました。
ファシリテーションを如何にうまくやれるかとか、実際にサービスに結び付けるところまで持って行けるのかとか、いろいろやる上では課題もありそうにも思えますが、今後はこういう「共創」ということもより増えて行きそうですね。
＜本日の題材＞
再帰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;
結果は以下です。
これを、再帰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;
結果は以下です。
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;
※経路のところで、CASTでNVARCHAR(4000) に変換しないでそのままで実行すると、
メッセージ240、レベル16、状態1、行1
再帰クエリ"REC_BUSYO" の列"経路" で、アンカーの型と再帰部分の型が一致していません。
のようなエラーが出ました。
今日は以上まで
にほんブログ村
WITH句と共通テーブル式(CTE)
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句を使っての共通テーブル式についてはまだ未対応のようです。
今日は以上まで
にほんブログ村
階層構造のデータ表示（Oracle）
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について、及びそれを使って階層構造を表示する方法について取り上げてみたいと思います。
今日は以上まで
にほんブログ村
MySQLのINSERT
ITコーディネータのシュウです。
11月も残りわずかになってきました。安倍首相の決断により、衆議院が解散されました。解散については賛否両論があり、今、激しく論争が展開されていますね。世界の先がよく読めないご時世ですから、このような時は、政治のリーダーがとても大切だと思います。私も、12月14日の総選挙には、しっかりと日本のことを考えて投票するつもりです。
また、先日、高倉健さんが逝去されましたね。私のとても好きな俳優さんの一人でした。最後の映画作品「あなたへ」をテレビで見ました。とても感動しました。「健さん、ありがとう！」と思わず言いたくなりました。ご冥福を心よりお祈りいたします。
＜本日の題材＞
MySQLのINSERT
前回、MySQLについて取り上げたので、今回もMySQLのことをもう少し取り上げてみようと思います。MySQLのINSERT文には興味深い機能がありましたので、取り上げてみました。
●一括INSERT
テーブルに複数のレコードを追加する場合に、OracleやSQL Serverなどでは１行１行INSERT文を記述すると思いますが、MySQLの場合には、以下のような書式で複数のデータをまとめて登録することができます。
INSERT INTO tbl_name (col_name1, col_name2, ...)
VALUES (value1, value2, ...), (value1, value2, ...), (value1, value2, ...);
試してみます。dbsuserユーザでsampledbに接続します。
mysql –u dbsuser –p
パスワードを入力
use sampledb
前回作成した「item_mst」に、追加でデータを3行登録します。Item_mst のテーブル定義を再度確認します。
DESC item_mst;
これはOracleとの互換のためにサポートされているようです。
他にも以下の方法などでテーブルの定義を確認できるようです。
・SHOW FULL COLUMNS FROM item_mst;
・SHOW CREAT TABLE item_mst;
それでは、今回は商品として野菜を3レコード登録します。
INSERT INTO item_mst(item_cd, item_name, price) values('Y0001', 'キャベツ', 160), ('Y0002', '玉ねぎ', 200), ('Y0003', '人参', 150);
「item_mst」テーブルのレコードを確認します。
SELECT item_cd, item_name, price
FROM item_mst
ORDER BY item_cd;
新しく野菜の3行がまとめて登録されてることが確認できました。
●IGNOREオプション
主キー制約や一意性制約を設定した列には、重複した値は制約違反のため挿入することができません。エラーが返ってきます。しかし、IGNOREオプションを指定した場合はエラーを返さずに（データは挿入されず）、終了させることができます。
通常のINSERT文では重複データの登録時にはエラーが返ってきます。
INSERT INTO item_mst(item_cd, item_name, price) values('K0005', 'マンゴー', 300);
ここで、IGNOREオプションを付けてINSERTを実行します。
INSERT IGNORE INTO item_mst(item_cd, item_name, price) values('K0005', 'マンゴー', 300);
エラーを出さずに終了しました。ただエラーが発生していないだけでデータは追加されていません。確認してみます。
SELECT item_cd, item_name, price
FROM item_mst
ORDER BY item_cd;
item_cdが「K0005」は、もともとの「もも」のままであることが確認できます。
●ON DUPLICATE KEY UPDATE句
ON DUPLICATE KEY UPDATE を使うと、テーブルの PRIMARY KEY 、もしくは UNIQUE インデックスの値と、INSERT文で挿入しようとしているデータの値が異なれば INSERTを行うが、同じであれば、ON DUPLICATE KEY UPDATE句で指定した値でUPDATE を実行します。
INSERT INTO item_mst(item_cd, item_name, price) values('K0005', 'キウイ', 100)
ON DUPLICATE KEY UPDATE item_name = VALUES(item_name), price = VALUES(price);
結果を確認してみます。
SELECT item_cd, item_name, price
FROM item_mst
ORDER BY item_cd;
主キーである「item_cd」が「K0005」で一致していたので、item_name, price がON DUPLICATE KEY UPDATE句で設定された（今回はINSERT文で指定した値）で更新されていることが確認されました。
※更新する値を指定する部分でVALUES(col_name)を使用すると、引数のカラムに対して新規でデータが追加する場合に格納するはずの値を参照できます。
なお、ON DUPLICATE KEY UPDATE句を使う際には、いくつか下記のような制限はあるようです。
・テーブルに主キーか、UNIQUEインデックスがないと、ON DUPLICATE KEY UPDATE は利用できない
・キーが複数行に一致する場合、MySQL は1行のみ更新する
・ON DUPLICATE KEY UPDATE を使用する場合は DELAYED オプションは無視される
今日は以上まで
にほんブログ村
MySQLとdbSheetClient
ITコーディネータのシュウです。
最近は、朝晩はとても冷え込んできました。そろそろ暖房器具を出している方も多いようです。風邪を引かないように注意しないと。。。
＜本日の題材＞
MySQLとdbSheetClient
今回は、ちょっと大きく内容を変えて、MySQLについて取り上げたいと思います。 MySQLは、世界でもっとも普及しているオープンソース・データベースとして知られていて、ウィキペディアの情報では、市場シェアでは他のオープンソース・データベースを圧倒しているとのこと。
元はスウェーデンの「MySQL AB」という企業が開発していましたが、2008年2月26日にサン・マイクロシステムズに買収され、さらに2010年1月27日、サン・マイクロシステムズがオラクルに買収されたため、現在はOracleの製品ですね。
今まであまり試したことはなかったのですが、そのMySQLを、私のほうで普段仕事で使用しているdbSheetClientというツールで使えるということなので、ちょっと簡単に試してみました。
MySQLをテストのためWindows仮想サーバにインストールした直後、サンプル用のデータベースを作成：
MySQLの対話型インターフェースで、OracleのSQL*Plusのようなツールがあるので、それを使ってみます。
まず、rootユーザで接続します。
mysql –u root –p
(※MySQLではユーザーをユーザー名＋接続ホスト名で管理していますので、本来はユーザー名には「root@localhost」と指定する必要があるのですが、ホスト名が「localhost」の場合に限って省略することが可能)
そうすると、パスワードを聞いてきますので、インストール時に設定したパスワードを入力します。
接続できました。
次に、サンプル用のデータベース「sampledb」を作成します。
Create database sampled;
データベースを確認してみると
Show databases;
Sampledbが作成されたのが確認できます。
ユーザを確認してみると
SELECT user, host FROM mysql.user;
rootユーザのみです。
dbSheetClientでアクセスして使うためのユーザ dbsuser (sampledbへのすべての権限(ALL)を付与)を作成：
GRANT ALL ON sampledb.* TO dbsuser@localhost IDENTIFIED BY ‘passwd’;
(CREATE USER文でもユーザは作成できますが、上記のGRANT文にすると権限を与える文でユーザを作成することが可能です) 簡単にデータベース、ユーザの作成ができました。
さて、普段業務で使用しているExcelシートをそのまま利用しつつ、データはデータベース化して管理し、複数のメンバーで共有しながらセキュリティ対策も可能なシステムがわりと簡単に作成できるツールがあればいいと思いませんか？
ここで、私が普段使用しているツールで、上記で説明したようなシステムを作成できるツールとして、dbSheetClientというものがあるのですが、それを使ってMySQLのデータを扱えるという内容を、今回は紹介してみたいと思います。
dbsuserでログインしてから、データベース「sampledb」に接続します。
use sampledb;
テーブル「item_mst」を作成します。
CREATE TABLE item_mst(
item_cd VARCHAR(10)
, item_name VARCHAR(20)
, price int
, DBS_STATUS VARCHAR(2) default '1'
,DBS_CREATE_USER VARCHAR(60) default 'SYSTEM'
,DBS_CREATE_DATE VARCHAR(30)
, DBS_UPDATE_USER VARCHAR(60) default 'SYSTEM'
, DBS_UPDATE_DATE VARCHAR(30)
, CONSTRAINT PK_item_mst PRIMARY KEY (item_cd));
(DBS_STATUS以降の項目は、dbSheetClientの制御用フィールドになり、更新をdbSheetの機能で行う際には必要な項目となります)
さて、dbSheetClientというツールを起動して、データベースの設定を行います。開発版を起動して、「DB」の設定のところで、下記のように MySQLの設定をします。
その後、「IOTG」というdbSheetClientの機能を使うと、先ほどのテーブル「item_mst」をEXCEL上に読込んだり、更新したりできるプログラムを簡単に作成できます。
その後、作成したプログラムをメニューから起動して、「読込」ボタンを押下します。最初はデータは1件もないので、データは表示されません。
では、この画面で、EXCEL上から項目 item_cd, item_name, price の値を5件ほど入力してみましょう。りんご、みかん、なし、かき、ももとそれぞれデータを入力します。その後、「更新」ボタンを押して更新を実行します。
OKを押して更新を行うと、MySQLデータベース上のデータが更新できます。 MySQL側でデータを確認：
select item_cd, item_name, price
from item_mst
order by item_cd;
MySQL側でExcelで登録したデータが確認できました。
通常、dbSheetClientは商用のＤＢであるOracle、SQL Serverを利用されることが多いですが、オープン・ソースデータベースであるMySQLについても対応しているようですね。 dbSheetClientの詳細については、以下のＨＰを確認してみてください http://www.newcom07.jp/dbsheetclient/index.html
今日は以上まで
にほんブログ村
カーソルについて(SQL Server)
ITコーディネータのシュウです。
秋も深まってきて、紅葉の季節になってきました。なかなか時間が取れないですが、ひとときでも、美しい自然の中を散策したいですね。
＜本日の題材＞
カーソル(SQL Server)
Oracleの場合のカーソルについて、前々回、前回と題材としてアップしてきましたが、今回はSQL Serverの場合を上げてみようと思います。カーソル処理の基本的な流れは同じですが、SQL Serverの場合は以下のようになります。
【カーソル処理の流れ】
カーソルの定義
(DECLARE <カーソル名> CURSOR FOR <SELECT文>)
カーソルのオープン (OPEN <カーソル名>)
カーソルから1行データの取出し
(FETCH NEXT FROM <カーソル名> INTO <変数名>)
@@FETCH_STATUS が 0 (success) の間ループ
(WHILE @@FECTH STATUS = 0
BEGIN
1行ごとに行いたい処理
次の行の取得
(FETCH NEXT FROM <カーソル名> INTO <変数名>)
END)
カーソルのクローズ (CLOSE <カーソル名>)
カーソル参照を削除 (DEALLOCATE <カーソル名>)
ちょっとくどいかも知れませんが、前回のOracleで行ったカーソル処理を SQL Serverで試してみます。
DECLARE
jyutyu_cur CURSOR FOR
SELECT J01.受注番号, J01.顧客番号, J01.受注日, J02.商品CD, J02.商品数
FROM dbo.受注テーブル J01
JOIN dbo.受注明細テーブル J02 ON J01.受注番号 = J02.受注番号
WHERE CONVERT(VARCHAR, J01.受注日, 111) = CONVERT(VARCHAR, GETDATE(), 111)
ORDER BY J01.受注番号, J02.受注明細番号;
DECLARE
@受注番号 VARCHAR(10)
, @顧客番号 VARCHAR(8)
, @受注日 DATE
, @商品CD VARCHAR(6)
, @商品数 DECIMAL(4)
, @在庫数 DECIMAL(8);
OPEN jyutyu_cur;
FETCH NEXT FROM jyutyu_cur INTO @受注番号, @顧客番号, @受注日, @商品CD, @商品数;
WHILE (@@fetch_status = 0)
BEGIN
SELECT @在庫数 =在庫数 FROM dbo.在庫テーブル
WHERE 商品CD = @商品CD;
IF @在庫数 >= @商品数
BEGIN
Print '受注番号='+ @受注番号+',商品CD='+ @商品CD+',商品数='+ CAST(@商品数 AS VARCHAR)+', 引当OK';
UPDATE dbo.在庫テーブル SET
在庫数 =在庫数 - @商品数
WHERE 商品CD = @商品CD;
END;
ELSE
Print '受注番号='+ @受注番号+',商品CD='+ @商品CD+',商品数='+ CAST(@商品数 AS VARCHAR)+', 引当不可';
FETCH NEXT FROM jyutyu_cur INTO @受注番号, @顧客番号, @受注日, @商品CD, @商品数;
END;
CLOSE jyutyu_cur;
DEALLOCATE jyutyu_cur;
結果は、以下
処理後の在庫テーブルの各商品の在庫数は以下になります。
SELECT 商品CD, 在庫数
FROM dbo.在庫テーブル
ORDER BY 商品CD;
SQLの記述の仕方が、OracleとSQL Serverでは多少異なることがわかると思いますが、行いたい処理は同様になすことができて、結果もOracleのときと同じになります。
今日は以上まで
にほんブログ村