データ分析のためのSQL

## はじめに

こんにちは！今回は、**データ分析のために活用できるSQLの機能**をまとめます。近年のデータ分析では、単なる抽出だけでなく、複雑な条件分岐や集計、行の順序に基づいた計算が必要になることが多いです。SQLでは、**ウィンドウ関数**や**WITH句**などの豊富な機能を用いて、こうした処理を柔軟かつ効率的に実装できます。

## 本文

### 1. データ処理・加工のためのSQL

分析やレポートで使いやすい形に加工するため、**欠損値の処理**や**複数テーブルの結合**などが必要です。以下の関数は、よく使われるデータ前処理の例です。

1. **CASE 関数**  
   条件分岐を行い、複数の条件に応じて値を切り替えます

   ```sql
   -- 構文
   CASE 
       WHEN <条件式1> THEN <結果1>
       WHEN <条件式2> THEN <結果2>
       ...
       ELSE <それ以外のときの結果>
   END
   ```

   ```sql
   -- 例
   SELECT 商品名,
          CASE WHEN 単価 >= 2000 THEN '高級品' 
               ELSE '標準品'
          END AS 商品区分
   FROM 商品;
   ```

2. **日付/時刻関数**  
   日付や時刻を扱うための関数で、**CURRENT_DATE**, **CURRENT_TIMESTAMP**などを用いれば、システム時刻を取得可能。ユーザ定義関数で日付の切り出しや加減算を行い、経過日数や月単位集計を作るのにも使えます

3. **COALESCE 関数**  
   **NULL**値を別の値（デフォルト値）に置き換える関数
   ```sql
   -- 例
   SELECT COALESCE(売上高, 0) AS 売上高0補完
   FROM 売上;
   ```

4. **CONCAT 関数**  
   文字列同士を連結する関数
   ```sql
   -- 例　
   SELECT CONCAT(姓, 名) AS 氏名
   FROM 顧客;
   ```

### 2. ウィンドウ関数

**ウィンドウ関数（Window Function）** は、SQL:2003以降で本格的に導入された機能で、「行に順序付け」や「ある範囲だけの集計」を簡単に表現できます。従来のSQLでは相関副問合せや複雑なJOINが必要だった操作が、ウィンドウ関数を使うとシンプルに書けるようになります。

```sql
-- 構文（例：AVG 関数を使う場合）
AVG(<列名>) OVER (
    [PARTITION BY 列名リスト]
    [ORDER BY 列名リスト]
    [フレーム句]
)
```

1. **PARTITION BY**  
   グループ化の対象列を指定。`GROUP BY`と似ていますが、行をまとめてしまうのではなく、ウィンドウ（論理的な区切り）を作った上で計算しつつ、元行をすべて表示できます

2. **ORDER BY**  
   ウィンドウ内での行の順序を定義します。並び順があることで、前行や後行との比較ができるようになります

3. **フレーム句**  
   ウィンドウ内のさらに細かい範囲（フレーム）を指定します。  

#### 2.1 ウィンドウ関数専用の関数

- **ROW_NUMBER()** : 連番を振る  
- **RANK(), DENSE_RANK()** : 順位付け（同値があった場合の処理が少し異なる）  
- **LAG(列名 [,n])** : 現在行からn行前の値を参照  
- **LEAD(列名 [,n])** : 現在行からn行後の値を参照  

```sql
-- 例
SELECT 商品名, 価格,
       ROW_NUMBER() OVER (ORDER BY 価格) AS 行番号,
       RANK()       OVER (ORDER BY 価格) AS ランク,
       DENSE_RANK() OVER(ORDER BY 価格) AS DENSEランク,
       LAG(価格)    OVER(ORDER BY 価格) AS 前の行の価格,
       LEAD(価格)   OVER(ORDER BY 価格) AS 次の行の価格,
  FROM 商品
 ORDER BY 行番号;
```

##### 2.1.1 サンプルテーブル

下記のような **商品** テーブルを用意し、**価格** 列をソートの基準にしています。

| 商品ID | 商品名 | 価格  |
|-------:|:-------|------:|
|      1 | A商品  | 100   |
|      2 | B商品  | 100   |
|      3 | C商品  | 200   |
|      4 | D商品  | 300   |
|      5 | E商品  | 300   |
|      6 | F商品  | 400   |

##### 2.1.2 実行結果

このデータで上記クエリを実行すると、概ね次のような結果が得られます（DBMSによって一部差がある場合もあります）。

| 商品名 | 価格 | 行番号 | ランク | DENSEランク | 前の行の価格 | 次の行の価格 |
|:------|-----:|------:|------:|-----------:|------------:|------------:|
| A商品  |  100 |     1 |     1 |          1 |       NULL  |         100 |
| B商品  |  100 |     2 |     1 |          1 |        100  |         200 |
| C商品  |  200 |     3 |     3 |          2 |        100  |         300 |
| D商品  |  300 |     4 |     4 |          3 |        200  |         300 |
| E商品  |  300 |     5 |     4 |          3 |        300  |         400 |
| F商品  |  400 |     6 |     6 |          4 |        300  |        NULL |

#### 2.2 既存の集約関数をウィンドウとして使う

**SUM()、COUNT()、AVG()** など、通常のGROUP BYで使う集約関数をウィンドウ関数として利用し、個々の行を保持しながら集計結果だけ追加できます。

