4. 単純質問

Show code cell source Hide code cell source

import os

if not os.path.isfile('data/SSDSE.db'):
    os.makedirs('data', exist_ok=True)

    if not os.path.isfile('download_SSDSE.py'):
        !wget https://raw.githubusercontent.com/hontolab-courses/database-lecturenote/main/content/sql/download_SSDSE.py
        import download_SSDSE

Show code cell source Hide code cell source

%load_ext sql
%config SqlMagic.feedback = 0
%sql sqlite:///data/SSDSE.db

データベースからデータを引っ張ってくるなど，データベースを操作するには具体的なツールが必要となる． SQL(Structured Query Language) は関係データベースの操作に特化した言語である． SQLはISOによって国際的に標準化されている． よく用いられる関係データベース管理システム（RDBMS）としてMySQLやPostregreSQL，Oracle Database，Microsoft SQL Serverなどが挙げられるが，どのRDBMSにもSQLは実装されている．

SQLを使うことで，関係データベースのユーザは

• データの登録（Create）

• データの読み出し（Read）

• データの更新（Update）

• データの削除（Delete）

が可能となる[1]． なお，本教材のターゲットはデータ分析人材の卵であることから，本教材では関係データベースからデータの読み出しに焦点を当ててSQLの説明を行う．

SQLはデータベースに対する問い合わせ言語であって，プログラミング言語ではない． そのため，プログラミング言語に比べて覚えることは少なく，問い合わせ内容を英語に近い形で書き下すことができるよう設計されている． データ分析人材はソフトウェアを開発することが目的ではないから，高度なプログラミング能力を持つ必要は必ずしもない． しかし，関係データベースに納められた大量のデータを自由自在に扱うためにも，SQLの習得は必須である．

Note

SQLの読み方

SQLの読み方は「エス・キュー・エル」である．SQLは1970年代にIBM社が開発したデータベース管理システムSystem Rの操作言語SEQUELを起源としている．関係データベースに熱狂していた世代の人の中には，SEQUELの名残でSQLのことを「シークエル」と呼ぶ人もいる．

Warning

SQLにも方言がある

RDBMSによって，SQLで使用できる関数が異なったりすることがある．特にSQLiteについては，簡易的なRDMBSということもあり，他のRDBMSでは実装されている機能や関数が実装されていない場合がある．自分が書いたSQL文がうまく動作しない場合は，マニュアルに当たってみよう．

4.1. SQLと関係データモデルの比較

SQLは関係データベースを操作する言語であり，それが扱うデータモデルは関係データモデルを実用的に拡張したものである． 「実用」のSQLデータモデルと「理論」の関係データモデルでは，同様の概念が異なる用語で定義されている． 以下，SQLと関係データモデルの用語の比較である．

関係データモデル	SQL
関係（リレーション; relation）	表（テーブル; table）
タプル（tuple）	レコード or 行（row）
属性（attribute）	列（カラム; column）
定義域（ドメイン; domain）	データ型

また，関係データモデルでは各属性はドメインで定義された値をもつが， 現実的にはある属性の値が存在しない，あるいは未定義（不明）のケースがありえる． そのような状況では，特殊な値であるNULL値（ヌルと読む）を用いる．

4.2. 基本形

関係データベースに対する最も典型的な問い合わせ要求は，

• 特定の表（table，テーブル） から

• 列（column，カラム） が特定の条件を満たす行（row）[2] を見つけ出し

• 結果を表形式で出力する

ことである．問い合わせのために記述するSQL文のことをクエリ（query） と呼ぶ．

SQLによる代表的なクエリは，以下の形式のSELECT文である．

SELECT
    都道府県名
FROM
    都道府県
WHERE
    人口 >= 5000000;

上記クエリは，

「都道府県」テーブルから「人口」という列の値が500万以上である行を見つけ，その行の列「都道府県名」を出力してください

という問い合わせ要求を表現したものである．

• SELECT句を用いて「問い合わせ結果に表示したい列情報」，

• FROM句を用いて「参照したい表」，

• WHERE句を用いて「表示する際の条件」

を指定している． 上記要求を英語に直してみると，SQL文は問い合わせ要求をできるだけ直感的に表現しようとしていることがお分かりいただけると思う．

上記クエリは単純な例ではあるが，関係データベースからデータを引っ張ってくる問い合わせについては，すべからく下記のようなSELECT句から始まるクエリが用いられる．

SELECT
    列名1, 列名2, ...
FROM
    参照する表1, 表2, ...
[WHERE 条件]
[GROUP BY 列名1, 列名2, ...]
[HAVING 条件]
[ORDER BY 列名1, 列名2, ...]
[LIMIT 数字]; 

