平成29年度春期 データベーススペシャリスト 午前Ⅱ 問8

集計前の各行の値を追って考える

条件別の件数を求める問題では、集計関数に渡る前に各行がどのような値へ変換されるかを自分で確かめることが大切です。小さな例の行を想定し、一行ずつ中間の値を書き出してから集計結果を考える手順を意識しましょう。

この問題で問われていること

確認すべき観点は一つ、条件に合う行と合わない行のそれぞれについて、条件式が返す値と、その値を集計関数がどう扱うかを区別して考えることです。関数の仕様は教科書やマニュアルで各自確認してください。

視点

集計を含む問合せは、二つの段階に分けて整理できます。一つ目は、各行に対して式を評価し、中間の値を得る段階です。二つ目は、その中間の値の集まりを集計関数でまとめる段階です。結果だけを見て判断せず、段階ごとに何が起きるかを分けて考えましょう。特に、条件に合わない行でどのような値が生じ、それが集計にどう影響するかは、自分で仕様を確認してから判断してください。

確認ポイント

• 数行の小さな例を自分で用意し、条件に合う行と合わない行を両方含めたか
• 各行について条件式が返す中間の値を、集計の前に書き出して確認したか
• 書き出した中間の値を集計関数がどう扱うかを、教科書やマニュアルの仕様で確かめたか

正解

ウ

解説

COUNT(式)は式の値がNULLの行を数えません。そのため、条件に合う行はTHEN 1で非NULLにし、合わない行はELSE NULLにすると、部署ごとの主任の人数と一般社員の人数を求められます。

理由

GROUP BY 部署コードで、社員を部署ごとのグループに分けます。COUNT(CASE WHEN 役職 = '主任' THEN 1 ELSE NULL END)では、役職が主任の行だけが1になり、それ以外の行はNULLになります。COUNT(式)はNULLでない値の件数を数えるので、結果は主任の人数になります。一般社員の列も同じ仕組みです。BB01のように該当者がいない部署では、COUNTは0を返すため、結果の例と一致します。

比較

ELSE 0とELSE -1は、条件に合わない行もNULLではない値にするため、COUNTではその行も1件として数えられ、結果は部署の全社員数になってしまいます。ELSE 0で正しく数えたい場合は、COUNTではなくSUMを使います。THEN NULL ELSE 1は条件と値の対応が逆になり、主任以外の人数を数えてしまいます。

選択肢ア

これは不正解だよ。COUNT(式)は値の大きさや符号は見ずに、NULLでない値の件数を数えるよ。ELSE -1だと、主任でない行も-1というNULL以外の値になって数えられるよ。だから主任の人数ではなく、部署の全社員数になってしまうね。

選択肢イ

これは不正解だよ。ELSE 0はSUMで集計するなら正しく働くけど、この問題はCOUNTだよ。COUNTにとって0はNULLではない普通の値なので、主任以外の行も1件として数えられ、結果は部署の全社員数になるよ。BB01の主任が0人になる結果の例も再現できないね。

選択肢ウ（正解）

これは正解だよ。COUNT(式)は、式の値がNULLの行を数えないよ。役職が'主任'の行だけTHEN 1で非NULLになり、それ以外の行はELSE NULLで数えられないから、部署ごとの主任の人数が求められるよ。該当者がいないBB01では0になり、結果の例とも一致するね。

選択肢エ

これは不正解だよ。THEN NULL ELSE 1は、値の対応が逆になっているよ。役職が'主任'の行はNULLになって数えられず、主任以外の行だけが1として数えられるので、主任の人数の列には主任以外の人数が入ってしまうね。一般社員の列も同じように逆になるよ。

学習のポイント

COUNT(式)はNULLを数えないこと、0や-1はNULLではない値として数えられることを押さえましょう。