つ 息抜きにSQLクイズでも (4)

# はじめに
仕事中の息抜きや、暇つぶし用にちょっとしたSQLクイズをどうぞ。

# 設定
あなたは、イベント管理サービスを運用する会社に勤めるDBエンジニアです。
同僚のアプリケーションエンジニアから、「管理者向けにイベントの一覧、登壇者の一覧、参加者の一覧をまとめてレポート出力したい。データを一撃で取得できるSQLを頼むわー」と依頼されました。

# データ構造の説明
## eventsテーブル
イベントを管理するテーブルです。以下の列があります。

* event_no イベント番号 PK
* event_name イベント名
* held_on 開催日

| event_no | event_name    | held_on    |
| -------- | ------------- | ---------- |
| 1        | RDBMS テックトーク  | 2021-05-05 |
| 2        | SQLアンチパターン読書会 | 2021-05-25 |
| 3        | NoSQLの現在      | 2021-05-28 |
| 4        | DBエンジニア LT大会  | 2021-05-30 |

## speakersテーブル
登壇者を管理するテーブルです。以下の列があります。

* event_no イベント番号 PK/FK
* order_of_speech 講演順 PK
* speaker_name 講演者名
* speech_title 講演タイトル

| event_no | order_of_speech | speaker_name | speech_title            |
| -------- | --------------- | ------------ | ----------------------- |
| 1        | 1               | 平野謙          | Oracle徹底解剖              |
| 1        | 2               | 鈴木康友         | PostgreSQLのパフォーマンステクニック |
| 2        | 1               | 宇野勝          | SQLアンチパターン第1章           |

## attendeesテーブル
参加者を管理するテーブルです。以下の列があります。

* event_no イベント番号 PK/FK
* order_of_application 申込順 PK
* account_name アカウント名

| event_no | order_of_application | account_name |
| -------- | -------------------- | ------------ |
| 1        | 1                    | alice        |
| 1        | 2                    | bob          |
| 1        | 3                    | chris        |
| 3        | 1                    | david        |

# 問題
```
レポートに出力する形式でデータを一撃で取得するSQLを書いてください。
```

具体的には以下のデータを出力してください。


| event_no | event_name    | held_on    | order_of_speech | speaker_name | speech_title            | order_of_application | account_name |
| -------- | ------------- | ---------- | --------------- | ------------ | ----------------------- | -------------------- | ------------ |
| 1        | RDBMS テックトーク  | 2021-05-05 | 1               | 平野謙          | Oracle徹底解剖              |                      |              |
| 1        | RDBMS テックトーク  | 2021-05-05 | 2               | 鈴木康友         | PostgreSQLのパフォーマンステクニック |                      |              |
| 1        | RDBMS テックトーク  | 2021-05-05 |                 |              |                         | 1                    | alice        |
| 1        | RDBMS テックトーク  | 2021-05-05 |                 |              |                         | 2                    | bob          |
| 1        | RDBMS テックトーク  | 2021-05-05 |                 |              |                         | 3                    | chris        |
| 2        | SQLアンチパターン読書会 | 2021-05-25 | 1               | 宇野勝          | SQLアンチパターン第1章           |                      |              |
| 3        | NoSQLの現在      | 2021-05-28 |                 |              |                         | 1                    | david        |
| 4        | DBエンジニア LT大会  | 2021-05-30 |                 |              |                         |                      |              |

詳細な要件は以下のとおりです。

* イベント番号順に昇順ソートする
* 同一イベント番号内では、登壇者、参加者の順にソートする
* 登壇者、参加者はそれぞれ講演順、申込順で昇順ソートする
* あるイベントに対して、（登壇者数＋参加者数）分の明細行を出力する。その際、イベントテーブルの情報は同一内容を繰り返し出力する
* 登壇者、参加者ともに0件のイベントであっても、1行明細行を出力する

例えば、イベント番号=1のイベントは、登壇者が2名、参加者が3名存在するので、5件の明細行を出力します。
イベント番号=4のイベントは、登壇者も参加者も0名ですが、明細行は1件出力します。

## 制約条件
* できるだけ標準SQLベースで書いてください

## 解決のポイント
* イベントテーブルを駆動表とし単純に登壇者テーブル、参加者テーブルと外部結合していくと掛け算により明細行数が増えてしまいます。どうしたらよいでしょうか？
* 登壇者、参加者ともに0件のイベントでも1行明細行を出力する仕様も鍵となりそうです。

