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
540
データベース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
610
データベース11: 正規化(1/2) - 望ましくない関係スキーマ
trycycle
0
590
データベース10: 拡張実体関連モデル
trycycle
0
620
データベース09: 実体関連モデル上の一貫性制約
trycycle
0
590
データベース08: 実体関連モデルとは?
trycycle
0
570
データベース14: B+木 & ハッシュ索引
trycycle
0
280
データベース15: ビッグデータ時代のデータベース
trycycle
0
230
データベース06: SQL (3/3) 副問い合わせ
trycycle
0
420
データベース04: SQL (1/3) 単純質問 & 集約演算
trycycle
0
610
Other Decks in Technology
See All in Technology
設計を積み重ねてシステムを刷新する
sansantech
PRO
0
180
開発組織を進化させる!AWSで実践するチームトポロジー
iwamot
2
460
Cracking the Coding Interview 6th Edition
gdplabs
14
28k
30→150人のエンジニア組織拡大に伴うアジャイル文化を醸成する役割と取り組みの変化
nagata03
0
200
JAWS DAYS 2025 アーキテクチャ道場 事前説明会 / JAWS DAYS 2025 briefing document
naospon
0
2.5k
アジャイルな開発チームでテスト戦略の話は誰がする? / Who Talks About Test Strategy?
ak1210
1
660
データベースの負荷を紐解く/untangle-the-database-load
emiki
2
540
ウォンテッドリーのデータパイプラインを支える ETL のための analytics, rds-exporter / analytics, rds-exporter for ETL to support Wantedly's data pipeline
unblee
0
140
株式会社Awarefy(アウェアファイ)会社説明資料 / Awarefy-Company-Deck
awarefy
3
11k
DeepSeekとは?何がいいの? - Databricksと学ぶDeepSeek! 〜これからのLLMに備えよ!〜
taka_aki
1
150
4th place solution Eedi - Mining Misconceptions in Mathematics
rist
0
150
開発者のための FinOps/FinOps for Engineers
oracle4engineer
PRO
1
160
Featured
See All Featured
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
114
50k
The Invisible Side of Design
smashingmag
299
50k
A Modern Web Designer's Workflow
chriscoyier
693
190k
10 Git Anti Patterns You Should be Aware of
lemiorhan
PRO
656
59k
Embracing the Ebb and Flow
colly
84
4.6k
We Have a Design System, Now What?
morganepeng
51
7.4k
Optimizing for Happiness
mojombo
376
70k
How GitHub (no longer) Works
holman
314
140k
Designing Experiences People Love
moore
140
23k
How to Ace a Technical Interview
jacobian
276
23k
GitHub's CSS Performance
jonrohan
1030
460k
The Cost Of JavaScript in 2023
addyosmani
47
7.4k
Transcript
SQL(2/3): 結合質問 ⼭本 祐輔 名古屋市⽴⼤学 データサイエンス学部 yusuke_yamamoto@acm.org 第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