このうち，問い合わせ結果に表示したい情報を指定するSELECT句，および問い合わせの際に参照するテーブルを指定しているFROM句は必須である． [ ]で囲まれた箇所については，書かなくてもSQLとして動作する． なお，SELECTやFROM，WHERE，GROUP BY，HAVING，ORDER BY，LIMITといった句の意味のついては後ほど説明するが，各句はこの順序で使う必要がある． 例えば，HAVING句はWHERE句の後ろに書かない． また，クエリの末尾にはピリオド（;）をつけ忘れてはいけない．

以後，SQLの基礎について説明する． 説明には，独立行政法人統計センターが公開している教育用標準データセット（SSDSE）の基本素材SSDSE-E（データの解説はこちら）から抜粋・加工したデータ（populationテーブル）を用いる． 以下の通り，populationテーブルには，47ある各都道府県に関する総人口，小学校児童数，中学校生徒数，高等学校生徒数，大学学生数のデータが2021年度，2020年度分入っている． このテーブルが格納された関係データベースが手元にあると想定して，SQLの使い方を説明する．

地域コード	都道府県	調査年度	総人口	小学校児童数	中学校生徒数	高等学校生徒数	大学学生数
R01000	北海道	2021	5183000	231714	122742	115335	79729
R02000	青森県	2021	1221000	54460	29940	30543	15419
R03000	岩手県	2021	1196000	55597	30269	29980	11340
R04000	宮城県	2021	2290000	112246	58748	55329	49580
R05000	秋田県	2021	945000	38992	21924	21448	8904
R06000	山形県	2021	1055000	49164	26969	27233	11801
R07000	福島県	2021	1812000	85322	46148	45647	14385
R08000	茨城県	2021	2852000	135782	72465	71842	30147
R09000	栃木県	2021	1921000	95315	51170	49674	20496
R10000	群馬県	2021	1927000	94185	50841	48521	28772
R11000	埼玉県	2021	7340000	363199	187395	163986	109500
R12000	千葉県	2021	6275000	306105	158265	141358	106037
R13000	東京都	2021	14010000	622820	311049	301712	676964
R14000	神奈川県	2021	9236000	451098	226599	195931	171164
R15000	新潟県	2021	2177000	103680	53720	51594	27546
R16000	富山県	2021	1025000	47818	26146	26068	10857
R17000	石川県	2021	1125000	56620	30336	29764	27627
R18000	福井県	2021	760000	39236	21196	20701	10065
R19000	山梨県	2021	805000	38572	20955	22717	16097
R20000	長野県	2021	2033000	101932	55189	52632	17032
R21000	岐阜県	2021	1961000	101805	54493	50563	20185
R22000	静岡県	2021	3608000	183614	98192	91613	33778
R23000	愛知県	2021	7517000	405839	209151	185920	176722
R24000	三重県	2021	1756000	90040	47567	44229	14062
R25000	滋賀県	2021	1411000	80289	41086	36673	31242
R26000	京都府	2021	2561000	119892	65187	66457	143095
R27000	大阪府	2021	8806000	422433	221610	207262	228194
R28000	兵庫県	2021	5432000	278500	143075	128298	115536
R29000	奈良県	2021	1315000	65989	35964	32530	20512
R30000	和歌山県	2021	914000	43676	23677	23349	7891
R31000	鳥取県	2021	549000	28027	14316	14321	6721
R32000	島根県	2021	665000	33162	17040	17145	7263
R33000	岡山県	2021	1876000	97981	50820	49501	39071
R34000	広島県	2021	2780000	147671	75326	68044	55487
R35000	山口県	2021	1328000	65000	33721	30983	18427
R36000	徳島県	2021	712000	34181	17432	16965	11761
R37000	香川県	2021	942000	49196	25629	24657	9118
R38000	愛媛県	2021	1321000	66494	33330	31473	16321
R39000	高知県	2021	684000	31226	16988	17139	9257
R40000	福岡県	2021	5124000	279290	139657	123508	109860
R41000	佐賀県	2021	806000	43903	23530	22422	7776
R42000	長崎県	2021	1297000	68834	35782	34415	17083
R43000	熊本県	2021	1728000	96415	48862	44284	24580
R44000	大分県	2021	1114000	56464	29624	29300	15189
R45000	宮崎県	2021	1061000	59639	30562	28856	9736
R46000	鹿児島県	2021	1576000	88636	45294	43029	15477
R47000	沖縄県	2021	1468000	101342	49716	43221	17882
R01000	北海道	2020	5224614	236396	123129	119773	79409
R02000	青森県	2020	1237984	55717	30206	32155	15400
R03000	岩手県	2020	1210534	56822	30388	31229	11489
R04000	宮城県	2020	2301996	114086	58381	57157	49410
R05000	秋田県	2020	959502	40192	22182	22266	8884
R06000	山形県	2020	1068027	50885	27473	28281	11723
R07000	福島県	2020	1833152	86804	47108	47571	14288
R08000	茨城県	2020	2867009	139002	72868	74121	30258
R09000	栃木県	2020	1933146	96704	51558	50745	20388
R10000	群馬県	2020	1939110	96764	51167	49994	28312
R11000	埼玉県	2020	7344765	366426	186455	167972	111852
R12000	千葉県	2020	6284480	309553	156873	145920	104866
R13000	東京都	2020	14047594	619291	304405	306302	673683
R14000	神奈川県	2020	9237337	454751	224709	200230	174710
R15000	新潟県	2020	2201272	105818	53720	53114	27153
R16000	富山県	2020	1034814	48757	26565	26722	10739
R17000	石川県	2020	1132526	57706	30259	30515	27369
R18000	福井県	2020	766863	40177	21170	21221	10068
R19000	山梨県	2020	809974	39153	21202	23384	16126
R20000	長野県	2020	2048011	103666	55507	54280	16388
R21000	岐阜県	2020	1978742	104118	54433	52577	19827
R22000	静岡県	2020	3633202	187254	97950	94652	33809
R23000	愛知県	2020	7542415	410482	206920	190309	177380
R24000	三重県	2020	1770254	91910	47472	45829	14064
R25000	滋賀県	2020	1413610	81054	40601	37868	31100
R26000	京都府	2020	2578087	121712	65443	67847	141870
R27000	大阪府	2020	8837685	427884	220342	214115	226452
R28000	兵庫県	2020	5465002	282758	142012	132810	116299
R29000	奈良県	2020	1324473	67172	36085	33849	20655
R30000	和歌山県	2020	922584	44501	23633	24240	7524
R31000	鳥取県	2020	553407	28238	14522	14572	6736
R32000	島根県	2020	671126	33921	17119	17707	7098
R33000	岡山県	2020	1888432	98893	50683	51008	39347
R34000	広島県	2020	2799702	149529	74729	69260	55652
R35000	山口県	2020	1342059	66289	33677	32051	18546
R36000	徳島県	2020	719559	34671	17397	17801	11867
R37000	香川県	2020	950244	49988	25567	25477	9129
R38000	愛媛県	2020	1334841	67607	33235	32547	16254
R39000	高知県	2020	691527	31918	16999	17646	9086
R40000	福岡県	2020	5135214	280977	136797	125635	110156
R41000	佐賀県	2020	811442	44590	23275	23034	7741
R42000	長崎県	2020	1312317	69812	35687	35385	17128
R43000	熊本県	2020	1738301	96934	48218	45401	24771
R44000	大分県	2020	1123852	57705	29212	29937	15278
R45000	宮崎県	2020	1069576	60450	30211	29590	9924
R46000	鹿児島県	2020	1588256	89738	44912	43928	15432
R47000	沖縄県	2020	1467480	101918	48763	44037	17932