# DDL/DML
ローカル環境や[DB Fiddle](https://www.db-fiddle.com/)などの環境で以下のSQLを流してください。
（動作確認は、DB Fiddle上のPostgreSQL 12で行いました）

> DB Fiddleで試す場合、左側のSchema SQLというペインに下記のSQLをコピー&ペーストし、右側のQuery SQLというペインにクエリを記述します。画面上部ナビバーのRunをクリックすると結果が表示されます。

```sql:DDL&DML
CREATE TABLE events (
    event_no integer,
    event_name varchar(50),
    held_on varchar(10),
    PRIMARY KEY (event_no)
);
CREATE TABLE speakers (
  event_no integer,
  order_of_speech integer,
  speaker_name varchar(50),
  speech_title varchar(50),
  PRIMARY KEY (event_no, order_of_speech)
);
CREATE TABLE attendees (
  event_no integer,
  order_of_application integer,
  account_name varchar(50),
  PRIMARY KEY (event_no, order_of_application)
);
INSERT INTO events VALUES (1, 'RDBMS テックトーク', '2021-05-05');
INSERT INTO events VALUES (2, 'SQLアンチパターン読書会', '2021-05-25');
INSERT INTO events VALUES (3, 'NoSQLの現在', '2021-05-28');
INSERT INTO events VALUES (4, 'DBエンジニア LT大会', '2021-05-30');
INSERT INTO speakers VALUES (1, 1, '平野謙', 'Oracle徹底解剖');
INSERT INTO speakers VALUES (1, 2, '鈴木康友', 'PostgreSQLのパフォーマンステクニック');
INSERT INTO attendees VALUES (1, 1, 'alice');
INSERT INTO attendees VALUES (1, 2, 'bob');
INSERT INTO attendees VALUES (1, 3, 'chris');
INSERT INTO speakers VALUES (2, 1, '宇野勝', 'SQLアンチパターン第1章');
INSERT INTO attendees VALUES (3, 1, 'david');
```

# 解答例
SQLの実現方法は色々あると思いますが、以下に一例を示します。

```sql:解答例
SELECT
  event_no,
  event_name,
  held_on,
  order_of_speech,
  speaker_name,
  speech_title,
  order_of_application,
  account_name
FROM
  (
    SELECT
      *,
      row_number() over(
        PARTITION by event_no
        ORDER BY
          (
            detail_type,
            order_of_speech,
            order_of_application
          )
      ) AS rownum
    FROM
      (
        SELECT
          e.*,
          s.order_of_speech,
          s.speaker_name,
          s.speech_title,
          a.order_of_application,
          a.account_name,
          case
            WHEN s.order_of_speech IS NOT NULL THEN 1
            WHEN a.order_of_application IS NOT NULL THEN 2
            ELSE 3
          end AS detail_type
        FROM
          (
            SELECT
              events.*,
              seq.*
            FROM
              events
              CROSS JOIN (
                SELECT
                  1 AS seq_no
                UNION
                SELECT
                  2 AS seq_no
              ) AS seq
          ) AS e
          LEFT JOIN speakers s ON e.seq_no = 1
          AND e.event_no = s.event_no
          LEFT JOIN attendees a ON e.seq_no = 2
          AND e.event_no = a.event_no
      ) records_inner
  ) records_outer
WHERE
  detail_type != 3
  OR rownum = 1
ORDER BY
  event_no,
  rownum
```

以下、上記SQLを解説します。少しSQLが長くトリッキーな部分もあるので、少しずつ解説します。

## 登壇者と参加者を別々に結合する
イベントテーブルを駆動表として、登壇者テーブルと参加者テーブルを順に結合すると、それぞれの行数の掛け算で結果の明細行が増えてしまいます。

例えばイベント番号=1のイベントを考えると、登壇者が2名、参加者が3名ですから、以下のように結果は6行となります。

```sql:順番に結合（不採用）
SELECT
  e.event_no,
  e.event_name,
  e.held_on,
  s.order_of_speech,
  s.speaker_name,
  s.speech_title,
  a.order_of_application,
  a.account_name
FROM
  EVENTS e
  LEFT JOIN speakers s ON e.event_no = s.event_no
  LEFT JOIN attendees a ON e.event_no = a.event_no
WHERE
  e.event_no = 1;
```

| event_no | event_name   | held_on    | order_of_speech | speaker_name | speech_title            | order_of_application | account_name |
| -------- | ------------ | ---------- | --------------- | ------------ | ----------------------- | -------------------- | ------------ |
| 1        | RDBMS テックトーク | 2021-05-05 | 1               | 平野謙          | Oracle徹底解剖              | 1                    | alice        |
| 1        | RDBMS テックトーク | 2021-05-05 | 1               | 平野謙          | Oracle徹底解剖              | 2                    | bob          |
| 1        | RDBMS テックトーク | 2021-05-05 | 1               | 平野謙          | Oracle徹底解剖              | 3                    | chris        |
| 1        | RDBMS テックトーク | 2021-05-05 | 2               | 鈴木康友         | PostgreSQLのパフォーマンステクニック | 1                    | alice        |
| 1        | RDBMS テックトーク | 2021-05-05 | 2               | 鈴木康友         | PostgreSQLのパフォーマンステクニック | 2                    | bob          |
| 1        | RDBMS テックトーク | 2021-05-05 | 2               | 鈴木康友         | PostgreSQLのパフォーマンステクニック | 3                    | chris        |

イベントテーブルに登壇者テーブルを結合した結果（2行）と、イベントテーブルに参加者テーブルを結合した結果（3行）を足し合わせた5行を結果として得るにはどうしたらよいでしょうか。
UNIONにより実現できそうですが、ここでは発想を変えてみます。

## CROSS JOINによる解決
CROSS JOIN（クロス結合、交差結合、直積などと呼ばれます）は2つのテーブルの全ての組み合わせを作成する結合方法です。
解答例のSQLの最内の副問合せでは、CROSS JOINを利用しています。

```sql
            SELECT
              events.*,
              seq.*
            FROM
              events
              CROSS JOIN (
                SELECT
                  1 AS seq_no
                UNION
                SELECT
                  2 AS seq_no
              ) AS seq
```

この部分の実行結果は以下となります。

| event_no | event_name    | held_on    | seq_no |
| -------- | ------------- | ---------- | ------ |
| 1        | RDBMS テックトーク  | 2021-05-05 | 1      |
| 1        | RDBMS テックトーク  | 2021-05-05 | 2      |
| 2        | SQLアンチパターン読書会 | 2021-05-25 | 1      |
| 2        | SQLアンチパターン読書会 | 2021-05-25 | 2      |
| 3        | NoSQLの現在      | 2021-05-28 | 1      |
| 3        | NoSQLの現在      | 2021-05-28 | 2      |
| 4        | DBエンジニア LT大会  | 2021-05-30 | 1      |
| 4        | DBエンジニア LT大会  | 2021-05-30 | 2      |

1, 2という連番をレコードに持つ副問合せを`events`テーブルにCROSS JOINすることで、`events`の各行を2行に**増幅**させました。
この後、`seq_no`が1の行に対しては`speakers`テーブルを、`seq_no`が2の行に対しては`atendees`テーブルをそれぞれ外部結合すると、掛け算ではなく足し算で結果を得ることができます。

それがSQLの以下の部分になります。（結果を見やすくするため `WHERE e.event_no = 1` という条件を追加しています）

```sql
    SELECT
              e.*,
              s.order_of_speech,
              s.speaker_name,
              s.speech_title,
              a.order_of_application,
              a.account_name,
              case
                WHEN s.order_of_speech IS NOT NULL THEN 1
                WHEN a.order_of_application IS NOT NULL THEN 2
                ELSE 3
              end AS detail_type
            FROM
              (
                SELECT
                  EVENTS.*,
                  seq.*
                FROM
                  EVENTS
                  CROSS JOIN (
                    SELECT
                      1 AS seq_no
                    UNION
                    SELECT
                      2 AS seq_no
                  ) AS seq
              ) AS e
              LEFT JOIN speakers s ON e.seq_no = 1
              AND e.event_no = s.event_no
              LEFT JOIN attendees a ON e.seq_no = 2
              AND e.event_no = a.event_no
              WHERE e.event_no = 1; -- 注）この行は元のSQLにはない
```

| event_no | event_name   | held_on    | seq_no | order_of_speech | speaker_name | speech_title            | order_of_application | account_name | detail_type |
| -------- | ------------ | ---------- | ------ | --------------- | ------------ | ----------------------- | -------------------- | ------------ | ----------- |
| 1        | RDBMS テックトーク | 2021-05-05 | 1      | 1               | 平野謙          | Oracle徹底解剖              |                      |              | 1           |
| 1        | RDBMS テックトーク | 2021-05-05 | 1      | 2               | 鈴木康友         | PostgreSQLのパフォーマンステクニック |                      |              | 1           |
| 1        | RDBMS テックトーク | 2021-05-05 | 2      |                 |              |                         | 1                    | alice        | 2           |
| 1        | RDBMS テックトーク | 2021-05-05 | 2      |                 |              |                         | 2                    | bob          | 2           |
| 1        | RDBMS テックトーク | 2021-05-05 | 2      |                 |              |                         | 3                    | chris        | 2           |

少し分かりづらいので、図示すると以下のイメージです。

![join_image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176589/fc031340-1c6a-5ab4-30f2-c15e8b1d0bdc.png)

※先ほどの副問合せの、以下のCASE式について

```sql
              case
                WHEN s.order_of_speech IS NOT NULL THEN 1
                WHEN a.order_of_application IS NOT NULL THEN 2
                ELSE 3
              end AS detail_typesql
```

これは、以下の仕様を実現するため、同一イベント内でのソート用に算出しています。

> * 同一イベント番号内では、登壇者、参加者の順にソートする

これについては後ほど改めて解説します。

## ウィンドウ関数を使ったソートと結果の絞り込み

先ほど解説した副問合せの外側を確認しましょう。見やすくするため、副問合せの部分をコメントに置き換えました。

```sql
SELECT
  event_no,
  event_name,
  held_on,
  order_of_speech,
  speaker_name,
  speech_title,
  order_of_application,
  account_name
FROM
  (
    SELECT
      *,
      row_number() over(
        PARTITION by event_no
        ORDER BY
          (
            detail_type,
            order_of_speech,
            order_of_application
          )
      ) AS rownum
    FROM
      ( /* 副問合せ */) records_inner
  ) records_outer
WHERE
  detail_type != 3
  OR rownum = 1
ORDER BY
  event_no,
  rownum
```

ここで考慮すべきは、ソート順に関する各仕様と、以下の仕様です。

> * 登壇者、参加者ともに0件のイベントであっても、1行明細行を出力する

まずはソートですが、イベント番号を第1ソートキーとし、さらに同一イベント番号内で仕様に従ったソートを行うため、ウィンドウ関数を使用しています。

```sql
      row_number() over(
        PARTITION by event_no
        ORDER BY
          (
            detail_type,
            order_of_speech,
            order_of_application
          )
      ) AS rownum
```

`PARTITION by event_no`によりイベント番号で分割したウィンドウの中で、`ORDER BY`をかけます。
`detail_type` は副問い合せの中でCASE式を使って算出したソート用の値で、以下のようになっていました。

* `speakers`テーブルにレコードを持つ場合、`1`
* `atendees`テーブルにレコードを持つ場合、`2`
* どちらのテーブルにもレコードを持たない場合、`3`

まずこの順序でソートし、その次のソートキーに`order_of_speech, order_of_application`をしているので、登壇者、参加者のそれぞれのソート順も仕様どおりになります。

最後に

> * 登壇者、参加者ともに0件のイベントであっても、1行明細行を出力する

の対応です。
このケース（テストデータでいうと、イベント番号=4のデータ）でも結果が0件とならないように、実は`speakers`テーブルおよび`atendees`テーブルの結合は**内部結合ではなく外部結合を選択していました**。
結果として、どちらか片方のテーブルでも0件の場合は`detail_type`が以下のレコードが結果に含まれることになります。

> * どちらのテーブルにもレコードを持たない場合、`3`

**上記レコードは最終結果からは除外する。ただし、登壇者、参加者ともに0件のイベントについては1レコードだけ残す。**

というのが以下のWHERE句の絞り込み条件の意図するところです。

```sql
WHERE
  detail_type != 3
  OR rownum = 1
```

# おわりに
いかがだったでしょうか？
解答例はちょっとトリッキーでしたが、こんなテクニックもあるんだなと知って頂くと、どこかで役立つかもしれません。
もっとすっきりした別解などありましたら是非コメント欄で教えてください！

# バックナンバー
[つ 息抜きにSQLクイズでも](https://qiita.com/yonetty/items/9413c0b35eef5773d4b0)
[つ 息抜きにSQLクイズでも (2)](https://qiita.com/yonetty/items/a57885e2b568a2b1d6eb)
[つ 息抜きにSQLクイズでも (3)](https://qiita.com/yonetty/items/00d5ab260dde370af77d)