Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

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

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

Y. Yamamoto

May 13, 2024
Tweet

More Decks by Y. Yamamoto

Other Decks in Technology

Transcript

  1. SQLとは?(2/2) SELECT * FROM 購買履歴 WHERE 店舗 = “A店” AND

    ⽇時 BETWEEN X AND Y ORDER BY 顧客ID; • データベースの操作に特化した⾔語 • 英語の命令⽂に似た書き⽅
  2. 射影(1/2) SELECT * FROM population; 地域コード 都道府県 調査年度 総人口 …

    R01000 北海道 2021 5183000 … R02000 青森県 2021 1221000 … … R04700 沖縄県 2020 1467480 … テーブル:population アスタリスクはすべての属性 指定したテーブルに含まれるレコードについて 指定した列の値を表示する
  3. 射影(2/2) SELECT 都道府県, 調査年度, 総⼈⼝ FROM population; テーブル:population 指定したテーブルに含まれるレコードについて 指定した列の値を表示する

    地域コード 都道府県 調査年度 総人口 … R01000 北海道 2021 5183000 … R02000 青森県 2021 1221000 … … R04700 沖縄県 2020 1467480 …
  4. 射影(2/2) SELECT 都道府県, 調査年度, 総⼈⼝ FROM population; 都道府県 調査年度 総人口

    北海道 2021 5183000 青森県 2021 1221000 沖縄県 2020 1467480 テーブル:population 指定したテーブルに含まれるレコードについて 指定した列の値を表示する
  5. レコード数の絞り込み SELECT * FROM population LIMIT 3; 地域コード 都道府県 調査年度

    総人口 … R01000 北海道 2021 5183000 … R02000 青森県 2021 1221000 … R03000 岩手県 2021 1196000 … テーブル:population LIMIT句は表示するレコード数を絞り込む レコード表⽰数を 先頭からN件に絞る 3件
  6. 選択 SELECT * FROM population WHERE 総⼈⼝ ≧ 7500000; WHERE句は条件にマッチするレコードを絞り込む

    地域コード 都道府県 調査年度 総人口 … R01300 東京都 2021 14010000 … R01400 神奈川県 2021 9236000 … R02300 愛知県 2021 7517000 … … テーブル:population ⼈⼝が750万以上 のレコードに絞り込む
  7. ⽂字列の扱い SELECT * FROM population WHERE 都道府県 = “京都府”; 文字列を条件に指定する時はダブルクォーツを使う

    地域コード 都道府県 調査年度 総人口 … R02600 京都府 2021 2561000 … R02600 京都府 2020 2578087 … テーブル:population ダブルクォーツで囲む ダブルクォーツで囲まないと⽂字列と認識されない
  8. 部分⽂字列のマッチング SELECT * FROM population WHERE 都道府県 LIKE “%京都%”; LIKE句を使うと部分文字列判定が可能

    地域コード 都道府県 調査年度 総人口 … R01300 東京都 2021 14010000 … R02600 京都府 2021 2561000 … R01300 東京都 2020 14047594 … R02600 京都府 2020 2578087 … テーブル:population パーセント記号は0⽂字以上の任意の⽂字
  9. 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 四則演算⼦はどこでも使える
  10. 複数条件の指定 SELECT 都道府県 FROM population WHERE 総⼈⼝ ≧ 1000000 AND

    ⼤学学⽣数 > ⾼等学校⽣徒数 ; 論理演算子AND/ORで複数条件指定が可能 都道府県 東京都 京都府 大阪府 … テーブル:population ( ) ( ) 丸括弧で条件を 明確化できる
  11. Q3: 選択 (2/3) Q. populationテーブル内のレコードのうち § 総⼈⼝が300万⼈未満,かつ § 都道府県名が県で終わる,かつ §

    中学校⽣徒数が⾼等学校⽣徒数以下 のレコードを求める表⽰するSQL⽂を書け.
  12. Q3: 選択 (2/3) Q. populationテーブル内のレコードのうち § 総⼈⼝が300万⼈未満,かつ § 都道府県名が県で終わる,かつ §

    中学校⽣徒数が⾼等学校⽣徒数以下 のレコードを求める表⽰するSQL⽂を書け. SELECT * FROM population WHERE 総⼈⼝ < 3000000 AND 都道府県 LIKE “%県” AND 中学校⽣徒数 ≦ ⾼等学校⽣徒数; A.
  13. コラム: SQLでは改⾏は無視される SELECT 都道府県 FROM population WHERE 総⼈⼝ ≧ 1000000

    AND ⼤学学⽣数 > ⾼等学校⽣徒数; SELECT 都道府県 FROM population WHERE (総⼈⼝ ≧ 1000000) AND (⼤学学⽣数 > ⾼等学校⽣徒数); = ⾃分・他⼈にとって可読性の⾼いコードを書こう
  14. Q3: 選択 (2/3) Q. populationテーブル内のレコードのうち § 総⼈⼝が300万⼈未満,かつ § 都道府県名が県で終わる,かつ §

    中学校⽣徒数が⾼等学校⽣徒数以下 のレコードを求める表⽰するSQL⽂を書け.
  15. Q3: 選択 (2/3) Q. populationテーブル内のレコードのうち § 総⼈⼝が300万⼈未満,かつ § 都道府県名が県で終わる,かつ §

    中学校⽣徒数が⾼等学校⽣徒数以下 のレコードを求める表⽰するSQL⽂を書け. SELECT * FROM population WHERE 総⼈⼝ < 3000000 AND 都道府県 LIKE “%県” AND 中学校⽣徒数 ≦ ⾼等学校⽣徒数; A.
  16. コラム: 重複を許してしまうSQL SQLは関係データモデルを扱うにも関わらず 問い合わせ結果に重複を許す SELECT 都道府県 FROM population WHERE (総⼈⼝

    ≧ 1000000) AND (⼤学学⽣数 > ⾼等学校⽣徒数); 都道府県 東京都 京都府 大阪府 東京都 京都府 大阪府 SQLの仕様として覚えておく
  17. DISTINCT修飾句 DISTINCT句は問い合わせ結果から 重複レコードを除外する SELECT DISTINCT 都道府県 FROM population WHERE (総⼈⼝

    ≧ 1000000) AND (⼤学学⽣数 > ⾼等学校⽣徒数); 都道府県 東京都 京都府 大阪府
  18. Q4: 選択 (3/3) Q. Q3のSQL⽂を修正して,Q3の条件を満たす 都道府県を表⽰するSQL⽂を書け. ただし, 出⼒される都道府県名に重複があってはなら ない. SELECT

    DISTINCT 都道府県 FROM population WHERE 総⼈⼝ < 3000000 AND 都道府県 LIKE “%県” AND 中学校⽣徒数 ≦ ⾼等学校⽣徒数; A.
  19. 整列(1/2) SELECT * FROM population ORDER BY 総⼈⼝ LIMIT 10;

    ORDER BY句は指定した列の値でレコードをソート 地域コード 都道府県 調査年度 総人口 … R03100 鳥取県 2021 549000 … R03100 鳥取県 2020 553407 … R03200 島根県 2021 665000 … … テーブル:population 総⼈⼝の昇順で レコードを並び替える 昇 順
  20. 整列(2/2) SELECT * FROM population ORDER BY 総⼈⼝ DESC LIMIT

    10; 降順(大きい順)にソートする時はDESCを指定 地域コード 都道府県 調査年度 総人口 … R01300 東京都 2020 14047594 … R01300 東京都 2021 14010000 … R01400 神奈川県 2020 9237337 … … テーブル:population 総⼈⼝の降順で レコードを並び替える 降 順
  21. 都道府県 年度 総人口 北海道 2021 5183000 青森県 2021 1221000 …

    北海道 2020 5524614 青森県 2020 1237984 … 集約演算とは? 購買ID 売上 T1 1400 T2 1340 T3 4980 … … T1500 2850 年度毎に ⼈⼝を合計したい 売上の平均 を求めたい 全レコードあるいはグループ化されたレコード をまとめて値を算出すること
  22. 例1: SUM 都道府県 調査年度 総人口 … 北海道 2021 5490000 …

    北海道 2020 5524614 … 青森県 2021 1221000 … 青森県 2020 1237984 … テーブル:population WHERE句で抽出した 全レコードを集約 SELECT SUM(総⼈⼝) FROM population WHERE 調査年度 = 2021; (GROUP BY句を使わない場合) 集約関数は抽出されたレコード全体を集約
  23. 例1: SUM SELECT SUM(総⼈⼝) FROM population WHERE 調査年度 = 2021;

    (GROUP BY句を使わない場合) SUM(総人口) 125500000 集約関数は抽出されたレコード全体を集約 都道府県 調査年度 総人口 … 北海道 2021 5490000 … 北海道 2020 5524614 … 青森県 2021 1221000 … 青森県 2020 1237984 … テーブル:population
  24. 都道府県 調査年度 総人口 … 北海道 2021 5490000 … 北海道 2020

    5524614 … 青森県 2021 1221000 … 青森県 2020 1237984 … 例2: COUNT SELECT COUNT(都道府県) FROM population; テーブル:population 都道府県の列にある レコード数を数える = 94個 COUNT関数は指定された列のレコード数を返す
  25. 例2: COUNT SELECT COUNT(DISTINCT 都道府県) FROM population; COUNT関数は指定された列のレコード数を返す 都道府県 調査年度

    総人口 … 北海道 2021 5490000 … 北海道 2020 5524614 … 青森県 2021 1221000 … 青森県 2020 1237984 … テーブル:population 都道府県の列にある レコード数を数える = 47個 (ただし重複は除く)
  26. GROUP BY(1/3) 都道府県 年度 総人口 北海道 2021 5183000 青森県 2021

    1221000 … 北海道 2020 5524614 青森県 2020 1237984 … 年度が同じ値のものを グループとしてまとめる SELECT 年度, SUM(総⼈⼝) FROM population GROUP BY 年度; GROUP BY句は指定した列の値が同じもの をグループ化
  27. 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
  28. GROUP BY(2/3) SELECT 年度, SUM(総⼈⼝) FROM population GROUP BY 年度;

    SELECTの中に GROUP BYで指定した列名を入れる
  29. GROUP BY(3/3) 都道府県 年度 総人口 北海道 2021 5183000 北海道 2020

    5524614 青森県 2021 1221000 青森県 2020 1237984 岩手県 2021 1196000 岩手県 2020 1210534 都道府県が同じ値のものを グループとしてまとめる SELECT 都道府県, AVG(総⼈⼝) FROM population GROUP BY 都道府県; 指定した列によってグループ化の結果は異なる
  30. 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 都道府県; 指定した列によってグループ化の結果は異なる
  31. Q8: GROUP BY (2/3) Q. populationテーブル内の総⼈⼝が300万⼈を超え るレコードに対して調査年度ごとの集約演算を ⾏い, 調査年度別に §

    「(各地域コードにおける)⼩学校児童数と 中学校⽣徒数の合計」の平均 § 「(各地域コードにおける)⾼等学校⽣徒数 と⼤学学⽣数の差」の平均 § 「(各地域コードにおける)⾼等学校⽣徒数 と⼤学学⽣数の差」の最⼩値 § 「(各地域コードにおける)⾼等学校⽣徒数 と⼤学学⽣数の差」の最⼤値 を求めるSQL⽂を書け.
  32. Q8: GROUP BY (2/3) A. SELECT 都道府県, AVG(⼩学校児童数+中学校⽣徒数) AS 義務教育⽣徒平均,

    AVG(⾼等学校⽣徒数+⼤学学⽣数) AS ⾼校・⼤学学⽣数平均, MAX(⼤学学⽣数-⾼等学校⽣徒数), MIN(⼤学学⽣数-⾼等学校⽣徒数) FROM population WHERE 総⼈⼝ ≧ 3000000 GROUP BY 調査年度;
  33. 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万以下で条件満たさず
  34. HAVING句 SELECT 都道府県, AVG(総⼈⼝) FROM population GROUP BY 都道府県 HAVING

    AVG(総⼈⼝) ≧ 5000000 ; HAVING句を使うと、集約演算結果の中から 条件を満たすものだけを抽出できる 都道府県 年度 総人口 北海道 2021 5183000 北海道 2020 5524614 青森県 2021 1221000 青森県 2020 1237984 … 年度 AVG(総人口) 北海道 5203807 埼玉県 7342382 千葉県 6279740 …
  35. Q9: GROUP BY (3/3) Q. populationテーブルのレコードに対して都道府県 ごとの集約演算を⾏い, 都道府県別に § 「(調査期間中の)総⼈⼝」の平均

    § 「(調査期間中の)⾼等学校⽣徒数」の平均 § 「(調査期間中の)⼤学学⽣数」の平均 § 「(調査期間中の)⾼等学校⽣徒数と⼤学学⽣数 の差」の平均 を求めるSQL⽂を書け. ただし「(調査期間中の) ⼤学学⽣数と⾼等学校⽣徒数の差」の平均が10000 以下になるものだけを表⽰せよ.
  36. 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 集約 関数
  37. 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 集約 関数
  38. Q9: GROUP BY (3/3) A. SELECT 都道府県, AVG(総⼈⼝) AS ⼈⼝平均,

    AVG(⾼等学校⽣徒数) AS ⾼校学⽣数平均, AVG(⼤学学⽣数) AS ⼤学⽣数平均, AVG(⼤学学⽣数-⾼等学校⽣徒数) FROM population GROUP BY 都道府県 HAVING ⼤学学⽣数 – ⾼等学校⽣徒数 ≧ 10000;
  39. SQLの記述順序([ ]の箇所は必要に応じて書く) SELECT 列名1, 列名2, ... FROM 参照する表1, 表2, ...

    [WHERE 条件] [GROUP BY 列名1, 列名2, ...] [HAVING 条件] [ORDER BY 列名1, 列名2, ...] [LIMIT 数字] ; 記述順序を間違うとエラー発⽣ セミコロンを忘れない!!
  40. 回 実施日 トピック 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 期末試験 今後の予定 76