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
April 29, 2024
Technology
0
36
データベース05: SQL(2/3) 結合質問
1. 集合演算
2. 結合演算
Y. Yamamoto
April 29, 2024
Tweet
Share
More Decks by Y. Yamamoto
See All by Y. Yamamoto
データベース06: SQL (3/3) 副問い合わせ
trycycle
0
32
データベース03: 関係データモデル
trycycle
0
140
データベース04: SQL (1/3) 単純質問 & 集約演算
trycycle
0
120
データベース02: データベースの概念
trycycle
0
200
データベース01: データベースを使わない世界
trycycle
1
150
ビッグデータ × AI = DX?
trycycle
0
96
名古屋市立大学データサイエンス学部 秋のオープンキャンパス模擬授業20231111
trycycle
0
1.8k
データマイニングと機械学習 - ニューラルネットワーク
trycycle
0
370
データマイニングと機械学習-SVM
trycycle
1
390
Other Decks in Technology
See All in Technology
サイボウズ 開発本部採用ピッチ / Cybozu Engineer Recruit
cybozuinsideout
PRO
9
37k
Blazor WASM × Code-first gRPC で始める C# ⼤統⼀理論
sansantech
PRO
1
290
株式会社EventHub・エンジニア採用資料
eventhub
0
2.1k
QA経験のないエンジニアリング マネージャーがQAのカジュアル面談に出て 苦労していること・気づいたこと / scrum fest niigata 2024
yoshikiiida
2
660
AWS アーキテクチャ作図入門/aws-architecture-diagram-101
ma2shita
16
6.7k
Oracle Base Database Service 技術詳細
oracle4engineer
PRO
5
38k
エムスリーQAチーム紹介資料 / Introduction of M3 QA Team
m3_engineering
1
320
ハードウェアを動かすTypeScriptの世界
9wick
3
1.2k
PhpStorm超絶技巧40分集中講義 #phpconkagawa
yusuke
4
760
データ基盤を支える技術
chanyou0311
5
2.9k
データ分析力を高めるSQL研修サービス『SQL Everyone』
hikarut
1
390
【TSkaigi】2024/05/11 当日スライド
kimitashoichi
14
3.9k
Featured
See All Featured
Robots, Beer and Maslow
schacon
PRO
155
8k
Principles of Awesome APIs and How to Build Them.
keavy
121
16k
Bash Introduction
62gerente
605
210k
Art, The Web, and Tiny UX
lynnandtonic
290
19k
Designing Experiences People Love
moore
136
23k
Music & Morning Musume
bryan
41
5.6k
4 Signs Your Business is Dying
shpigford
176
21k
Navigating Team Friction
lara
179
13k
Optimising Largest Contentful Paint
csswizardry
13
2.4k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
22
1.4k
Building Better People: How to give real-time feedback that sticks.
wjessup
356
18k
The Brand Is Dead. Long Live the Brand.
mthomps
49
30k
Transcript
SQL(2/3): 結合質問 ⼭本 祐輔 名古屋市⽴⼤学 データサイエンス学部
[email protected]
第5回 データベース 2024年5月20日
典型的な関係データモデル 学生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⽂を書け. 表⽰の際には都道府県名の重複は除く. 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 INTERSECTION SELECT * FROM university_student_population_top10; INTERSECTION句はテーブルAとテーブルBの 両方に含まれるレコードを返す
共通集合 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000
… R28000 兵庫県 5432000 R40000 福岡県 5124000 9件 SELECT * FROM elderly_population_top10 INTERSECTION SELECT * FROM university_student_population_top10; INTERSECTION句はテーブル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句を使って書くこと. SELECT DISTINCT 都道府県
FROM population WHERE ⼤学学⽣数 ≧ 70000 INTERSECT SELECT DISTINCT 都道府県 FROM population WHERE ⼩学校児童数 ≧ 300000; A.
結合演算 2 Join Operations
今⽇のメニュー 集合演算 + = + = 結合演算 表を縦⽅向につなぐ (レコードの数を操作する) 表を横⽅向につなぐ
(関連するレコードをつなぐ) 結合演算を使いこなすことが関係データベース操作の鍵
関係データモデル上の⼀貫性制約 結合演算 直積(交差結合) 内部結合 外部結合 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⽂を書け. 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⽂を書け. SELECT * FROM population
INNER JOIN activity USING (地域コード); A.
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が同じ⾏を探して結合 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⽂を書け. 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.都道府県; 左外部結合 外部結合は条件を満たさなかったレコードを⾒つけることが可能
Q8: 右外部結合 Q. populationテーブルとactivityテーブルを 列「地域コード」が等しいという条件で 右外部結合を⾏うSQL⽂を書け. SELECT * FROM population
RIGHT OUTER JOIN activity USING (地域コード); A.
回 実施日 トピック 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 期末試験 今後の予定 47