データベース05: SQL(2/3) 結合質問

1. 集合演算
2. 結合演算

講義ノートURL
https://dbnote.hontolab.org/content/sql/02.html

Transcript

1. SQL(2/3): 結合質問 ⼭本 祐輔 名古屋市⽴⼤学 データサイエンス研究科 yusuke_yamamoto@acm.org 第5回 データベース
2. 講義ノート https://bit.ly/3xqTSds
3. 典型的な関係データモデル 学生ID 姓 名 入学年 所属 s00001 川澄 桜 2023 A学部 s00002 山畑 滝子 2024 B学部 s00003 田辺 通 2024 C学部 科目ID 科目名 開講年度 c0001 線形代数 2023 c0002 線形代数 2024 c0003 統計学入門 2024 科目ID 学生ID 成績 c0001 s00001 不可 c0002 s00001 良 c0002 s00002 優 c0003 s00003 可 学⽣ 科⽬ 履修 … … … データの正しさの確保するためにデータを複数の表に分割
4. 典型的な関係データモデル 学生ID 姓 名 入学年 所属 s00001 川澄 桜 2023 A学部 s00002 山畑 滝子 2024 B学部 s00003 田辺 通 2024 C学部 科目ID 科目名 開講年度 c0001 線形代数 2023 c0002 線形代数 2024 c0003 統計学入門 2024 科目ID 学生ID 成績 c0001 s00001 不可 c0002 s00001 良 c0002 s00002 優 c0003 s00003 可 学⽣ 科⽬ 履修 … … … データの正しさの確保するためにデータを複数の表に分割 関係データベースでは 複数の表を組み合わせることが 頻繁に起こる
5. 今⽇のメニュー 集合演算 + = + = 結合演算 表を縦⽅向につなぐ （レコードの数を操作する） 表を横⽅向につなぐ （関連するレコードをつなぐ）
6. 以降の説明で利⽤するデータ 独⽴⾏政法⼈統計センターが公開している 教育⽤標準データセットの基本素材SSDSE-E および市区町村SSDSE-Aから抜粋・加⼯したデータ 出典: https://www.nstac.go.jp/use/literacy/ssdse/
7. elderly_population_top10テーブル 元データの出典: https://www.nstac.go.jp/use/literacy/ssdse/#SSDSE-E 65歳以上の⼈⼝数上位10位の都道府県データ
8. university_student_population_top10テーブル 元データの出典: https://www.nstac.go.jp/use/literacy/ssdse/#SSDSE-E ⼤学学⽣数上位10位の都道府県データ
9. activityテーブル 元データの出典: https://www.nstac.go.jp/use/literacy/ssdse/#SSDSE-A 5種類の活動について過去1年以内に活動したことの ある⼈の割合について都道府県別にまとめたデータ
10. Q1: 復習 Q. populationテーブルにあるレコードのうち， ⼤学学⽣数が7万以上かつ⼩学校児童数が30万 以上の都道府県名を表⽰するSQL⽂を書け． 表⽰の際には都道府県名の重複は除く．
11. Q1: 復習 Q. populationテーブルにあるレコードのうち， ⼤学学⽣数が7万以上かつ⼩学校児童数が30万 以上の都道府県名を表⽰するSQL⽂を書け． 表⽰の際には都道府県名の重複は除く． SELECT DISTINCT 都道府県 FROM population WHERE ⼤学学⽣数 ≧ 70000 AND ⼩学校児童数 ≧ 300000; A.
12. 集合演算 1 Set Operations
13. 今⽇のメニュー 集合演算 + = + = 結合演算 表を縦⽅向につなぐ （レコードの数を操作する） 表を横⽅向につなぐ （関連するレコードをつなぐ）
14. 集合の代数学 2つの集合から別の集合を作り出す数学的操作 A B 2 4 6 8 10 12 16 20 A B 2 4 6 8 10 12 16 20 共通集合 𝑨 ∩ 𝑩 和集合 𝑨 ∪ 𝑩 A B 2 4 6 8 10 12 16 20 差集合 𝑨 − 𝑩 1 2 3 合 否 (1, 合) (1, 否) (2, 合) (2, 否) (3, 合) (3, 否) A B 直積集合 𝑨×𝑩 集合を扱う関係データモデルにも集合演算がある
15. 和集合 SELECT * FROM elderly_population_top10 UNION SELECT * FROM university_student_population_top10; 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R22000 静岡県 3608000 … 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R26000 京都府 2561000 … elderly_population_top10 university_student_population_top10 ∪ UNION句はテーブルAとテーブルBのいずれかに 含まれるレコードを返す
16. 和集合 SELECT * FROM elderly_population_top10 UNION SELECT * FROM university_student_population_top10; UNION句はテーブルAとテーブルBのいずれかに 含まれるレコードを返す 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R04000 福岡県 5124000 11件
17. 関係データモデルにおける集合演算の注意点 (1/2) SELECT ⽂1 UNION SELECT ⽂2; ここだけ取り出しても 実⾏可能なSQL⽂にする WHERE句なども 混ぜることが可能 SELECT * FROM elderly_population_top10 WHERE 総⼈⼝ ≧ 7000000 UNION SELECT * FROM university_student_population_top10 WHERE 総⼈⼝ ≧ 7000000;
18. 関係データモデルにおける集合演算の注意点 (2/2) – 和両⽴ SELECT 列名1, 列名2, 列名3… UNION SELECT 列名1, 列名2, 列名3… ; （縦方向の）関係の集合演算では 関係の属性(列)集合の要素と順序が一致する必要あり 和両⽴条件 完全⼀致
19. 共通集合 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R22000 静岡県 3608000 … 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R26000 京都府 2561000 … elderly_population_top10 university_student_population_top10 ∪ SELECT * FROM elderly_population_top10 INTERSECT SELECT * FROM university_student_population_top10; INTERSECT句はテーブルAとテーブルBの 両方に含まれるレコードを返す
20. 共通集合 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R28000 兵庫県 5432000 R40000 福岡県 5124000 9件 SELECT * FROM elderly_population_top10 INTERSECT SELECT * FROM university_student_population_top10; INTERSECT句はテーブルAとテーブルBの 両方に含まれるレコードを返す
21. 差集合 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R22000 静岡県 3608000 … 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R26000 京都府 2561000 … elderly_population_top10 university_student_population_top10 ー SELECT * FROM elderly_population_top10 EXCEPT SELECT * FROM university_student_population_top10; EXCEPT句はテーブルAには存在するがテーブルB には存在しないレコードを返す
22. 差集合 SELECT * FROM elderly_population_top10 EXCEPT SELECT * FROM university_student_population_top10; 地域コード 都道府県 総人口 R22000 静岡県 3608000 A ‒ BとB ‒ Aの集合演算は結果が異なることに注意 EXCEPT句はテーブルAには存在するがテーブルB には存在しないレコードを返す
23. Q2: 集合演算 Q. populationテーブルにあるレコードのうち， ⼤学学⽣数が7万以上かつ⼩学校児童数が30万 以上の都道府県名を表⽰するSQL⽂を書け． ただしINTERSECT句を使って書くこと．
24. Q2: 集合演算 Q. populationテーブルにあるレコードのうち， ⼤学学⽣数が7万以上かつ⼩学校児童数が30万 以上の都道府県名を表⽰するSQL⽂を書け． ただしINTERSECT句を使って書くこと． SELECT DISTINCT 都道府県 FROM population WHERE ⼤学学⽣数 ≧ 70000 INTERSECT SELECT DISTINCT 都道府県 FROM population WHERE ⼩学校児童数 ≧ 300000; A.
25. 結合演算 2 Join Operations
26. 今⽇のメニュー 集合演算 + = + = 結合演算 表を縦⽅向につなぐ （レコードの数を操作する） 表を横⽅向につなぐ （関連するレコードをつなぐ） 結合演算を使いこなすことが関係データベース操作の鍵
27. 関係データモデルにおける3つの結合演算 結合演算 直積（交差結合） 内部結合 外部結合 SELECT * FROM A XXX JOIN B… ; 結合演算のSQL形式
28. 直積 集合S1 , S2 , …, Sn が与えられたとき， 𝑆! ×𝑆" …×𝑆# = 𝑥! , … , 𝑥# 𝑥! ∈ 𝑆! , … , 𝑥# ∈ 𝑆# } なる 𝑆! × ⋯×𝑆" を S1 , …, Sn 上の直積集合と呼ぶ 例 A = 2, 3 , 𝐵 = {−2, −3} が与えられたとき A×𝐵 = 2, −2 , 2, −3 , 3, −2 , (3, −3) 2 A 3 -2 B -3
29. 直積（交差結合; CROSS JOIN）(1/2) 科目名 開講時期 データベース 2年前期 機械学習 3年前期 統計モデリング 2年後期 氏名 職階 山畑 教授 桜山 教授 川澄 准教授 田辺 講師 テーブル「科⽬」 テーブル「教員」 × 直積 SELECT * FROM 科⽬ CROSS JOIN 教員; 直積はテーブルAとテーブルBにあるレコードの すべての組み合わせを返す
30. 直積（交差結合; CROSS JOIN）(1/2) 科目名 開講時期 氏名 職階 データベース 2年前期 山畑 教授 データベース 2年前期 桜山 教授 データベース 2年前期 川澄 准教授 データベース 2年前期 田辺 講師 機械学習 3年前期 山畑 教授 … SELECT * FROM 科⽬ CROSS JOIN 教員; 直積はテーブルAとテーブルBにあるレコードの すべての組み合わせを返す 12件
31. 直積（交差結合; CROSS JOIN）(1/2) 科目名 開講時期 氏名 職階 データベース 2年前期 山畑 教授 データベース 2年前期 桜山 教授 データベース 2年前期 川澄 准教授 データベース 2年前期 田辺 講師 機械学習 3年前期 山畑 教授 … SELECT * FROM 科⽬ , 教員; 直積はテーブルAとテーブルBにあるレコードの すべての組み合わせを返す CROSS JOINを カンマで省略表記
32. Q3: 直積 Q. populationテーブルとactivityテーブルの 直積を求めるSQL⽂を書け．
33. Q3: 直積 Q. populationテーブルとactivityテーブルの 直積を求めるSQL⽂を書け． SELECT * FROM population, activity; A.
34. 直積（交差結合; CROSS JOIN）(2/2) SELECT * FROM university_student_population_top10 AS u, activity AS a WHERE u.都道府県 = a.都道府県; WHERE句と組み合わせれば直積結果を絞り込める 名前が⻑いので AS修飾句で別名をつける 特定のテーブルの要素を 明⽰的に参照するときはドットを使う
35. 内部結合（INNER JOIN）(1/3) 日時 顧客ID 商品ID 個数 2019/1/1 い A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 商品マスタに商品Dに関するデータがないので無視される 指定した列が指定条件を満たす行を探して 2つの表の行を結合 商品IDが同じ⾏を探して結合
36. 内部結合（INNER JOIN）(2/3) SELECT 購買履歴.*, 商品.* FROM 購買履歴 INNER JOIN 商品 ON 購買履歴.商品ID = 商品.商品ID; 日時 顧客ID 商品ID 個数 2019/1/1 い A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 結合条件 商品IDが同じ⾏を探して結合
37. 内部結合（INNER JOIN）(3/3) SELECT 購買履歴.*, 商品.* FROM 購買履歴 INNER JOIN 商品 USING (商品ID); 日時 顧客ID 商品ID 個数 2019/1/1 い A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 結合に使う列名が 2つの表で同じであれば USINGで条件を短く書ける 商品IDが同じ⾏を探して結合
38. Q4: 内部結合 Q. populationテーブルとactivityテーブルを列 「地域コード」が等しいという条件で 内部結合を⾏うSQL⽂を書け．
39. Q4: 内部結合 Q. populationテーブルとactivityテーブルを列 「地域コード」が等しいという条件で 内部結合を⾏うSQL⽂を書け． SELECT * FROM population INNER JOIN activity USING (地域コード); A.
40. Q5: 直積再び Q. populationテーブルとactivityテーブルを列 「地域コード」が等しいという条件で内部 結合を⾏うSQL⽂をINNER JOINを⽤いず， 直積演算を⽤いて書け．
41. Q5: 直積再び Q. populationテーブルとactivityテーブルを列 「地域コード」が等しいという条件で内部 結合を⾏うSQL⽂をINNER JOINを⽤いず， 直積演算を⽤いて書け． SELECT * FROM population, activity WHERE population.地域コード = activity.地域コード; A.
42. 直積に対する絞り込み vs. 内部結合 SELECT * FROM university_student_population_top10 AS u, activity AS a WHERE u.都道府県 = a.都道府県; 直積の場合 直積集合をすべて生成した後，WHERE句で絞り込む ①ここで直積集合を作った後 ②直積集合から該当レコードを絞り込む
43. 直積に対する絞り込み vs. 内部結合 SELECT * FROM university_student_population_top10 AS u, INNER JOIN u USING (都道府県); 内部結合の場合 結合条件を満たす組み合わせのみを抽出し表示 内部的には直積を計算していない
44. JOINとWHEREの評価順序 SELECT university_student_population_top10.*, activity.学習・⾃⼰啓発・訓練 AS 学習・⾃⼰鍛錬 FROM university_student_population_top10 AS u INNER JOIN activity ON u.都道府県 = activity.都道府県 WHERE u.総⼈⼝ >= 7000000; ①まず内部結合 ②FROM句の内容を条件で絞り込む
45. 外部結合（OUTER JOIN） 日時 顧客ID 商品ID 個数 2019/1/1 い A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 指定した列が指定条件を満たす表の行を結合 （条件を満たさない時，結合される側の⾏に空値で結合する） 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 2022/3/7 に D 8 商品IDが同じ⾏を探して結合
46. 外部結合（OUTER JOIN） 日時 顧客ID 商品ID 個数 2019/1/1 い A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 指定した列が指定条件を満たす表の行を結合 （条件を満たさない時，結合される側の⾏に空値で結合する） 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 2022/3/7 に D 8 商品IDが同じ⾏を探して結合 NULL値
47. 左外部結合（LEFT OUTER JOIN） SELECT 購買履歴.*, 商品.* FROM 購買履歴 LEFT OUTER JOIN 商品 ON 購買履歴.商品ID = 商品.商品ID; 結合条件 日時 顧客ID 商品ID 個数 2019/1/1 い A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 商品IDが同じ⾏を探して結合 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 2022/3/7 に D 8 NULL値
48. Q7: 左外部結合 Q. populationテーブルとactivityテーブルを 列「地域コード」が等しいという条件で 左外部結合を⾏うSQL⽂を書け．
49. Q7: 左外部結合 Q. populationテーブルとactivityテーブルを 列「地域コード」が等しいという条件で 左外部結合を⾏うSQL⽂を書け． SELECT * FROM population LEFT OUTER JOIN activity USING (地域コード); A.
50. 右外部結合（RIGHT OUTER JOIN） SELECT 購買履歴.*, 商品.* FROM 購買履歴 RIHGT OUTER JOIN 商品 ON 購買履歴.商品ID = 商品.商品ID; 日時 顧客ID 商品ID 個数 2019/1/1 い A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 商品IDが同じ⾏を探して結合 日時 顧客ID 個数 商品ID 商品名 単価 2019/1/1 い 2 A 伊左衛門 130 2019/1/1 い 3 B 午前の紅茶 150 C おいしい牛乳 250 NULL値
51. 外部結合の例 SELECT university_student_population_top10.*, activity.学習・⾃⼰啓発・訓練 AS 学習・⾃⼰鍛錬 FROM university_student_population_top10 AS u LEFT OUTER JOIN activity ON u.都道府県 = activity.都道府県; 左外部結合 外部結合は条件を満たさなかったレコードを⾒つけることが可能
52. 回 実施日 トピック 1 04/14 ガイダンス：データベースを使わない世界 2 04/21 データベースの概念 3 04/28 関係データモデル 4 05/12 SQL (1/3) 5 05/19 SQL (2/3) 6 05/26 SQL (3/3) 7 06/02 SQL演習 – レポート課題1 8 06/09 実体関連モデル (1/3) 9 06/16 実体関連モデル (2/3) 10 06/23 実体関連モデル (3/3) 11 06/30 正規化 (1/2) 12 07/07 正規化 (2/2) 13 07/14 データベース設計演習 – レポート課題2 14 07/21 索引付け 15 07/28 授業の振り返り or 発展的話題 16 08/04 期末試験 今後の予定 54