4.3. 射影（SELECT）

最も単純なSQLはSELECT句とFROM句のみからなるものである． 射影（projection） とはFROM句で指定されたテーブルから，SELECT句で指定した特定の列のデータのみを抽出する操作である．

例題として用いるpopulationテーブルには，列として「地域コード」「都道府県」「調査年度」「総人口」「小学校児童数」「中学校生徒数」「高等学校生徒数」「大学学生数」があるが，ケースによって特定の列のデータのみ欲しい場合がある． そのようなケースで用いるのが射影である． 例えば，populationテーブルから「都道府県」「調査年度」「総人口」の列のデータのみを抽出する場合，SQL文は以下となる．

SELECT
    都道府県, 調査年度, 総人口
FROM
    population; 

都道府県	調査年度	総人口
北海道	2021	5183000
青森県	2021	1221000
岩手県	2021	1196000
宮城県	2021	2290000
秋田県	2021	945000
山形県	2021	1055000
福島県	2021	1812000
茨城県	2021	2852000
栃木県	2021	1921000
群馬県	2021	1927000
埼玉県	2021	7340000
千葉県	2021	6275000
東京都	2021	14010000
神奈川県	2021	9236000
新潟県	2021	2177000
富山県	2021	1025000
石川県	2021	1125000
福井県	2021	760000
山梨県	2021	805000
長野県	2021	2033000
岐阜県	2021	1961000
静岡県	2021	3608000
愛知県	2021	7517000
三重県	2021	1756000
滋賀県	2021	1411000
京都府	2021	2561000
大阪府	2021	8806000
兵庫県	2021	5432000
奈良県	2021	1315000
和歌山県	2021	914000
鳥取県	2021	549000
島根県	2021	665000
岡山県	2021	1876000
広島県	2021	2780000
山口県	2021	1328000
徳島県	2021	712000
香川県	2021	942000
愛媛県	2021	1321000
高知県	2021	684000
福岡県	2021	5124000
佐賀県	2021	806000
長崎県	2021	1297000
熊本県	2021	1728000
大分県	2021	1114000
宮崎県	2021	1061000
鹿児島県	2021	1576000
沖縄県	2021	1468000
北海道	2020	5224614
青森県	2020	1237984
岩手県	2020	1210534
宮城県	2020	2301996
秋田県	2020	959502
山形県	2020	1068027
福島県	2020	1833152
茨城県	2020	2867009
栃木県	2020	1933146
群馬県	2020	1939110
埼玉県	2020	7344765
千葉県	2020	6284480
東京都	2020	14047594
神奈川県	2020	9237337
新潟県	2020	2201272
富山県	2020	1034814
石川県	2020	1132526
福井県	2020	766863
山梨県	2020	809974
長野県	2020	2048011
岐阜県	2020	1978742
静岡県	2020	3633202
愛知県	2020	7542415
三重県	2020	1770254
滋賀県	2020	1413610
京都府	2020	2578087
大阪府	2020	8837685
兵庫県	2020	5465002
奈良県	2020	1324473
和歌山県	2020	922584
鳥取県	2020	553407
島根県	2020	671126
岡山県	2020	1888432
広島県	2020	2799702
山口県	2020	1342059
徳島県	2020	719559
香川県	2020	950244
愛媛県	2020	1334841
高知県	2020	691527
福岡県	2020	5135214
佐賀県	2020	811442
長崎県	2020	1312317
熊本県	2020	1738301
大分県	2020	1123852
宮崎県	2020	1069576
鹿児島県	2020	1588256
沖縄県	2020	1467480

