「Puzzle36」の編集履歴（バックアップ）一覧はこちら

「Puzzle36」(2008/05/15 (木) 15:00:06) の最新版変更点

追加された行は緑色になります。

削除された行は赤色になります。

*問題
-役職テーブル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
--------+-----------
 White  | D
 Smith  | B
 Jones  | O
(3 行)


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

*問題
-役職テーブル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 行)


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