SQLでジョインテーブルとGROUP BYを用いた複雑なデータ集計の方法

SQLを使ったデータベース操作では、複数のテーブルから関連データを取得し、集計することがよくあります。特に、大規模なデータセットに対して複雑な分析を行う場合、ジョインテーブルとGROUP BYの活用が欠かせません。本記事では、これらのSQL機能を駆使したデータ集計方法について、基本的な概念から実践的なクエリ作成、さらに最適化のテクニックまでを詳しく解説します。

集計値が増えすぎるときは、JOIN後の行数とキーの一意性を先に確認します。1対多をさらに別の1対多へ結合すると行が増え、同じ金額を重複加算し得ます。GROUP BYは重複加算を自動で直しません。以下のIDはマスターで一意という前提で、同名の別部署・店舗を分けるためIDも集計キーにしています。

ジョインテーブルの基本

ジョインテーブルは、複数のテーブルを結合して関連データを取得するためのSQL機能です。以下に、代表的なジョインの種類とその使い方を紹介します。

INNER JOIN

INNER JOINは、両方のテーブルに共通するデータのみを返します。主に一致するレコードを取得するために使用されます。

SELECT A.column1, B.column2
FROM TableA A
INNER JOIN TableB B ON A.id = B.id;

LEFT JOIN

LEFT JOINは、左側のテーブルの全てのデータと、右側のテーブルに一致するデータを返します。右側に一致するデータがない場合、NULLが返されます。

SELECT A.column1, B.column2
FROM TableA A
LEFT JOIN TableB B ON A.id = B.id;

RIGHT JOIN

RIGHT JOINは、右側のテーブルの全てのデータと、左側のテーブルに一致するデータを返します。左側に一致するデータがない場合、NULLが返されます。

SELECT A.column1, B.column2
FROM TableA A
RIGHT JOIN TableB B ON A.id = B.id;

FULL JOIN

FULL JOINは、両方のテーブルの全てのデータを返し、一致しない部分はNULLで埋めます。全てのデータを含む結合を行いたい場合に使用されます。次はSQL ServerやPostgreSQL向けで、MySQL 8.4にはFULL OUTER JOIN構文がありません。後半のDATE_FORMAT例とは対象製品が違います。

SELECT A.column1, B.column2
FROM TableA A
FULL JOIN TableB B ON A.id = B.id;

GROUP BYの基本

GROUP BYは、指定した列に基づいてデータをグループ化し、集計関数を使用して各グループの結果を返すためのSQL機能です。主に、特定のカテゴリごとの集計を行いたい場合に使用されます。

GROUP BYの基本構文

GROUP BYの基本的な構文は以下の通りです。

SELECT column, AGGREGATE_FUNCTION(column)
FROM Table
GROUP BY column;

ここで、AGGREGATE_FUNCTIONはSUM、AVG、COUNT、MAX、MINなどの集計関数です。

具体例

例えば、各部門の平均給与を求めるクエリは以下のようになります。

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

このクエリでは、employeesテーブルから各部門の平均給与を計算します。

複数列でのグループ化

複数の列を使ってデータをグループ化することもできます。

SELECT department, job_title, COUNT(*)
FROM employees
GROUP BY department, job_title;

このクエリでは、部門と職位ごとに従業員の数をカウントします。

複雑なデータ集計の例

ジョインテーブルとGROUP BYを組み合わせることで、複数のテーブルからのデータを集約し、より複雑な集計を行うことができます。ここでは、社員とその部署に関するデータを集計する具体例を紹介します。

複数テーブルのデータ集計

例えば、各部署の社員数と平均給与を集計する場合、以下のようなクエリを使用します。

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

このクエリでは、departmentsテーブルとemployeesテーブルをINNER JOINで結合し、各部署ごとの社員数と平均給与を集計しています。

複数の集計関数を使用

さらに、複数の集計関数を組み合わせて、様々な統計情報を取得することも可能です。

SELECT d.department_id, d.department_name, 
       COUNT(e.employee_id) AS num_employees, 
       AVG(e.salary) AS avg_salary, 
       MAX(e.salary) AS max_salary, 
       MIN(e.salary) AS min_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

このクエリでは、各部署ごとの社員数、平均給与、最高給与、最低給与を一度に集計しています。

条件付き集計

集計を行う前に条件を指定してデータをフィルタリングすることもできます。

SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
WHERE e.salary > 50000
GROUP BY d.department_id, d.department_name;

このクエリでは、給与が50,000を超えるの社員のみを対象に、各部署ごとの社員数と平均給与を集計しています。

このように、ジョインテーブルとGROUP BYを組み合わせることで、複数のテーブルからデータを取得し、様々な視点からデータを分析することが可能です。

実践的なクエリの作成

ここでは、実際のビジネスシナリオに基づいた複雑なクエリの作成方法を紹介します。例えば、売上データベースから各店舗ごとの月別売上を集計する場合を考えてみましょう。

シナリオ: 各店舗の月別売上集計

このシナリオでは、以下のようなデータが含まれるテーブルを使用します：

• stores テーブル：各店舗の情報を含む
• orders テーブル：注文情報を含む

以下のDATE_FORMATを使う月別例はMySQL向けです。同名の別店舗をまとめないよう店舗IDも集計キーに含めます。各マスターIDが一意で、注文は1行1件という前提です。

