2-2 集計とグループ化 - データを分析する

集計とグループ化 - データを分析する¶

集計関数とGROUP BYを使えば、データの「全体像」が見えてくる。件数、合計、平均、最大、最小。これらを自在に取り出す方法を学ぶ。

なぜこれを学ぶのか¶

前回（2-1）でSELECT文の条件指定を学んだ。「30歳以上のユーザー」「営業部の社員」など、条件に合うデータを取り出せるようになった。

しかし、実務では「データそのもの」だけでなく「データの傾向」を知りたいことが多い。

• 「ユーザーは全部で何人いるのか」
• 「社員の平均年齢は何歳か」
• 「部署ごとの人数を知りたい」
• 「最も売れた商品は何か」

こうした「集計」はレポート作成、ダッシュボード、経営判断に欠かせない。SQLの集計関数とGROUP BYを使えば、データベースが自動で計算してくれる。

使用するテーブル¶

この記事では、前回と同じusersテーブルを使う。DB Fiddle で「PostgreSQL 15」を選択し、以下のSQLを「Schema SQL」に貼り付けてほしい。

-- usersテーブルを作成
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL,
    department VARCHAR(50),
    salary INTEGER NOT NULL,
    created_at DATE NOT NULL
);

-- サンプルデータを投入
INSERT INTO users (name, email, age, department, salary, created_at) VALUES
    ('田中太郎', 'tanaka@example.com', 28, '営業部', 350000, '2023-04-01'),
    ('山田花子', 'yamada@example.com', 35, '開発部', 450000, '2022-08-15'),
    ('佐藤一郎', 'sato@example.com', 42, '開発部', 550000, '2021-01-10'),
    ('鈴木次郎', 'suzuki@example.com', 25, '営業部', 300000, '2024-01-05'),
    ('高橋美咲', 'takahashi@example.com', 31, '人事部', 400000, '2023-06-20'),
    ('田村健太', 'tamura@example.com', 29, '開発部', 420000, '2023-09-01'),
    ('中村由美', 'nakamura@example.com', 38, NULL, 380000, '2020-03-15'),
    ('小林誠', 'kobayashi@example.com', 45, '営業部', 480000, '2019-11-01');

作成されるテーブル:

今回はsalary（給与）列を追加した。集計関数の説明に使う。

集計関数の基本¶

集計関数は、複数の行から1つの値を計算する関数である。「全員の年齢を足す」「平均を出す」といった計算を、SQLが自動で行ってくれる。

主要な集計関数¶

これらを1つずつ見ていこう。

COUNT：件数を数える¶

COUNTは、行の件数を数える関数である。「何件あるか」を知りたいときに使う。

COUNT(*)：全件数を数える¶

SELECT COUNT(*) FROM users;

結果:

usersテーブルには8件のデータがある。*は「すべての行」を意味する。

COUNT(列名)：NULLを除いて数える¶

SELECT COUNT(department) FROM users;

結果:

COUNT(department)はNULLの行を除外して数える。中村由美さん（department=NULL）がカウントされないため、7件になる。

COUNT(DISTINCT 列名)：重複を除いて数える¶

SELECT COUNT(DISTINCT department) FROM users;

結果:

部署の種類は「営業部」「開発部」「人事部」の3つ。NULLは含まれない。

WHERE句との組み合わせ¶

-- 30歳以上のユーザーは何人？
SELECT COUNT(*) FROM users WHERE age >= 30;

結果:

条件に合う行だけをカウントできる。

SUM：合計を計算する¶

SUMは、数値列の合計を計算する関数である。

基本的な使い方¶

SELECT SUM(salary) FROM users;

結果:

全社員の給与合計は333万円。

WHERE句との組み合わせ¶

-- 開発部の給与合計
SELECT SUM(salary) FROM users WHERE department = '開発部';

結果:

開発部3人の給与合計は142万円（45万 + 55万 + 42万）。

AVG：平均を計算する¶

AVGは、数値列の平均を計算する関数である。

基本的な使い方¶

SELECT AVG(age) FROM users;

結果:

全社員の平均年齢は約34.1歳。

小数点以下を丸める¶

AVGの結果は小数点以下が長くなることがある。ROUND関数で丸められる。

