はじめに
こんにちは、シルです。
SQLの学習にあたり、学んだことメモしておきます。
学習にお役立てください。
参考までに…
SQLコマンド集：基本から応用
前回までの内容です
SQL#1 環境準備と基本操作
SQL#2 テーブルとデータベースの管理
SQL#3 ユーザー管理と権限の設定
SQL#4 サーバーステータスとインデックス管理
SQL#5 データベースのサイズ管理と構造確認
SQL#6 基本的なデータ操作
SQL#7 データ検索と削除の拡張
学習の概要
本記事では、ウィンドウ関数を使ったデータ分析手法を学びます。
ウィンドウ関数は、特定の範囲や条件でデータをグループ化し、前後の値を参照しながら集計や順位付けを行う際に役立ちます。
これにより、より詳細で高度なデータ分析が可能となります。
含まれる操作
SQL操作は以下の通り
1. ROW_NUMBER() – 順位付け
2. RANK() – 順位付け（同順位あり）
3. LEAD() – 次の行の値を取得
4. LAG() – 前の行の値を取得
5. NTILE() – グループ分け
実践
1. ROW_NUMBER() – 順位付け
売上データを降順に並べ、各レコードに一意の順位を付与します。
SELECT EmployeeID, SaleAmount,
ROW_NUMBER() OVER (ORDER BY SaleAmount DESC) AS RowNum
FROM Sales;
2. RANK() – 順位付け（同順位あり）
同じ売上金額の場合は同順位にし、それ以降の順位をスキップします。
SELECT EmployeeID, SaleAmount,
RANK() OVER (ORDER BY SaleAmount DESC) AS Ranking
FROM Sales;
3. LEAD() – 次の行の値を取得
各行に対して、次の行の売上金額を表示します。
SELECT EmployeeID, SaleAmount,
LEAD(SaleAmount, 1) OVER (ORDER BY SaleAmount DESC) AS NextSale
FROM Sales;
4. LAG() – 前の行の値を取得
各行に対して、前の行の売上金額を表示します。
SELECT EmployeeID, SaleAmount,
LAG(SaleAmount, 1) OVER (ORDER BY SaleAmount DESC) AS PreviousSale
FROM Sales;
5. NTILE() – グループ分け
売上金額を一定数のグループに分割します。
SELECT EmployeeID, SaleAmount,
NTILE(4) OVER (ORDER BY SaleAmount DESC) AS Quartile
FROM Sales;
テストデータの準備
まず、売上データを格納する「Sales」テーブルを作成し、サンプルデータを挿入します。
このテーブルを基にして、ウィンドウ関数の実践を進めます。
CREATE TABLE Sales (
SaleID INT PRIMARY KEY AUTO_INCREMENT,
EmployeeID INT,
SaleAmount DECIMAL(10, 2),
SaleDate DATE
);
-- サンプルデータの挿入
INSERT INTO Sales (EmployeeID, SaleAmount, SaleDate)
VALUES
(1, 5000.00, '2024-01-15'),
(2, 3000.00, '2024-01-20'),
(3, 4000.00, '2024-02-05'),
(4, 4500.00, '2024-02-10'),
(1, 3500.00, '2024-02-15'),
(2, 2500.00, '2024-02-20'),
(3, 6000.00, '2024-03-01'),
(4, 7000.00, '2024-03-05');
課題
1. ROW_NUMBER()
各従業員の売上金額に対して、一意の順位を付けてください。
2. RANK()
同じ売上金額の従業員に対して、同順位を付けてください。
3. LEAD()
各従業員の売上金額に対して、次の売上を表示してください。
4. LAG()
各従業員の売上金額に対して、前の売上を表示してください。
5. NTILE()
売上金額を基に、4つのグループに分けてください（第1四分位～第4四分位）。
まとめ
この記事では、ウィンドウ関数を用いてデータ分析の高度なテクニックを学びました。
ROW_NUMBER()やRANK()で順位を付けたり、LEAD()やLAG()で前後の値を参照したり、NTILE()でデータをグループ分けすることで、データをより細かく分析できるようになります。
これらの手法は、特にビジネスレポートやデータのトレンド分析で活用されます。
次回は、さらに高度な集計と結合クエリを使ったデータ分析に挑戦します。
コメント