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データを一番下に持ってくることができました。

今日は以上まで

 

 

にほんブログ村

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

今日は以上まで

 

にほんブログ村