NULL

NULLのソート順

2014年12月27日ＩＴＣシュウちゃんコメントをどうぞ

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

NULLについて

2014年12月18日ＩＴＣシュウちゃんコメントをどうぞ

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 拠点;

今日は以上まで

にほんブログ村

関数

集計関数（小計、合計）

2014年12月3日ＩＴＣシュウちゃんコメントをどうぞ

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が独自のようですね。

今日は以上まで

にほんブログ村

SELECT, 再帰SQL

再帰SQL

2014年12月1日ＩＴＣシュウちゃんコメントをどうぞ

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" の列"経路" で、アンカーの型と再帰部分の型が一致していません。
のようなエラーが出ました。

今日は以上まで

にほんブログ村

DB & SQL 技術ブログ

月別アーカイブ: 2014年12月

NULLのソート順

NULLについて

集計関数（小計、合計）

再帰SQL

主にSQLについて書いていきたいと思います。