はじめに
この実験 (Lab) では、MySQL のウィンドウ関数 (Window Functions) の強力さを探求します。現在の行に関連するテーブル行のセット全体で計算を実行する方法を学びます。
まずサンプルデータセットを作成し、次に ROW_NUMBER(), SUM(), AVG(), LAG() などの関数を OVER() 句と組み合わせて高度なデータ分析を実行します。この実験では、これらの強力な関数を理解し適用するための実践的なステップバイステップの例を提供します。
データベースとテーブルのセットアップ
ウィンドウ関数を使用する前に、データベースとサンプルデータを含むテーブルが必要です。このステップでは、company という名前のデータベースと employees という名前のテーブルを作成します。
まず、デスクトップからターミナルを開きます。
root ユーザーとして MySQL サーバーに接続します。これは実験 (Lab) 環境であるため、パスワードなしで接続するには sudo を使用できます。
sudo mysql -u root
接続すると、MySQL プロンプト (mysql>) が表示されます。
次に、company データベースを作成し、それに切り替えます。IF NOT EXISTS 句は、データベースが既に存在する場合にエラーを防ぎます。
CREATE DATABASE IF NOT EXISTS company;
USE company;
次に、employees テーブルを作成します。このテーブルは、従業員 ID、名前、部署、給与を格納します。
CREATE TABLE IF NOT EXISTS employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
次に、employees テーブルにサンプルデータを挿入します。
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice', 'Sales', 60000.00),
(2, 'Bob', 'Marketing', 55000.00),
(3, 'Charlie', 'Sales', 65000.00),
(4, 'David', 'IT', 70000.00),
(5, 'Eve', 'Marketing', 62000.00)
ON DUPLICATE KEY UPDATE
employee_name = VALUES(employee_name),
department = VALUES(department),
salary = VALUES(salary);
データが正しく挿入されたことを確認するには、employees テーブルからすべての行を表示できます。
SELECT * FROM employees;
出力には、挿入した 5 つのレコードが表示されるはずです。
+-------------+---------------+------------+----------+
| employee_id | employee_name | department | salary |
+-------------+---------------+------------+----------+
| 1 | Alice | Sales | 60000.00 |
| 2 | Bob | Marketing | 55000.00 |
| 3 | Charlie | Sales | 65000.00 |
| 4 | David | IT | 70000.00 |
| 5 | Eve | Marketing | 62000.00 |
+-------------+---------------+------------+----------+
5 rows in set (0.00 sec)
データベースとテーブルの準備ができたので、次のステップに進み、ウィンドウ関数について学びます。
ROW_NUMBER() を使用した行のランク付け
ROW_NUMBER() 関数は、結果セットのパーティション内の各行に一意の連番整数を割り当てます。これは、ランキングやページネーションによく使用されます。
基本的な構文は次のとおりです。
ROW_NUMBER() OVER (ORDER BY column_name [ASC|DESC])
OVER(): この句は、関数のウィンドウ（行のセット）を定義します。ORDER BY:OVER()内のこの句は、行番号が割り当てられる順序を指定します。
MySQL シェルで続けて、ROW_NUMBER() を使用して、従業員を給与の降順でランク付けします。
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM
employees;
このクエリは salary_rank 列を追加し、従業員を給与の高い順に番号付けします。
+---------------+------------+----------+-------------+
| employee_name | department | salary | salary_rank |
+---------------+------------+----------+-------------+
| David | IT | 70000.00 | 1 |
| Charlie | Sales | 65000.00 | 2 |
| Eve | Marketing | 62000.00 | 3 |
| Alice | Sales | 60000.00 | 4 |
| Bob | Marketing | 55000.00 | 5 |
+---------------+------------+----------+-------------+
5 rows in set (0.00 sec)
ご覧のとおり、David は最も給与が高いため、ランクは 1 です。これは、ROW_NUMBER() を使用して単純なランキングを作成する方法を示しています。
SUM() を使用した累積合計の計算
累積合計（または累計和）とは、新しい数値が追加されるたびに更新される数値のシーケンスの合計です。SQL では、これを SUM() OVER() を使用して計算できます。
構文は次のとおりです。
SUM(column_name) OVER (ORDER BY column_name [ASC|DESC])
この関数は、ORDER BY 句で指定された順序で列の値を合計します。
次に、employee_id で並べ替えて、給与の累積合計を計算しましょう。
SELECT
employee_name,
salary,
SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM
employees;
結果は、各従業員の給与と、その行までの累積合計を示します。
+---------------+----------+---------------+
| employee_name | salary | running_total |
+---------------+----------+---------------+
| Alice | 60000.00 | 60000.00 |
| Bob | 55000.00 | 115000.00 |
| Charlie | 65000.00 | 180000.00 |
| David | 70000.00 | 250000.00 |
| Eve | 62000.00 | 312000.00 |
+---------------+----------+---------------+
5 rows in set (0.00 sec)
たとえば、Bob の running_total は、彼の給与と Alice の給与の合計です（60000.00 + 55000.00 = 115000.00）。これは、時間の経過に伴う売上や経費などの累積メトリックを追跡するのに役立ちます。
PARTITION BY を使用したグループ計算
PARTITION BY 句は、結果セットをパーティション（グループ）に分割し、各パーティションにウィンドウ関数を独立して適用します。これは、特定のカテゴリ内で計算を実行する場合に役立ちます。
構文は次のとおりです。
function() OVER (PARTITION BY column_name ORDER BY ...)
PARTITION BY を使用して、各部署内で給与に基づいて従業員をランク付けしましょう。
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM
employees;
このクエリは、データを department でパーティション化し、各部署の従業員を給与でランク付けします。
+---------------+------------+----------+--------------+
| employee_name | department | salary | rank_in_dept |
+---------------+------------+----------+--------------+
| David | IT | 70000.00 | 1 |
| Eve | Marketing | 62000.00 | 1 |
| Bob | Marketing | 55000.00 | 2 |
| Charlie | Sales | 65000.00 | 1 |
| Alice | Sales | 60000.00 | 2 |
+---------------+------------+----------+--------------+
5 rows in set (0.00 sec)
ランキングは部署ごとにリセットされていることに注意してください。たとえば、Eve と Charlie は両方ともランク 1 ですが、それぞれの「Marketing」および「Sales」部署内でのランクです。これにより、グローバルなランキングと比較して、より詳細な分析が可能になります。
LAG() を使用した行の比較
LAG() 関数は、現在の行よりも前の指定された物理的なオフセットにある行にアクセスを提供します。これは、現在の行の値と前の行の値とを比較する場合に役立ちます。
構文は次のとおりです。
LAG(expression, offset, default_value) OVER (ORDER BY ...)
expression: 取得する列または式。offset: 遡る行数（デフォルトは 1）。default_value: オフセットが範囲外の場合に返す値（例：最初の行）。
employee_id で並べ替えられたリストの前の従業員の給与を見つけましょう。
SELECT
employee_name,
salary,
LAG(salary) OVER (ORDER BY employee_id) AS previous_salary
FROM
employees;
このクエリは、前の行から給与を取得します。前の行がない最初の行では、NULL が返されます。
+---------------+----------+-----------------+
| employee_name | salary | previous_salary |
+---------------+----------+-----------------+
| Alice | 60000.00 | NULL |
| Bob | 55000.00 | 60000.00 |
| Charlie | 65000.00 | 55000.00 |
| David | 70000.00 | 65000.00 |
| Eve | 62000.00 | 70000.00 |
+---------------+----------+-----------------+
5 rows in set (0.00 sec)
これを使用して、連続する給与間の差を計算できます。前の給与が NULL の場合（最初の行）、結果も NULL になります。
SELECT
employee_name,
salary,
salary - LAG(salary) OVER (ORDER BY employee_id) AS salary_diff
FROM
employees;
このクエリは、現在の従業員の給与と前の従業員の給与との差を計算します。
+---------------+----------+-------------+
| employee_name | salary | salary_diff |
+---------------+----------+-------------+
| Alice | 60000.00 | NULL |
| Bob | 55000.00 | -5000.00 |
| Charlie | 65000.00 | 10000.00 |
| David | 70000.00 | 5000.00 |
| Eve | 62000.00 | -8000.00 |
+---------------+----------+-------------+
5 rows in set (0.00 sec)
これで、いくつかの主要なウィンドウ関数を練習しました。MySQL シェルを終了できます。
exit;
まとめ
この実験では、MySQL のウィンドウ関数について学習しました。ROW_NUMBER() を使用した行番号の割り当て方、SUM() OVER() を使用した累積合計の計算方法、PARTITION BY を使用した特定のグループでの計算の実行方法、および LAG() を使用した前の行からのデータへのアクセス方法を学びました。
これらの関数をサンプルデータセットに適用することで、SQL クエリ内で直接高度なデータ分析を実行する実践的な経験を得ることができました。これらは、データから複雑なレポートやインサイトを生成するための貴重なスキルです。
