目的

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

データの説明

ER図っぽいもの

データ構造の説明

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などの環境で以下のSQLを流してください。
（動作確認は、DB Fiddle上のPostgreSQL 11で行いました）

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

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の実現方法は色々あると思いますが、以下に一例を示します。

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を指定しています。

        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に対する結合は左外部結合としています。

        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の最小値を求めておきます。
これはウィンドウ関数を使用して実現できます。

    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句で絞り込みをかけます。

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 という条件によって、１行目と２行目のデータが残ることになります。

おわりに

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

つ 息抜きにSQLクイズでも

目的