ひよっこのひよっこによるひよっこのための SQL TIPS

# はじめに
最近 SQL を触り始めた初心者が、TOPSIC SQL CONTEST を通して学んだちょっとしたテクニックを掘り下げてシェアしたいと思います。タイトルはふざけてますが、内容はいたって真面目です。

https://topsic-contest.jp/contests

::: note warn
本記事は、初心者向けですが、`ORDER BY`, `GROUP BY`, `CASE`, `JOIN`, `UNION`などをすでに知っている前提で進めています。
::: 

::: note warn
本記事は、目的の結果を得るためのロジックに注目しており、パフォーマンス的に優れた書き方か否かを全く考慮していません。ご容赦ください。
::: 

# 目次
- 任意の文字列の並び替え
- 再帰構造
- 縦持ちと横持ち
- 行間比較

# 任意の文字列の並び替え
これは、会社の偉い人順に並び替えたいときなどに使えるテクニックで、**「文字列ごとに数値を割り当て、その数値を並び替える」** という発想で実装します。具体的な実装方法のうち、最も簡単なのは`ORDER BY`句に`CASE`式を組み込むことです。次の単純なテーブルを想定してください。（`COMPANY　テーブル`とでも命名しておきます）

| KIND | DESCRIPTION |
| ---- | ---- |
| 社長 | 1番偉い |
| 課長 | 3番目に偉い |
| 部長 | 2番目に偉い |

ここで、偉い人順に並び替える SQL は次のように実現できます。

```SQL
SELECT
    *
FROM
    COMPANY
ORDER BY
    CASE KIND
        WHEN '社長' THEN 1
	    WHEN '部長' THEN 2
	    WHEN '課長' THEN 3
        ELSE 100
    END;
```

得られる結果は次の通りです。

| KIND | DESCRIPTION |
| ---- | ---- |
| 社長 | 1番偉い |
| 部長 | 2番目に偉い |
| 課長 | 3番目に偉い |


# 再帰構造
一般的には、会社における上司と部下の関係を考慮した売り上げを取得するといった階層構造で表されるデータを扱う例が多いようですが、今回遭遇したのは以下のようなパターンです。

```sql
WITH RECURSIVE SEQ_TBL(SEQ_NO) AS ( 
    SELECT
        1
    UNION ALL 
    SELECT
        SEQ_NO + 1 
    FROM
        SEQ_TBL 
    WHERE
        SEQ_NO < 6
) 
-- 結果確認
SELECT * FROM SEQ_TBL;
```

これは、`WITH RECURSIVE`句と呼ばれ、SQL で再帰を実現する構文です。

::: note info
PostgreSQL 以外では、RECURSIVE は省略可能なようですが、より汎用的な書き方にするため、今回はあえて省略していません。
:::

動作原理を理解しないことには何がなんだかさっぱりだと思うので、まずはどのように動くかを確認していきましょう。基本構文は次の通りです。

``` sql
WITH RECURSIVE テーブル名(カラム1, カラム2, ...) AS (
	非再帰項
	UNION ALL
	再帰項
)
```

以下は厳密な内部動作とは異なりますが、今回は再帰に特化した記事ではないため、**どのようなテーブルが作成されるかをイメージするのに必要な手順**に絞って確認します：

1. 非再帰項を評価し、`WITH RECURSIVE`句で定義したテーブルに追加
2. 再帰項を評価し、`WITH RECURSIVE`句で定義したテーブルに`UNION ALL`
3. 再帰項で得られる結果が空になるまで **2** を実行（空であれば追加されない）