SELECT句内で指定した「都道府県」「調査年度」「総人口」列のデータのみが表示された．

populationテーブルは合計で94行のレコードが格納されているため，表が縦に長くなってしまう． SQL文の問い合わせで得られた結果のうち，先頭の\(N\)行だけを表示させるためには，以下のようにLIMIT句を使うとよい．

SELECT
    都道府県, 調査年度, 総人口
FROM
    population
LIMIT 10;  -- コメント:先頭の10件のみ表示

%%sql

SELECT
    都道府県, 調査年度, 総人口
FROM
    population
LIMIT 10;

都道府県	調査年度	総人口
北海道	2021	5183000
青森県	2021	1221000
岩手県	2021	1196000
宮城県	2021	2290000
秋田県	2021	945000
山形県	2021	1055000
福島県	2021	1812000
茨城県	2021	2852000
栃木県	2021	1921000
群馬県	2021	1927000

上記SQL文では表示する列を「都道府県」「調査年度」「総人口」に限定したが，テーブルがもつすべての列情報を表示させたい場合もあるだろう． そのようなケースでは，以下のようにSELECT句にアスタリスク（*） を使えばよい（アスタリスクを用いると，すべての列名を列挙するのと同等の結果が得られる）（★Quiz1★）

SELECT
    *
FROM
    population
LIMIT 10; 

地域コード	都道府県	調査年度	総人口	小学校児童数	中学校生徒数	高等学校生徒数	大学学生数
R01000	北海道	2021	5183000	231714	122742	115335	79729
R02000	青森県	2021	1221000	54460	29940	30543	15419
R03000	岩手県	2021	1196000	55597	30269	29980	11340
R04000	宮城県	2021	2290000	112246	58748	55329	49580
R05000	秋田県	2021	945000	38992	21924	21448	8904
R06000	山形県	2021	1055000	49164	26969	27233	11801
R07000	福島県	2021	1812000	85322	46148	45647	14385
R08000	茨城県	2021	2852000	135782	72465	71842	30147
R09000	栃木県	2021	1921000	95315	51170	49674	20496
R10000	群馬県	2021	1927000	94185	50841	48521	28772

Tip

読みやすいSQL

SQL文では改行や余分な空白は無視される．そのため，上記SQL文はSELECT * FROM population LIMIT 10;と解釈される．

それでもわざわざ改行や余分な空白を入れたりしているのは，SQL文を読みやすくするためである． 複雑な問い合わせを行う場合，SQL文も複雑かつ長くなる． そういったSQL文を読むのは苦痛であるしミスも見落としやすくなるので，できる限りSQL文を読みやすくしておくほうがよい．

4.4. 選択（WHERE）

選択とは関係データベースから特定の条件を満たすレコードを抽出する操作である． 選択を行うにはWHERE句はを用いる． WHERE句を使うことで，列の値と定数，あるいは列同士の値を比較して，FROM句で参照したテーブル中のデータを絞り込むことができる．

WHERE句内で使える代表的な比較演算子は，以下の通りである：

• =（等しい）

