集計関数
はじめに
DBにため込んだデータを登録されたままの状態で抽出するだけでなく、様々な加工を施すことができます。
そのひとつが集計です。ここでは集計するためのSQL文を学んで行きましょう!
Step1 : 集計関数
SQLには、いくつか関数が用意されています。
基本的な集計関数と内容を表にしました。
集計関数 | 内容 |
---|---|
COUNT() | 件数を数えて返却する |
SUM() | 合計を算出して返却する |
MAX() | 最大値を探索して返却する |
MIN() | 最小値を探索して返却する |
AVG() | 平均値を算出して返却する |
COUNT()
COUNT() は抽出された件数を出力します。
SELECT COUNT(列名) FROM テーブル名
列名はアスタリスクを使用しても構いません。
ただアスタリスクを使用すると全列が検索対象になるため、列名を指定するよりも処理時間がかかります。
また、NULLが入る可能性がある列を指定した場合、 NULL は件数にカウントされません。
全レコード件数を知りたい場合は主キーをカウント対象列としましょう。
WHERE句で条件をつければ対象レコード件数を知ることができます。
それでは現在登録されている注文件数をカウントしてみましょう。
SELECT COUNT(OrderNo) FROM Order_header
では次に合計が10,000円以上の注文件数をカウントしてみましょう。
SELECT COUNT(OrderNo) FROM Order_header WHERE Total >= 10000
SUM()
SUM() は合計を出力します。
SELECT SUM(列名) FROM テーブル名
現在登録されている売上金額を全部足してみましょう。
SELECT SUM(Total) FROM Order_header
次は指定した購入者に対する、これまでの売上金額を合計します。
SELECT SUM(Total) FROM Order_header WHERE PurchaserCode = '0505'
MAX()
MAX() は最大値を出力します。
SELECT MAX(列名) FROM テーブル名
注文のうち最も高い売上金額を表示します。
SELECT MAX(Total) FROM Order_header
MIN()
MIN() は最小値を出力します。
SELECT MIN(列名) FROM テーブル名
注文のうち最も低い売上金額を表示します。
SELECT MIN(Total) FROM Order_header
AVG()
AVG() は平均を出力します。
SELECT AVG(列名) FROM テーブル名
売上の平均金額を出力してみましょう。
SELECT AVG(Total) FROM Order_header
Step2 : GROUP BY
先ほどの例で、ある購入者に対する総売上金額を出力してみました。
もし数千人、数万人の購入者がいて一人一人SELECT文を実行するのは大変です。
各購入者の総売上金額の出力をひとつのSQL文で対処できないだろうか…
そんな時に役立つのが GROUP BY
です。
SELECT PurchaserCode, SUM(Total) FROM Order_header
エラーです。それではSELECT文をよく見てみましょう。
SUM(Total)をSELECT文から消去すると、 「購入者コードを注文ヘッダーテーブルから抽出する」という記述です。
それでは次にPurchaserCodeをSELECT文から消してみます。
「登録されている売上金額の合計を出力する」です。
ということは出力項目においてSELECT対象が明らかに違います。
知りたいのは購入者ごとの合計 です。
これを解決するのがGROUP BY句
です。
SELECT 列名1, 集計関数(列名2) [, 列名3 … ] FROM テーブル名 GROUP BY 列名1 [, 列名3 … ]
先ほどの例に当てはめてみましょう。
SELECT PurchaserCode, SUM(Total) FROM Order_header GROUP BY PurchaserCode
GROUP BY句にはまとめたい列名を記述します。この例では購入者ごとにまとめたいのでPurchaserCodeを指定します。
するとSUM(Total)の対象は全レコードではなく、PurchaserCodeでグループ分けされた
レコードとなります。同じくPurchaserCodeもPurchaserCodeでグループ分けされた列を
SELECTしているため、「購入者ごとの売上金額の合計」という結果が得られるのです。
では同じくCOUNT()を使用して購入者ごとの注文回数を調べましょう。
SELECT PurchaserCode, COUNT(OrderNo) FROM Order_header GROUP BY PurchaserCode
ここでもう少し条件を加えて、注文回数が1回の購入者を調べます。
集計された結果に対する条件はWHERE句では記述できません。 HAVING句を使います。
SELECT PurchaserCode, COUNT(OrderNo) FROM Order_header GROUP BY PurchaserCode HAVING COUNT(OrderNo) = 1
HAVING句の後ろに集計行に対する条件を指定します。集計関数が関わらない条件ならばWHERE句も使えます。
購入者を指定した購入件数を調べます。実行してみましょう。
SELECT PurchaserCode, COUNT(OrderNo) FROM Order_header WHERE PurchaserCode = '0501' GROUP BY PurchaserCode SELECT PurchaserCode, COUNT(OrderNo) FROM Order_header GROUP BY PurchaserCode HAVING PurchaserCode = '0501'
WHERE句でもHAVING句でも同じ結果になりました。結果は同じでも検索手順が異なります。
WHERE句のほうは購入者コード’0501’の情報を抽出後、購入者コードでグループ分けしています。
HAVING句では、まずグループ分けを行ってから購入者コード’0501’のグループを抽出します。
● WHERE句による検索手順
● HAVING句による検索手順
このように、集計関数がWHERE句で使用できないのは、グループ分けを行わない限り値を抽出できないからです。