```sql
-- 商品区分ごとに平均単価を求めつつ、個々の商品も表示
SELECT 商品名, 商品区分,
       AVG(単価) OVER (PARTITION BY 商品区分) AS 区分別平均
FROM 商品;
```

##### 2.2.1 サンプルテーブル

仮に「商品」テーブルが以下のような構成・データを持つとします。

| 商品ID | 商品名 | 商品区分   | 単価  |
|-------:|:-------|:-----------|------:|
|      1 | りんご   | フルーツ    | 100   |
|      2 | みかん   | フルーツ    | 200   |
|      3 | にんじん | 野菜       | 150   |
|      4 | レタス   | 野菜       | 120   |
|      5 | ステーキ | 肉         | 500   |
|      6 | ひき肉   | 肉         | 350   |

##### 2.2.2 実行結果

上記クエリを実行した際、概ね次のような結果が得られます（DBMSによって一部差がある場合もあります）。

| 商品名   | 商品区分  | 区分別平均 |
|:--------|:----------|----------:|
| りんご   | フルーツ   | 150.0     |
| みかん   | フルーツ   | 150.0     |
| にんじん | 野菜      | 135.0     |
| レタス   | 野菜      | 135.0     |
| ステーキ | 肉        | 425.0     |
| ひき肉   | 肉        | 425.0     |

従来のGROUP BYでは、1行にまとめられてしまう情報が、ウィンドウ関数なら「元の行＋集計結果」の形で表示可能です。

#### 2.3 フレーム句の開始点・終了点に使える句

ウィンドウ関数のフレーム句では、行単位または値単位で「どこからどこまでを計算対象にするか」を指定できます。例えば、`ROWS BETWEEN 5 PRECEDING AND CURRENT ROW` のように記述し、ウィンドウ内の「現在行の5行前」から「現在行」までを集計対象にするなどの操作が可能です。

```sql
-- 構文
[ROWS|RANGE] BETWEEN 開始点 AND 終了点
```

以下は、開始点・終了点に使える句の一覧です。

| 句                     | 説明                                                                                         |
|------------------------|----------------------------------------------------------------------------------------------|
| **CURRENT ROW**        | 現在の行だけ。ここを始点／終点にすると「現在行を含む集計」を表現できる                        |
| **n PRECEDING**        | 現在の行から **n行前**。例えば `3 PRECEDING` は「3行前」                                      |
| **n FOLLOWING**        | 現在の行から **n行後**。例えば `2 FOLLOWING` は「2行後」                                     |
| **UNBOUNDED PRECEDING**| ウィンドウの先頭（最初の行）から現在の行まで。範囲を「開始から現在行」までに限定したいとき     |
| **UNBOUNDED FOLLOWING**| 現在の行からウィンドウの末尾（最後の行）まで。範囲を「現在行から終わり」までに限定したいとき |

> **補足**  
> - `ROWS`を使うと、あくまで「行数」に注目したフレーム指定を行います
> - `RANGE`を使う場合、列値（ソートキー）の差分に注目して範囲を指定することができます


### 3. WITH句（共通テーブル式：CTE）

**WITH句**は、副問合せを使いやすくし、クエリを整理するための仕組みです。
```sql
-- 構文
WITH <テンポラリテーブル名> AS (
    SELECT ...
)
SELECT ...
```
複雑なサブクエリを何度も書く代わりに、WITH句内で仮のテーブル（CTE: Common Table Expressions）を定義し、それをメインクエリで参照します。可読性や保守性が向上し、必要に応じて**再帰的**に利用することも可能です（`WITH RECURSIVE`）。

#### 3.1 シンプルな例

```sql
-- 例
WITH TEMP (商品数) AS (
    SELECT COUNT(*) FROM 商品
)
SELECT 商品区分, COUNT(*) * 100.0 / 商品数 AS 割合
  FROM 商品 CROSS JOIN TEMP
  GROUP BY 商品区分, 商品数;
```

ここでは仮想テーブル`TEMP`を先に作っておき、そこから全商品の個数を参照して比率を算出しています。

#### 3.2 再帰的な利用

`WITH RECURSIVE`を使うと、自己参照テーブルなどを階層的にたどるクエリを書けます。例えば、ログの親子関係を辿る場合などに有用です。

```sql
-- 例
WITH RECURSIVE TEMP (ログID) AS (
    SELECT ログID FROM ログ関連 WHERE ログID = '101'
    UNION ALL
    SELECT A.ログID FROM ログ関連 A, TEMP B WHERE A.親ログID = B.ログID
)
SELECT ログID FROM TEMP;
```

## まとめ

**データ分析のためのSQL**では、単純な検索や集計だけでなく、**条件分岐（CASE）**や**欠損値処理（COALESCE）**、**文字列操作（CONCAT）** などを組み合わせて前処理を行うことが多くあります。そして、**ウィンドウ関数**を用いることで、行の順序や特定の区間を意識した集計・ランキング・差分計算などをシンプルに表現できます。

さらに、**WITH句**（CTE）を使うことで副問合せを見やすくまとめ、複雑な分析クエリも整理しやすくなります。これらの機能を上手に使いこなすと、従来のSQLでは困難だった高度なデータ分析ロジックを少ないクエリで実現できるようになります。

今後のデータ分析業務を効率化するために、ぜひウィンドウ関数やWITH句などのSQL機能を活用してみてください。