• !=（等しくない）

• <（より小さい）

• >（より大きい）

• <=（以下）

• >=（以上）

例えば，populationテーブルから総人口数が750万以上のレコードを抽出するSQL文は以下となる．

SELECT
    *
FROM
    population
WHERE
    総人口 >= 7500000; 

地域コード	都道府県	調査年度	総人口	小学校児童数	中学校生徒数	高等学校生徒数	大学学生数
R13000	東京都	2021	14010000	622820	311049	301712	676964
R14000	神奈川県	2021	9236000	451098	226599	195931	171164
R23000	愛知県	2021	7517000	405839	209151	185920	176722
R27000	大阪府	2021	8806000	422433	221610	207262	228194
R13000	東京都	2020	14047594	619291	304405	306302	673683
R14000	神奈川県	2020	9237337	454751	224709	200230	174710
R23000	愛知県	2020	7542415	410482	206920	190309	177380
R27000	大阪府	2020	8837685	427884	220342	214115	226452

比較したい列のデータ型が文字列の場合は比較したい文字列をダブルクォーテーション（”） もしくはシングルクォーテーション（’） で囲う必要がある．

以下は，populationテーブルから都道府県名が「京都府」のレコードを抽出するSQL文の例である（★Quiz2★）．

SELECT
    *
FROM
    population
WHERE
    都道府県 = "京都府"; 

地域コード	都道府県	調査年度	総人口	小学校児童数	中学校生徒数	高等学校生徒数	大学学生数
R26000	京都府	2021	2561000	119892	65187	66457	143095
R26000	京都府	2020	2578087	121712	65443	67847	141870

文字列の条件指定においては，列データに特定の文字列を含むレコードを抽出したいケースもある． そのようなケースではLIKE句を用いる． 以下の例のように，WHERE句内にLIKE %部分文字列%といった文を書くと，指定した部分文字列を列データに含むレコードに絞り込むことができる． なお%（パーセント）は0文字以上の任意の文字列を意味する．

以下は，populationテーブルから都道府県名に「京都」の文字を含むレコードのみを抽出するSQL文の例である．

SELECT
    *
FROM
    population
WHERE
    都道府県 LIKE "%京都%"; 

地域コード	都道府県	調査年度	総人口	小学校児童数	中学校生徒数	高等学校生徒数	大学学生数
R13000	東京都	2021	14010000	622820	311049	301712	676964
R26000	京都府	2021	2561000	119892	65187	66457	143095
R13000	東京都	2020	14047594	619291	304405	306302	673683
R26000	京都府	2020	2578087	121712	65443	67847	141870

「京都」の前後にパーセント記号をつけることによって，都道府県名が「〜京都」もしくは「京都〜」のパターンにマッチするレコードのみに絞り込んでいる． もしLIKE句の条件を%京都%ではなく%京都にした場合，京都府のレコードはマッチしなくなる．

Note

SQLの処理順序

SELECT文による問い合わせが行われたとき，関係データベース管理システムは以下のステップで処理を行う．

1. FROM句で指定した表を参照

2. 表中の各レコードがWHERE句で指定された条件を満たしているかを確認

3. ステップ2で条件を満たしていると判定された行のみ，その行にあるSELECT句で指定した列のデータを表示

WHERE句では条件を複数指定することもできる． 条件は論理演算子であるANDもしくはORで結合することができる．

• 条件1 AND 条件2と指定すれば，条件1と条件2をともに満たすレコード

• 条件1 OR 条件2と指定すれば，条件1もしくは条件2のいずれかを満たすレコード

を抽出することができる．

以下は，populationテーブルから総人口が100万人以上でかつ大学生数が高校生数よりも多い都道府県を抽出するSQL文の例である（★Quiz3★）．

SELECT
    都道府県
FROM
    population
WHERE
    (総人口 >= 1000000)
    AND (大学学生数 > 高等学校生徒数); 

都道府県
東京都
京都府
大阪府
東京都
京都府
大阪府

Tip

条件の明確化

条件の前後に丸括弧をつけることで，条件の記述範囲を明確にすることができる． 条件の範囲やAND/ORのかかる順序をわかりやすくするためにも，3つ以上の条件を組み合わせるような場合には条件の前後に丸括弧をつけることをオススメする．

上記結果には重複する結果が含まれているが，通常SQLは重複した結果があってもそのまま出力される． 行の重複を除いた結果を出力したい場合，SELECTの直後にDISTINCTを指定する．

上記の例においては，以下のようなSQL文を発行すると重複のない結果が得られる（★Quiz4★）．

SELECT DISTINCT
    都道府県
FROM
    population
WHERE
    (総人口 >= 1000000)
    AND (大学学生数 > 高等学校生徒数); 

都道府県
東京都
京都府
大阪府

