グループ関数の使用による集計データのレポート
問６
あなたは、歩合の種類を数えるレポートを作成しようと考えています。ただし、NULLも1つの種類として数える必要があります。目的の結果を得るためには、どの問合せを使用しますか。最適なものを選択してください。
- a. SELECT COUNT(commission_pct) FROM employees;
- b. SELECT COUNT(NVL(commission_pct,'No Data')) FROM employees;
- c. SELECT COUNT(DISTINCT NVL(commission_pct,'No Data')) FROM employees;
-
d. SELECT COUNT(DISTINCT NVL(TO_CHAR(commission_pct),'No Data'))
FROM employees;
本講座第5回で解説した、グループ関数の使用による集計データのレポートでは、出題頻度が多いにもかかわらず、COUNT関数とDISTINCTを組み合わせた「種類の数」を求める設問を含めなかったので、今回用意しました。選択肢を順に実行しながら解説します。
COUNT関数を含め、グループ関数はNULLを処理の対象にしません。そのため、次に示す選択肢aの実行結果は正解のように見えますが、NULLを数える対象に含んでいないので（種類も数えていません）、設問の意図を満たしていません。したがって、選択肢aは不正解です。
SQL> --選択肢a
SQL> SELECT COUNT(commission_pct) FROM employees;
COUNT(COMMISSION_PCT)
---------------------
35
「だったら、NULLはNVL関数を使ってNULL以外の値に変え、COUNT関数の処理対象に含まれるようにすればいいんじゃないか」その発想は正しいのですが、NVL関数の引数はデータ型を同じにしなければいけません。設問のデータ構造からcommission_pctは数値型なのに、文字列の'No Data'を第2引数に指定することはできません。したがって、選択肢bは不正解です（以下は選択肢bでの実行例）。
SQL> --選択肢b
SQL> SELECT COUNT(NVL(commission_pct,'No Data')) FROM employees;
SELECT COUNT(NVL(commission_pct,'No Data')) FROM employees
*
行1でエラーが発生しました。:
ORA-01722: 数値が無効です。
「種類を求めるんだから、COUNT関数の中でDISTINCTを指定しなければいい」という発想も正しいです。しかし、選択肢bの解説のとおり、NVL関数の使用方法を間違えていては実行できません。したがって、選択肢cは不正解です（以下は選択肢cでの実行例）。
SQL> --選択肢c
SQL> SELECT COUNT(DISTINCT NVL(commission_pct,'No Data')) FROM employees;
SELECT COUNT(DISTINCT NVL(commission_pct,'No Data')) FROM employees
*
行1でエラーが発生しました。:
ORA-01722: 数値が無効です。
NVL関数を使用するには、2つの引数のデータ型を一致させればいいわけですから、commission_pctを文字列に変換し、NVL関数でNULLをNULL以外に置き換えてから、DISTINCTで重複を省いて、COUNT関数で数える。これで、設問の意図を満たします。正解は、そのように記述されている選択肢dです。
もちろん、NVL(commission_pct,999)のように第2引数に数値を指定するほうが、TO_CHAR関数を用いずに済むので簡単だと思いますが、置き換える数値が第1引数の列内にないことを確認したうえで指定しないと、求めた種類の数が間違ってしまいます。実務では注意してください。
この設問では、commission_pctという名前から「データは0～1または0～100という割合を表す数値（パーセント）しか含まれていないかな」と想像して、それ以外の数値を第2引数に指定すればよいでしょう。しかし、データの中身を直接見ることができない状況でSQL文を記述することもあるでしょうから、選択肢dのようにTO_CHAR関数を使う方法は、面倒ですが正しい意味があるのです。
SQL> --選択肢d
SQL> SELECT COUNT(DISTINCT NVL(TO_CHAR(commission_pct),'No Data'))
2 FROM employees;
COUNT(DISTINCTNVL(TO_CHAR(COMMISSION_PCT),'NODATA'))
----------------------------------------------------
8
