アドホックな分析に使えるウィンドウ関数と活用例をまとめてみた

## **はじめに**

SQLのウィンドウ関数は、データ分析において非常に強力なツールです。今回は、ウィンドウ関数の全体像を整理した上で、実際に使われるケースを紹介します。



## **1. ウィンドウ関数の全体像**

以下の表に、ウィンドウ関数の代表例をまとめました。これを参考にして、各関数の用途をイメージしてください。

#### **ウィンドウ関数の分類**

| **カテゴリ**       | **関数名**          | **説明**                                                                                     | **使用例**                                                      |
|--------------------|---------------------|----------------------------------------------------------------------------------------------|----------------------------------------------------------------|
| **順位付け系**      | `ROW_NUMBER()`      | 各行に連続した番号を付与。同じ値でも一意の番号を振る。                                         | ユニークな行を特定、または行番号を付与する。                    |
|                    | `RANK()`           | 同じ値の場合は同順位とし、次の順位をスキップする。                                            | 順位付けリストやトップNリストで、順位がスキップされる場合。      |
|                    | `DENSE_RANK()`      | 同じ値の場合は同順位とするが、次の順位をスキップしない。                                       | 順位付けリストで連続したランキングが必要な場合。                |
|                    | `NTILE(n)`          | データを n 個のグループに分割し、それぞれのグループ番号を付ける。                              | 売上データをパフォーマンスに応じて上位10%、20%に分けるなど。     |
| **集計系**         | `SUM()`             | ウィンドウ内での合計を計算。                                                                  | 部署ごとの給与合計、店舗ごとの売上合計。                        |
|                    | `AVG()`             | ウィンドウ内での平均を計算。                                                                  | 部署ごとの平均給与、商品の平均売上。                            |
|                    | `COUNT()`           | ウィンドウ内の行数をカウント。                                                                | 商品数や注文数のカウント。                                      |
|                    | `MAX()` / `MIN()`   | ウィンドウ内の最大値/最小値を取得。                                                          | 部署内の最高給与、最安商品の価格。                              |
| **行間比較系**      | `LAG()`             | 前の行の値を取得。                                                                           | 時系列データで、前回値との差分を計算。                          |
|                    | `LEAD()`            | 次の行の値を取得。                                                                           | 時系列データで、次回の予測値や比較に使用。                      |
|                    | `FIRST_VALUE()`     | ウィンドウ内で最初の値を取得。                                                               | 最初に記録された売上データや初期値を取得。                      |
|                    | `LAST_VALUE()`      | ウィンドウ内で最後の値を取得。                                                               | 最後に記録された売上データや終値を取得。                        |
|                    | `NTH_VALUE(n)`      | ウィンドウ内の n 番目の値を取得。                                                            | ウィンドウ内で特定の順位のデータを取得。                        |

## **2. 今回のテーマ**

今回は、ウィンドウ関数の中でも以下に焦点を当てます：
1. **順位付け系の関数**：`ROW_NUMBER`、`RANK`、`DENSE_RANK`
2. **行間比較系の関数**：`LAG`、`LEAD`、`FIRST_VALUE`

これらは、順位付けや時系列データ分析の場面で頻繁に使われ、データのトレンドやパフォーマンス評価を行う際に役立ちます。

## **3. サンプルテーブルの作成**

以下のSQLを使ってサンプルテーブルを作成し、データ分析を試してみましょう。

```sql
-- サンプルテーブル作成
CREATE TABLE employees (
    employee_id INT,
    department VARCHAR(50),
    employee_name VARCHAR(50),
    salary INT
);

CREATE TABLE sales (
    sale_id INT,
    store_id INT,
    product VARCHAR(50),
    revenue INT
);

-- サンプルデータ挿入
INSERT INTO employees VALUES
(1, 'Sales', 'Alice', 7000),
(2, 'Sales', 'Bob', 6000),
(3, 'Sales', 'Charlie', 6000),
(4, 'HR', 'Carol', 8000),
(5, 'HR', 'Dave', 7000),
(6, 'HR', 'Eve', 7000);

INSERT INTO sales VALUES
(1, 1, 'A', 200),
(2, 1, 'B', 150),
(3, 1, 'C', 100),
(4, 2, 'D', 300),
(5, 2, 'E', 200),
(6, 2, 'F', 150);
```

## **4. 順位付け系関数のユースケース**

#### **例1: 部署ごとの給与ランキング（`RANK`）**
部署ごとに社員を給与の高い順に並べ、順位を付けます。

```sql
SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;
```

| department | employee_name | salary | salary_rank |
|------------|---------------|--------|-------------|
| Sales      | Alice         | 7000   | 1           |
| Sales      | Bob           | 6000   | 2           |
| Sales      | Charlie       | 6000   | 2           |
| HR         | Carol         | 8000   | 1           |
| HR         | Dave          | 7000   | 2           |
| HR         | Eve           | 7000   | 2           |

## **5. 行間比較系関数のユースケース**

#### **例2: 売上データの差分計算（`LAG`）**
店舗ごとの売上データで、前回の売上との差を計算します。

```sql
SELECT 
    store_id,
    product,
    revenue,
    LAG(revenue) OVER (PARTITION BY store_id ORDER BY sale_id) AS previous_revenue,
    revenue - LAG(revenue) OVER (PARTITION BY store_id ORDER BY sale_id) AS revenue_diff
FROM sales;
```