4.5. 整列（ORDER BY）

データ分析では，大きいもの（小さいもの）順にデータを整列（ソート） させたいケースが多々ある． そのようなケースで使用するのがORDER BY句である．

以下のようにORDER BYの後に列名を指定することで，SQL文で抽出したレコードを指定した列の値の小さいもの順（昇順） に並び替えることができる．

SELECT 
    *
FROM
    population
ORDER BY 
    総人口
LIMIT 10; --- 先頭の10件のみ表示

地域コード	都道府県	調査年度	総人口	小学校児童数	中学校生徒数	高等学校生徒数	大学学生数
R31000	鳥取県	2021	549000	28027	14316	14321	6721
R31000	鳥取県	2020	553407	28238	14522	14572	6736
R32000	島根県	2021	665000	33162	17040	17145	7263
R32000	島根県	2020	671126	33921	17119	17707	7098
R39000	高知県	2021	684000	31226	16988	17139	9257
R39000	高知県	2020	691527	31918	16999	17646	9086
R36000	徳島県	2021	712000	34181	17432	16965	11761
R36000	徳島県	2020	719559	34671	17397	17801	11867
R18000	福井県	2021	760000	39236	21196	20701	10065
R18000	福井県	2020	766863	40177	21170	21221	10068

ORDER BYはデフォルトは小さいもの順（昇順, in ascending order）でレコードをソートする． 大きいもの順（降順, in descending order）でソートしたい場合は，ORDER BYで列名を指定する際，列名の後にDESCキーワードを付ける（★Quiz5★，★Quiz6★）．

SELECT 
    *
FROM
    population
ORDER BY 
    総人口 DESC --- DESCを付けることで総人口の降順で結果を並び替える
LIMIT 10; 

地域コード	都道府県	調査年度	総人口	小学校児童数	中学校生徒数	高等学校生徒数	大学学生数
R13000	東京都	2020	14047594	619291	304405	306302	673683
R13000	東京都	2021	14010000	622820	311049	301712	676964
R14000	神奈川県	2020	9237337	454751	224709	200230	174710
R14000	神奈川県	2021	9236000	451098	226599	195931	171164
R27000	大阪府	2020	8837685	427884	220342	214115	226452
R27000	大阪府	2021	8806000	422433	221610	207262	228194
R23000	愛知県	2020	7542415	410482	206920	190309	177380
R23000	愛知県	2021	7517000	405839	209151	185920	176722
R11000	埼玉県	2020	7344765	366426	186455	167972	111852
R11000	埼玉県	2021	7340000	363199	187395	163986	109500

4.6. 集約関数

合計値や平均値の計算など，データの集計はデータ集合の特徴を知る上での基礎となる． 関係データベースから抽出したレコード集合に対して集計処理を行いたい場合は集約関数（aggregate functions） を用いる． SQLに実装されている代表的な集約関数は以下の通りである．

• SUM: 合計値の計算

• MAX: 最大値の計算

• MIN: 最小値の計算

• AVG: 平均値の計算

• COUNT: 行数のカウント

関数の引数には計算に用いたい列名などを指定する． （次節で解説するGROUP BY句を用いない場合）集計関数は，WHERE句までで絞り込まれたレコード全体を1つのグループと見なして 集計処理を行う．

例えば，populationテーブルから調査年度が2021年度のレコードだけに限定して，各都道府県の総人口の合計値を計算するSQL文は以下となる．

SELECT
    SUM(総人口)
FROM
    population
WHERE
    調査年度 = 2021; 

SUM(総人口)
125500000

SELECT句に複数集約関数を指定することで，複数の集計処理を同時に行うこともできる． 以下は，populationテーブルを調査年度が2021年度のレコードを用いて，各都道府県の総人口の「合計値」「平均値」「最大値と最小値の差」を計算するSQL文である．

SELECT
    SUM(総人口),
    AVG(総人口),
    MAX(総人口) - MIN(総人口) --- SELECT句内では四則演算もできる
FROM
    population
WHERE
    調査年度 = 2021;

SUM(総人口)	AVG(総人口)	MAX(総人口) - MIN(総人口)
125500000	2670212.765957447	13461000

Note

様々な演算子

SELECT句やWHERE句の中では，四則演算も行うことができる．加算（+），減算（-），乗算（*），除算（/）といった四則演算のための演算子以外にも，余りを求めるための剰余演算子（%），絶対値を求めるABS関数といった様々な算術演算ツールが用意されている．気になった演算があればマニュアルを調べてみよう．

行数（レコード数）をカウントするCOUNT関数は，しばしば引数にすべての列を意味するアスタリスク（*）が用いられる． 以下は，populationテーブルに格納されたレコード数を調べるSQL文である．

SELECT
    COUNT(*)
