Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

つ 息抜きにSQLクイズでも

目的

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

データの説明

ER図っぽいもの

erd.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などの環境で以下の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の後半部分です。こちらのpriority2としています。
なお、付属品が存在しない商品の場合になしを表示するため、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

商品毎に優先度の最小値を取得

商品に対して付属品が登録されている場合、商品タイプ経由で取得した標準付属品とレコードが混在することになります。(priority1のレコードと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 という条件によって、1行目と2行目のデータが残ることになります。

おわりに

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

yonetty
某SIerでアーキテクトとしてエンタープライズ向けシステム・製品の開発に携わっています。 Twitter: @tyonekubo
https://blog.ynkb.xyz/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away