データベース04: SQL (1/3) 単純質問 & 集約演算

1. SQLとは？
2. Google Colaboratory
3. 単純質問
4. 集約演算

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

Transcript

1. SQL(1/3): 単純質問 ⼭本 祐輔 名古屋市⽴⼤学 データサイエンス研究科 yusuke_yamamoto@acm.org 第4回 データベース
2. 講義ノート https://bit.ly/3xqTSds
3. SQLとは? 1 What is SQL?
4. SQLとは？(1/2) - 関係データベースを操作・制御するための⾔語 - （⽅⾔はあるが）ISOによって国際的に標準化されている データベース SQL(Structured Query Language) データベースに対する操作・問い合わせ要求 クエリ ユーザ SQLで書かれたクエリ 結果
5. SQLとプログラミング⾔語との関係 SQL Python R C++ Java Javascript データベース⾔語 プログラミング⾔語 汎⽤プログラミング⾔語 統計特化 難しい 容易 ＋ SQLとプログラミング⾔語1つを使えるようになろう
6. SQLとは？(2/2) SELECT * FROM 購買履歴 WHERE 店舗 = “A店” AND ⽇時 BETWEEN X AND Y ORDER BY 顧客ID; • データベースの操作に特化した⾔語 • 英語の命令⽂に似た書き⽅
7. SQLでできること データの定義 データの操作 データの制御 テーブルや索引などの定義 データの登録，読み出し，更新，削除 データベースへのアクセス権の管理 それぞれの頭⽂字を とってCRUDと呼ばれる 本講義では「読み出し」のためのSQLを中⼼に学ぶ
8. SQLが実装された様々な関係データベース管理システム 商⽤ オープンソース 本番環境 テスト環境/組込 本授業で使うのはコレ
9. SQLと関係データモデルの⽐較 関係 タプル 属性 ドメイン テーブル レコード(or ⾏) カラム(or 列) データ型 関係データモデル SQL
10. SQLの基本 2 Introduction to SQL
11. 基本形 特定のテーブルから特定の条件を満たす⾏を⾒つけ 該当する⾏の特定の列の値を表⽰する SELECT 列名 FROM テーブル名 WHERE 条件;
12. 基本形 特定のテーブルから特定の条件を満たす⾏を⾒つけ 該当する⾏の特定の列の値を表⽰する セミコロンを忘れない!! SELECT 列名 FROM テーブル名 WHERE 条件;
13. 「都道府県」テーブルから列「⼈⼝」の値が 500万以上の⾏を⾒つけ，その「都道府県名」 を表⽰して 例 SELECT 都道府県名 FROM 都道府県 WHERE ⼈⼝ ≧ 5000000;
14. 以降の説明で利⽤するデータ 独⽴⾏政法⼈統計センターが公開している 教育⽤標準データセット（SSDSE）の 基本素材SSDSE-Eから抜粋・加⼯したデータ 出典: https://www.nstac.go.jp/use/literacy/ssdse/#SSDSE-E データをダウンロードしてを触ってみよう
15. Google Colaboratoryとは？ l ブラウザで実⾏できるPython(& R)の実⾏環境 l Googleのインフラ上で対話的にPythonを実⾏できる
16. クリックして “Colab”をクリック
17. Editをクリック Clear all outputs をクリック
18. セル：コードを書く箇所 セル セル
19. コードの実⾏ セル左端の再⽣ボタンをクリックすると， そのセルのコードを実⾏できる
20. 新規セルの作り⽅ 1. 画⾯左上の「+コード」をクリック 2. 空のセルが追加される
21. SQLの実⾏ 1. 読み込むデータベースを設定 2. セルの冒頭に“%%sql”と書く． 1⾏空けて、その下にSQL⽂を書いて実⾏
22. 実⾏したいセルを選択 「再⽣」ボタンをクリックして （もしくはshift+enterで）セルを実⾏
23. このセルがあるところまですべてのセルを実⾏ %%sqlと書かれた箇所がGoogle Colab上で SQLを実⾏できる特殊なセルです
24. 単純質問のSQL 3 SQL for querying a single table
25. 射影（1/2） SELECT * FROM population; 地域コード 都道府県 調査年度 総人口 … R01000 北海道 2021 5183000 … R02000 青森県 2021 1221000 … … R04700 沖縄県 2020 1467480 … テーブル：population アスタリスクはすべての属性 指定したテーブルに含まれるレコードについて 指定した列の値を表示する
26. 射影（2/2） SELECT 都道府県, 調査年度, 総⼈⼝ FROM population; テーブル：population 指定したテーブルに含まれるレコードについて 指定した列の値を表示する 地域コード 都道府県 調査年度 総人口 … R01000 北海道 2021 5183000 … R02000 青森県 2021 1221000 … … R04700 沖縄県 2020 1467480 …
27. 射影（2/2） SELECT 都道府県, 調査年度, 総⼈⼝ FROM population; 都道府県 調査年度 総人口 北海道 2021 5183000 青森県 2021 1221000 沖縄県 2020 1467480 テーブル：population 指定したテーブルに含まれるレコードについて 指定した列の値を表示する
28. レコード数の絞り込み SELECT * FROM population LIMIT 3; 地域コード 都道府県 調査年度 総人口 … R01000 北海道 2021 5183000 … R02000 青森県 2021 1221000 … R03000 岩手県 2021 1196000 … テーブル：population LIMIT句は表示するレコード数を絞り込む レコード表⽰数を 先頭からN件に絞る 3件
29. Q1: 射影 Q. 「都道府県」「調査年度」「⼤学学⽣数」 フィールドに限定して，populationテーブル のレコードを表⽰するSQL⽂を書け． なお，表⽰するレコード数は30件とせよ．
30. Q1: 射影 Q. 「都道府県」「調査年度」「⼤学学⽣数」 フィールドに限定して，populationテーブル のレコードを表⽰するSQL⽂を書け． なお，表⽰するレコード数は30件とせよ． SELECT 都道府県, 調査年度, ⼤学⽣数 FROM population LIMIT 30; A.
31. 選択 SELECT * FROM population WHERE 総⼈⼝ ≧ 7500000; WHERE句は条件にマッチするレコードを絞り込む 地域コード 都道府県 調査年度 総人口 … R01300 東京都 2021 14010000 … R01400 神奈川県 2021 9236000 … R02300 愛知県 2021 7517000 … … テーブル：population ⼈⼝が750万以上 のレコードに絞り込む
32. ⽂字列の扱い SELECT * FROM population WHERE 都道府県 = “京都府”; 文字列を条件に指定する時はダブルクォーツを使う 地域コード 都道府県 調査年度 総人口 … R02600 京都府 2021 2561000 … R02600 京都府 2020 2578087 … テーブル：population ダブルクォーツで囲む ダブルクォーツで囲まないと⽂字列と認識されない
33. Q2: 選択 (1/3) Q. populationテーブル内のレコードのうち， 都道府県が愛知県であるものを表⽰する SQL⽂を書け．
34. Q2: 選択 (1/3) Q. populationテーブル内のレコードのうち， 都道府県が愛知県であるものを表⽰する SQL⽂を書け． SELECT * FROM population WHERE 都道府県 = “愛知県”; A.
35. 部分⽂字列のマッチング SELECT * FROM population WHERE 都道府県 LIKE “%京都%”; LIKE句を使うと部分文字列判定が可能 地域コード 都道府県 調査年度 総人口 … R01300 東京都 2021 14010000 … R02600 京都府 2021 2561000 … R01300 東京都 2020 14047594 … R02600 京都府 2020 2578087 … テーブル：population パーセント記号は0⽂字以上の任意の⽂字
36. WHERE句の中で使える演算⼦の例 演算子 意味 A = B AはBである A != B AはBでない A > B AはBより大きい A >= B AはB以上 X BETWEEN A AND B XはA以上B以下 A IN (B, C, D) AはB,C，Dのいずれか A LIKE ‘xxx%’ Aはxxxから始まる文字列 A AND B AかつB A OR B AまたはB 参考：https://www.sejuku.net/blog/51583 四則演算⼦はどこでも使える
37. 複数条件の指定 SELECT 都道府県 FROM population WHERE 総⼈⼝ ≧ 1000000 AND ⼤学学⽣数 > ⾼等学校⽣徒数 ; 論理演算子AND/ORで複数条件指定が可能 都道府県 東京都 京都府 大阪府 … テーブル：population ( ) ( ) 丸括弧で条件を 明確化できる
38. Q3: 選択 (2/3) Q. populationテーブル内のレコードのうち § 総⼈⼝が300万⼈未満，かつ § 都道府県名が県で終わる，かつ § 中学校⽣徒数が⾼等学校⽣徒数以下 のレコードを求める表⽰するSQL⽂を書け．
39. Q3: 選択 (2/3) Q. populationテーブル内のレコードのうち § 総⼈⼝が300万⼈未満，かつ § 都道府県名が県で終わる，かつ § 中学校⽣徒数が⾼等学校⽣徒数以下 のレコードを求める表⽰するSQL⽂を書け． SELECT * FROM population WHERE 総⼈⼝ < 3000000 AND 都道府県 LIKE “%県” AND 中学校⽣徒数 ≦ ⾼等学校⽣徒数; A.
40. コラム: SQLでは改⾏は無視される SELECT 都道府県 FROM population WHERE 総⼈⼝ ≧ 1000000 AND ⼤学学⽣数 > ⾼等学校⽣徒数; SELECT 都道府県 FROM population WHERE (総⼈⼝ ≧ 1000000) AND (⼤学学⽣数 > ⾼等学校⽣徒数); = ⾃分・他⼈にとって可読性の⾼いコードを書こう
41. Q3: 選択 (2/3) Q. populationテーブル内のレコードのうち § 総⼈⼝が300万⼈未満，かつ § 都道府県名が県で終わる，かつ § 中学校⽣徒数が⾼等学校⽣徒数以下 のレコードを求める表⽰するSQL⽂を書け．
42. Q3: 選択 (2/3) Q. populationテーブル内のレコードのうち § 総⼈⼝が300万⼈未満，かつ § 都道府県名が県で終わる，かつ § 中学校⽣徒数が⾼等学校⽣徒数以下 のレコードを求める表⽰するSQL⽂を書け． SELECT * FROM population WHERE 総⼈⼝ < 3000000 AND 都道府県 LIKE “%県” AND 中学校⽣徒数 ≦ ⾼等学校⽣徒数; A.
43. コラム: 重複を許してしまうSQL SQLは関係データモデルを扱うにも関わらず 問い合わせ結果に重複を許す SELECT 都道府県 FROM population WHERE (総⼈⼝ ≧ 1000000) AND (⼤学学⽣数 > ⾼等学校⽣徒数); 都道府県 東京都 京都府 大阪府 東京都 京都府 大阪府 SQLの仕様として覚えておく
44. DISTINCT修飾句 DISTINCT句は問い合わせ結果から 重複レコードを除外する SELECT DISTINCT 都道府県 FROM population WHERE (総⼈⼝ ≧ 1000000) AND (⼤学学⽣数 > ⾼等学校⽣徒数); 都道府県 東京都 京都府 大阪府
45. Q4: 選択 (3/3) Q. Q3のSQL⽂を修正して，Q3の条件を満たす 都道府県を表⽰するSQL⽂を書け． ただし， 出⼒される都道府県名に重複があってはなら ない．
46. Q4: 選択 (3/3) Q. Q3のSQL⽂を修正して，Q3の条件を満たす 都道府県を表⽰するSQL⽂を書け． ただし， 出⼒される都道府県名に重複があってはなら ない． SELECT DISTINCT 都道府県 FROM population WHERE 総⼈⼝ < 3000000 AND 都道府県 LIKE “%県” AND 中学校⽣徒数 ≦ ⾼等学校⽣徒数; A.
47. 整列（1/2） SELECT * FROM population ORDER BY 総⼈⼝ LIMIT 10; ORDER BY句は指定した列の値でレコードをソート 地域コード 都道府県 調査年度 総人口 … R03100 鳥取県 2021 549000 … R03100 鳥取県 2020 553407 … R03200 島根県 2021 665000 … … テーブル：population 総⼈⼝の昇順で レコードを並び替える 昇 順
48. 整列（2/2） SELECT * FROM population ORDER BY 総⼈⼝ DESC LIMIT 10; 降順（大きい順）にソートする時はDESCを指定 地域コード 都道府県 調査年度 総人口 … R01300 東京都 2020 14047594 … R01300 東京都 2021 14010000 … R01400 神奈川県 2020 9237337 … … テーブル：population 総⼈⼝の降順で レコードを並び替える 降 順
49. Q5: ソート (1/2) Q. populationテーブル内のレコードのうち調査 年度が2021のものについて，⼤学学⽣数順の ⼤きいもの順に並び替えて表⽰するSQL⽂を 書け．
50. Q5: ソート (1/2) Q. populationテーブル内のレコードのうち調査 年度が2021のものについて，⼤学学⽣数順の ⼤きいもの順に並び替えて表⽰するSQL⽂を 書け． SELECT * FROM population WHERE 調査年度 = 2021 ORDER BY ⼤学学⽣数 DESC; A.
51. Q6: ソート (2/2) Q. populationテーブル内のレコードのうち調査 年度が2021のものについて，総⼈⼝に占める ⼤学学⽣数の割合が⼤きいもの順に並び替え て表⽰するSQL⽂を書け． その際，総⼈⼝に 占める⼤学学⽣数の割合も合わせて表⽰せよ．
52. Q6: ソート (2/2) Q. populationテーブル内のレコードのうち調査 年度が2021のものについて，総⼈⼝に占める ⼤学学⽣数の割合が⼤きいもの順に並び替え て表⽰するSQL⽂を書け． その際，総⼈⼝に 占める⼤学学⽣数の割合も合わせて表⽰せよ． SELECT *, ⼤学学⽣数 / 総⼈⼝ AS ⼤学⽣割合 FROM population WHERE 調査年度 = 2021 ORDER BY ⼤学学⽣数 / 総⼈⼝ DESC; A.
53. 集約演算のSQL 4 SQL for aggregating data
54. 都道府県 年度 総人口 北海道 2021 5183000 青森県 2021 1221000 … 北海道 2020 5524614 青森県 2020 1237984 … 集約演算とは？ 購買ID 売上 T1 1400 T2 1340 T3 4980 … … T1500 2850 年度毎に ⼈⼝を合計したい 売上の平均 を求めたい 全レコードあるいはグループ化されたレコード をまとめて値を算出すること
55. 代表的な集約関数 SUM(列名) AVG(列名) COUNT(*) MAX(列名) MIN(列名) 指定された列の値を合計 指定された列の最⼤値を返す 指定された列の値を平均 指定された列の最少値を返す 抽出されたレコード数を返す 抽出・グループ化されたレコード群に対して適用
56. 例1: SUM 都道府県 調査年度 総人口 … 北海道 2021 5490000 … 北海道 2020 5524614 … 青森県 2021 1221000 … 青森県 2020 1237984 … テーブル：population WHERE句で抽出した 全レコードを集約 SELECT SUM(総⼈⼝) FROM population WHERE 調査年度 = 2021; （GROUP BY句を使わない場合） 集約関数は抽出されたレコード全体を集約
57. 例1: SUM SELECT SUM(総⼈⼝) FROM population WHERE 調査年度 = 2021; （GROUP BY句を使わない場合） SUM(総人口) 125500000 集約関数は抽出されたレコード全体を集約 都道府県 調査年度 総人口 … 北海道 2021 5490000 … 北海道 2020 5524614 … 青森県 2021 1221000 … 青森県 2020 1237984 … テーブル：population
58. 都道府県 調査年度 総人口 … 北海道 2021 5490000 … 北海道 2020 5524614 … 青森県 2021 1221000 … 青森県 2020 1237984 … 例2: COUNT SELECT COUNT(都道府県) FROM population; テーブル：population 都道府県の列にある レコード数を数える = 94個 COUNT関数は指定された列のレコード数を返す
59. 例2: COUNT SELECT COUNT(DISTINCT 都道府県) FROM population; COUNT関数は指定された列のレコード数を返す 都道府県 調査年度 総人口 … 北海道 2021 5490000 … 北海道 2020 5524614 … 青森県 2021 1221000 … 青森県 2020 1237984 … テーブル：population 都道府県の列にある レコード数を数える = 47個 (ただし重複は除く)
60. GROUP BY（1/3） 都道府県 年度 総人口 北海道 2021 5183000 青森県 2021 1221000 … 北海道 2020 5524614 青森県 2020 1237984 … 年度が同じ値のものを グループとしてまとめる SELECT 年度, SUM(総⼈⼝) FROM population GROUP BY 年度; GROUP BY句は指定した列の値が同じもの をグループ化
61. GROUP BY（1/3） 都道府県 年度 総人口 北海道 2021 5183000 青森県 2021 1221000 … 北海道 2020 5524614 青森県 2020 1237984 … SELECT 年度, SUM(総⼈⼝) FROM population GROUP BY 年度; GROUP BY句は指定した列の値が同じもの をグループ化 年度 SUM(総人口) 2021 125500000 2020 126146099
62. GROUP BY（2/3） SELECT 年度, SUM(総⼈⼝) FROM population GROUP BY 年度; SELECTの中に GROUP BYで指定した列名を入れる
63. GROUP BY（3/3） 都道府県 年度 総人口 北海道 2021 5183000 北海道 2020 5524614 青森県 2021 1221000 青森県 2020 1237984 岩手県 2021 1196000 岩手県 2020 1210534 都道府県が同じ値のものを グループとしてまとめる SELECT 都道府県, AVG(総⼈⼝) FROM population GROUP BY 都道府県; 指定した列によってグループ化の結果は異なる
64. GROUP BY（3/3） 都道府県 年度 総人口 北海道 2021 5183000 北海道 2020 5524614 青森県 2021 1221000 青森県 2020 1237984 岩手県 2021 1196000 岩手県 2020 1210534 年度 AVG(総人口) 北海道 5203807 青森県 1229492 岩手県 1203267 … SELECT 都道府県, AVG(総⼈⼝) FROM population GROUP BY 都道府県; 指定した列によってグループ化の結果は異なる
65. Q7: GROUP BY (1/3) Q. populationテーブル内のレコードに対して都道府県 ごとの集約演算を⾏い，都道府県別に -（調査期間中の）⼤学学⽣数の平均 -（調査期間中の）⼤学学⽣数の最⼤値と最⼩値の差 を求めるSQL⽂を書け．
66. Q7: GROUP BY (1/3) Q. populationテーブル内のレコードに対して都道府県 ごとの集約演算を⾏い，都道府県別に -（調査期間中の）⼤学学⽣数の平均 -（調査期間中の）⼤学学⽣数の最⼤値と最⼩値の差 を求めるSQL⽂を書け． SELECT 都道府県, AVG(⼤学学⽣数) AS ⼤学⽣数平均, MAX(⼤学学⽣数) - MIN(⼤学学⽣数) AS 差 FROM population GROUP BY 都道府県; A.
67. Q8: GROUP BY (2/3) Q. populationテーブル内の総⼈⼝が300万⼈を超え るレコードに対して調査年度ごとの集約演算を ⾏い， 調査年度別に § 「（各地域コードにおける）⼩学校児童数と 中学校⽣徒数の合計」の平均 § 「（各地域コードにおける）⾼等学校⽣徒数 と⼤学学⽣数の差」の平均 § 「（各地域コードにおける）⾼等学校⽣徒数 と⼤学学⽣数の差」の最⼩値 § 「（各地域コードにおける）⾼等学校⽣徒数 と⼤学学⽣数の差」の最⼤値 を求めるSQL⽂を書け．
68. Q8: GROUP BY (2/3) A. SELECT 都道府県, AVG(⼩学校児童数+中学校⽣徒数) AS 義務教育⽣徒平均, AVG(⾼等学校⽣徒数+⼤学学⽣数) AS ⾼校・⼤学学⽣数平均, MAX(⼤学学⽣数-⾼等学校⽣徒数), MIN(⼤学学⽣数-⾼等学校⽣徒数) FROM population WHERE 総⼈⼝ ≧ 3000000 GROUP BY 調査年度;
69. HAVING句 SELECT 都道府県, AVG(総⼈⼝) FROM population GROUP BY 都道府県 HAVING AVG(総⼈⼝) ≧ 5000000 ; HAVING句を使うと、集約演算結果の中から 条件を満たすものだけを抽出できる 都道府県 年度 総人口 北海道 2021 5183000 北海道 2020 5524614 青森県 2021 1221000 青森県 2020 1237984 … 年度 AVG(総人口) 北海道 5203807 青森県 1229492 岩手県 1203267 … 埼玉県 7342382 千葉県 6279740 50万以下で条件満たさず
70. HAVING句 SELECT 都道府県, AVG(総⼈⼝) FROM population GROUP BY 都道府県 HAVING AVG(総⼈⼝) ≧ 5000000 ; HAVING句を使うと、集約演算結果の中から 条件を満たすものだけを抽出できる 都道府県 年度 総人口 北海道 2021 5183000 北海道 2020 5524614 青森県 2021 1221000 青森県 2020 1237984 … 年度 AVG(総人口) 北海道 5203807 埼玉県 7342382 千葉県 6279740 …
71. Q9: GROUP BY (3/3) Q. populationテーブルのレコードに対して都道府県 ごとの集約演算を⾏い， 都道府県別に § 「（調査期間中の）総⼈⼝」の平均 § 「（調査期間中の）⾼等学校⽣徒数」の平均 § 「（調査期間中の）⼤学学⽣数」の平均 § 「（調査期間中の）⾼等学校⽣徒数と⼤学学⽣数 の差」の平均 を求めるSQL⽂を書け． ただし「（調査期間中の） ⼤学学⽣数と⾼等学校⽣徒数の差」の平均が10000 以下になるものだけを表⽰せよ．
72. HAVING句と WHERE句の違い（1/2） SELECT 調査年度, SUM(総⼈⼝) FROM population GROUP BY 調査年度 HAVING SUM(総⼈⼝) >= 5000000; HAVING句はGROUP BYの後に評価される (GROUP BYの集約演算結果に対して絞り込みを⾏う) 都道府県 年度 総人口 北海道 2021 5183000 青森県 2021 1221000 … 北海道 2020 5524614 青森県 2020 1237984 … 年度 SUM(総人口) 2021 125500000 2020 126146099 どの結果も500万以上でOK 集約 関数
73. HAVING句と WHERE句の違い（2/2） SELECT 調査年度, SUM(総⼈⼝) FROM population WHERE 総⼈⼝ ≧ 5000000 GROUP BY 調査年度; WHERE句はGROUP BYの前に評価される (WHERE句で絞り込まれた結果をGROUP BYする) 都道府県 年度 総人口 北海道 2021 5183000 青森県 2021 1221000 … 北海道 2020 5524614 青森県 2020 1237984 … 年度 SUM(総人口) 2021 68923000 2020 69119106 集約 関数
74. Q9: GROUP BY (3/3) A. SELECT 都道府県, AVG(総⼈⼝) AS ⼈⼝平均, AVG(⾼等学校⽣徒数) AS ⾼校学⽣数平均, AVG(⼤学学⽣数) AS ⼤学⽣数平均, AVG(⼤学学⽣数-⾼等学校⽣徒数) FROM population GROUP BY 都道府県 HAVING ⼤学学⽣数 – ⾼等学校⽣徒数 ≧ 10000;
75. SQLの記述順序（[ ]の箇所は必要に応じて書く） SELECT 列名1, 列名2, ... FROM 参照する表1, 表2, ... [WHERE 条件] [GROUP BY 列名1, 列名2, ...] [HAVING 条件] [ORDER BY 列名1, 列名2, ...] [LIMIT 数字] ; 記述順序を間違うとエラー発⽣ セミコロンを忘れない!!
76. 回 実施日 トピック 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 期末試験 今後の予定 76