FROM
    population;

COUNT(*)
94

上記のSQL文はSELECT * FROM population;の実行結果の行数を数えていると考えればよい． 以下のSQL文は結果だけ見ると上記のSQL文と同じになるが，処理の流れとしてはSELECT 都道府県 FROM population;の実行結果の行数を数えていることになる．

SELECT
    COUNT(都道府県)
FROM
    population;

COUNT(都道府県)
94

なお，上記の結果には2021年度と2020年度の結果が含まれているため，同じ都道府県名が2回数えられてしまっている． 都道府県名の重複を除いて行数をカウントしたい場合は，以下のSQL文のようにDISTINCTを使う．

SELECT
    COUNT(DISTINCT 都道府県)
FROM
    population;

COUNT(DISTINCT 都道府県)
47

4.7. グループ化による集約演算（GROUP BY）

前節までに解説した集約のためのSQL文は，WHERE句で絞り込まれたレコード全体に対して集計操作を行うものであった． しかし実際にデータ分析を行う場合，レコード全体での集計にとどまらず，ある基準でまとめられたグループごとに集計を行うことも少なくない． GROUP BY句はグループごとに集約演算を行うための機能である．

「GROUP BY句 + 列名」の形式で指定をすると，指定された列名について同じ値をもつレコードが1つのグループにまとめられたグループ表が（ユーザには見えない形で）一時的に作成される． 例えば，populationテーブルに対して問い合わせを行うSQL文内で「GROUP BY 地域コード」と書くと，以下のようなイメージのグループ表が一時的に作成される（点線がグループを表す）． GROUP BY句を用いたクエリを用いると，まとめられたグループのそれぞれに対してSELECT句で指定された集約関数が適用される．

以下は，populationテーブルを用いて，都道府県ごとに2021年と2020年の総人口の平均値を計算するSQL文である．

SELECT
    地域コード,
    都道府県,
    AVG(総人口)
FROM
    population
GROUP BY
    地域コード
LIMIT 10; --- 先頭の10件のみ表示

地域コード	都道府県	AVG(総人口)
R01000	北海道	5203807.0
R02000	青森県	1229492.0
R03000	岩手県	1203267.0
R04000	宮城県	2295998.0
R05000	秋田県	952251.0
R06000	山形県	1061513.5
R07000	福島県	1822576.0
R08000	茨城県	2859504.5
R09000	栃木県	1927073.0
R10000	群馬県	1933055.0

GROUP BY句はWHERE句と組み合わせて使うこともできる． WHERE句を使うことで，ある条件で絞り込んだレコード集合に対してGROUP BYを適用することができる．

以下は，populationテーブルの中で総人口が500万を超えるレコードに限定して，都道府県ごとに2021年と2020年の総人口の平均値を計算するSQL文である（★Quiz7★，★Quiz8★）．

SELECT
    地域コード,
    都道府県,
    AVG(総人口)
FROM
    population
WHERE
    総人口 >= 5000000
GROUP BY
    地域コード;

地域コード	都道府県	AVG(総人口)
R01000	北海道	5203807.0
R11000	埼玉県	7342382.5
R12000	千葉県	6279740.0
R13000	東京都	14028797.0
R14000	神奈川県	9236668.5
R23000	愛知県	7529707.5
R27000	大阪府	8821842.5
R28000	兵庫県	5448501.0
R40000	福岡県	5129607.0

グループごとに集計した結果にチェックを行い，指定した条件を満たした結果を抽出したいケースもある． そのようなケースではHAVING句を用いる． HAVING句はWHERE句と同じ形式で条件を指定するが，GROUP BY句の後に書くことに注意しよう（WHERE句はGROUP BY句の前）．

以下は，populationテーブルのレコードについて，都道府県ごとに2021年と2020年の「大学学生数」の平均値を計算し，平均大学学生数が10万を超えたものについて，都道府県名，平均総人口，平均大学学生数を表示するSQL文である．WHERE句を用いた場合とHAVING句を用いた場合で挙動が異なることを意識しよう（★Quiz9★）．

SELECT
    地域コード,
    都道府県,
    AVG(総人口),
    AVG(大学学生数)
FROM
    population
GROUP BY
    地域コード
HAVING
    AVG(大学学生数) >= 100000;

地域コード	都道府県	AVG(総人口)	AVG(大学学生数)
R11000	埼玉県	7342382.5	110676.0
R12000	千葉県	6279740.0	105451.5
R13000	東京都	14028797.0	675323.5
R14000	神奈川県	9236668.5	172937.0
R23000	愛知県	7529707.5	177051.0
R26000	京都府	2569543.5	142482.5
R27000	大阪府	8821842.5	227323.0
R28000	兵庫県	5448501.0	115917.5
R40000	福岡県	5129607.0	110008.0

