はじめに
SQLの基本(集計関数、グループ化)を自分なりにまとめました。
以下で紹介するDISTINCTや四則演算、関数は主にSELECTで使います。
DISTINCT 重複データ削除
構文:DISTINCT(カラム名)
これで自動的にカラムの重複データを削除してくれる。
SELECT DISTINCT(name)
FROM closet;
四則演算
例えば、消費税を含んだ値にしたいとき。
カラム名 * 1.1のようにする。指定したカラムの各全データに1.1をかけた値が取れる。
SELECT name, price * 1.1
FROM closet;
四則演算はORDER BYで使うこともある。
例えば、買った服をフリマアプリで売った時の差額の大きい順を考える。
SELECT name, buyPrice - sellPrice
FROM closet
ORDER BY buyPrice - sellPrice DESC;
SUM関数 合計を求める
構文：SUM(カラム名)
以下は、クローゼット内の服の合計額を求めるとき。
SELECT SUM(price)
FROM closet;
実行結果
| SUM(price) |
|---|
| 12000 |
SUM関数はWHEREと一緒に使える。
以下は、ドレスシャツの合計額を求めるとき。
SELECT SUM(price)
FROM closet;
WHERE name = "ドレスシャツ";
AVG関数 (avarage: 平均値)
平均を求めたい時。
AVG(カラム名)
SELECT AVG(price)
FROM closet;
実行結果
| AVG(price) |
|---|
| 4000 |
SELECT AVG(price)
FROM closet;
WHERE name = "ドレスシャツ";
COUNT関数 いくつか数える
カラムのデータ数を数えたい時。
COUNT(カラム名)
NULLのデータは数えない。
COUNT(price)
nullも含めて数えたい時は*(全てのカラム)を指定する。
これはレコードの数を数える。
SELECT COUNT(*)
FROM closet;
WHEREで条件を絞れる。
SELECT COUNT(*) --レコードの数
FROM closet
WHERE name = "ドレスシャツ"; --レコードの数から条件に一致するものだけに絞る
MAX・MIN 関数 最大・最小
最大・最小を求めるとき。
並び替えてもよいが、一発で求められる。
MAX(カラム名)
MIN(カラム名)
最も値段の高いアイテムのデータ。
SELECT MAX(price)
FROM closet;
WHEREと一緒に。ドレスシャツで一番高いもの。
SELECT MAX(price)
FROM closet;
WHERE name = "ドレスシャツ";
以上、集計関数について。
ここからグループ化について。
GROUP BY グループ化
例えば、日付(date)ごとに金額の合計を取りたいとき。
GROUP BY カラム名を使う。
指定したカラム名で、完全に同じデータが、自動的にグループになる。
FROMの後ろにGROUP BY カラム名を書く。
SELECT SUM(price), date --2.集計する
FROM closet
GROUP BY date; --1.グループ化して
出力例
| SUM(price) | date |
|---|---|
| 3000 | 2022-09-01 |
| 4000 | 2022-09-03 |
| 5000 | 2022-09-07 |
GROUP BYでグループ化されているときに、集計関数(SUM,AVG,COUNTなど)を使うと、グループごとに集計される。
GROUP BYを使うとき、SELECTで使えるのは、
GROUP BYで指定したカラム名と、集計関数(SUM,AVGなど)のみ。
なので反対に、SELECTで使いたいカラムは、GROUP BYしておく。
SELECT price, date --priceは使えない。SUM(price)なら可.
FROM closet
GROUP BY date;
グループでまとめるので、取るデータもまとめたものだけ。
日付"ごと"とあったら、GROUP BYを使う。
GROUP BY (複数)
複数のカラムでもグループ化ができる。例えば、日付と名前など。
構文：GROUP BY カラム１, カラム2, ...
コンマで繋げる。
SELECT SUM(price), date, name
FROM closet
GROUP BY date, name;
データの組み合わせの数だけグループができる。
各グループごとに集計ができる。
SELECE SUM(price), date, nameや
SELECE COUNT(*), date, nameなど。
GROUP BY (WHERE)
より細かい条件でグループ化をしたいとき、WHEREを使う。
WHEREを先に
GROUP BYを後に書く。
実行順は、以下の表の通り。
| 上から順に実行 | SQL |
|---|---|
| 検索 | WHERE |
| グループ化 | GROUP BY |
| 関数 | COUNT, SUM, AVG, MAX, MIN |
例えば、日付とカテゴリーごとに、ドレスシャツに使ったお金の合計を取得するときは、
- WHEREで名前が"ドレスシャツ"のレコードを検索する。
- 日付とカテゴリーでグループ化する。
- 集計関数で集計する。
以上の順になる。コードを書くと、以下のようになる。
SELECT SUM(price), date, category
FROM closet
WHERE name = "ドレスシャツ" --1. ドレスシャツのレコードを検索
GROUP BY date, category; --2. 日付とカテゴリーでグループ分け
HAVING
グループ化して集計した後、さらにしぼりこむ時に使う。
例えば、グループ化して、合計金額を集計した後、さらに合計金額が4000円を超えたものに絞り込むとき、
HAVINGを使ってHAVING SUM(price) >= 4000と書く。
GROUP BY カラム名 が先で、
HAVING 条件が後にくるようにする。
実行順は以下の通り。
| 上から実行 | SQL |
|---|---|
| 検索 | WHERE |
| グループ化 | GROUP BY |
| 関数 | COUNT, SUM, AVG, MAX, MIN |
| 条件絞り込み | HAVING |
WHEREとHAVINGの違いは、
WHEREはグループ化される前のテーブル全体が対象で、
HAVINGはGROUP BYでグループ化されたデータが対象。
なので、HAVINGは、必ずグループ化されたデータのカラムを使う。
グループ化した後のデータを絞り込みたいなら、HAVINGを使う。
集計関数で絞り込む時は、HAVING。WHEREではない。
SELECT SUM(price), date
FROM closet
WHERE name = "ドレスシャツ"
GROUP BY date,
HAVING SUM(price) > 4000; --必ずグループ化されたデータのカラムを使う。
以上、グループ化について。