集約と並べ替え

COUNT: テーブルのレコード数(行数)を教える
SUM: テーブルの数値列のデータを合計する
AVG: テーブルの数値列のデータを平均する
MAX:　テーブルの任意の列のデータの最大値を求める
MIN: テーブルの任意の列のデータの最小値を求める

COUNT

select count(*) from shohin;
-------
     8
(1 row)

NULLを除外して行数を数える

select shiire_tanka from shohin; 
 shiire_tanka 
--------------
          500
          320
         2800
         2800
         5000

          790

(8 rows)

NULLは除外されるので

 select count(shiire_tanka) from shohin; 
 count 
-------
     6

SUM

SUMはテーブルの数値列のデータを合計する

select sum(hanbai_tanka) from shohin; 
sum  
-------
 16780
(1 row)

NULLは除外されて計算される

select sum(shiire_tanka) from shohin;  --nullを除外して計算できる
  sum  
-------
 12210

AVG

AVGはテーブルの数値列のデータを平均する

select avg(hanbai_tanka) from shohin;
 avg          
-----------------------
 2097.5000000000000000
(1 row)

NULLは除外されて計算される

select avg(shiire_tanka) from shohin;
 avg          
-----------------------
 2035.0000000000000000
(1 row)

MAX MIN

MAX MINは最大最小を求める

select max(hanbai_tanka), min(shiire_tanka) from shohin;
 max  | min 
------+-----
 6800 | 320
(1 row)

select max(torokubi), min(torokubi) from shohin;
 max     |    min     
------------+------------
 2009-11-11 | 2009-01-15
(1 row)

DISTINCT

DISTINCTは重複を除外することができる
そしてCOUNTとDISTINCTと併用して使うこともできる

select count(distinct shohin_bunrui) from shohin;  --同じ分類のものを一つに集約してcountしている
 count 
-------
     3
(1 row)

テーブルをグループに切り分けるGROUP BY

GROUP BY句で分類ごとにグループに分けることができる。
GROUP BYの注意点
1. SELECT句に書けるものが限定される
2. GROUP BY句にはSELECT句でつけた列の別名は使えない。
3. GROUP BY句には集約結果をソートしない
4. WHERE句に集約関数を書くことはできない

商品分類でグループ分する

 select shohin_bunrui, count(*) from shohin 
 group by shohin_bunrui;
shohin_bunrui | count 
---------------+-------
 キッチン用品    |     4
 衣服          |     2
 事務用品       |     2
(3 rows)

句にも順序がある
1. SELECT
2. FROM
3. WHERE
4. GROUP BY

集約キーにNULLが含まれている場合

select shiire_tanka, count(*)
from shohin
group by shiire_tanka;
shiire_tanka | count 
--------------+-------
              |     2  --NULLの部分が集約されている
          320 |     1
          500 |     1
         2800 |     2
         5000 |     1
          790 |     1
(6 rows)

GROUP BY と　WHERE句

select shiire_tanka, count(*)
from shohin
where shohin_bunrui = '衣服'
group by shiire_tanka;
 shiire_tanka | count 
--------------+-------
          500 |     1
         2800 |     1
(2 rows)

HAVING句

たとえばグループをキッチン用品、事務用品、衣服で分けた場合、事務用品と衣服だけ選択したい。
このようなときにHAVING句を使用する。
ちなみにWHERE句はグループに対して条件を指定できない。

語句の順序は以下となる
1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING

HAVING句切り分けたグループにたいして条件を選択できる。

 select shohin_bunrui, Count(*) --分類と分類ごとのカウント
 from shohin
 group by shohin_bunrui        --分類ごとにグループに分ける
 having count(*) = 2;   --分類ごとのレコード数が2のを選択
 shohin_bunrui | count 
---------------+-------
 衣服          |     2
 事務用品      |     2
(2 rows)

-- HAVING句がなしの場合

select shohin_bunrui, count(*)
from shohin
group by shohin_bunrui;
 shohin_bunrui | count 
---------------+-------
 キッチン用品  |     4
 衣服          |     2
 事務用品      |     2
(3 rows)

select shohin_bunrui, avg(hanbai_tanka)
from shohin
group by shohin_bunrui;
shohin_bunrui |          avg          
---------------+-----------------------
 キッチン用品  | 2795.0000000000000000
 衣服          | 2500.0000000000000000
 事務用品      |  300.0000000000000000
(3 rows)