Note

SQLの処理順序（再び）

本章ではSELECT句，FROM句，WHERE句，ORDER BY句，GROUP BY句，HAVING句が登場したが，これらが用いられたSELECT文による問い合わせが行われたとき，どのような順序で処理が行われているかを意識しよう．関係データベース管理システムは以下のステップで処理を行う．

1. FROM句で指定した表を参照

2. WHERE句があればWHERE句で指定された条件を満たすレコードを選択．なければFROM句で指定した表中の全レコードを選択．

3. （GROUP BY句があれば）ステップ2で選択されたレコードをグループ化する

4. （HAVING句があれば）ステップ3でまとめられたグループに対する条件付けを行う

5. （ORDER BY句があれば）指定された基準に基づきレコードをソートする

6. 条件を満たしたものについて，SELECT句で指定された値を表示．

---

4.8. クイズ

本クイズでは，独立行政法人統計センターが公開している教育用標準データセット（SSDSE）の基本素材SSDSE-E（データの解説はこちら）から抜粋・加工したデータ（populationテーブル）を用いる． populationテーブルの内容については，コチラを参照せよ．

なお，データをダウンロードしていない者は，以下のコードを実行してデータをダウンロードしてからクイズに取り組むこと．

import os

if not os.path.isfile('data/SSDSE.db'):
    os.makedirs('data', exist_ok=True)

    if not os.path.isfile('download_SSDSE.py'):
        !wget https://raw.githubusercontent.com/hontolab-courses/database-lecturenote/main/content/sql/download_SSDSE.py
        import download_SSDSE

%load_ext sql
%config SqlMagic.feedback = 0
%sql sqlite:///data/SSDSE.db

4.8.1. Q1. 射影

「都道府県」「調査年度」「大学学生数」フィールドに限定して，populationテーブルのレコードを表示するSQL文を書け． なお，表示するレコード数は30件とせよ．

%%sql

4.8.2. Q2. 選択 (1/3)

populationテーブル内のレコードのうち，都道府県が愛知県で調査年度が2020であるものを表示するSQL文を書け．

%%sql

4.8.3. Q3. 選択 (2/3)

populationテーブル内のレコードのうち

• 総人口が300万人未満，かつ

• 都道府県名が県で終わる，かつ

• 中学校生徒数が高等学校生徒数以下

であるレコードを求める表示するSQL文を書け．

%%sql

4.8.4. Q4. 選択 (3/3)

Q3のSQL文を修正して，Q3の条件を満たす都道府県を表示するSQL文を書け． ただし，出力される都道府県名に重複があってはならない．

%%sql

4.8.5. Q5. ソート (1/2)

populationテーブル内のレコードのうち調査年度が2021のものについて，大学学生数順の大きいもの順に並び替えて表示するSQL文を書け．

%%sql

4.8.6. Q6. ソート (2/2)

populationテーブル内のレコードのうち調査年度が2021のものについて，総人口に占める大学学生数の割合が大きいもの順に並び替えて表示するSQL文を書け． その際，総人口に占める大学学生数の割合も合わせて表示せよ．

%%sql

4.8.7. Q7. GROUP BY (1/3)

populationテーブル内のレコードに対して都道府県ごとの集約演算を行い，都道府県別に

• （調査期間中の）大学学生数の平均

• （調査期間中の）大学学生数の最大値と最小値の差

を求めるSQL文を書け．

%%sql

4.8.8. Q8. GROUP BY (2/3)

populationテーブル内の総人口が300万人を超えるレコードに対して調査年度ごとの集約演算を行い， 調査年度別に

• 「（各地域コードにおける）小学校児童数と中学校生徒数の合計」の平均

• 「（各地域コードにおける）高等学校生徒数と大学学生数の差」の平均

• 「（各地域コードにおける）高等学校生徒数と大学学生数の差」の最小値

• 「（各地域コードにおける）高等学校生徒数と大学学生数の差」の最大値

を求めるSQL文を書け．

%%sql

4.8.9. Q9. GROUP BY (3/3)

populationテーブルのレコードに対して都道府県ごとの集約演算を行い， 都道府県別に

• 「（調査期間中の）総人口」の平均

• 「（調査期間中の）高等学校生徒数」の平均

• 「（調査期間中の）大学学生数」の平均

• 「（調査期間中の）高等学校生徒数と大学学生数の差」の平均

を求めるSQL文を書け． ただし，「（調査期間中の）大学学生数と高等学校生徒数の差」の平均が10000以下になるものだけを表示せよ．

%%sql

---

[1]

データの作成，読み出し，更新，削除はデータベースに求められる主要な操作で，それぞれの頭文字を取ってCRUD（クラッド）と呼ばれる．

[2]

レコード（record）と呼ぶこともある．