Puzzle36 - SQLパズル演習

問題

• 役職テーブルRolesから、重役'D'役員'O'、二役を兼務する人物'B'を出力する

 person | role             person | role 
 -------+------　　　　　　-------+------
 Smith  | O                Smith  | B
 Smith  | D                Jones  | O
 Jones  | O　　　　→　    White  | D
 White  | D
 Brown  | X

fumikoの回答1

SQL文

 SELECT (case when R1.person is null then R2.person else R1.person end),
        (case when R1.role is null then 'D'
              when R2.role is null then 'O'
              else 'B' end) as combined_role
 FROM  (select person,role from Roles where role = 'O') as R1 full outer join 
       (select person,role from Roles where role = 'D') as R2
        on R1.person = R2.person 

考え方

• role = 'O'だけのテーブル(R1)、role = 'D'だけのテーブル(R2)をそれぞれ作成
• ふたつのテーブルをpersonで全外部結合する(R3)

  person | role | person | role
 --------+------+--------+------
  Jones  | O    |        |
  Smith  | O    | Smith  | D
         |      | White  | D

• R1.personがnullならR2.personを、R2.personがnullならR1.personを出力
• R1.roleがnullならroleは'D'R2.roleがnullならroleは'O'それ以外は'B'を出力

実行結果

 person | combined_role
 -------+---------------
 Jones  | O
 Smith  | B
 White  | D

fumikoの回答2

SQL文

 SELECT person,
       (case when count(*) = 2 then 'B' else r1.role end)
 FROM  (select person,role
        from Roles
        where role = 'O' or role = 'D') as r1
 GROUP BY person;

考え方

• roleが'O'または'D'の人のテーブルを作成(↓r1)

person | role
-------+------
Smith  | O
Smith  | D
Jones  | O
White  | D

• personごとにGROUP BYして、count(*) = 2ならO,D兼任のはずなのでrole = 'B'、count(*) = 1ならroleをそのまま出力

実行結果

 ERROR:  
 column "r1.role" must appear in the GROUP BY clause
 or be used in an aggregate function

→groupbyしているので、そのグループ内に複数の値があることを考えて、r1.roleのように特定の値を指定することはできない

satomiの回答

SQL文

SELECT person,
(CASE WHEN count(*)=2 THEN 'B'
      WHEN role='D'   THEN 'D'
      WHEN role='O'   THEN 'O'
      END) as combined_role
FROM Roles
WHERE role in ('D','O')
GROUP BY person;

考え方

• personでグループ化
• Rolesから、役職がDまたはOの者だけ選ぶ
• countが2の人は、２役を兼務していると考えられるのでB
• roleがDの人はD
• roleがOの人はO

実行結果

--------+---------------
 person | combined_role 
--------+---------------
 Jones  | O             
 Smith  | B             
 White  | D             
--------+---------------

chiakiより補足

プレゼンした解答の、コストや実行結果についての補則です。

• 実行結果エラー

解答１のままだと、次のようなエラーが出てしまいます。

ERROR: column "r1.role" must appear in the GROUP BY clause or be used in an agg
regate function
（Posgre）

なので、SELECT節で、R1.roleをただ出力するのではなく、
R1.roleの最大値を取って出力すると、うまくいきました。

sql=# SELECT R1.person, max(R1.role)
sql-# FROM Roles AS R1
sql-# WHERE R1.role IN ('D','O')
sql-# GROUP BY R1.person
sql-# HAVING COUNT ( DISTINCT R1.role ) = 1
sql-# UNION
sql-# SELECT R2.person, 'B'
sql-# FROM Roles AS R2
sql-# WHERE R2.role IN ('D','O')
sql-# GROUP BY R2.person
sql-# HAVING COUNT ( DISTINCT R2.role ) = 2;

person | max

Jones  | O             
Smith  | B             
White  | D             

(3 行)

解答４も同じように書き換える必要がありました。

また、解答６はうまく結果が表示されませんでした。

PostgreSQL では、型チェックが厳しく、
SUM 関数の戻り値を整数型に変換しないと正しく動かないので注意。

と書いてあったので、SQLを少し変えてみました。

sql=# SELECT person,
sql-# SUBSTRING ('DOB', CAST(SUM (POSITION (role IN 'DO')) AS INTEGER), 1
)
sql-# FROM Roles
sql-# WHERE role IN ('D','O')
sql-# GROUP BY person;

person | substring

Jones  | O             
Smith  | B             
White  | D             

(3 行)

また、コスト計算をすると、解答４，５，６が速そうでした。解答１が一番遅いようです。