さて、前回の記事ではSQLの基本となる前提知識とデータベースの検索方法をSELECT句を中心に解説してきました。
今回は、このSQLシリーズの続きである集約関数について解説していきます。
それでは、さっそく見ていきましょう。
①集約関数とは何か
集約関数とは、関数を使うことで複数行の入力から1行を出力する働きを持ちます。
ここでの「集約」とは複数行を1行にまとめるという意味になります。
集約関数は具体的には以下のような関数のことをいいます。
- COUNT: テーブルのレコード数を数える
- SUM: テーブルの数値列のデータを合計する
- AVG: テーブルの数値列のデータを平均する
- MAX: テーブルの任意の列のデータの最大値を求める
- MIN: テーブルの任意の列のデータの最小値を求める
これらの関数がSQLに用意されている集計用の関数です。
前回のShohinテーブルを使ってそれそれさらっと見ていきましょう。
その前に、Shohinテーブルのデータをおさらいしておきます。
Shohinテーブルのデータは以下のようになっています。
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+----------------+---------------+--------------+--------------+------------
0001 | Tシャツ | 衣服 | 1000 | 500 | 2009-09-20
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
0003 | カッターシャツ | 衣服 | 4000 | 2800 |
0004 | 包丁 | キッチン用品 | 3000 | 2800 | 2009-09-20
0005 | 圧力鍋 | キッチン用品 | 6800 | 5000 | 2009-01-15
0006 | フォーク | キッチン用品 | 500 | | 2009-09-20
0007 | おろしがね | キッチン用品 | 880 | 790 | 2008-04-28
0008 | ボールペン | 事務用品 | 100 | | 2009-11-11
(8 行)
②COUNT関数
さて、COUNT関数ではレコード数をカウントできる機能です。
実際に使ってみます。
SELECT COUNT(*)
FROM Shohin;
関数を使う場合はこのように()の中に入力する情報を入れます。
これを「引数」と呼ぶので覚えておきましょう。
出力結果は以下です。
count
-------
8
(1 行)
これで、レコードの数を数えることができました。
③SUM関数
さて、次はSUM関数です。
これは、テーブルの数値列の合計を求めることができます。
SELECT SUM(hanbai_tanka)
FROM Shohin;
出力結果は以下です。
sum
-------
16780
(1 行)
しっかり販売単価額の合計値が計算されています。
④AVG関数
サクサクいきます。
次はAVG関数。平均値を求めます。
SELECT AVG(hanbai_tanka)
FROM Shohin;
出力結果は以下です。
avg
-----------------------
2097.5000000000000000
(1 行)
⑤MIN関数、MAX関数
さて、最後は同時にいきます。
MIN関数を最小値を、MAX関数は最大値を求めます。
SELECT MAX(hanbai_tanka), MIN(shiir_tanka)
FROM Shohin;
出力結果は以下です。
max | min
------+-----
6800 | 320
(1 行)
さて、ここまで集約関数を見てきましたが、実は実際にはGROUP BY句を使ってテーブルを「商品分類ごと」や「登録日ごと」などのように分類して集計を行うことが多いです。
というわけで、GROUP BY句を使ってテーブルをいくつかのグループに分けてデータを集約していきましょう。
⑥GROUP BY句
たとえば、先ほどのCOUNT関数をGROUP BY句で商品分類ごとの行数を数えてみましょう。
コードは以下のようになります。
SELECT shohin_bunrui, COUNT(*)
FROM Shohin
GROUP BY shohin_bunrui;
実行結果は以下になります。
shohin_bunrui | count
---------------+-------
キッチン用品 | 4
衣服 | 2
事務用品 | 2
(3 行)
しっかりと商品分類ごとに行数のカウントができています。
このように、GROUP BY句ではテーブルのデータをいくつかのグループに切り分けることができます。
今回の場合だと、商品分類ごとにテーブルのデータを切り分けていることになります。
これまで、様々な句を見てきましたが、これらの句は併用することができます。
たとえば、今回のGROUP BY句もWHERE句などと併用することができます。
たとえば、以下のような感じです。
SELECT shiire_tanka, COUNT(*)
FROM Shohin
WHERE shohin_bunrui = '衣服'
GROUP BY shiire_tanka;
とすると、実行結果は以下になります。
shiire_tanka | count
--------------+-------
500 | 1
2800 | 1
(2 行)
まず、WHERE句に「衣服」が指定されているので、テーブルのデータから商品分類の「衣服」のみのデータに絞り込まれます。
次に、GROUP BY句によって仕入単価ごとにグループ分けされます。
最後に、SELECT句に仕入単価とカウント関数を指定しているので、出力されるテーブルには仕入単価とレコードの行数が表示されます。
つまり、WHERE句とGROUP BY句を併用した場合の実行順序は、FROM→WHERE→GROUP BY→SELECTとなります。
ちなみに、記述する順番も決まっていて、SELECT→FROM→WHERE→GROUP BYの順番になります。
そして、上の例の場合、カウント関数を使っているのでSELECT句の仕入単価はGROUP BY句や集約関数内で使用しなければなりません。
たとえば、GROUP BY句だけ削除して実行するとエラーになってしまいます。
なぜかというと、仕入単価ごとにグループ分けしてくれないとカウントできないからですね。
⑦HAVING句
さて、次にHAVING句を見ていきます。
HAVING句はGROUP BY句の条件を指定できます。
GROUP BY版のWHERE句だと思ってください。
たとえば、商品分類で集約したグループに対して、「含まれる行数が2行」という条件を指定すると、以下のようになります。
SELECT shohin_bunrui, COUNT(*)
FROM Shohin
GROUP BY shohin_bunrui
HAVING COUNT(*) = 2;
実行結果は以下になります。
shohin_bunrui | count
---------------+-------
衣服 | 2
事務用品 | 2
(2 行)
上の例では、まずGROUP BY句によって商品分類ごとにグループ分けされ、その中でHAVING句によってレコード数が「2」のデータだけが取り出されています。
⑧ORDER BY句
最後に、ORDER BY句を見ていきます。
ORDER BY句は表示順を指定することができます。
表示順は昇順と降順があり、昇順はASC、降順はDESCを使います。
具体的には以下のコード。
SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka
FROM Shohin
ORDER BY hanbai_tanka DESC;
出力結果は以下です。
shohin_id | shohin_mei | hanbai_tanka | shiire_tanka
-----------+----------------+--------------+--------------
0005 | 圧力鍋 | 6800 | 5000
0003 | カッターシャツ | 4000 | 2800
0004 | 包丁 | 3000 | 2800
0001 | Tシャツ | 1000 | 500
0007 | おろしがね | 880 | 790
0002 | 穴あけパンチ | 500 | 320
0006 | フォーク | 500 |
0008 | ボールペン | 100 |
(8 行)
はい、販売単価が降順に並んでいることが確認できると思います。
昇順にしたいときにはDESCの部分をASCに変えればよいです。
コメント