--GROUP BY を指定した場合
select shohin_bunrui, avg(hanbai_tanka)
from shohin
group by shohin_bunrui
having avg(hanbai_tanka) >= 2500;
shohin_bunrui |          avg          
---------------+-----------------------
 キッチン用品  | 2795.0000000000000000
 衣服          | 2500.0000000000000000
(2 rows)

HAVING句に書ける要素は
・定数
・集約関数
・GROUP BY句で指定した列名

ただし以下のような使い方はできない

select shohin_bunrui, Count(*)
from shohin
group by shohin_bunrui
having shohin_mei = 'ボールペン';
ERROR:  column "shohin.shohin_mei" must appear in the GROUP BY clause or be used in an aggregate function
LINE 4: having shohin_mei = 'ボールペン';

理由は分類によって集約されているので、例えば文房具という分類の中に「消しゴム、ボールペン、シャーペン」などがデータで入っていたとしても、分類としてグループ化されているので、グループ化後は商品名を指定することはできない。

ちなみにWHERE句とHAVING句で同じ出力をするようにかける

select shohin_bunrui, Count(*)
from shohin
group by shohin_bunrui;
 shohin_bunrui | count 
---------------+-------
 キッチン用品  |     4
 衣服          |     2
 事務用品      |     2
(3 rows)

select shohin_bunrui, Count(*)
from shohin
where shohin_bunrui = '衣服'
group by shohin_bunrui;
 shohin_bunrui | count 
---------------+-------
 衣服          |     2
(1 row)

条件も同じで返す結果が同じであれば、WHERE句を書いた方がいい
理由はHAVING句はグループに対する条件指定で、単なる行に対する条件はWHERE句で書いたほうが読みやすい。

ほかにも実行速度が違うという点が挙げられる。
どういうことかというと、WHERE句の実行順序はGROUP BY句よりも先です。なので、WHERE句で先に条件を指定することで、レコード数を減らすことができます。
たとえば分類が文房具1,000件、衣服が10,000件あった場合、先に文房具1,000件に絞りグループ化したほうが1,000件に対してのグループ化になる。
HAVING句の場合は11,000件のデータから分類をそれぞれグループ化し、そこから分類が文房具に絞り込むので、データのやりとりに時間がかかる。
できるだけ早い通信になることにこしたことはないので、WHERE句を先に書いたほうがいい。

昇順と降順

データを昇順にしたり、降順にするためにはORDER BY句を使う

select * from shohin
order by hanbai_tanka desc; --DESCを指定することで販売単価を降順にする
 shohin_id |   shohin_mei   | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi  
-----------+----------------+---------------+--------------+--------------+------------
 0005      | 圧力鍋          | キッチン用品     |         6800 |         5000 | 2009-01-15
 0003      | カッターシャツ     | 衣服           |         4000 |         2800 | 
 0004      | 包丁            | キッチン用品    |         3000 |         2800 | 2009-09-20
 0001      | Tシャツ         | 衣服           |         1000 |          500 | 2009-09-20
 0007      | おろしがね       | キッチン用品     |          880 |          790 | 2008-04-28
 0002      | 穴あけパンチ     | 事務用品        |          500 |          320 | 2009-09-11
 0006      | フォーク         | キッチン用品     |          500 |              | 2009-09-20
 0008      | ボールペン      | 事務用品        |          100 |              | 2009-11-11
(8 rows)

select * from shohin
order by hanbai_tanka ; --何も指定しないと昇順になる。明示的に昇順に指定したい場合はASCを指定する。
 shohin_id |   shohin_mei   | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi  
-----------+----------------+---------------+--------------+--------------+------------
 0008      | ボールペン       | 事務用品       |          100 |              | 2009-11-11
 0006      | フォーク         | キッチン用品     |          500 |              | 2009-09-20
 0002      | 穴あけパンチ     | 事務用品        |          500 |          320 | 2009-09-11
 0007      | おろしがね       | キッチン用品     |          880 |          790 | 2008-04-28
 0001      | Tシャツ         | 衣服           |         1000 |          500 | 2009-09-20
 0004      | 包丁           | キッチン用品     |         3000 |         2800 | 2009-09-20
 0003      | カッターシャツ     | 衣服          |         4000 |         2800 | 
 0005      | 圧力鍋         | キッチン用品     |         6800 |         5000 | 2009-01-15
(8 rows)

SQL 第2版 ゼロからはじめるデータベース操作3章