つ 息抜きにSQLクイズでも

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

# データの説明
## ER図っぽいもの

<img width="767" alt="erd.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176589/c30d0722-4225-24fe-41fc-8f27e8ff9ea4.png">


## データ構造の説明
`product_types`には商品タイプが入っています。

| product_type_id  | type_name |
|:-----------------|:----------|
| 10               | デスクトップ |
| 20               | ノートブック |
| 30               | タブレット |

`products`には商品が入っています。

| product_id_id  | product_type_id | product_name |
|:-----------------|:----------|:------|
| 11               | 10        | FMW |
| 21               | 20        | WAIO |
| 22               | 20        | DenoBook |
| 31               | 30        | yPad |

`accessories`には付属品が入っています。

| accessory_id  | accessory_name |
|:-----------------|:----------|
| 1                | モニター |
| 2                | キーボード |
| 3                | マウス |
| 4                | バッテリー |
| 5                | ドッキングステーション |

`default_accessories`には標準付属品が入っています（商品タイプと付属品の関連テーブル）

| product_type_id  | accessory_id |
|:-----------------|:----------|
| 10               | 1 |
| 10               | 2 |
| 10               | 3 |
| 20               | 3 |
| 20               | 4 |

`product_accessories`には商品固有の付属品が入っています（商品と付属品の関連テーブル）

| product_id  | accessory_id |
|:-----------------|:----------|
| 21               | 3 |
| 21               | 5 |

