はじめに
こんにちは、シルです。
SQLの学習にあたり、学んだことメモしておきます。
学習にお役立てください。
参考までに…
SQLコマンド集：基本から応用
前回までの内容です
SQL#1 環境準備と基本操作
SQL#2 テーブルとデータベースの管理
SQL#3 ユーザー管理と権限の設定
SQL#4 サーバーステータスとインデックス管理
SQL#5 データベースのサイズ管理と構造確認
SQL#6 基本的なデータ操作
SQL#7 データ検索と削除の拡張
SQL#8 ウィンドウ関数を使ったデータ分析
学習の概要
本記事では、集計関数やJOINを活用し、複数のテーブルからデータを結合し、集計・分析する方法を学びます。
JOINを使用してテーブル間の関係を明示し、GROUP BYでデータをグループ化し、集計関数でデータを効果的に集計します。
特に、INNER JOIN、LEFT JOIN、RIGHT JOINなどの使い方に焦点を当て、データの関連性を基にした分析を行います。
含まれる操作
SQL操作は以下の通り
1. GROUP BY – グループ化
2. INNER JOIN – 内部結合
3. LEFT JOIN – 左外部結合
4. RIGHT JOIN – 右外部結合
5. FULL OUTER JOIN – 全外部結合
6. CROSS JOIN – クロス結合
7. SELF JOIN – 自己結合
8. NATURAL JOIN – 自然結合
9. COUNT() – レコード数を取得
10. SUM() – 合計値を取得
11. AVG() – 平均値を取得
12. MAX() – 最大値を取得
13. MIN() – 最小値を取得
実践
1. GROUP BY – グループ化
部署ごとの従業員の数を集計します。
SELECT DepartmentID, COUNT(EmployeeID) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;
2. INNER JOIN – 内部結合
従業員とその部署情報を取得します。
SELECT e.EmployeeID, e.FirstName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
3. LEFT JOIN – 左外部結合
部署に所属していない従業員も含めてすべての従業員を取得します。
SELECT e.EmployeeID, e.FirstName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
4. RIGHT JOIN – 右外部結合
すべての部署と、各部署に所属している従業員を取得します。
SELECT e.EmployeeID, e.FirstName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
5. FULL OUTER JOIN – 全外部結合
すべての従業員とすべての部署を取得し、一致しない部分にはNULLを返します。
MySQLではFULL OUTER JOINがサポートされていないため、UNIONを使用して実現します。
SELECT e.EmployeeID, e.FirstName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
UNION
SELECT e.EmployeeID, e.FirstName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
6. CROSS JOIN – クロス結合
すべての従業員とすべての部署を組み合わせて取得します。
SELECT e.FirstName, d.DepartmentName
FROM Employees e
CROSS JOIN Departments d;
7. SELF JOIN – 自己結合
同じテーブルを2回参照し、上司と部下の関係を取得します。
SELECT e1.FirstName AS Employee, e2.FirstName AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;
8. NATURAL JOIN – 自然結合
共通の列名を基に自動的に結合します。
SELECT * FROM Employees NATURAL JOIN Departments;
9. COUNT() – レコード数を取得
部署ごとの従業員数を集計します。
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;
10. SUM() – 合計値を取得
部署ごとの給与の合計を計算します。
SELECT DepartmentID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID;
11. AVG() – 平均値を取得
部署ごとの給与の平均を計算します。
SELECT DepartmentID, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID;
12. MAX() – 最大値を取得
部署ごとの最高給与を計算します。
SELECT DepartmentID, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DepartmentID;
13. MIN() – 最小値を取得
部署ごとの最低給与を計算します。
SELECT DepartmentID, MIN(Salary) AS MinSalary
FROM Employees
GROUP BY DepartmentID;
テストデータの準備
まず、以下の2つのテーブル「Departments」と「Employees」を作成し、サンプルデータを挿入します。
このテーブルを基に、集計と結合の操作を実践します。
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DepartmentID INT,
Salary DECIMAL(10, 2),
ManagerID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
-- サンプルデータの挿入
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, 'HR'), (2, 'IT'), (3, 'Finance');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, ManagerID)
VALUES
('John', 'Doe', 1, 50000, NULL),
('Jane', 'Smith', 2, 60000, 1),
('Mike', 'Johnson', 3, 55000, 1),
('Sara', 'Williams', 1, 52000, NULL),
('James', 'Brown', 2, 61000, 2);
課題
1. GROUP BY
部署ごとの従業員数を集計してください。
2. INNER JOIN
従業員とその部署名を結合して表示してください。
3. LEFT JOIN
部署に所属していない従業員も含めて全員を表示してください。
4. CROSS JOIN
すべての従業員とすべての部署を組み合わせて表示してください。
5. SUM()
各部署の従業員の給与合計を計算してください。
まとめ
この記事では、複数のテーブルからデータを結合し、集計関数でデータを分析する方法を学びました。
JOINを使って複数テーブルを結合することで、実際の業務データを統合し、さらにGROUP BYやSUM()などの集計関数でデータを整理・分析できます。
次回は、インデックスの管理と最適化について学び、データベースのパフォーマンスを向上させる手法を学びます。
コメント