[メモ] SQL学習2 (DISTINCT, 四則演算, SUM, AVG, COUNT, MAX / MIN, GROUP BY, HAVING)

DISTINCT

「DISTINCT」は、指定のカラムの検索結果から重複するデータを除くことができる。
以下例はuserテーブルからnameカラムの重複するデータを省略して取得している。

SELECT DISTINCT(name)
FROM user;

四則演算

SQLでは四則演算を使って取得結果を加工することができる。
以下例はfoodテーブルのfood_nameカラムとpriceカラムに100掛けたデータを取得している。

SELECT food_name, price * 100
FROM food;

SUM

数値の合計を計算したい時は「SUM」を使う。
指定したカラムの合計を計算して取得する。
WHEREと併用可能。
以下例はuserテーブルのnameカラムの山田さんの支払い額の合計を取得している。

SELECT SUM(pay_money)
FROM user
WHERE name = '山田';

AVG

数値の平均を求めるには「AVG」を使う。
指定したカラムに保存されているデータの平均を計算する。
WHEREと併用可能。
以下例はuserテーブルのnameカラムの山田さんの睡眠時間の平均を取得している。

SELECT AVG(sleep_time)
FROM user
WHERE name = '山田';

COUNT

「COUNT」は指定したカラムのデータの合計数を計算する。
COUNTはNULLデータの数を計算しない。
例えば、全5件のデータ中3件がNULLの場合、取得結果は全部で2件となる。

NULLデータも含めて計算したい場合は「*」で全カラムを指定する。
「*」を使った場合、特定のカラムのデータの数ではなく、レコードの数を計算する。

WHEREと併用可能。

以下例はWHEREによって山田さんがいくつボールを買ったかを取得している。

-- ballカラム内のNULLデータを含んで取得する場合
SELECT COUNT(*)
FROM toy
WHERE name = '山田';

-- ballカラム内のNULLデータを含まずに取得する場合
SELECT COUNT(ball)
FROM toy
WHERE name = '山田';

MAX / MIN

指定したカラム内のデータで最大のデータを取得したい場合は「MAX」を、最小のデータを取得したい場合は「MIN」を使う。
WHEREと併用可能。
以下例は山田さんが買った一番高い金額を取得している。

SELECT MAX(price)
FROM shop
WHERE name = '山田';

GROUP BY

「GROUP BY」を用いると、データをグループ化することができる。
「GROUP BY カラム名」とすることで、指定したカラムで完全に同一のデータを持つレコードどうしが同じグループとなる。
グループ化するには、今までの集計関数を取得するFROMの後ろに「GROUP BY カラム名」を追加する。

GROUP BYを用いる場合、SELECTで使えるのはGROUP BYに指定しているカラム名と、集計関数のみ。

*SELECTで集計関数を使っていない場合、日付ごとに集計された値が取得できない。(A)

SELECT SUM(price), shop_date
FROM shop
GROUP BY shop_date;

-- *A
SELECT price, shop_date
FROM shop
GROUP BY shop_date;

GROUP BYに複数のカラムを指定する

「GROUP BY」は複数のカラム名を適用させることができ、その場合は、カラム名同士をコンマ(,)で区切る。
以下例のように日付と名前ごとに合計金額を取得できる。そのほかにも、日付と名前ごとに来店回数を取得することもできる。

SELECT SUM(price), shop_date, name
FROM shop
GROUP BY shop_date, name;

GROUP BYとWHEREを併用する

GROUP BYはWHEREと併用することができ、WHEREの後に記述する。

実行順序は上から

1. WHERE (条件検索)
2. GROUP BY (グループ化)
3. COUNT, SUM, AVG, MAX, MIN (集計関数)

SELECT 集計関数
FROM テーブル名
WHERE 条件
GROUP BY カラム名A, カラム名B;

以下例は「帽子」のレコードを検索して、日付と名前でグループ化している。
最後にグループ化した結果に関して集計関数(今回はSUM関数)で計算している。

SELECT SUM(price), shop_date, name
FROM shop
WHERE category = "帽子"
GROUP BY shop_date, name;

HAVING

GROUP BYでグループ化したデータを更に絞り込みたい場合には「HAVING」を用いる。
「GROUP BY カラム名 HAVING 条件」のようにすることで、条件を満たすグループを取得することが可能。
グループ化された後で条件を元に検索される。

実行順序は上から

1. WHERE (条件検索)
2. GROUP BY (グループ化)
3. COUNT, SUM, AVG, MAX, MIN (集計関数)
4. HAVING

WHEREはグループ化される前のテーブル全体を検索対象とするのに対し、HAVINGはGROUP BYによってグループ化されたデータを検索対象としている

HAVINGの注意点として、グループ化された後のテーブルから検索するため、条件文で使うカラムは必ずグループ化されたテーブルのカラムを使う。

以下例は日付でグループ化して集計、そして合計額1000円以下を条件にデータを取得している。

SELECT SUM(price), shop_date
FROM shop
GROUP BY shop_date
HAVING SUM(price) <= 1000;