# 問題
```
商品毎の付属品一覧を取得するSQLを書いてください。
```
## 前提事項
* `product_accessories`にデータが入っている商品については、それらを付属品とて表示します。
* `product_accessories`にデータがない商品については、`default_accessories`に入っている（商品タイプ毎の）標準付属品を表示します。
* 上記のいずれにもデータがない商品についても結果を出力し、付属品を`なし`と表示します。
* 結果は、商品名(`product_name`）と付属品名（`accessory_name`）を表示します。`product_name`の昇順、`accessory_id`の昇順でソートします。

## 制約事項
* できるだけ標準SQLで記述してください。
* できるだけDISTINCTは使用しないでください。

## 期待結果
| product_name  | accessory_name |
|:-----------------|:----------|
| DenoBook | マウス |
| DenoBook | バッテリー |
| FMW | モニター |
| FMW | キーボード |
| FMW | マウス |
| WAIO | マウス |
| WAIO | ドッキングステーション |
| yPad | なし |

# DDL/DML
ローカル環境や [DB Fiddle](https://www.db-fiddle.com/)などの環境で以下のSQLを流してください。
（動作確認は、DB Fiddle上のPostgreSQL 11で行いました）
> DB Fiddleで試す場合、左側の`Schema SQL`というペインに下記のSQLをコピー&ペーストし、右側の`Query SQL`というペインにクエリを記述します。画面上部ナビバーの`Run`をクリックすると結果が表示されます。

```sql
CREATE TABLE accessories (
  accessory_id integer,
  accessory_name varchar(50),
  PRIMARY KEY (accessory_id)
);
CREATE TABLE product_types (
  product_type_id integer,
  type_name varchar(50),
  PRIMARY KEY (product_type_id)
);
CREATE TABLE products (
  product_id integer,
  product_type_id integer,
  product_name varchar(50),
  PRIMARY KEY (product_id)
);
CREATE TABLE default_accessories (
  product_type_id integer,
  accessory_id integer,
  PRIMARY KEY (product_type_id, accessory_id)
);
CREATE TABLE product_accessories (
  product_id integer,
  accessory_id integer,
  PRIMARY KEY (product_id, accessory_id)
);
INSERT INTO accessories VALUES (1, 'モニター');
INSERT INTO accessories VALUES (2, 'キーボード');
INSERT INTO accessories VALUES (3, 'マウス');
INSERT INTO accessories VALUES (4, 'バッテリー');
INSERT INTO accessories VALUES (5, 'ドッキングステーション');
INSERT INTO product_types VALUES (10, 'デスクトップ');
INSERT INTO product_types VALUES (20, 'ノートブック');
INSERT INTO product_types VALUES (30, 'タブレット');
INSERT INTO default_accessories VALUES (10, 1);
INSERT INTO default_accessories VALUES (10, 2);
INSERT INTO default_accessories VALUES (10, 3);
INSERT INTO default_accessories VALUES (20, 3);
INSERT INTO default_accessories VALUES (20, 4);
INSERT INTO products VALUES (11, 10, 'FMW');
INSERT INTO products VALUES (21, 20, 'WAIO');
INSERT INTO products VALUES (22, 20, 'DenoBook');
INSERT INTO products VALUES (31, 30, 'yPad');
INSERT INTO product_accessories VALUES(21, 3);
INSERT INTO product_accessories VALUES(21, 5);
```

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

```sql
select 
  sub2.product_name, 
  coalesce(acc.accessory_name, 'なし') as accessory_name
from 
  (
    select 
      sub.product_name, 
      sub.accessory_id, 
      min(sub.priority) over (partition by sub.product_name) as min_priority, 
      sub.priority 
    from 
      (
        select 
          p.product_name, 
          pa.accessory_id, 
          1 as priority 
        from 
          products p 
          inner join product_accessories pa on p.product_id = pa.product_id 
        union all 
        select 
          p.product_name, 
          da.accessory_id, 
          2 as priority 
        from 
          products p 
          inner join product_types pt on p.product_type_id = pt.product_type_id 
          left join default_accessories da on pt.product_type_id = da.product_type_id
      ) as sub
  ) as sub2 
  left join accessories acc on sub2.accessory_id = acc.accessory_id 
where 
  sub2.priority = sub2.min_priority 
order by 
  sub2.product_name, 
  sub2.accessory_id
```

以下、SQLの解説となります。

## 商品に対して登録された付属品の取得
最内の副問合せのunionの前半部分です。商品に対する付属品があれば優先するため、`priority`列に`1`を指定しています。

```sql
        select 
          p.product_name, 
          pa.accessory_id, 
          1 as priority 
        from 
          products p 
          inner join product_accessories pa on p.product_id = pa.product_id 
        union all 
```

## 商品タイプに対して登録された標準付属品の取得
最内の副問合せのunionの後半部分です。こちらの`priority`は`2`としています。
なお、付属品が存在しない商品の場合に`なし`を表示するため、`default_accessories`に対する結合は左外部結合としています。

```sql
        union all 
        select 
          p.product_name, 
          da.accessory_id, 
          2 as priority 
        from 
          products p 
          inner join product_types pt on p.product_type_id = pt.product_type_id 
          left join default_accessories da on pt.product_type_id = da.product_type_id
```

## 商品毎に優先度の最小値を取得
商品に対して付属品が登録されている場合、商品タイプ経由で取得した標準付属品とレコードが混在することになります。（`priority`が`1`のレコードと`2`のレコード）。
`priority`が小さい方のレコードのみに絞り込みたいため、商品の単位でグルーピングしたレコード中における`priority`の最小値を求めておきます。
これはウィンドウ関数を使用して実現できます。

```sql
    select 
      sub.product_name, 
      sub.accessory_id, 
      min(sub.priority) over (partition by sub.product_name) as min_priority, 
      sub.priority 
    from 
      (
        select 
          -- ...商品の付属品取得
        union all 
        select 
          -- ...商品タイプの標準付属品取得
      ) as sub
```

### 商品毎に優先度が高いレコードに絞り込み
さらに外側に副問合せを置き(`sub2`)、WHERE句で絞り込みをかけます。

```sql
select 
  sub2.product_name, 
  coalesce(acc.accessory_name, 'なし') as accessory_name
from 
  (
    select 
      sub.product_name, 
      sub.accessory_id, 
      min(sub.priority) over (partition by sub.product_name) as min_priority, 
      sub.priority 
    from 
      (
        -- sub副問合せ
      ) as sub
  ) as sub2 
  left join accessories acc on sub2.accessory_id = acc.accessory_id 
where 
  sub2.priority = sub2.min_priority -- 優先度(priority)で絞り込み ■
order by 
  sub2.product_name, 
  sub2.accessory_id
```

例えば商品`WAIO`の場合、副問合せ`sub`からは以下のようなレコードが返されますが、

| product_name  | accessory_id | min_priority | priority | (備考) |
|:--------------|:-------------|:-------------|:---------|:--------|
| WAIO          | 3            | 1            | 1        | 商品付属品 |
| WAIO          | 5            | 1            | 1        | 商品付属品 |
| WAIO          | 3            | 1            | 2        | 標準付属品 |
| WAIO          | 4            | 1            | 2        | 標準付属品 |

`sub2.priority = sub2.min_priority` という条件によって、１行目と２行目のデータが残ることになります。

# おわりに
暇つぶしになったでしょうか？
よい別解があったら是非コメント欄で教えてください！