手作業による集計は、とても面倒で間違いも起こりやすいです。そこで集計関数を使い、集計を行います。集計関数を使うと、SELECT文による検索結果が集計された形で出力されるようになります。
第５章で紹介した関数は、検索結果の各行に対して、同じ処理や計算を行うように命令するものです。これらの関数を使うことによって、検索結果の行が増減することはありません。一方集計関数は、集計の対象となったすべての行に１回だけ計算を行い、１つの答えを出します。必然的に、結果表は必ず１行になります。
通常の関数と同様に、DBMS製品によって利用できる集計関数は異なります。しかしほとんどの製品に共通して利用可能なもの５つを表にまとめます。
| 分類 | 関数名 | 説明 |
|---|---|---|
| SUM | 各行の値の合計を求める | |
| MAX | 各行の値の最大値を求める | |
| MIN | 各行の値の最小値を求める | |
| AVG | 各行の値の平均値を求める | |
| 計数 | COUNT | 行数をカウントする |
５つの関数のうち、COUNT関数だけ他の４つと少し特性が異なります。
これらの関数には引数として１つの列名を渡します。また、列名だけではなく「SUM(出金額*1.05)」のように、列名を含む式を指定することも可能です。
単純に検索結果の行数を得るには、COUNT(*)という記述が便利です。COUNT関数はあくまでも該当した行数を取得する関数であり、検索結果の値自体がなんであるかは問いません。
AVG、SUM、COUNTの各関数では、「DISTINCT」を指定することによって、その列で重複している値を除いた状態で集計が行われます。
集計関数は、「SELECT文の選択列リスト部分」やHAVING句の中で利用します。WHERE句の中では利用できません。
また、「検索結果」に対して集計を行うための道具である集計関数は、UPDATE文、INSERT文、DELETE文で利用することはできません。
データベースでは、デコボコ型の結果表は認められていません。結果表は常に列ごとの行数が一致するn行m列の長方形でなければなりません。結果表がデコボコ型になるようなSQL文を実行すると、エラーになります。
５つの集計関数は、いずれも１つの列を引数として受け取り、集計を行います。しかし、引数にどのような型の列を指定できるかは、関数によって異なります。
| 関数名 | 数値型 | 文字列型 | 日付型 |
|---|---|---|---|
| SUM | 各数値の合計 | × | × |
| MAX | 各数値の最大 | 並び替えて最後の文字列 | 最も新しい日付 |
| MIN | 各数値の最小 | 並び替えて最初の文字列 | 最も古い日付 |
| AVG | 各数値の平均値 | × | × |
| COUNT | 行数 | 行数 | 行数 |
NULLを含む計算や比較は、基本的にNULLとなることは紹介しました。しかし、集計関数の場合はそれぞれ取扱いが異なります。
| 集計関数 | 集計時のNULLの扱い | 全行がNULLの場合の集計結果 | |
|---|---|---|---|
| SUM | 無視 (NULLは集計に影響を与えない) | NULL | |
| MAX | |||
| MIN | |||
| AVG | |||
| COUNT | 列名指定 | 無視 (NULLは集計に影響を与えない) | ０ |
| *指定 | NULLを含んでカウント | 該当行数 | |
なお、NULLを０に読み替えて計算したい場合は、COALESCE関数を使うとよいでしょう。
SQLには、集計に先だって、指定した基準で検索結果をいくつかのまとまりに分けるグループ化と呼ばれる機能が備わっています。集計はグループごとに行われ、グループごとの集計結果が結果表の形で得られます。例えば、今回の「費目別の出金額集計表」を得るには、家計簿テーブルの各行を「費目」列の内容で分類し、分類した費目ごとに出金額の合計を求めればよいのですから、次のようなSQL文で実現することが可能です。
グループ化による集計を行うSQL文は、次のような構文で記述します。
グループ集計は、３つのステップで実行されます。
①もとの表に対して通常の検索処理を行います。この時、SELECT文の選択列リストやWHERE句によって、列や行が絞り込まれます。
②検索結果はGROUP BY句で指定された列に同じ値を持つ行ごとに分類されます。
③各グループに対して集計関数の処理が行われ、結果表となります。
最終的な結果表は、グループの数と等しくなります。
GROUP BY句に複数の列をカンマで区切って指定することで、複数の列を基準にしたグループ化もできます。
集計を行った後の結果表に対して絞り込みを行いたい場合はHAVING句を用います。
HAVING句はWHERE句と同じように、ANDやORの論理演算で複数の条件式を組み合わせることもできます。しかし、HAVING句は、集計結果がすべて揃った最後に実行されるので、WHERE句と異なり、集計関数を記述することが可能になるのです。
非常に大量のデータを通り扱う場合、集計テーブルと呼ばれるテーブルを用いて、次のような工夫がなされる場合があります。
◆作った直後は正しい集計結果が格納されていますが、日にちが経ってテーブルに新たな行が加わるにつれ、集計結果が食い違ってきます。ですから、一定のタイミングで集計処理を実行し、集計テーブルの内容を最新の状態に更新する作業が必要です。
最終的には費目と出金額を知りたいのですから、「SELECT 費目, 出金額 FROM 家計簿 WHERE・・・」のように書き始めるでしょう、しかし、WHERE句の続きで手が止まってしまうはずです。条件式の右辺に書くべき具体的な値は、実際に家計簿テーブルを調べてみないとわからないからです。
SELECT文で何らかの検索結果を得て、得られた具体的な値を用いてさらにSELECTやUPDATEなどを実行したい場合、それを１つのSQL文で記述することができます。
SELECT文をよく見ると、初めに書いた表を構成する、２つのSQL文が組み合わさって構成されていることがわかります。
一般的に、あるものがその内側に別のものを内包している状態をネスト構造や入れ子と呼びます。他のSQL文の一部分として登場するSELECT文のことを、副問い合わせや副照会、またはサブクエリと呼びます。
副問い合わせ使うことによって、複雑で高度なSQL文を書くことが可能になります。初めから長くて複雑なSQL文をいきなり書くのは難しいので、初めは、個々のSQL文を１つずつ作り、あとから組み立ててあげれば簡単です。
副問い合わせを含むSQL文では、まず副問い合わせのSELECT文が実行され、その結果である具体的な値に化けます。その後、化けた値を当てはめて組み立てられた外側のSQL文が実行されていきます。
検索結果が１行１列の値となる副問い合わせを指します。単一の値を記述するような場所なら基本的にどこでも記述できます。代表的な場所としては、SELECT文の選択リストやUPDATEのSET句が挙げられます。また１つの値との判定を行うWHERE句の条件式に記述することができます。
この例では問い合わせが「5000」という具体的な数値に変化します。そして最終的には家計簿集計テーブルの平均の列に「5000」をSETするUPDATE文になるのです。
このSQL文の副問い合わせは、家計簿集計テーブルの食費に関係する合計額を取得する内容です。集計テーブルの食費に対応する行は１行なので、検索結果は１つの値になります。
検索結果が複数の行からなる単一列(n行１列)の値となる副問い合わせです。副問い合わせを実行した結果は、複数の値に化けるとも考えることができます。複数行副問い合わせは、SQL文中で複数の値を列挙するような場所に記述できます。IN、ANY、ALL演算子を用いた条件式が代表的な事例です。
IN演算子の右側には、文字列値が列挙されていますが、この部分を副問い合わせに置き換えることができます。
この例では、「<」演算子をANYと組み合わせているので、３～４行目は「副問い合わせの結果で得られる複数の値のいずれかより出金額が小さければ」という意味の条件式になります。ANYをALLに書き換えると、「副問い合わせの結果で得られる複数の値のすべてよりも出金額が小さければ」という条件になります。
◆複数行副問い合わせは単一の値の代わりに記述することはできません。単一の値の代わりに、複数行副問い合わせを記述するとエラーになります。副問い合わせの結果得られる複数の値のうち、どれと比較すればいいかわからないからです。
NOT IN演算子は、右辺に列挙された値を不等号を使って１つひとつ比較し、すべての値と等しくしないことを判定する演算子です。よって、右辺に１つでもNULLが含まれると、NOT IN演算子による比較結果はすべてNULLとなります。WHEREは結果がTRUEとなる行だけを抽出するので、SELECT文では１行も結果が得られないことになります。
逆に、IN演算子は、右辺に列挙された値を等号を使って比較していき、いずれかの値と等しければTRUEと判断する演算子です。したがって、右辺にNULLが含まれていても、等しい値が１つでもあれば、結果を得ることができます。
検索結果が複数の行と複数の列から成る表形式(n行m列)の値となる副問い合わせです。この副問い合わせを実行した結果は、表の形に化けると考えることもできます。通常のSQL文において表を記述することのできる箇所、例えばSELECT文のFROM句やINSERT文などに記述することができます。
外側のSQL文は、「SELECT～FROM～」の単純なSELECT文ですが、そのFROM句は１つの大きな副問い合わせで構成されています。家計簿テーブルと家計簿アーカイブの２０１３年１月分がUNIONでつながっているため、これを１つのテーブルのようにとらえることが可能です。また、副問い合わせに「SUB」という別名が付けられています。その別名を利用して、外側のSELECTの選択列リストでは、抽出対象とする副問い合わせとその項目を明示しています。
INSERT文は、１００行分のデータを追加したい場合は、１００回のINSERT文を実行するしかありません。
しかし、副問い合わせを使えば１回のINSERT文で複数行のデータを登録することが可能になります。
２行目以降、最後まで副問い合わせです。今までと異なり今回の問い合わせは、カッコでくくられていません。今回の副問い合わせは、INSER文のVALUE以降の記述に相当する内容に化けるものです。SELECTの検索結果がそのままテーブルに登録すべき値として処理されます。
なお、もし副問い合わせの結果表の列と登録するテーブルの列が完全に一致していれば、「INSERT INTO 家計簿集計 SELECT ～」のように、INSERTの列名指定を省力することもできます。