2つの列をGROUP BYして詳細な分析を行う：SQLチュートリアル

2024-07-02

MySQLで2つの列をGROUP BYする：詳細ガイド

MySQLのGROUP BY句は、データを列に基づいてグループ化し、集計情報を取得するための強力なツールです。1つの列だけでなく、複数の列をGROUP BYすることで、より詳細な分析が可能になります。

2つの列をGROUP BYする基本構文
集計関数の使用
HAVING句で条件を指定
複数の集計関数を使用
サブクエリとGROUP BYの組み合わせ
GROUP BYによるパフォーマンスへの影響

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

説明:

SELECT: 取得したい列を指定します。
列1, 列2: GROUP BYの対象となる2つの列をカンマ区切りで記述します。
集計関数(列3): 各グループに対して実行する集計関数を指定します。カッコ内に集計対象の列を記述します。
FROM: データを取得するテーブル名を指定します。
GROUP BY: GROUP BYを行う列を指定します。

例:

社員テーブル employees において、部署と役職ごとに平均給与を算出する例：

SELECT department, job_title, AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title;

このクエリは、各部署・役職の組み合わせごとにグループ化し、平均給与を算出します。結果は次のようになります。

department	job_title	avg_salary
営業	担当者	35000
営業	マネージャー	52000
経理	主任	48000
経理	部長	65000

GROUP BYと組み合わせて使用できる代表的な集計関数は以下の通りです。

COUNT(): グループ内の行数をカウントします。
SUM(): グループ内の列の値を合計します。

これらの関数を組み合わせて、より詳細な分析を行うことができます。

各部署・役職の組み合わせごとに、社員数、給与合計、平均給与、最低給与、最高給与を算出する例：

SELECT department, job_title,
       COUNT(*) AS employee_count,
       SUM(salary) AS salary_total,
       AVG(salary) AS avg_salary,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary
FROM employees
GROUP BY department, job_title;

HAVING句を使用すると、GROUP BYの結果に対して条件を指定することができます。これは、特定の条件を満たすグループのみを抽出する場合に役立ちます。

構文:

SELECT 列1, 列2, 集計関数(列3)
FROM テーブル名
GROUP BY 列1, 列2
HAVING 条件;

各部署・役職の組み合わせのうち、平均給与が40000円以上のグループのみを抽出する例：

SELECT department, job_title, AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title
HAVING AVG(salary) >= 40000;

SELECT句で複数の集計関数を使用することができます。各グループに対して複数の集計値を取得できます。

SELECT department, job_title,
       COUNT(*) AS employee_count,
       SUM(salary) AS salary

SELECT department, job_title,
       COUNT(*) AS employee_count,
       SUM(salary) AS salary_total,
       AVG(salary) AS avg_salary,
       STDDEV(salary) AS salary_stddev
FROM employees
GROUP BY department, job_title;

employee_count: そのグループに属する社員数
salary_total: そのグループの給与の合計
avg_salary: そのグループの平均給与

結果

| department | job_title | employee_count | salary_total | avg_salary | salary_stddev |
|---|---|---|---|---|---|
| 営業 | 担当者 | 10 | 350000 | 35000 | 5000 |
| 営業 | マネージャー | 5 | 260000 | 52000 | 10000 |
| 経理 | 主任 | 7 | 336000 | 48000 | 8000 |
| 経理 | 部長 | 3 | 195000 | 65000 | 15000 |

STDDEV(salary) は、給与の標準偏差を計算する関数です。
結果表には、各グループの給与分布のばらつきを示す salary_stddev 列が追加されています。

この例は、GROUP BY と集計関数を使用して、従業員の給与データに関する詳細な分析を行う方法を示しています。

補足

より複雑な分析を行う場合は、WHERE 句や JOIN 句などの他の SQL クエリ要素と GROUP BY を組み合わせて使用することができます。
集計関数と HAVING 句を組み合わせることで、より詳細な条件に基づいて結果を抽出することができます。
GROUP BY を使用すると、クエリのパフォーマンスに影響する場合があります。適切なインデックスを作成することで、パフォーマンスを向上させることができます。

MySQLで2つの列をGROUP BYする：その他の方法

サブクエリを使用して、GROUP BY 句で使用される値を動的に生成することができます。これは、複雑な分析を行う場合に役立ちます。

SELECT job_title
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    GROUP BY department
)
HAVING AVG(salary) >= 40000;

このクエリは、まず employees テーブルに対してサブクエリを実行します。
サブクエリは、各部署の平均給与を算出します。
外部クエリは、employees テーブルに対して再度クエリを実行し、サブクエリで算出した平均給与よりも高い給与を持つ役職のみを抽出します。

CUBEおよびROLLUP関数を使用する

CUBE および ROLLUP 関数は、多次元集計を生成するために使用できます。これらの関数は、階層データの分析に役立ちます。

部署、役職、および性別ごとに社員数をカウントする例：

SELECT department, job_title, gender, COUNT(*) AS employee_count
FROM employees
GROUP BY department, job_title, gender
WITH ROLLUP;

| department | job_title | gender | employee_count |
|---|---|---|---|
| NULL | NULL | NULL | 25 |
| 営業 | NULL | NULL | 12 |
| 営業 | 担当者 | NULL | 10 |
| 営業 | 担当者 | 男性 | 8 |
| 営業 | 担当者 | 女性 | 2 |
| 営業 | マネージャー | NULL | 2 |
| 営業 | マネージャー | 男性 | 2 |
| 経理 | NULL | NULL | 13 |
| 経理 | 主任 | NULL | 7 |
| 経理 | 主任 | 男性 | 5 |
| 経理 | 主任 | 女性 | 2 |
| 経理 | 部長 | NULL | 6 |
| 経理 | 部長 | 男性 | 6 |

WITH ROLLUP 句を指定すると、GROUP BY 句で指定したすべての列の組み合わせに対して結果が生成されます。
結果表には、各レベルの集計情報が表示されます。

GROUP BY 結合を使用して、2つのテーブルのデータを列ごとにグループ化することができます。これは、複数のテーブルからデータを分析する場合に役立ちます。

employees テーブルと departments テーブルを結合し、各部署の平均給与を算出する例：

SELECT d.department_name, AVG(e.salary) AS avg_salary
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id
GROUP BY d.department_name;