SELECT s.store_id, s.store_name, 
       DATE_FORMAT(o.order_date, '%Y-%m') AS month, 
       SUM(o.amount) AS total_sales
FROM stores s
INNER JOIN orders o ON s.store_id = o.store_id
GROUP BY s.store_id, s.store_name, month
ORDER BY s.store_name, s.store_id, month;

このクエリのポイントは以下の通りです：

• INNER JOIN を使用して stores テーブルと orders テーブルを結合
• DATE_FORMAT 関数を使用して order_date を月単位にフォーマット
• SUM 関数を使用して各月の総売上を計算
• GROUP BY で店舗ID・店舗名と年月ごとにデータをグループ化
• ORDER BY で結果を店舗名と月で並べ替え

シナリオ: 商品カテゴリごとの月別売上集計

下のp.priceは現在の商品マスター単価です。過去の実売上を再現したい場合、販売時単価を注文明細に保存しているならその列を使います。値引き・税・返品の扱いも業務定義に合わせます。現在単価を使った計算を確定済み過去売上と同一視しないでください。

次に、商品カテゴリごとの月別売上を集計するシナリオを考えます。ここでは、以下のテーブルを使用します：

• products テーブル：各商品の情報を含む
• categories テーブル：各商品のカテゴリ情報を含む
• order_items テーブル：注文の詳細を含む

クエリは次のようになります：

SELECT c.category_id, c.category_name, 
       DATE_FORMAT(o.order_date, '%Y-%m') AS month, 
       SUM(oi.quantity * p.price) AS total_sales
FROM categories c
INNER JOIN products p ON c.category_id = p.category_id
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
GROUP BY c.category_id, c.category_name, month
ORDER BY c.category_name, c.category_id, month;

このクエリのポイントは以下の通りです：

• 複数の INNER JOIN を使用してカテゴリ、商品、注文、注文詳細の各テーブルを結合
• 各商品の売上を quantity * price で計算し、 SUM 関数で集計
• GROUP BY でカテゴリID・カテゴリ名と年月ごとにデータをグループ化
• ORDER BY で結果をカテゴリ名と月で並べ替え

これらの例からわかるように、実際のビジネスシナリオに基づいたクエリを作成する際には、テーブル間の関係を理解し、適切なジョインと集計関数を組み合わせることが重要です。

最適化のテクニック

複雑なクエリのパフォーマンスを向上させるためには、適切な最適化のテクニックを使用することが重要です。ここでは、クエリの実行速度を改善するためのいくつかの方法を紹介します。

インデックスの使用

インデックスを適切に使用することで、データベースの検索速度を大幅に向上させることができます。ジョインや検索条件に使用する列にインデックスを作成します。

CREATE INDEX idx_department_id ON employees(department_id);
CREATE INDEX idx_order_date ON orders(order_date);

この例では、department_idとorder_date列にインデックスを作成しています。索引が採用されるか、速くなるかは条件や件数に依存します。既存索引と重複しないか、作成時のロック・容量・更新コストを確認してから追加します。

サブクエリの活用

派生表は集計単位を分けたり読みやすくするために使えます。ただし、下のように同じ集計を外側でSELECTするだけでは高速化は保証されません。前後の実行計画と結果を比較します。

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

この例では、サブクエリで部署ごとの社員数と平均給与を計算し、その結果をメインクエリで利用しています。

結合順序の最適化

SQLの記述順がそのまま実行時の結合順になるとは限りません。オプティマイザーは統計情報や索引、条件を基に計画を選びます。小さい表から書くだけで速くなると考えず、MySQLでは次のEXPLAINで計画を確認します。

EXPLAIN SELECT s.store_id, s.store_name, DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(o.amount) AS total_sales
FROM stores s
INNER JOIN orders o ON s.store_id = o.store_id
GROUP BY s.store_id, s.store_name, month
ORDER BY s.store_name, s.store_id, month;

EXPLAINを使用してクエリプランを確認し、データベースエンジンが最適な結合順序を使用しているかをチェックします。

適切な集計関数の使用

集計関数を適切に使用することで、クエリのパフォーマンスを向上させることができます。例えば、冗長な集計を避けるために、必要最低限の集計関数のみを使用します。

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

この例では、COUNTとAVGの集計関数を使用して必要な情報だけを取得しています。

ビューの利用

ビューを利用することで、複雑なクエリを簡素化し、再利用可能なクエリを作成できます。ビューを定義しておくと、クエリの可読性も向上します。

CREATE VIEW department_summary AS
SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

この例では、部署ごとの社員数と平均給与を計算するビューを作成しています。ビューを使用すると、後で簡単に集計結果を参照できます。

まとめ

ジョインテーブルとGROUP BYを組み合わせたデータ集計は、複雑なデータ分析に非常に有用です。適切なジョインの種類とGROUP BYの使用方法を理解し、クエリの最適化技術を駆使することで、効率的かつ効果的なデータ集計が可能になります。これらのテクニックを活用して、データベースのパフォーマンスを最大限に引き出しましょう。

公式資料：MySQL JOIN構文、EXPLAINによる確認。CREATE INDEXやCREATE VIEWはDBの定義を変更するため、検証環境と権限・運用ルールを確認して実施します。