SELECT ROUND(AVG(age), 1) FROM users;

結果:

ROUND(値, 桁数)で指定した桁数に丸める。

AVGとNULL¶

AVGはNULLの行を除外して計算する。

-- 仮に salary に NULL がある場合、その行は平均計算から除外される
SELECT AVG(salary) FROM users;

5人中2人がNULLの場合、3人の平均が計算される。「0」として扱われるわけではない点に注意。

MAX / MIN：最大値・最小値を取得¶

MAXは最大値、MINは最小値を取得する関数である。

最大値¶

SELECT MAX(salary) FROM users;

結果:

最高給与は55万円（佐藤一郎さん）。

最小値¶

SELECT MIN(salary) FROM users;

結果:

最低給与は30万円（鈴木次郎さん）。

日付にも使える¶

-- 最も古い入社日
SELECT MIN(created_at) FROM users;

-- 最も新しい入社日
SELECT MAX(created_at) FROM users;

結果:

文字列の場合はアルファベット順（辞書順）で比較される。

複数の集計関数を同時に使う¶

SELECT
    COUNT(*) AS 人数,
    SUM(salary) AS 給与合計,
    AVG(salary) AS 平均給与,
    MAX(salary) AS 最高給与,
    MIN(salary) AS 最低給与
FROM users;

結果:

1つのクエリで複数の集計を取得できる。ASで列に別名を付けると結果が見やすくなる。

DISTINCT：重複を排除する¶

DISTINCTは、結果から重複を排除するキーワードである。集計関数ではないが、データの種類を調べるときによく使う。

基本的な使い方¶

SELECT DISTINCT department FROM users;

結果:

重複する部署名が1つにまとめられる。NULLも1つの値として扱われる。

DISTINCTなしの場合¶

SELECT department FROM users;

結果:

DISTINCTがないと、行数分の結果が返る。

複数列のDISTINCT¶

SELECT DISTINCT department, age FROM users;

「部署と年齢」の組み合わせで重複を排除する。

GROUP BY：グループごとに集計する¶

GROUP BYは、データをグループ分けして、グループごとに集計する機能である。「部署ごとの人数」「年齢ごとの売上」といった分析に使う。

基本構文¶

SELECT 列名, 集計関数(列名) FROM テーブル名 GROUP BY 列名;

部署ごとの人数を数える¶

SELECT department, COUNT(*) FROM users GROUP BY department;

結果:

営業部3人、開発部3人、人事部1人、未所属1人。

部署ごとの平均給与¶

SELECT department, AVG(salary) AS 平均給与 FROM users GROUP BY department;

結果:

開発部の平均給与が最も高い。

複数の集計を同時に¶

SELECT
    department,
    COUNT(*) AS 人数,
    SUM(salary) AS 給与合計,
    ROUND(AVG(salary)) AS 平均給与,
    MAX(salary) AS 最高給与,
    MIN(salary) AS 最低給与
FROM users
GROUP BY department;

結果:

1つのクエリで部署ごとの詳細な分析ができる。

GROUP BYの重要なルール¶

SELECT句に書ける列は、GROUP BY句に含まれている列か、集計関数だけである。

-- エラー: name は GROUP BY に含まれていない
SELECT department, name, COUNT(*) FROM users GROUP BY department;

なぜエラーになるか？営業部には3人いるが、nameには「田中太郎」「鈴木次郎」「小林誠」のどれを表示すればよいか決められないため。

-- 正しい: GROUP BY に含まれる列と集計関数だけ
SELECT department, COUNT(*) FROM users GROUP BY department;

複数列でグループ化¶

-- 部署と入社年ごとの人数
SELECT
    department,
    EXTRACT(YEAR FROM created_at) AS 入社年,
    COUNT(*) AS 人数
FROM users
GROUP BY department, EXTRACT(YEAR FROM created_at)
ORDER BY department, 入社年;

EXTRACT(YEAR FROM 日付)は日付から年を取り出す関数。

HAVING：グループを絞り込む¶

HAVINGは、GROUP BYで作ったグループに対して条件を指定する句である。「2人以上の部署だけ」といった絞り込みができる。

WHEREとHAVINGの違い¶

基本的な使い方¶

