ウィンドウ関数の基本

ウィンドウ関数とは

ウィンドウ関数（OLAP関数とも呼ばれる）はデータ分析処理を行うための機能で、以下の基本構文を持ちます：

<ウィンドウ関数> OVER (PARTITION BY <グループ化列> ORDER BY <ソート列>)

ウィンドウ関数として使用できるのは：

• 専用ウィンドウ関数（RANK, DENSE_RANK, ROW_NUMBERなど）
• 集計関数（SUM, AVG, COUNT, MAX, MINなど）

ウィンドウ関数の用途

主に以下のような分析タスクに使用されます：

• 部門ごとの業績ランキング作成
• 各部門のトップN従業員の抽出

データ準備

テーブル構造

CREATE TABLE `student_scores` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` VARCHAR(10) NOT NULL,
  `student_name` VARCHAR(10) NOT NULL,
  `subject_id` VARCHAR(10) NOT NULL,
  `subject_name` VARCHAR(20) NOT NULL,
  `score` INT NOT NULL
);

CREATE TABLE `transactions` (
  `txn_id` INT AUTO_INCREMENT PRIMARY KEY,
  `txn_number` VARCHAR(10),
  `txn_item` VARCHAR(10),
  `txn_date` VARCHAR(10)
);

主要なウィンドウ関数

順序関数

RANK関数

SELECT
  subject_id, subject_name,
  RANK() OVER(PARTITION BY subject_id ORDER BY score DESC) AS ranking,
  student_id, student_name, score
FROM student_scores;

DENSE_RANK関数

SELECT 
  subject_id, subject_name,
  DENSE_RANK() OVER(PARTITION BY subject_id ORDER BY score DESC) AS ranking,
  student_id, student_name, score 
FROM student_scores;

ROW_NUMBER関数

SELECT
  subject_id, subject_name,
  ROW_NUMBER() OVER(PARTITION BY subject_id ORDER BY score DESC) AS row_num,
  student_id, student_name, score
FROM student_scores;

分布関数

SELECT
  txn_date, percentage
FROM (
  SELECT
    txn_id, txn_number, txn_item, txn_date,
    CUME_DIST() OVER(ORDER BY txn_date ASC) AS percentage
  FROM transactions
) AS temp
GROUP BY txn_date, percentage;

オフセット関数

SELECT *, 
  LAG(txn_item, 1, "なし") OVER() AS previous_item
FROM transactions;

SELECT *, 
  LEAD(txn_item, 1, "なし") OVER() AS next_item
FROM transactions;

先頭/末尾関数

SELECT *,
  FIRST_VALUE(score) OVER(PARTITION BY subject_id ORDER BY score DESC) AS highest_score,
  (FIRST_VALUE(score) OVER(PARTITION BY subject_id ORDER BY score DESC) - score) AS score_diff
FROM student_scores;