LoginSignup

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

This article is a Private article. Only a writer and users who know the URL can access it.
Please change open range to public in publish setting if you want to share this article with other users.

More than 3 years have passed since last update.

@nyan_tech_24

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

Last updated at Posted at 2020-06-11

#集約と並べ替え

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)
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?