-- 2人以上いる部署だけ表示
SELECT department, COUNT(*) AS 人数
FROM users
GROUP BY department
HAVING COUNT(*) >= 2;

結果:

人事部（1人）とNULL（1人）は除外される。

HAVINGで集計関数を使う¶

-- 平均給与が40万円以上の部署
SELECT department, AVG(salary) AS 平均給与
FROM users
GROUP BY department
HAVING AVG(salary) >= 400000;

結果:

WHEREとHAVINGの併用¶

-- 30歳以上の社員だけで集計し、2人以上いる部署を表示
SELECT department, COUNT(*) AS 人数
FROM users
WHERE age >= 30
GROUP BY department
HAVING COUNT(*) >= 2;

結果:

処理の流れ:

1. WHERE: 30歳以上の社員に絞る（5人）
2. GROUP BY: 部署ごとにグループ化
3. HAVING: 2人以上のグループだけ残す

よくある間違い：HAVINGでWHEREを使う¶

-- 誤り: 集計結果の条件にWHEREは使えない
SELECT department, COUNT(*) FROM users GROUP BY department WHERE COUNT(*) >= 2;

-- 正しい: 集計結果の条件にはHAVINGを使う
SELECT department, COUNT(*) FROM users GROUP BY department HAVING COUNT(*) >= 2;

WHEREはグループ化の前、HAVINGはグループ化の後に適用される。

ORDER BYとの組み合わせ¶

集計結果を並べ替えるには、ORDER BYを使う。

-- 人数が多い順に表示
SELECT department, COUNT(*) AS 人数
FROM users
GROUP BY department
ORDER BY 人数 DESC;

結果:

SQLの実行順序¶

SQLの句には処理される順序がある。記述順序とは異なる点に注意。

集計のパフォーマンス¶

集計処理は、データ量が増えると重くなりやすい。大量データを扱う際のポイントを押さえておこう。

インデックスの活用¶

WHERE句で絞り込む列にインデックスがあると、集計対象のデータを効率的に取得できる。

-- departmentにインデックスがあると速くなる
SELECT department, COUNT(*) FROM users WHERE department = '開発部' GROUP BY department;

ただし、GROUP BY自体にはインデックスが直接効かないケースも多い。

COUNT(*)とCOUNT(1)¶

COUNT(*)とCOUNT(1)は同じ結果を返し、パフォーマンスもほぼ同じ。どちらを使っても問題ない。

-- どちらも同じ
SELECT COUNT(*) FROM users;
SELECT COUNT(1) FROM users;

大量データでの注意点¶

大量データで集計が遅い場合の対策:

1. WHERE句で対象を絞る: 全件ではなく必要な範囲だけ集計
2. インデックスを活用: 絞り込み条件にインデックスを作成
3. マテリアライズドビュー: 集計結果を事前計算して保存
4. サマリーテーブル: 日次・月次の集計を別テーブルに保存

これらの詳細はPart 4「パフォーマンス編」で扱う。

実践例：売上分析レポート¶

ここまでの知識を組み合わせた、実践的なクエリを見てみよう。

部署別の人員・給与サマリー¶

SELECT
    COALESCE(department, '未所属') AS 部署,
    COUNT(*) AS 人数,
    SUM(salary) AS 給与合計,
    ROUND(AVG(salary)) AS 平均給与,
    MAX(salary) AS 最高給与,
    MIN(salary) AS 最低給与,
    MAX(salary) - MIN(salary) AS 給与幅
FROM users
GROUP BY department
HAVING COUNT(*) >= 1
ORDER BY 平均給与 DESC;

結果:

このクエリのポイント:

• COALESCE(department, '未所属'): NULLを「未所属」に置き換え
• 複数の集計関数を同時に使用
• MAX(salary) - MIN(salary): 給与幅を計算
• ORDER BY 平均給与 DESC: 平均給与の高い順に並べ替え

まとめ¶

• COUNT(*)で全件数、COUNT(列名)でNULL以外をカウント
• SUMは合計、AVGは平均、MAXは最大、MINは最小
• DISTINCTで重複を排除
• GROUP BYでグループごとに集計
• HAVINGでグループを条件で絞り込む
• WHEREは行、HAVINGはグループに対する条件
• SQLの実行順序: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

用語¶