| store_id | product | revenue | previous_revenue | revenue_diff |
|----------|---------|---------|------------------|--------------|
| 1        | A       | 200     | NULL             | NULL         |
| 1        | B       | 150     | 200              | -50          |
| 1        | C       | 100     | 150              | -50          |
| 2        | D       | 300     | NULL             | NULL         |
| 2        | E       | 200     | 300              | -100         |
| 2        | F       | 150     | 200              | -50          |

## **6. 時系列データでの LAG と LEAD の活用**

`LAG` と `LEAD` は、時系列データの分析に非常に役立つウィンドウ関数です。過去または未来の行を参照してデータ間の関係やトレンドを分析できます。


### **時系列データの例**

以下のテーブルは、各店舗の売上データとその時間を記録したものです。

| sale_id | store_id | product | revenue | sale_time           |
|---------|----------|---------|---------|---------------------|
| 1       | 1        | A       | 200     | 2024-11-01 10:00:00 |
| 2       | 1        | B       | 150     | 2024-11-01 11:00:00 |
| 3       | 1        | C       | 100     | 2024-11-01 12:00:00 |
| 4       | 2        | D       | 300     | 2024-11-01 10:00:00 |
| 5       | 2        | E       | 200     | 2024-11-01 11:00:00 |
| 6       | 2        | F       | 150     | 2024-11-01 12:00:00 |

### **1. LAG: 前回の売上との差を計算**

`LAG` を使い、前回の売上とその時間差を計算します。

```sql
SELECT 
    store_id,
    product,
    revenue,
    sale_time,
    LAG(revenue) OVER (PARTITION BY store_id ORDER BY sale_time) AS previous_revenue,
    TIMESTAMPDIFF(MINUTE, LAG(sale_time) OVER (PARTITION BY store_id ORDER BY sale_time), sale_time) AS time_diff_minutes
FROM sales_time;
```

#### **結果:**
| store_id | product | revenue | sale_time           | previous_revenue | time_diff_minutes |
|----------|---------|---------|---------------------|------------------|-------------------|
| 1        | A       | 200     | 2024-11-01 10:00:00 | NULL             | NULL              |
| 1        | B       | 150     | 2024-11-01 11:00:00 | 200              | 60                |
| 1        | C       | 100     | 2024-11-01 12:00:00 | 150              | 60                |
| 2        | D       | 300     | 2024-11-01 10:00:00 | NULL             | NULL              |
| 2        | E       | 200     | 2024-11-01 11:00:00 | 300              | 60                |
| 2        | F       | 150     | 2024-11-01 12:00:00 | 200              | 60                |

- **`LAG(revenue)`**: 前回の売上を取得。
- **`TIMESTAMPDIFF`**: 前回の売上から現在までの経過時間（分単位）を計算。

### **2. LEAD: 次回の売上と経過時間の予測**

`LEAD` を使い、次回の売上との差や次回イベントまでの時間を予測します。

```sql
SELECT 
    store_id,
    product,
    revenue,
    sale_time,
    LEAD(revenue) OVER (PARTITION BY store_id ORDER BY sale_time) AS next_revenue,
    LEAD(revenue) OVER (PARTITION BY store_id ORDER BY sale_time) - revenue AS revenue_diff_next,
    LEAD(sale_time) OVER (PARTITION BY store_id ORDER BY sale_time) AS next_sale_time,
    TIMESTAMPDIFF(MINUTE, sale_time, LEAD(sale_time) OVER (PARTITION BY store_id ORDER BY sale_time)) AS time_until_next_sale
FROM sales_time;
```

#### **結果:**
| store_id | product | revenue | sale_time           | next_revenue | revenue_diff_next | next_sale_time      | time_until_next_sale |
|----------|---------|---------|---------------------|--------------|-------------------|---------------------|----------------------|
| 1        | A       | 200     | 2024-11-01 10:00:00 | 150          | -50               | 2024-11-01 11:00:00 | 60                   |
| 1        | B       | 150     | 2024-11-01 11:00:00 | 100          | -50               | 2024-11-01 12:00:00 | 60                   |
| 1        | C       | 100     | 2024-11-01 12:00:00 | NULL         | NULL              | NULL                | NULL                 |
| 2        | D       | 300     | 2024-11-01 10:00:00 | 200          | -100              | 2024-11-01 11:00:00 | 60                   |
| 2        | E       | 200     | 2024-11-01 11:00:00 | 150          | -50               | 2024-11-01 12:00:00 | 60                   |
| 2        | F       | 150     | 2024-11-01 12:00:00 | NULL         | NULL              | NULL                | NULL                 |

- **`LEAD(revenue)`**: 次回の売上を取得。
- **`revenue_diff_next`**: 次回の売上との差分を計算。
- **`time_until_next_sale`**: 次回のイベント時間までの経過時間を予測。

### **時系列分析における活用例**

1. **トレンド分析**:
   - `LAG` を使って、過去の売上推移を確認し、上昇傾向や下降傾向を把握。
2. **次回予測**:
   - `LEAD` を活用し、次回の売上やイベント時間を予測。
3. **移動平均や季節性の分析**:
   - `LAG` と集計関数（`AVG`, `SUM` など）を組み合わせて、移動平均を計算。

これらのテクニックを組み合わせることで、時系列データの変化を深く理解し、ビジネス上の意思決定に役立てることができます！
## **今後の活用**

これらのウィンドウ関数を理解し、実践的に使えるようになることで、データ分析やパフォーマンス評価の精度を大幅に向上させることができます。

今回は、順位付け系と行間比較系に焦点を当てましたが、次回は「集計系関数」や「ウィンドウ指定オプション（`ROWS`, `RANGE`）」を深掘りする予定です。ぜひ試してみてください！