Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
データベース05: SQL(2/3) 結合質問
Search
Y. Yamamoto
May 20, 2024
Technology
0
490
データベース05: SQL(2/3) 結合質問
1. 集合演算
2. 結合演算
講義ノートURL
https://dbnote.hontolab.org/content/sql/02.html
Y. Yamamoto
May 20, 2024
Tweet
Share
More Decks by Y. Yamamoto
See All by Y. Yamamoto
データベース12: 正規化(2/2) - データ従属性に基づく正規化
trycycle
0
530
データベース11: 正規化(1/2) - 望ましくない関係スキーマ
trycycle
0
510
データベース10: 拡張実体関連モデル
trycycle
0
530
データベース09: 実体関連モデル上の一貫性制約
trycycle
0
510
データベース08: 実体関連モデルとは?
trycycle
0
520
データベース14: B+木 & ハッシュ索引
trycycle
0
240
データベース15: ビッグデータ時代のデータベース
trycycle
0
140
データベース06: SQL (3/3) 副問い合わせ
trycycle
0
380
データベース04: SQL (1/3) 単純質問 & 集約演算
trycycle
0
530
Other Decks in Technology
See All in Technology
Amazon CloudWatch Network Monitor 導入ガイド_デモ説明付き
yukimmmm
0
140
エンジニア候補者向け資料2024.11.07.pdf
macloud
0
4.5k
君は隠しイベントを見つけれるか?
mujyun
0
400
生成AIと知識グラフの相互利用に基づく文書解析
koujikozaki
1
150
組み込みLinuxの時系列
puhitaku
2
750
利きプロセススケジューラ
sat
PRO
4
2.2k
IaC運用を楽にするためにCDK Pipelinesを導入したけど、思い通りにいかなかった話
smt7174
1
130
20241031_AWS_生成AIハッカソン_GenMuck
tsumita
0
120
QAEチームが辿った3年 ボクらが改善業務にスクラムを選んだワケ / 20241108_cloudsign_ques23
bengo4com
0
100
日経電子版におけるリアルタイムレコメンドシステム開発の事例紹介/nikkei-realtime-recommender-system
yng87
2
670
ガバメントクラウド先行事業中間報告を読み解く
sugiim
1
1.9k
Fargateを使った研修の話
takesection
0
160
Featured
See All Featured
Writing Fast Ruby
sferik
626
61k
Principles of Awesome APIs and How to Build Them.
keavy
126
17k
Designing Dashboards & Data Visualisations in Web Apps
destraynor
228
52k
The Pragmatic Product Professional
lauravandoore
31
6.3k
Ruby is Unlike a Banana
tanoku
96
11k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
328
21k
Agile that works and the tools we love
rasmusluckow
327
21k
Designing on Purpose - Digital PM Summit 2013
jponch
115
6.9k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
131
33k
Dealing with People You Can't Stand - Big Design 2015
cassininazir
364
23k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
28
9.1k
The Power of CSS Pseudo Elements
geoffreycrofte
72
5.3k
Transcript
SQL(2/3): 結合質問 ⼭本 祐輔 名古屋市⽴⼤学 データサイエンス学部
[email protected]
第5回 データベース 2024年5月20日
講義ノート https://bit.ly/3xqTSds
典型的な関係データモデル 学生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 可 学⽣ 科⽬ 履修 … … … データの正しさの確保するためにデータを複数の表に分割
典型的な関係データモデル 学生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 可 学⽣ 科⽬ 履修 … … … データの正しさの確保するためにデータを複数の表に分割 関係データベースでは 複数の表を組み合わせることが 頻繁に起こる
今⽇のメニュー 集合演算 + = + = 結合演算 表を縦⽅向につなぐ (レコードの数を操作する) 表を横⽅向につなぐ
(関連するレコードをつなぐ)
以降の説明で利⽤するデータ 独⽴⾏政法⼈統計センターが公開している 教育⽤標準データセットの基本素材SSDSE-E および市区町村SSDSE-Aから抜粋・加⼯したデータ 出典: https://www.nstac.go.jp/use/literacy/ssdse/
elderly_population_top10テーブル 元データの出典: https://www.nstac.go.jp/use/literacy/ssdse/#SSDSE-E 65歳以上の⼈⼝数上位10位の都道府県データ
university_student_population_top10テーブル 元データの出典: https://www.nstac.go.jp/use/literacy/ssdse/#SSDSE-E ⼤学学⽣数上位10位の都道府県データ
activityテーブル 元データの出典: https://www.nstac.go.jp/use/literacy/ssdse/#SSDSE-A 5種類の活動について過去1年以内に活動したことの ある⼈の割合について都道府県別にまとめたデータ
Q1: 復習 Q. populationテーブルにあるレコードのうち, ⼤学学⽣数が7万以上かつ⼩学校児童数が30万 以上の都道府県名を表⽰するSQL⽂を書け. 表⽰の際には都道府県名の重複は除く.
Q1: 復習 Q. populationテーブルにあるレコードのうち, ⼤学学⽣数が7万以上かつ⼩学校児童数が30万 以上の都道府県名を表⽰するSQL⽂を書け. 表⽰の際には都道府県名の重複は除く. SELECT DISTINCT 都道府県
FROM population WHERE ⼤学学⽣数 ≧ 70000 AND ⼩学校児童数 ≧ 300000; A.
集合演算 1 Set Operations
今⽇のメニュー 集合演算 + = + = 結合演算 表を縦⽅向につなぐ (レコードの数を操作する) 表を横⽅向につなぐ
(関連するレコードをつなぐ)
集合の代数学 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 直積集合 𝑨×𝑩 集合を扱う関係データモデルにも集合演算がある
和集合 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のいずれかに 含まれるレコードを返す
和集合 SELECT * FROM elderly_population_top10 UNION SELECT * FROM university_student_population_top10;
UNION句はテーブルAとテーブルBのいずれかに 含まれるレコードを返す 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R04000 福岡県 5124000 11件
関係データモデルにおける集合演算の注意点 (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;
関係データモデルにおける集合演算の注意点 (2/2) – 和両⽴ SELECT 列名1, 列名2, 列名3… UNION SELECT
列名1, 列名2, 列名3… ; (縦方向の)関係の集合演算では 関係の属性(列)集合の要素と順序が一致する必要あり 和両⽴条件 完全⼀致
共通集合 地域コード 都道府県 総人口 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の 両方に含まれるレコードを返す
共通集合 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000
… R28000 兵庫県 5432000 R40000 福岡県 5124000 9件 SELECT * FROM elderly_population_top10 INTERSECT SELECT * FROM university_student_population_top10; INTERSECT句はテーブルAとテーブルBの 両方に含まれるレコードを返す
差集合 地域コード 都道府県 総人口 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 には存在しないレコードを返す
差集合 SELECT * FROM elderly_population_top10 EXCEPT SELECT * FROM university_student_population_top10;
地域コード 都道府県 総人口 R22000 静岡県 3608000 A ‒ BとB ‒ Aの集合演算は結果が異なることに注意 EXCEPT句はテーブルAには存在するがテーブルB には存在しないレコードを返す
Q2: 集合演算 Q. populationテーブルにあるレコードのうち, ⼤学学⽣数が7万以上かつ⼩学校児童数が30万 以上の都道府県名を表⽰するSQL⽂を書け. ただしINTERSECT句を使って書くこと.
Q2: 集合演算 Q. populationテーブルにあるレコードのうち, ⼤学学⽣数が7万以上かつ⼩学校児童数が30万 以上の都道府県名を表⽰するSQL⽂を書け. ただしINTERSECT句を使って書くこと. SELECT DISTINCT 都道府県
FROM population WHERE ⼤学学⽣数 ≧ 70000 INTERSECT SELECT DISTINCT 都道府県 FROM population WHERE ⼩学校児童数 ≧ 300000; A.
結合演算 2 Join Operations
今⽇のメニュー 集合演算 + = + = 結合演算 表を縦⽅向につなぐ (レコードの数を操作する) 表を横⽅向につなぐ
(関連するレコードをつなぐ) 結合演算を使いこなすことが関係データベース操作の鍵
関係データモデルにおける3つの結合演算 結合演算 直積(交差結合) 内部結合 外部結合 SELECT * FROM A XXX
JOIN B… ; 結合演算のSQL形式
直積 集合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
直積(交差結合; CROSS JOIN)(1/2) 科目名 開講時期 データベース 2年前期 機械学習 3年前期 統計モデリング
2年後期 氏名 職階 山畑 教授 桜山 教授 川澄 准教授 田辺 講師 テーブル「科⽬」 テーブル「教員」 × 直積 SELECT * FROM 科⽬ CROSS JOIN 教員; 直積はテーブルAとテーブルBにあるレコードの すべての組み合わせを返す
直積(交差結合; CROSS JOIN)(1/2) 科目名 開講時期 氏名 職階 データベース 2年前期 山畑
教授 データベース 2年前期 桜山 教授 データベース 2年前期 川澄 准教授 データベース 2年前期 田辺 講師 機械学習 3年前期 山畑 教授 … SELECT * FROM 科⽬ CROSS JOIN 教員; 直積はテーブルAとテーブルBにあるレコードの すべての組み合わせを返す 12件
直積(交差結合; CROSS JOIN)(1/2) 科目名 開講時期 氏名 職階 データベース 2年前期 山畑
教授 データベース 2年前期 桜山 教授 データベース 2年前期 川澄 准教授 データベース 2年前期 田辺 講師 機械学習 3年前期 山畑 教授 … SELECT * FROM 科⽬ , 教員; 直積はテーブルAとテーブルBにあるレコードの すべての組み合わせを返す CROSS JOINを カンマで省略表記
Q3: 直積 Q. populationテーブルとactivityテーブルの 直積を求めるSQL⽂を書け.
Q3: 直積 Q. populationテーブルとactivityテーブルの 直積を求めるSQL⽂を書け. SELECT * FROM population, activity;
A.
直積(交差結合; CROSS JOIN)(2/2) SELECT * FROM university_student_population_top10 AS u, activity
AS a WHERE u.都道府県 = a.都道府県; WHERE句と組み合わせれば直積結果を絞り込める 名前が⻑いので AS修飾句で別名をつける 特定のテーブルの要素を 明⽰的に参照するときはドットを使う
内部結合(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が同じ⾏を探して結合
内部結合(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が同じ⾏を探して結合
内部結合(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が同じ⾏を探して結合
Q4: 内部結合 Q. populationテーブルとactivityテーブルを列 「地域コード」が等しいという条件で 内部結合を⾏うSQL⽂を書け.
Q4: 内部結合 Q. populationテーブルとactivityテーブルを列 「地域コード」が等しいという条件で 内部結合を⾏うSQL⽂を書け. SELECT * FROM population
INNER JOIN activity USING (地域コード); A.
Q5: 直積再び Q. populationテーブルとactivityテーブルを列 「地域コード」が等しいという条件で内部 結合を⾏うSQL⽂をINNER JOINを⽤いず, 直積演算を⽤いて書け.
Q5: 直積再び Q. populationテーブルとactivityテーブルを列 「地域コード」が等しいという条件で内部 結合を⾏うSQL⽂をINNER JOINを⽤いず, 直積演算を⽤いて書け. SELECT *
FROM population, activity WHERE population.地域コード = activity.地域コード; A.
直積に対する絞り込み vs. 内部結合 SELECT * FROM university_student_population_top10 AS u, activity
AS a WHERE u.都道府県 = a.都道府県; 直積の場合 直積集合をすべて生成した後,WHERE句で絞り込む ①ここで直積集合を作った後 ②直積集合から該当レコードを絞り込む
直積に対する絞り込み vs. 内部結合 SELECT * FROM university_student_population_top10 AS u, INNER
JOIN u USING (都道府県); 内部結合の場合 結合条件を満たす組み合わせのみを抽出し表示 内部的には直積を計算していない
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句の内容を条件で絞り込む
外部結合(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が同じ⾏を探して結合
外部結合(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値
左外部結合(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値
Q7: 左外部結合 Q. populationテーブルとactivityテーブルを 列「地域コード」が等しいという条件で 左外部結合を⾏うSQL⽂を書け.
Q7: 左外部結合 Q. populationテーブルとactivityテーブルを 列「地域コード」が等しいという条件で 左外部結合を⾏うSQL⽂を書け. SELECT * FROM population
LEFT OUTER JOIN activity USING (地域コード); A.
右外部結合(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値
外部結合の例 SELECT university_student_population_top10.*, activity.学習・⾃⼰啓発・訓練 AS 学習・⾃⼰鍛錬 FROM university_student_population_top10 AS u
LEFT OUTER JOIN activity ON u.都道府県 = activity.都道府県; 左外部結合 外部結合は条件を満たさなかったレコードを⾒つけることが可能
回 実施日 トピック 1 04/15 ガイダンス:データベースを使わない世界 2 04/22 データベースの概念 3
04/29(祝) 関係データモデル 4 05/13 SQL (1/3) 5 05/20 SQL (2/3) 6 05/27 SQL (3/3) 7 06/03 SQL演習 – レポート課題1 8 06/10 実体関連モデル (1/3) 9 06/17 実体関連モデル (2/3) 10 06/24 実体関連モデル (3/3) 11 07/01 正規化 (1/2) 12 07/08 正規化 (2/2) 13 07/15(祝) データベース設計演習 – レポート課題2 14 07/22 索引付け 15 07/29 NoSQL 16 08/05 期末試験 今後の予定 54