データベース06: SQL (3/3) 副問い合わせ

1. 副問い合わせ
2. テーブルの定義と更新

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

Transcript

1. SQL(3/3): 副問い合わせ ⼭本 祐輔 名古屋市⽴⼤学 データサイエンス学部 yusuke_yamamoto@acm.org 第6回 データベース 2024年5月27日
2. 講義ノート https://bit.ly/3xqTSds
3. 以降の説明で利⽤するデータ 独⽴⾏政法⼈統計センターが公開している 教育⽤標準データセットの基本素材SSDSE-E および市区町村SSDSE-Aから抜粋・加⼯したデータ 出典: https://www.nstac.go.jp/use/literacy/ssdse/
4. 副問い合わせ 1 Sub Query
5. Q0: 復習 Q. 2021年度と2020年度の調査結果が含まれる populationテーブルを⽤いて，都道府県別の ⼤学学⽣数の平均値を算出するSQL⽂を書け．
6. Q0: 復習 Q. 2021年度と2020年度の調査結果が含まれる populationテーブルを⽤いて，都道府県別の ⼤学学⽣数の平均値を算出するSQL⽂を書け． SELECT 地域コード, 都道府県, AVG(⼤学学⽣数) AS 平均学⽣数 FROM population GROUP BY 地域コード; A.
7. 本⽇のお題 この結果を 平均大学生数 で並び替えて 上位10位を 知りたい…
8. 結果がテーブルなら population_avgテーブル SELECT * FROM population_avg ORDER BY DESC 平均⼤学⽣数 LIMIT 10; population_avgテーブルがあればいいのに…
9. 副問い合わせ (1/2) SELECT * FROM ORDER BY DESC 平均⼤学⽣数 LIMIT 10; population_avg これを求めるSQL⽂に 置き換える SQL文の中で別のSQL文の実行結果を参照 （別のSQL⽂の結果を⼀時的に保持しておき後で参照する）
10. 副問い合わせ (2/2) SQL文の中で別のSQL文の実行結果を参照 （別のSQL⽂の結果を⼀時的に保持しておき後で参照する） SELECT * FROM ORDER BY DESC 平均⼤学⽣数 LIMIT 10; カッコで包んだ別のSQLで置き換えた ( SELECT 地域コード, 都道府県, AVG(⼤学学⽣数) AS 平均学⽣数 FROM population GROUP BY 地域コード )
11. 結果例 元のテーブルからSQL⼀発で 複雑な処理結果を得られる
12. 副問い合わせ (2/2) SQL文の中で別のSQL文の実行結果を参照 （別のSQL⽂の結果を⼀時的に保持しておき後で参照する） SELECT * FROM ORDER BY DESC 平均⼤学⽣数 LIMIT 10; ( SELECT 地域コード, 都道府県, AVG(⼤学学⽣数) AS 平均学⽣数 FROM population GROUP BY 地域コード ) SELECT⽂の中に埋め込まれると読みづらい…
13. WITH句 WITH句を使って副問い合わせを外出しできる SELECT * FROM ( SELECT 地域コード, 都道府県, AVG(⼤学学⽣数) AS 平均学⽣数 FROM population GROUP BY 地域コード ) ORDER BY DESC 平均⼤学⽣数 LIMIT 10; SELECT * FROM population_avg ORDER BY DESC 平均⼤学⽣数 LIMIT 10; = WITH句を使えば副問い合わせSQLの可読性UP WITH population_avg AS ( SELECT 地域コード, 都道府県, AVG(⼤学学⽣数) AS 平均学⽣数 FROM population GROUP BY 地域コード )
14. Q1: 副問い合わせ（1/2） Q. populationテーブルにおいて調査年度が2021時点 の⼩学校児童数上位20件のレコードを求め，それら とactivityテーブルの内容を結合した内容を表⽰す るSQL⽂を書け．
15. Q1: 副問い合わせ（1/2） Q. populationテーブルにおいて調査年度が2021時点 の⼩学校児童数上位20件のレコードを求め，それら とactivityテーブルの内容を結合した内容を表⽰す るSQL⽂を書け． WITH top20 AS ( SELECT 地域コード FROM population WHERE 調査年度 = 2021 ORDER BY ⼩学校児童数 DESC LIMIT 20 ) SELECT * FROM activity INNER JOIN top20 USING(地域コード); A.
16. 副問い合わせを使わないと対応できない問題 elderly_population_top10テーブルにおいて65歳 以上⼈⼝数の上位10件に「⼊っていない」都道府県 について，activityテーブルで過去1年以内に「旅 ⾏・⾏楽」活動をしたことのある⼈の割合が5割を 超える都道府県と割合を表⽰するSQL⽂は？ 「⼊っていない」がやっかい…
17. 副問い合わせを使わなくても対応できる問題（1/3） elderly_population_top10テーブルにおいて65歳 以上⼈⼝数の上位10件に「⼊っている」都道府県 について，activityテーブルで過去1年以内に「旅 ⾏・⾏楽」活動をしたことのある⼈の割合が5割を 超える都道府県と割合を表⽰するSQL⽂は？
18. 副問い合わせを使わなくても対応できる問題（2/3） elderly_population_top10テーブルにおいて65歳 以上⼈⼝数の上位10件に「⼊っている」都道府県 について，activityテーブルで過去1年以内に「旅 ⾏・⾏楽」活動をしたことのある⼈の割合が5割を 超える都道府県と割合を表⽰するSQL⽂は？ 2つのテーブルを内部結合したら求められそう
19. 副問い合わせを使わなくても対応できる問題（3/3） elderly_population_top10テーブルにおいて65歳 以上⼈⼝数の上位10件に「⼊っている」都道府県 について，activityテーブルで過去1年以内に「旅 ⾏・⾏楽」活動をしたことのある⼈の割合が5割を 超える都道府県と割合を表⽰するSQL⽂は？ SELECT activity.都道府県, activity.旅⾏・⾏楽 FROM activity JOIN elderly_population_top10 ON activity.都道府県 = elderly_population_top10.都道府県 AND activity.旅⾏・⾏楽 >= 50; 「⼊っていない」をどうSQLで表現するか？
20. やりたいこと activityテーブル elderly_population_top10 ⼊っているか（Not-inか）を 確認できればよい
21. IN演算⼦ & NOT IN演算⼦ SELECT * FROM activity WHERE 都道府県 IN ( “愛知県”, “岐⾩県”) ; IN演算子 リストアップした要素集合に値が含まれていればTRUE SELECT * FROM activity WHERE 都道府県 NOT IN ( “愛知県”, “岐⾩県”) ; NOT IN演算子 リストアップした要素集合に値が含まれていなければTRUE
22. 副問い合わせをWHERE句で使う (1/2) SELECT activity.都道府県, activity.旅⾏・⾏楽 FROM activity activity.都道府県 NOT IN (65歳以上⼈⼝数がTOP10の都道府県リスト) AND activity.旅⾏・⾏楽 >= 50; elderly_population_top10テーブルにおいて65歳 以上⼈⼝数の上位10件に「⼊っていない」都道府県 について，activityテーブルで過去1年以内に 「旅⾏・⾏楽」活動をしたことのある⼈の割合が 5割を超える都道府県と割合を表⽰するSQL⽂は？
23. 副問い合わせをWHERE句で使う (2/2) SELECT activity.都道府県, activity.旅⾏・⾏楽 FROM activity activity.都道府県 NOT IN ( SELECT 都道府県 FROM elderly_population_top10 ) AND activity.旅⾏・⾏楽 >= 50; elderly_population_top10テーブルにおいて65歳 以上⼈⼝数の上位10件に「⼊っていない」都道府県 について，activityテーブルで過去1年以内に 「旅⾏・⾏楽」活動をしたことのある⼈の割合が 5割を超える都道府県と割合を表⽰するSQL⽂は？ 副問い合わせ
24. Q1: 副問い合わせ（1/2） Q. populationテーブルにおいて調査年度が2021時点 の⼩学校児童数上位20件のレコードを求め，それら とactivityテーブルの内容を結合した内容を表⽰す るSQL⽂を書け．ただしJOINは使わずに書くこと．
25. Q1: 副問い合わせ（1/2） WITH top20 AS ( SELECT 地域コード FROM population WHERE 調査年度 = 2021 ORDER BY ⼩学校児童数 DESC LIMIT 20 ) SELECT * FROM activity WHERE activity.地域コード IN top20; A.
26. Q2: 副問い合わせ（2/2） Q. activityテーブルにおいて「ボランティア」の 値が上位10件以内に⼊る都道府県を求める SQL⽂を考えよ．その上で，population テーブルを⽤いて，「ボランティア」の値が 上位10件以内に⼊る都道府県の2年の調査期間 における平均総⼈⼝を求めるSQL⽂を書け．
27. Q2: 副問い合わせ（2/2） WITH top10 AS ( SELECT 地域コード FROM activity ORDER BY ボランティア DESC LIMIT 10 ) SELECT 都道府県, AVG(総⼈⼝) AS 平均総⼈⼝ FROM population JOIN top10 USING(地域コード) GROUP BY 都道府県; A.
28. これまでに学んだデータ問い合わせのためのSQL lSELECT lWHERE lORDER BY lGROUP BY l集合演算 (UNION, INTERSECTIONなど) l結合演算 (直積, INNER JOIN, OUTER JOIN) l副問い合わせ lWITH句 lAS修飾句，IN述語
29. 分析のための発展的なSQL l条件分岐 l数値，⽂字列，⽇時データ処理の関数 l⾼度な分析を⾏うためのWindow関数 画像出典：Amazon.com SQLは使えば使うほど上達する 参考書1 参考書2
30. テーブル定義と更新のためのSQL 2 SQL to define and update tables
31. データベースとテーブルの定義 CREATE DATABASE / TABLE⽂を⽤いて データベースとテーブルを定義． CREATE TABLE テーブル名 ( ); ①テーブルが持つ属性とそのデータ型, ②主キー, ③（必要なら）外部キー ④（必要なら）その他制約 CREATE DATABASE データベース名;
32. SQLでサポートしている代表的なデータ型 ⽂字列を扱う型 “⼭本祐輔” 421 10000 2024/4/29 令和6年4⽉29⽇ INTEGER型 VARCHAR型 DATETIME型 “3.141592” 整数を扱う型 421.0 -3.141592 FLOAT型 実数を扱う型 TRUE FALSE BOOLEAN型 真偽値を扱う型 ⽇付・時間を 統⼀的に扱う型 …
33. populationテーブルの場合 CREATE TABLE population ( 地域コード VARCHAR(10), 都道府県 VARCHAR(5), 調査年度 INT, 総⼈⼝ INT, … PRIMARY KEY (地域コード, 調査年度) ); 属性と そのデータ型を定義 主キーを定義 RDBMSによって微妙に異なるのでドキュメントを⾒よう
34. レコードの追加 INSERT⽂を⽤いる INSERT INTO テーブル名 ( 属性1, 属性2, …, 属性n ) VALUES (属性1の値, …, 属性nの値), (属性1の値, …, 属性nの値), …; 1⾏1⾏が追加する レコードに対応 ここに並べた列名に対応した値 をVALUES以下のカッコ内に書く
35. レコード追加の例 INSERT INTO population ( 地域コード, 都道府県, 調査年度, 総⼈⼝, ⼩学校児童数, 中学校⽣徒数, ⾼等学校⽣徒数, ⼤学学⽣数 ) VALUES ('R01000', '北海道', '2022', xxx, …, xxx), ('R01000', '北海道', '2023', xxx, …, xxx);
36. レコードの削除 構⽂ DELETE FROM テーブル名 WHERE 削除条件; DELETE FROM population WHERE 調査年度 = 2022 OR 調査年度 = 2023; 例
37. レコードの修正 UPDATE テーブル名 SET 更新操作 WHERE 更新を⾏うデータの絞り込み条件; 構⽂ UPDATE population SET 総⼈⼝ = 総⼈⼝ + 100 WHERE 調査年度 = 2022; 例 SQL⽂はドキュメントを⾒ながら書ければOK
38. 回 実施日 トピック 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 期末試験 今後の予定 38