[SQL] GROUP BYとよく使う関数紹介
GROUP BY（グループ化、グループ別集計）
データを特定の基準でグループ化して結果を取得する際に使用します。
- 通常、<span style="color:red">単一行関数</span>と一緒に使用されることが多いです。
- 単一行関数：
SUM()、AVG()、MAX()、MIN()、COUNT()など、結果が1行で返される関数のことを指します。 -
GROUP BYで指定した列は、単一行関数と一緒にクエリできます！ - グループ化後の条件指定には
HAVING句を使用します。 -
WHERE句はSELECTよりも先に実行されます。
🌟クエリの実行順序🌟
SQL文法の順序
- SELECT
- FROM
- WHERE
- GROUP BY
- HAVING
- ORDER BY
SQL実際の実行順序
- FROM：各テーブルの確認
- ON：結合条件の確認
- JOIN：テーブルの結合（マージ）
- WHERE：データ抽出条件の確認
- GROUP BY：特定列でデータをグループ化
- HAVING：グループ化後の条件確認
- SELECT：データの抽出
- DISTINCT：重複削除
- ORDER BY：データの並べ替え
🙌SELECT句は実行される順序が遅いことを認識しましょう！
💁♀️エイリアス（別名）はFROM、SELECT、ORDER BY句でのみ使用可能です。
参考_GROUP BYの複数列指定が可能
列名でグループ化
SELECT year,
month,
COUNT(*) AS count
FROM example_table
GROUP BY year, month;
#### 列番号でグループ化
`ORDER BY`句では列名を番号で代用することができます。
```sql
SELECT year,
month,
COUNT(*) AS count
FROM example_table
GROUP BY 1, 2;
```sql
-- プログラミングテストでの使用例
-- 年、月、性別でグループ化してユーザー数をカウント
-- SELECT句、GROUP BY句、ORDER BY句を参照
SELECT YEAR(SALES_DATE) AS YEAR,
MONTH(SALES_DATE) AS MONTH,
GENDER,
COUNT(DISTINCT U.USER_ID) AS USERS
FROM ONLINE_SALE AS O
JOIN USER_INFO AS U
ON O.USER_ID = U.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER;
GROUP BYとよく使われる関数
日付に関する関数と機能
NOW()
NOW() : 現在の日付と時刻を取得
SELECT NOW();
CURRENT_DATE()
CURRENT_DATE() : 現在の日付のみを取得
SELECT CURRENT_DATE();
CURRENT_TIME()
CURRENT_TIME() : 現在の時刻のみを取得
SELECT CURRENT_TIME();
DATE_FORMAT
DATE_FORMAT(date, format[, locale]) : 日付のフォーマットを変更
フォーマット例：YYYY-MM-DDなど
大文字、小文字、%、記号を使用
日付フォーマット使用例
| 項目 | フォーマット例 |
|---|---|
| 年 |
Y => yyyy, y => YY |
| 月 |
M => 英語表記, m => 数字表記 |
| 日 |
D => 英語表記, d => 数字表記 |
| 時 |
H => 数字表記, h => 数字表記 |
| 分 |
I => 数字表記, i => 数字表記 |
| 秒 |
S => 数字表記, s => 数字表記 |
💁♀️ %y の後に記号を追加すると、好きな形式で出力可能です！
例:
-
%Y-%m= 2024-08 -
%Y:%m= 2024:08
日付フォーマットの例
SELECT NOW(),
DATE_FORMAT(NOW(), '%Y-%m-%d') AS 'YYYY-MM-DD', -- 2024-08-06
DATE_FORMAT(NOW(), '%y-%M-%D') AS 'yy-Month-Day', -- 24-August-6th
DATE_FORMAT(NOW(), '%Y%m%d') AS 'YYYYMMDD', -- 20240806
DATE_FORMAT(NOW(), '%y%m%d') AS 'yyMMdd', -- 240806
DATE_FORMAT(NOW(), '%y-%m-%d %H:%i:%s') AS 'yy-mm-dd HH:MM:SS', -- 24-08-06 09:16:59
DATE_FORMAT(NOW(), '%h:%i:%s') AS 'hh:MM:SS';
日付および時間から特定の情報を抽出する
NOW() -- 2024-08-06 09:29:02 を基準とした例
- 年:
YEAR(NOW())→ 2024 - 月:
MONTH(NOW())→ 8 - 日:
DAY(NOW())→ 6 - 時:
HOUR(NOW())→ 9 - 分:
MINUTE(NOW())→ 29 - 秒:
SECOND(NOW())→ 2
単一行関数の紹介
単一行関数とは？
-
SUM()のようにデータの数に関係なく、結果が1つだけ返される関数
例:SUM(),MAX(),MIN(),AVG(),COUNT()
COUNT()
COUNT()関数の注意点
NULLを防ぐために、COUNT()関数には主キー（PK）を入れることを推奨！
-- 例) テーブル内のデータ数を取得
SELECT COUNT(EMPNO) FROM emp;
COUNT()関数を使用して重複する項目の数を取得する
SELECT 重複値カラム名, COUNT(重複値カラム名) FROM テーブル名 GROUP BY 重複値カラム名 HAVING COUNT(重複値カラム名) > 1;
-- 例) NULLを除いた重複した名前をカウントする場合
SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL
GROUP BY NAME
HAVING COUNT(NAME) > 1
AND COUNT(NAME) IS NOT NULL;
AVG()
AVG()関数の注意点
平均計算時はNULLデータに注意が必要です。
通常、合計は値があるものだけを計算します。
-- 例) 従業員10人中4人だけがコミッションデータを持つ場合、そのクエリは4人のコミッションの平均を出力します。
SELECT AVG(COMM) FROM emp;
NULL値があるデータの平均を取得する方法
例) 従業員全体を基準にコミッションを知りたい場合
<span style=color:red>IFNULL()は単一行関数ではありません！</span> IFNULLでNULL値を0に変換してから計算すれば良いです！
-- IFNULL(カラム名, 何に? ) : NULL値を置き換える関数（数字、文字列可能）
SELECT COMM, IFNULL(COMM,0) FROM emp;
-- 関数内に関数を組み合わせることも可能
SELECT AVG(IFNULL(COMM, 0)) FROM emp;
SELECT ROUND(AVG(IFNULL(COMM, 0)), 2) FROM emp;
<hr/>
参考関数
CONCAT()
取得した値の後ろに文字列を追加したい場合に使用します。
-- 例えば、月給の後ろに「万円」を付けたい場合は？
-- 取得例) 月給 / 100万円
SELECT CONCAT(COMM, '万円') AS 月給 FROM EMP;
次のコードは、プログラミングのSQLコーディングテストで使用したものです。 私が求めた値にROUND関数で四捨五入し、その後ろに「km」を追加するコードです。
参考!!!
😲ORDER BY句で'TOTAL_DISTANCE'で降順ソートしたところ、間違いだと言われました。どうやらCONCATで文字列を追加したため、それが文字列として認識されたようです（推測）。ROUND(SUM(D_BETWEEN_DIST),1) --> INT型として出力されるように設定し、降順にしたら正解でした！
-- SELECT句を参考！
-- 取得例) 分単線 / 19.5km / 2.45km ..
SELECT ROUTE
,CONCAT(ROUND(SUM(D_BETWEEN_DIST),1), 'km')
AS TOTAL_DISTANCE
,CONCAT(ROUND(AVG(IFNULL(D_BETWEEN_DIST, 0)), 2), 'km')
AS AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE
GROUP BY ROUTE
ORDER BY ROUND(SUM(D_BETWEEN_DIST),1) DESC;
Discussion