::: note info
実際には作業テーブル、WITHテーブル、結果テーブルの 3 つのテーブルを保持することで再帰を実現しているようです。具体的なアルゴリズムに興味がある方は[このあたり](https://qiita.com/Shoyu_N/items/f1786f99545fa5053b75)が参考になると思われます。
:::

では、上記の手順を最初に示したスクリプトを用いて確認していきます。

#### 1. 非再帰項を評価し、`WITH RECURSIVE`句で定義したテーブルに追加
```sql
WITH RECURSIVE SEQ_TBL(SEQ_NO) AS ( 
    -- 非再帰項
    SELECT
        1
    --　以下は2に続く
```
この時点で、`WITH RECURSIVE`句で定義したテーブルである`SEQ_TBL テーブル`の SEQ_NO カラムに 1 が追加されます。

#### 2. 再帰項を評価し、`WITH RECURSIVE`句で定義したテーブルに`UNION ALL`
```sql
    UNION ALL
    -- 再帰項
    SELECT
        SEQ_NO + 1 
    FROM
        SEQ_TBL 
    WHERE
        SEQ_NO < 6
```

この`SELECT`文により、すでに 1 が存在している`SEQ_TBL テーブル`の SEQ_NO カラムに +1 しているため、 SEQ_NO カラムに 2 が入っているテーブルが得られます（`WHERE`句の条件も満たす）。このテーブルと`SEQ_TBL テーブル`を`UNION ALL`するため、次のテーブルを得ます。

| SEQ_NO |
| ---- |
| 1 |
| 2 |

#### 3. 再帰項で得られる結果が空になるまで **2** を実行（空であれば追加されない）
再帰項が空になるのは、SEQ_NO が 6 、すなわち SEQ_NO + 1 が 7 になるときです。しかしこの時は`SEQ_TBL テーブル`には追加されないので、最終的には、6 までが追加された次のテーブルを得ることができるとわかります。

| SEQ_NO |
| ---- |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |

# 縦持ちと横持ち
例えば、次のとある人物とその趣味についてまとめた`HOBBIES テーブル`について考えてみましょう。
このとき、横持ちとは次のようなデータの持ち方のことです。

| NAME | HOBBY1 | HOBBY2 | HOBBY3 |
| ---- | ---- | ---- | ---- |
| 山本 | サッカー | チャリ旅 | ピアノ |
| 渡辺 | 少林寺 | バイク | NULL |

渡辺くんは趣味が若干少ないですね。
これを縦持ちにすると、次のようになります。（`NULL`を除いた場合）

| NAME | HOBBY |
| ---- | ---- |
| 山本 | サッカー |
| 山本 | チャリ旅 |
| 山本 | ピアノ |
| 渡辺 | 少林寺 |
| 渡辺 | バイク |

これらを相互に変換し合います。変換のモチベーションとしては、**横持ちにすると人間にとって直感的で見やすい**ものになりますし、縦持ちにすると**データの追加、削除が行いやすく、上記の例のように`NULL`を発生させにくくなる**といった利点が挙げられます。

## 横持ち → 縦持ち
以下の2つの書き方が主流なようです：

1. `UNION (ALL)`で結合する方法
2. ID テーブルを`CROSS JOIN`する方法

ロジックが簡単なのは1の方ですが、カラム数が多くなるほど結合がしんどくなりそうなので、より汎用的に書けそうなのは2の方といった印象を勝手ながらもっています。それぞれで書いてみます。

### 1. `UNION (ALL)`で結合
行っていることは、趣味それぞれを1つずつ取得するクエリを書き、それらを縦に結合するだけです。
ロジックは単純ですが、`UNION ALL`を使用するので、カラム名を揃えておくことを忘れないようにしましょう。

```sql
SELECT
    WHO, HOBBY1 AS HOBBY
FROM
    HOBBIES
UNION ALL
SELECT
    WHO, HOBBY2 AS HOBBY
FROM
    HOBBIES
UNION ALL
SELECT
    WHO, HOBBY3 AS HOBBY
FROM
    HOBBIES;
```

### 2. `CROSS JOIN`で結合
この方法は先ほどよりも少しややこしいです。

1. 趣味の数分の ID(HOBBY_ID) を持つテーブルを作成する
2. `HOBBIES テーブル`のレコードと ID の全組み合わせで結合する（`CROSS JOIN`）
3. `CASE`式で値を持つ HOBBY に絞る

実際に、**2** で`CROSS JOIN`した後に得られるテーブルは以下のようになっているため、HOBBY_ID ごとに場合分けすることで、縦持ちになることが想像できます。

| NAME | HOBBY1 | HOBBY2 | HOBBY3 | HOBBY_ID |
| ---- | ---- | ---- | ---- | ---- |
| 山本 | サッカー | チャリ旅 | ピアノ | 1 |  
| 山本 | サッカー | チャリ旅 | ピアノ | 2 |
| 山本 | サッカー | チャリ旅 | ピアノ | 3 |
| 渡辺 | 少林寺 | バイク | NULL | 1 |
| 渡辺 | 少林寺 | バイク | NULL | 2 |
| 渡辺 | 少林寺 | バイク | NULL | 3 |

具体的な実装としては、趣味の数だけ ID を持つ、`HOBBY_NUM テーブル`を作り、`CROSS JOIN`した後に必要なカラムを場合分けしてやればいいです。ID を持つテーブルはカラムが少なければ直接作れますし、汎用性を持たせたければ、上述した再帰を用いて生成することも可能です。

```sql
-- 先ほどの再帰で作る（カラム名 HOBBY_ID は AS 句で生成）
WITH HOBBY_NUM AS (
  SELECT 1 AS HOBBY_ID
  UNION ALL
  SELECT
    HOBBY_ID + 1
  FROM
    HOBBY_NUM
  WHERE
    HOBBY_ID < 3
)

-- 以下がメイン
SELECT
   H.NAME,
   CASE HN.HOBBY_ID
        WHEN 1 THEN HOBBY1
        WHEN 2 THEN HOBBY2
        WHEN 3 THEN HOBBY3
        ELSE NULL
    END AS HOBBY
FROM
    HOBBIES AS H
CROSS JOIN
    HOBBY_NUM AS HN;
```

後は`NULL`を排除するために、各`SELECT`文に`WHERE`句をつけて排除したり、サブクエリにした後に`NULL`以外を選択するようにすればいいと思います。

## 縦持ち → 横持ち
少し複雑ですが、発想しては1レコードにまとめる起点にしたいカラム（ここでは NAME）でグループ化したあとに、趣味番号ごとに`CASE`式を用います（なければ、`ROW_NUMBER`等で生成し、結合しておけばいい）。ややこしいのは`MAX`を使う部分です。

```sql
SELECT
   NAME,
   MAX(CASE HOBBY_ID WHEN 1 THEN HOBBY ELSE NULL END) AS HOBBY1,
   MAX(CASE HOBBY_ID WHEN 2 THEN HOBBY ELSE NULL END) AS HOBBY2,
   MAX(CASE HOBBY_ID WHEN 3 THEN HOBBY ELSE NULL END) AS HOBBY3
FROM
    HOBBIES
GROUP BY
    NAME;
```

実際に、上記のコードから **`MAX`と`GROUP BY`を省いた形で実行**すると、次のようなデータが得られます。

| NAME | HOBBY1 | HOBBY2 | HOBBY3 |
| ---- | ---- | ---- | ---- |
| 山本 | サッカー | NULL | NULL |
| 山本 | NULL | チャリ旅 | NULL |
| 山本 | NULL | NULL | ピアノ |
| 渡辺 | 少林寺 | NULL | NULL |
| 渡辺 | NULL | バイク | NULL |
| 渡辺 | NULL | NULL | NULL |

つまり、`HOBBIES テーブル`における1レコードに対してすべての`CASE`が走っているので、条件不一致の部分は`NULL`になっています。この **`NULL`を消すために NAME でグループ分けしてから、`MAX`を使うという必要がある**ということです。

# 行間比較
SQL において同一レコード内のカラム同士の比較は簡単ですが、行間比較をするのは相対的に難しいです。これを実現する有名なテクニックとして、「相関サブクエリ」や「ウィンドウ関数」なるものが存在するようですが、今回は、「自己結合による行間比較」に出会ったので共有します。

例えば、次のような月ごとの収入と支出をまとめた`TOTAL_EXPENSES テーブル`があるとします。

| MONTH | KIND | AMOUNT |
| ---- | ---- | ---- |
| MAY | INCOME | 100000 |
| MAY | EXPENSE | 50000 |
| JUNE | INCOME | 30000 |
| JUNE | EXPENSE | 60000 |

ここから INCOME と EXPENSE の差がプラスになる月とその差額を取得したい場合に、自己結合によって差額を得る次の実装が考えられます。

```sql
SELECT
  TE1.MONTH,
  TE2.AMOUNT - TE1.AMOUNT AS DIFF
FROM 
  TOTAL_EXPENSES AS TE1
  INNER JOIN TOTAL_EXPENSES AS TE2
    ON TE1.MONTH = TE2.MONTH
    AND TE2.KIND = "EXPENSE"
WHERE
  DIFF > 0;
```

自己結合した後に、得られるテーブルは以下のようになります。

| MONTH | KIND | AMOUNT | MONTH | KIND | AMOUNT |
| ---- | ---- | ---- | ---- | ---- | ---- |
| MAY | INCOME | 100000 | MAY | EXPENSE | 50000 |
| MAY | EXPENSE | 50000 | MAY | EXPENSE | 50000 |
| JUNE | INCOME | 30000 | JUNE | EXPENSE | 60000 |
| JUNE | EXPENSE | 60000 | JUNE | EXPENSE | 60000 |

ここでは、片方のテーブル（`TE2`）の条件を絞ることで**比較したいレコードを複製できる**ため、列同士の比較にもっていくことができているというわけです。

したがって、最終的に次のテーブルを得ることができます。

| MONTH | DIFF |
| ---- | ---- |
| MAY | 50000 |

# おわりに
長くなってしまいましたが、お読みいただきありがとうございました。
もし間違い等ございましたら、ご指摘いただけますと幸いです。
こんな SQL を実務で書くのかは定かではないですが、実際に解いてみて思考力を養成することができていると感じています。本記事がもし誰かのお役に立てるのであれば幸いです。

# 参考
https://topsic-contest.jp/contests

https://qiita.com/ryosuketter/items/1e97eebc82fb40bb739e

https://qiita.com/Shoyu_N/items/f1786f99545fa5053b75

https://www.oracle.com/jp/technical-resources/article/complete-set-sql-of-the-oracle-database.html

https://zenn.dev/itoo/articles/sql_with_recursive

https://codezine.jp/article/detail/2679

https://www.cross-m.co.jp/column/data_marketing/dtm20220708/

https://style.potepan.com/articles/26726.html