| ●集約関数 NULLの扱いに注意 |
RDBMSには，数値カラムの合計値計算（SUM）や平均値計算（AVG）を行う集約関数が用意されている。例えば図3[拡大表示]の販売テーブルにおいて「値引き」の合計値を求める場合，
SELECT SUM(NEBIKI)
FROM HANBAI_TBL ;
とすると，NEBIKI（値引き）の合計金額を求めることができる（図3(1)）。合計値や平均値のほか，最大値（MAX），最小値（MIN），レコード数の算出（COUNT）などの関数がある。集約関数を利用する際は，集計対象にNULL値がある場合に注意が必要だ。
NULL値のレコードは対象外になる
集約関数の集計対象にNULL値が含まれる場合，集約関数はNULL値を除いて計算する。合計値計算や最大値，最小値の関数ではNULL値があってもなくても結果は変わらないが，平均値（AVG）とレコード数の算出（COUNT）の場合はNULLの有無で結果が変わる。
図3の販売テーブルにある「値引き」データの平均値を求める場合，「AVG（NEBIKI）」とすれば計算できる（図3(2)）。だがAVG関数はNULL値のレコードを除外するため，値引きの合計を全レコード数で割った値とは異なる結果となる。図3(2)の場合は非NULL値のレコードが2レコードなので，値引きの合計値を2で割った結果となる。もし全部のレコード数で割った平均値を求めたい場合は，NULL値を0（ゼロ）に置き換えてAVG関数を使う。具体的にはNVL関数＊を使って，「AVG（NVL（NEBIKI,0））」とする（同(2)'）。
レコード数の算出関数（COUNT）も同様である。図3(3)のように「COUNT（NEBIKI）」とすると，NEBIKIの値がNULL値のレコードを除外する。全レコード数を算出する場合は「COUNT（＊）」とする（同(3)'）。
| ●GROUP BY句，HAVING句 便利であるが乱用してはならない |
SQL文にはレコードをグループ化する「GROUP BY句」や，GROUP BY句でグループ化したレコードからさらに条件抽出する「HAVING句」がある。これらを使えば1つのSQL文で複雑な条件を処理することができるが，使い方や実行性能に注意すべき点がある。
レコードをグループ化して条件で抽出する
まず，GROUP BY句とHAVING句の基本的な動きを確認しておこう。図4[拡大表示]上のSQL文は図3の販売テーブルに対するもので，「商品コードごとに集計した金額の合計が50万円以上の商品の『商品コード』『数量の合計』『金額の合計』を算出する」SQL文である。商品コード（SYOHIN_CD）ごとの数量（SUURYO）と金額（KINGAKU）の合計値を計算する必要があるが，そのような場合，指定カラムの値でレコードをグループ化する「GROUP BY句」を使うと便利である。
SELECT SYOHIN_CD,SUM(SUURYO),SUM(KINGAKU)
FROM HANBAI_TBL
GROUP BY SYOHIN_CD ;
と記述すると，SYOHIN_CD（商品コード）ごとにレコードをグループ化する。「SUM（SUURYO）」はSYOHIN_CD（商品コード）ごとの数量（SUURYO）の合計値で，「SUM（KINGAKU）」はSYOHIN_CD（商品コード）ごとの金額（KINGAKU）の合計値を計算する。
HAVING句は，GROUP BY句でグループ化したレコードから所定の条件で抽出したい場合に利用する。図4の例では，「商品コードごとに集計した金額が50万円以上の商品」であるため，先述したSQL文に「HAVING SUM（KINGAKU）＞＝500000」を追加すると，目的のレコードが抽出される。
GROUP BY句と出力カラムはペアになる
GROUP BY句を利用した場合，SELECT＊の後ろに記述するカラムは，（1）集約関数か（2）GROUP BY句で指定したカラムになる。この点はミスしやすいので注意が必要である。
まず多いミスが，GROUP BY句で指定していないカラムをSELECTの後ろに記述する場合である。図5[拡大表示](1)のSQL文の場合，1行目が構文エラーになる。エラーの原因は，SELECTの後ろに「KUBUN」を記述していることである。GROUP BY句に「SYOHIN_CD」はあるが「KUBUN」はない。「SUM（SUURYO）」は集約関数なので問題ない。実際，このSQL文の動きを考えた場合，GROUP BY句で指定した「SYOHIN_CD」カラムでグループ化するため，SYOHIN_CD以外のカラム値は複数の値を持つことになり，集計しないと出力できない。
次は，GROUP BY句で指定しているにもかかわらず，SELECTの後ろに記述しない場合である。図5(2)のSQL文はエラーにはならないが，出力結果の意味が不明になる。「GROUP BY KUBUN，SYOHIN_CD」としているので，KUBUN（商品区分）とSYOHIN_CD（商品コード）の2つのカラム値でグループ化し，集計している。しかし出力結果に「KUBUN」しか指定していないため，結果をどのように読み取ればよいかが分からない。図5(2)の場合は，
SELECT KUBUN,SYOHIN_CD,SUM(SUURYO)
FROM HANBAI_TBL
GROUP BY KUBUN,SYOHIN_CD ;
とすれば，商品区分（KUBUN）と商品コード（SYOHIN_CD）ごとの集計値であることが明らかになる。
HAVING句の安易な利用は禁物
GROUP BY句の結果をさらに抽出するHAVING句は便利である半面，SQL文の実行性能の観点では最適な方法でない場合があるので注意が必要である。
「SYOHIN_CD（商品コード）が'001'である商品の『商品コード』『数量の合計』『金額の合計』を取り出す」SQL文を考えてみよう。GROUP BY句とHAVING句を利用すれば図6[拡大表示](1)のように，
SELECT SYOHIN_CD,SUM(SUURYO),SUM(KINGAKU)
FROM HANBAI_TBL
GROUP BY SYOHIN_CD
HAVING SYOHIN_CD = '001' ;
というSQL文になる。このSQL文は，販売テーブルをSYOHIN_CDの値でグループ化して集計し，その後，SYOHIN_CDが'001'のレコードだけを抽出する。
同じ結果を導き出すSQL文は，HAVING句を使わずにWHERE句を使って記述できる。具体的には図6(2)のように，
SELECT SYOHIN_CD,SUM(SUURYO),SUM(KINGAKU)
FROM HANBAI_TBL
WHERE SYOHIN_CD = '001'
GROUP BY SYOHIN_CD ;
というSQL文になる。後者のSQL文は，販売テーブルからSYOHIN_CDが'001'のレコードを絞り込み，その後で集計する。
どちらのSQL文でも同じ結果になるが，実行性能には差がある。販売テーブルにはSYOHIN_CDにインデックスが付いているのだが，図6(1)では全レコードを読み取る必要があるのでインデックスを利用しない。一方図6(2)のSQL文ではWHERE句の条件でレコードを絞り込む際にインデックスを利用できる。インデックスが利用できる分，HAVING句を使わない図6(2)のSQL文の方が実行性能が高い。HAVING句を使う場合は，WHERE句などで効率よく記述できないかを検討するようにしよう。
◇ ◇ ◇
次回は，副問い合わせやビューを利用する際に注意すべき点を説明する予定である。
|
玉川 敏一 シーズ・ラボ ITソリューション部 セクションマネージャー |
