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
30
データベース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
19
データベース03: 関係データモデル
trycycle
0
120
データベース05: SQL(2/3) 結合質問
trycycle
0
18
データベース02: データベースの概念
trycycle
0
190
データベース01: データベースを使わない世界
trycycle
1
140
ビッグデータ × AI = DX?
trycycle
0
89
名古屋市立大学データサイエンス学部 秋のオープンキャンパス模擬授業20231111
trycycle
0
1.6k
データマイニングと機械学習 - ニューラルネットワーク
trycycle
0
360
データマイニングと機械学習-SVM
trycycle
1
380
Other Decks in Technology
See All in Technology
Microsoft Intune 勉強会 第 2 回目
tamaiyutaro
2
530
cgroup v2 で何が変わったのか / TechFeed Experts Night #28
tenforward
2
120
シンプルなHITL機械学習と様々なタスクにおけるHITL機械学習
naohachi89
0
210
類似ロジック実装をiOS/Android間で合わせる道標No.1
fumiyasac0921
1
190
【NW X Security JAWS#3】L3-4:AWS環境のIPv6移行に向けて知っておきたいこと
shotashiratori
1
730
LLM開発・活用の舞台裏@2024.04.25
yushin_n
3
1.4k
.NET Profiler in 2024.
kkamegawa
2
2.9k
TailwindCSSでUIライブラリを作る際のハマりどころ
shuta13
0
190
One engineer company with Ruby on Rails
rstankov
2
470
Rustで「プリズモイダル法」を利用して「土量計算」をガチでやる
nokonoko1203
1
360
AWS アーキテクチャ作図入門/aws-architecture-diagram-101
ma2shita
16
6.6k
開発スピードの維持向上を支える、テスト設計の 漸進的進化への取り組み / Continuous Test Design Development for Speed of Product Development
ropqa
0
120
Featured
See All Featured
Rebuilding a faster, lazier Slack
samanthasiow
74
8.3k
Designing the Hi-DPI Web
ddemaree
276
33k
Building Flexible Design Systems
yeseniaperezcruz
320
37k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
12
1k
jQuery: Nuts, Bolts and Bling
dougneiner
60
7.2k
What’s in a name? Adding method to the madness
productmarketing
PRO
17
2.7k
How to name files
jennybc
65
93k
10 Git Anti Patterns You Should be Aware of
lemiorhan
649
58k
YesSQL, Process and Tooling at Scale
rocio
165
13k
Reflections from 52 weeks, 52 projects
jeffersonlam
345
19k
Building Adaptive Systems
keathley
32
1.9k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
8
3.4k
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