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
データベース04: SQL (1/3) 単純質問 & 集約演算
Search
Y. Yamamoto
April 27, 2024
Technology
0
110
データベース04: SQL (1/3) 単純質問 & 集約演算
1. SQLとは?
2. Google Colaboratory
3. 単純質問
4. 集約演算
Y. Yamamoto
April 27, 2024
Tweet
Share
More Decks by Y. Yamamoto
See All by Y. Yamamoto
データベース06: SQL (3/3) 副問い合わせ
trycycle
0
27
データベース03: 関係データモデル
trycycle
0
130
データベース05: SQL(2/3) 結合質問
trycycle
0
27
データベース02: データベースの概念
trycycle
0
200
データベース01: データベースを使わない世界
trycycle
1
140
ビッグデータ × AI = DX?
trycycle
0
91
名古屋市立大学データサイエンス学部 秋のオープンキャンパス模擬授業20231111
trycycle
0
1.6k
データマイニングと機械学習 - ニューラルネットワーク
trycycle
0
370
データマイニングと機械学習-SVM
trycycle
1
390
Other Decks in Technology
See All in Technology
Babylon.jsと色々なものを組み合わせる:ブラウザのAPIやガジェットや2D描画ライブラリなど / Babylon.js 勉強会 vol.3
you
PRO
0
200
大規模言語モデル (LLM)における低精度数値表現
pfn
PRO
3
260
Amplify 🩷 Bedrock 〜生成AI入門〜
minorun365
PRO
10
1.2k
20240509 CloudWatch でいろいろなものを監視してみよう
masaruogura
1
110
DevRelによる信頼構築とデータ駆動で変わるエンジニア採用 / DevRel Trust Building to Data Driven Engineering Hiring
bobtani
1
120
本番環境で Cloudflareを 使ってみた話
miu_crescent
2
110
2024春 注目のWeb系 OSS & SaaS 3選
makies
0
210
Observabilityジャーニーを実現するためのAWSサービス:CloudWatch編
o11yfes2023
0
110
社内での継続的な機械学習勉強会の開催のコツ
yudai00
2
330
【リラン】AIの光と闇?失敗しないために知っておきたいAIリスクとその対応 ①政府の動き編
tkhresk
0
120
M5stackで使用できるpHセンサの開発
shinrinakamura
1
300
今日からできる!簡単 .NET 高速化 Tips -2024 edition-
xin9le
8
5.1k
Featured
See All Featured
Code Reviewing Like a Champion
maltzj
515
39k
Building Flexible Design Systems
yeseniaperezcruz
320
37k
Fireside Chat
paigeccino
22
2.7k
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
155
14k
StorybookのUI Testing Handbookを読んだ
zakiyama
13
4.6k
Templates, Plugins, & Blocks: Oh My! Creating the theme that thinks of everything
marktimemedia
20
1.8k
The Mythical Team-Month
searls
217
42k
What's new in Ruby 2.0
geeforr
337
31k
Designing for humans not robots
tammielis
247
25k
I Don’t Have Time: Getting Over the Fear to Launch Your Podcast
jcasabona
23
1.6k
GraphQLの誤解/rethinking-graphql
sonatard
56
9.3k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
275
13k
Transcript
SQL(1/3): 単純質問 ⼭本 祐輔 名古屋市⽴⼤学 データサイエンス学部
[email protected]
第4回 データベース 2024年5月13日
SQLとは? 1 What is SQL?
SQLとは?(1/2) - 関係データベースを操作・制御するための⾔語 - (⽅⾔はあるが)ISOによって国際的に標準化されている データベース SQL(Structured Query Language) データベースに対する操作・問い合わせ要求
クエリ ユーザ SQLで書かれたクエリ 結果
SQLとは?(2/2) SELECT * FROM 購買履歴 WHERE 店舗 = “A店” AND
⽇時 BETWEEN X AND Y ORDER BY 顧客ID; • データベースの操作に特化した⾔語 • 英語の命令⽂に似た書き⽅
SQLでできること データの定義 データの操作 データの制御 テーブルや索引などの定義 データの登録,読み出し,更新,削除 データベースへのアクセス権の管理 それぞれの頭⽂字を とってCRUDと呼ばれる 本講義では「読み出し」のためのSQLを中⼼に学ぶ
SQLが実装された様々な関係データベース管理システム 商⽤ オープンソース 本番環境 テスト環境/組込 本授業で使うのはコレ
SQLと関係データモデルの⽐較 関係 タプル 属性 ドメイン テーブル レコード(or ⾏) カラム(or 列)
データ型 関係データモデル SQL
SQLの基本 2 Introduction to SQL
基本形 特定のテーブルから特定の条件を満たす⾏を⾒つけ 該当する⾏の特定の列の値を表⽰する SELECT 列名 FROM テーブル名 WHERE 条件;
基本形 特定のテーブルから特定の条件を満たす⾏を⾒つけ 該当する⾏の特定の列の値を表⽰する セミコロンを忘れない!! SELECT 列名 FROM テーブル名 WHERE 条件;
「都道府県」テーブルから列「⼈⼝」の値が 500万以上の⾏を⾒つけ,その「都道府県名」 を表⽰して 例 SELECT 都道府県名 FROM 都道府県 WHERE ⼈⼝
≧ 5000000;
以降の説明で利⽤するデータ 独⽴⾏政法⼈統計センターが公開している 教育⽤標準データセット(SSDSE)の 基本素材SSDSE-Eから抜粋・加⼯したデータ 出典: https://www.nstac.go.jp/use/literacy/ssdse/#SSDSE-E データをダウンロードしてを触ってみよう
Google Colaboratoryとは? l ブラウザで実⾏できるPython(& R)の実⾏環境 l Googleのインフラ上で対話的にPythonを実⾏できる
クリックして “Colab”をクリック
Editをクリック Clear all outputs をクリック
セル:コードを書く箇所 セル セル
コードの実⾏ セル左端の再⽣ボタンをクリックすると, そのセルのコードを実⾏できる
新規セルの作り⽅ 1. 画⾯左上の「+コード」をクリック 2. 空のセルが追加される
SQLの実⾏ 1. 読み込むデータベースを設定 2. セルの冒頭に“%%sql”と書く. 1⾏空けて、その下にSQL⽂を書いて実⾏
実⾏したいセルを選択 「再⽣」ボタンをクリックして (もしくはshift+enterで)セルを実⾏
このセルがあるところまですべてのセルを実⾏ %%sqlと書かれた箇所がGoogle Colab上で SQLを実⾏できる特殊なセルです
単純質問のSQL 3 SQL for querying a single table
射影(1/2) SELECT * FROM population; 地域コード 都道府県 調査年度 総人口 …
R01000 北海道 2021 5183000 … R02000 青森県 2021 1221000 … … R04700 沖縄県 2020 1467480 … テーブル:population アスタリスクはすべての属性 指定したテーブルに含まれるレコードについて 指定した列の値を表示する
射影(2/2) SELECT 都道府県, 調査年度, 総⼈⼝ FROM population; 都道府県 調査年度 総人口
北海道 2021 5183000 青森県 2021 1221000 沖縄県 2020 1467480 テーブル:population 指定したテーブルに含まれるレコードについて 指定した列の値を表示する
レコード数の絞り込み SELECT * FROM population LIMIT 3; 地域コード 都道府県 調査年度
総人口 … R01000 北海道 2021 5183000 … R02000 青森県 2021 1221000 … R03000 岩手県 2021 1196000 … テーブル:population LIMIT句は表示するレコード数を絞り込む レコード表⽰数を 先頭からN件に絞る 3件
Q1: 射影 Q. 「都道府県」「調査年度」「⼤学学⽣数」 フィールドに限定して,populationテーブル のレコードを表⽰するSQL⽂を書け. なお,表⽰するレコード数は30件とせよ. SELECT 都道府県, 調査年度,
⼤学⽣数 FROM population LIMIT 30; A.
選択 SELECT * FROM population WHERE 総⼈⼝ ≧ 7500000; WHERE句は条件にマッチするレコードを絞り込む
地域コード 都道府県 調査年度 総人口 … R01300 東京都 2021 14010000 … R01400 神奈川県 2021 9236000 … R02300 愛知県 2021 7517000 … … テーブル:population ⼈⼝が750万以上 のレコードに絞り込む
⽂字列の扱い SELECT * FROM population WHERE 都道府県 = “京都府”; 文字列を条件に指定する時はダブルクォーツを使う
地域コード 都道府県 調査年度 総人口 … R02600 京都府 2021 2561000 … R02600 京都府 2020 2578087 … テーブル:population ダブルクォーツで囲む ダブルクォーツで囲まないと⽂字列と認識されない
Q2: 選択 (1/3) Q. populationテーブル内のレコードのうち, 都道府県が愛知県で調査年度が2020である ものを表⽰するSQL⽂を書け. SELECT * FROM
population WHERE 都道府県 = “愛知県” AND 調査年度 = 2020; A.
部分⽂字列のマッチング SELECT * FROM population WHERE 都道府県 LIKE “%京都%”; LIKE句を使うと部分文字列判定が可能
地域コード 都道府県 調査年度 総人口 … R01300 東京都 2021 14010000 … R02600 京都府 2021 2561000 … R01300 東京都 2020 14047594 … R02600 京都府 2020 2578087 … テーブル:population パーセント記号は0⽂字以上の任意の⽂字
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 四則演算⼦はどこでも使える
複数条件の指定 SELECT 都道府県 FROM population WHERE 総⼈⼝ ≧ 1000000 AND
⼤学学⽣数 > ⾼等学校⽣徒数 ; 論理演算子AND/ORで複数条件指定が可能 都道府県 東京都 京都府 大阪府 … テーブル:population ( ) ( ) 丸括弧で条件を 明確化できる
Q3: 選択 (2/3) Q. populationテーブル内のレコードのうち § 総⼈⼝が300万⼈未満,かつ § 都道府県名が県で終わる,かつ §
中学校⽣徒数が⾼等学校⽣徒数以下 のレコードを求める表⽰するSQL⽂を書け. SELECT * FROM population WHERE 総⼈⼝ < 3000000 AND 都道府県 LIKE “%県” AND 中学校⽣徒数 ≦ ⾼等学校⽣徒数; A.
コラム: SQLでは改⾏は無視される SELECT 都道府県 FROM population WHERE 総⼈⼝ ≧ 1000000
AND ⼤学学⽣数 > ⾼等学校⽣徒数; SELECT 都道府県 FROM population WHERE (総⼈⼝ ≧ 1000000) AND (⼤学学⽣数 > ⾼等学校⽣徒数); = ⾃分・他⼈にとって可読性の⾼いコードを書こう
Q3: 選択 (2/3) Q. populationテーブル内のレコードのうち § 総⼈⼝が300万⼈未満,かつ § 都道府県名が県で終わる,かつ §
中学校⽣徒数が⾼等学校⽣徒数以下 のレコードを求める表⽰するSQL⽂を書け. SELECT * FROM population WHERE 総⼈⼝ < 3000000 AND 都道府県 LIKE “%県” AND 中学校⽣徒数 ≦ ⾼等学校⽣徒数; A.
コラム: 重複を許してしまうSQL SQLは関係データモデルを扱うにも関わらず 問い合わせ結果に重複を許す SELECT 都道府県 FROM population WHERE (総⼈⼝
≧ 1000000) AND (⼤学学⽣数 > ⾼等学校⽣徒数); 都道府県 東京都 京都府 大阪府 東京都 京都府 大阪府 SQLの仕様として覚えておく
DISTINCT修飾句 DISTINCT句は問い合わせ結果から 重複レコードを除外する SELECT DISTINCT 都道府県 FROM population WHERE (総⼈⼝
≧ 1000000) AND (⼤学学⽣数 > ⾼等学校⽣徒数); 都道府県 東京都 京都府 大阪府
Q4: 選択 (3/3) Q. Q3のSQL⽂を修正して,Q3の条件を満たす 都道府県を表⽰するSQL⽂を書け. ただし, 出⼒される都道府県名に重複があってはなら ない. SELECT
DISTINCT 都道府県 FROM population WHERE 総⼈⼝ < 3000000 AND 都道府県 LIKE “%県” AND 中学校⽣徒数 ≦ ⾼等学校⽣徒数; A.
整列(1/2) SELECT * FROM population ORDER BY 総⼈⼝ LIMIT 10;
ORDER BY句は指定した列の値でレコードをソート 地域コード 都道府県 調査年度 総人口 … R03100 鳥取県 2021 549000 … R03100 鳥取県 2020 553407 … R03200 島根県 2021 665000 … … テーブル:population 総⼈⼝の昇順で レコードを並び替える 昇 順
整列(2/2) SELECT * FROM population ORDER BY 総⼈⼝ DESC LIMIT
10; 降順(大きい順)にソートする時はDESCを指定 地域コード 都道府県 調査年度 総人口 … R01300 東京都 2020 14047594 … R01300 東京都 2021 14010000 … R01400 神奈川県 2020 9237337 … … テーブル:population 総⼈⼝の降順で レコードを並び替える 降 順
Q5: ソート (1/2) Q. populationテーブル内のレコードのうち調査 年度が2021のものについて,⼤学学⽣数順の ⼤きいもの順に並び替えて表⽰するSQL⽂を 書け. SELECT *
FROM population WHERE 調査年度 = 2021 ORDER BY ⼤学学⽣数 DESC; A.
Q6: ソート (2/2) Q. populationテーブル内のレコードのうち調査 年度が2021のものについて,総⼈⼝に占める ⼤学学⽣数の割合が⼤きいもの順に並び替え て表⽰するSQL⽂を書け. その際,総⼈⼝に 占める⼤学学⽣数の割合も合わせて表⽰せよ.
SELECT *, ⼤学学⽣数 / 総⼈⼝ AS ⼤学⽣割合 FROM population WHERE 調査年度 = 2021 ORDER BY ⼤学学⽣数 / 総⼈⼝ DESC; A.
集約演算のSQL 4 SQL for aggregating data
都道府県 年度 総人口 北海道 2021 5183000 青森県 2021 1221000 …
北海道 2020 5524614 青森県 2020 1237984 … 集約演算とは? 購買ID 売上 T1 1400 T2 1340 T3 4980 … … T1500 2850 年度毎に ⼈⼝を合計したい 売上の平均 を求めたい 全レコードあるいはグループ化されたレコード をまとめて値を算出すること
代表的な集約関数 SUM(列名) AVG(列名) COUNT(*) MAX(列名) MIN(列名) 指定された列の値を合計 指定された列の最⼤値を返す 指定された列の値を平均 指定された列の最少値を返す
抽出されたレコード数を返す 抽出・グループ化されたレコード群に対して適用
例1: SUM 都道府県 調査年度 総人口 … 北海道 2021 5490000 …
北海道 2020 5524614 … 青森県 2021 1221000 … 青森県 2020 1237984 … テーブル:population WHERE句で抽出した 全レコードを集約 SELECT SUM(総⼈⼝) FROM population WHERE 調査年度 = 2021; (GROUP BY句を使わない場合) 集約関数は抽出されたレコード全体を集約
例1: SUM SELECT SUM(総⼈⼝) FROM population WHERE 調査年度 = 2021;
(GROUP BY句を使わない場合) SUM(総人口) 125500000 集約関数は抽出されたレコード全体を集約 都道府県 調査年度 総人口 … 北海道 2021 5490000 … 北海道 2020 5524614 … 青森県 2021 1221000 … 青森県 2020 1237984 … テーブル:population
都道府県 調査年度 総人口 … 北海道 2021 5490000 … 北海道 2020
5524614 … 青森県 2021 1221000 … 青森県 2020 1237984 … 例2: COUNT SELECT COUNT(都道府県) FROM population; テーブル:population 都道府県の列にある レコード数を数える = 94個 COUNT関数は指定された列のレコード数を返す
例2: COUNT SELECT COUNT(DISTINCT 都道府県) FROM population; COUNT関数は指定された列のレコード数を返す 都道府県 調査年度
総人口 … 北海道 2021 5490000 … 北海道 2020 5524614 … 青森県 2021 1221000 … 青森県 2020 1237984 … テーブル:population 都道府県の列にある レコード数を数える = 47個 (ただし重複は除く)
GROUP BY(1/3) 都道府県 年度 総人口 北海道 2021 5183000 青森県 2021
1221000 … 北海道 2020 5524614 青森県 2020 1237984 … 年度が同じ値のものを グループとしてまとめる SELECT 年度, SUM(総⼈⼝) FROM population GROUP BY 年度; GROUP BY句は指定した列の値が同じもの をグループ化
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
GROUP BY(2/3) SELECT 年度, SUM(総⼈⼝) FROM population GROUP BY 年度;
SELECTの中に GROUP BYで指定した列名を入れる
GROUP BY(3/3) 都道府県 年度 総人口 北海道 2021 5183000 北海道 2020
5524614 青森県 2021 1221000 青森県 2020 1237984 岩手県 2021 1196000 岩手県 2020 1210534 都道府県が同じ値のものを グループとしてまとめる SELECT 都道府県, AVG(総⼈⼝) FROM population GROUP BY 都道府県; 指定した列によってグループ化の結果は異なる
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 都道府県; 指定した列によってグループ化の結果は異なる
Q7: GROUP BY (1/3) Q. populationテーブル内のレコードに対して都道府県 ごとの集約演算を⾏い,都道府県別に -(調査期間中の)⼤学学⽣数の平均 -(調査期間中の)⼤学学⽣数の最⼤値と最⼩値の差 を求めるSQL⽂を書け.
SELECT 都道府県, AVG(⼤学学⽣数) AS ⼤学⽣数平均, MAX(⼤学学⽣数) - MIN(⼤学学⽣数) AS 差 FROM population GROUP BY 都道府県; A.
Q8: GROUP BY (2/3) Q. populationテーブル内の総⼈⼝が300万⼈を超え るレコードに対して調査年度ごとの集約演算を ⾏い, 調査年度別に §
「(各地域コードにおける)⼩学校児童数と 中学校⽣徒数の合計」の平均 § 「(各地域コードにおける)⾼等学校⽣徒数 と⼤学学⽣数の差」の平均 § 「(各地域コードにおける)⾼等学校⽣徒数 と⼤学学⽣数の差」の最⼩値 § 「(各地域コードにおける)⾼等学校⽣徒数 と⼤学学⽣数の差」の最⼤値 を求めるSQL⽂を書け.
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万以下で条件満たさず
HAVING句 SELECT 都道府県, AVG(総⼈⼝) FROM population GROUP BY 都道府県 HAVING
AVG(総⼈⼝) ≧ 5000000 ; HAVING句を使うと、集約演算結果の中から 条件を満たすものだけを抽出できる 都道府県 年度 総人口 北海道 2021 5183000 北海道 2020 5524614 青森県 2021 1221000 青森県 2020 1237984 … 年度 AVG(総人口) 北海道 5203807 埼玉県 7342382 千葉県 6279740 …
Q9: GROUP BY (3/3) Q. populationテーブルのレコードに対して都道府県 ごとの集約演算を⾏い, 都道府県別に § 「(調査期間中の)総⼈⼝」の平均
§ 「(調査期間中の)⾼等学校⽣徒数」の平均 § 「(調査期間中の)⼤学学⽣数」の平均 § 「(調査期間中の)⾼等学校⽣徒数と⼤学学⽣数 の差」の平均 を求めるSQL⽂を書け. ただし「(調査期間中の) ⼤学学⽣数と⾼等学校⽣徒数の差」の平均が10000 以下になるものだけを表⽰せよ.
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 集約 関数
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 集約 関数
SQLの記述順序([ ]の箇所は必要に応じて書く) SELECT 列名1, 列名2, ... FROM 参照する表1, 表2, ...
[WHERE 条件] [GROUP BY 列名1, 列名2, ...] [HAVING 条件] [ORDER BY 列名1, 列名2, ...] [LIMIT 数字] ; 記述順序を間違うとエラー発⽣ セミコロンを忘れない!!
回 実施日 トピック 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 期末試験 今後の予定 68