6. 副問い合わせ & テーブルの定義と更新

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
%config SqlMagic.displaylimit = 50
%sql sqlite:///data/SSDSE.db

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

6.1. 副問い合わせ

関係データベースへの問い合わせが複雑になってくると，SQLによる問い合わせ結果を使ってさらにSQL文を書きたいケースも発生する． このようなケースで役に立つのが副問い合わせ（サブクエリ; subqueryと呼ぶことも） である． 副問い合わせを用いると，FROM句やWHERE句の中でSELECT文を書くことができ，SQL文の中で別のSQL文の実行結果を参照することができる．

例題を通して副問い合わせの使い方を確認しよう． populationテーブルを用いて

1. 2021年度と2020年度の大学学生数の平均値を都道府県別に算出し，

2. 平均大学生数の上位10の都道府県を表示する

SQL文について考えたい． 上記ステップ1だけを行うSQL文であれば，以下のようになる．

/* ステップ1だけを行うSQL文 */
SELECT
  地域コード,
  都道府県,
  AVG(大学学生数) AS 平均大学生数 --- 列名に名前をつける
FROM
  population
GROUP BY
  都道府県; 

地域コード	都道府県	平均大学生数
R24000	三重県	14063.0
R26000	京都府	142482.5
R41000	佐賀県	7758.5
R28000	兵庫県	115917.5
R01000	北海道	79569.0
R12000	千葉県	105451.5
R30000	和歌山県	7707.5
R11000	埼玉県	110676.0
R44000	大分県	15233.5
R27000	大阪府	227323.0
R29000	奈良県	20583.5
R04000	宮城県	49495.0
R45000	宮崎県	9830.0
R16000	富山県	10798.0
R35000	山口県	18486.5
R06000	山形県	11762.0
R19000	山梨県	16111.5
R21000	岐阜県	20006.0
R33000	岡山県	39209.0
R03000	岩手県	11414.5
R32000	島根県	7180.5
R34000	広島県	55569.5
R36000	徳島県	11814.0
R38000	愛媛県	16287.5
R23000	愛知県	177051.0
R15000	新潟県	27349.5
R13000	東京都	675323.5
R09000	栃木県	20442.0
R47000	沖縄県	17907.0
R25000	滋賀県	31171.0
R43000	熊本県	24675.5
R17000	石川県	27498.0
R14000	神奈川県	172937.0
R18000	福井県	10066.5
R40000	福岡県	110008.0
R07000	福島県	14336.5
R05000	秋田県	8894.0
R10000	群馬県	28542.0
R08000	茨城県	30202.5
R42000	長崎県	17105.5
R20000	長野県	16710.0
R02000	青森県	15409.5
R22000	静岡県	33793.5
R37000	香川県	9123.5
R39000	高知県	9171.5
R31000	鳥取県	6728.5
R46000	鹿児島県	15454.5

今やりたいことは，この結果テーブルを平均大学生数で大きい順に並べ替えて，上位10件の都道府県レコードのみを出力することである． 仮に上記結果テーブルがpopulation_avgという名で存在していれば，以下のようなSQL文を書くことで目的を達成できる．

--- これは仮のSQL文
SELECT * FROM population_avg ORDER BY 平均大学生数 DESC LIMIT 10;

上記のようなことは，ステップ1のSQL文を「副問い合わせ」することで実現できる． 具体的には，上記仮のSQL文のFROM句にあるpopulation_avgの箇所について，下記のSQL文のようにカッコで囲んだ「ステップ1だけを行う」SELECT文で置き換える．

SELECT
  *
FROM
  (
    SELECT
      地域コード,
      都道府県,
      AVG(大学学生数) AS 平均大学生数
    FROM
      population
    GROUP BY
      都道府県
  ) 
ORDER BY
  population_avg.平均大学生数 DESC
LIMIT 10; 

地域コード	都道府県	平均大学生数
R13000	東京都	675323.5
R27000	大阪府	227323.0
R23000	愛知県	177051.0
R14000	神奈川県	172937.0
R26000	京都府	142482.5
R28000	兵庫県	115917.5
R11000	埼玉県	110676.0
R40000	福岡県	110008.0
R12000	千葉県	105451.5
R01000	北海道	79569.0

副問い合わせを用いることで，SQL文の実行結果を別のSQL文の中で参照することができる． なお，副問い合わせはSQL文が複雑で読みにくくなる． そのため，少しでもわかりやすくするために，副問い合わせ箇所にAS修飾句で名前をつけたり，WITH句を使って副問い合わせ箇所を外出しすることができる．

以下のSQL文は，上記SQL文をAS修飾句やWITH句を使ってわかりやすく書き換えたものである．

/* 分かりやすく書き換えたSQL1 */
SELECT
  *
FROM
  (
    SELECT
      地域コード,
      都道府県,
      AVG(大学学生数) AS 平均大学生数
    FROM
      population
    GROUP BY
      都道府県
  ) AS population_avg -- AS修飾句を使って，副問い合わせの結果テーブルに名前をつける
ORDER BY
  population_avg.平均大学生数 DESC
LIMIT 10;

/* 分かりやすく書き換えたSQL2 */
/* WITH句を使って，副問い合わせ箇所をメインのSQL文の外に出している */
WITH population_avg AS (
    SELECT
      地域コード,
      都道府県,
      AVG(大学学生数) AS 平均大学生数
    FROM
      population
    GROUP BY
      都道府県    
)
SELECT
  *
FROM
  population_avg
ORDER BY
  population_avg.平均大学生数 DESC
LIMIT 10; 

上の例ではFROM句内で副問い合わせ指定を行っていたが，WHERE句においても副問い合わせを用いることができる． 例えば，前講の例題で用いた

• 65歳以上人口数の上位10都道府県のリストが格納されたelderly_population_top10テーブル

• 「学習・自己啓発・訓練」「スポーツ」「趣味・娯楽」「ボランティア」「旅行・行楽」について過去1年以内に活動したことのある人の割合（%）について都道府県別にまとめたactivityテーブル

の2つを用いて，

65歳以上人口数の上位10件に「入っていない」都道府県について，過去1年以内に「旅行・行楽」活動をしたことのある人の割合が5割を超える場合のみ，その割合を地域コード，都道府県名とともに表示する

ようなSQL文を考えてみよう． ちなみに，

65歳以上人口数の上位10件に「入っている」都道府県について，過去1年以内に「旅行・行楽」活動をしたことのある人の割合が5割を超える場合のみ，その割合を地域コード，都道府県名とともに表示する

ようなSQL文であれば，以下となる（★Quiz1★）．

SELECT
  activity.地域コード,
  activity.都道府県,
  activity.旅行・行楽
FROM
  activity
  JOIN
    elderly_population_top10
    ON activity.都道府県 = elderly_population_top10.都道府県
  AND activity.旅行・行楽 >= 50;

地域コード	都道府県	旅行・行楽
R01000	北海道	51.0
R11000	埼玉県	51.5
R13000	東京都	55.5
R14000	神奈川県	54.5
R23000	愛知県	57.6
R27000	大阪府	51.9
R28000	兵庫県	51.6
R40000	福岡県	52.3

65歳以上人口数の上位10件に「入っている」都道府県のケースとは違って，65歳以上人口数の上位10件に「入っていない」都道府県のケースの場合，

1. activityテーブル内の各都道府県について，elderly_population_topテーブル内にあるか否かを判定し，

2. ない場合のみ，その都道府県の「旅行・行楽」の値を見る

3. 「旅行・行楽」の値が50を超えるものだけ，地域コード，都道府県名とともに表示する

という流れになる． 上記ステップ1では「別のテーブルを活用した対象レコードの存在判定」を行っているが，存在判定は EXISTS述語 を使った副問い合わせで対応することができる． 上記例の場合，以下のようなSQL文になる．

SELECT
  activity.地域コード,
  activity.都道府県,
  activity.旅行・行楽
FROM
  activity
WHERE
　--- NOT EXISTSは存在しない場合Trueを，存在する場合はFalseを返す
  NOT EXISTS (　
    SELECT
      *
    FROM
      elderly_population_top10
    WHERE
      elderly_population_top10.都道府県 = activity.都道府県
  )
  AND activity.旅行・行楽 >= 50; 

地域コード	都道府県	旅行・行楽
R04000	宮城県	52.2
R10000	群馬県	50.8
R25000	滋賀県	52.2
R29000	奈良県	50.4
R43000	熊本県	50.5

WHERE句内でEXISTSが用いられた場合，FROM句で指定されたテーブルの各レコードについて，

• EXISTS以下の副問い合わせ内容を満たすものが1つでも存在すればTrue（真），

• 存在しなければFalse（偽）

の値を返す． NOT EXISTSを用いた場合は逆の振る舞いになる．すなわち，

• NOT EXISTS以下の副問い合わせ内容を満たすものが1つでも存在すればFalse（偽），

• 1つも存在しなければTrue（真）

の値を返す． EXISTSの振る舞いは少しややこしいが，副問い合わせを用いたSQL文では比較的よく用いられるので頭に入れておこう（★Quiz2★）．

Note

IN述語

EXISTS述語と似たようなものにIN述語がある． IN述語はWHERE句内で列名 IN リストと書くと，あるレコードの列の値がリスト内にあるかどうかをTrue or Falseで返す． なお，リストと書かれたところは(値, 値, 値)のようにベタ書きで指定してもよいし，副問い合わせでも指定してもよい．

例えば，NOT EXISTS述語を用いた先の例のSQL文はIN述語を使って以下のように書き換えられる．

SELECT
  activity.地域コード,
  activity.都道府県,
  activity.旅行・行楽
FROM
  activity
WHERE
　--- activity.都道府県の値がelderly_population_top10.都道府県のリストになければTrueを返す
  activity.都道府県 NOT IN (
    SELECT
      elderly_population_top10.都道府県
    FROM
      elderly_population_top10
  )
  AND activity.旅行・行楽 >= 50; 

EXISTS述語よりもIN述語の方が挙動が分かりやすいが，実行速度はEXISTS述語の方が速い． INを用いた場合，副問い合わせのSQL文は条件に当てはまるレコードをすべて列挙する． それに対してEXISTSを用いた場合は，条件に当てはまるレコードが1つでも見つかれば処理を終了して次の処理に移るので速い．

Tip

問い合わせのための発展的なSQL

これまでに述べてきたSQLは，関係データベースから所望のレコードを引っ張ってくるための基礎にすぎない． 解説していないSQLは他にも色々ある． 例えば，

• 条件分岐をするためのCASE句

• 数値，文字列処理のための様々な関数

• より高度な集約演算を行うためのWindow関数（分析関数）

などがある． 興味のある方は

• 本橋智光（著）. 2018. 前処理大全-データ分析のためのSQL/R/Python実践テクニック. 技術評論社

• 加嵜長門, 田宮直人（著）. 2017. ビッグデータ分析・活用のためのSQLレシピ. マイナビ出版

など，実践的かつ面白い書籍があるので探して読んでみるとよい．

6.2. テーブルの定義と更新

SQLの締めくくりとして，関係データベースにおけるテーブルの定義と更新操作（挿入．削除．更新）について説明する．

6.2.1. テーブルの定義

SQLでテーブルの定義を行うにはCREATE TABLE文を用いる． テーブル定義では

• テーブル名

• テーブルが持つ列名

• 各列のデータ型

• 主キー

• （必要なら）外部キー

を指定する．

データ型は関係データモデルの講で説明した定義域（domain） に対応するものである． 関係データベースで扱える代表的なデータ型は以下の通りである．

• VARCHAR(n): n文字まで扱える可変長の文字列型．通常文字列を扱うときはこれを指定する．

• INT: 整数型

• FLOAT: 浮動小数点型

• BOOLEAN: 真偽値型（TRUEもしくはFALSEの値をもつ）

• DATETIME: 時刻を併せ持つ日付型

例えば，例題で用いてきたpopulationテーブルをゼロから定義する場合，以下のようなSQL文となる．

CREATE TABLE population ( --- TABLEの横にテーブル名を書く
    地域コード VARCHAR(10),　--- 10文字あれば地域コードを網羅的に表現できるので10を指定
    都道府県 VARCHAR(5), --- 5文字あれば都道府県を網羅的に表現できるので5を指定
    調査年度 INT,
    総人口 INT,
    小学校児童数 INT,
    中学校生徒数 INT,
    高等学校生徒数 INT,
    大学学生数 INT,
    PRIMARY KEY (地域コード, 調査年度)　 --- 主キーとして地域コードを指定
);

なお，上記は最低限の説明である． 実際にSQL文を使ってテーブル定義をする際には，初期値や外部キー制約，インデックスなど，想定するアプリケーションに応じて様々な設定を行うことになる． 使用する関係データベース管理システムによって微妙に設定方法が異なるので，RDBMSに対応したドキュメントを適宜参照すること．

6.2.2. レコードの追加

レコードの追加にはINSERT INTO文を用いる． 以下は，populationテーブルに2つのレコードを追加するSQL文の例である．

INSERT INTO
  population ( --- ここに並べた列名に対応した値をVALUES以下のカッコ内に書く．
    地域コード,
    都道府県,
    調査年度,
    総人口,
    小学校児童数,
    中学校生徒数,
    高等学校生徒数,
    大学学生数
  )
VALUES 
--- VALUES以下に追加したいレコード数分だけ値をカッコで並べる．カッコ内の順番は列名の順序に対応
--- xxxは適当な値
  ('R01000', '北海道', '2022', xxx, xxx, xxx, xxx, xxx),
  ('R01000', '北海道', '2023', xxx, xxx, xxx, xxx, xxx);

6.2.3. レコードの削除

レコードの削除にはDELETE文を用いる． 以下は，populationテーブルから調査年度が2022もしくは2023のレコードを削除するSQL文の例である．

DELETE FROM
  population
WHERE
  調査年度 = 2022 OR 調査年度 = 2023; -- SELECT分と同様に`WHERE`句で条件を指定できる

6.2.4. レコードの更新

レコードの更新にはUPDATE文を用いる． 以下は，populationテーブルから調査年度が2022のレコードについて，総人口の値更新前の総人口から100増やしたものに更新するSQL文である．

UPDATE 
  population
SET
  総人口 = 総人口 + 100
WHERE
  調査年度 = 2022; -- SELECT分と同様に`WHERE`句で条件を指定できる

---

6.3. クイズ

本クイズでは，独立行政法人統計センターが公開している教育用標準データセット（SSDSE）の基本素材SSDSE-E（データの解説はこちら）および市区町村（SSDSE-A）から抜粋・加工したデータを用いる． なお，

• populationテーブルの内容については第4講

• activityテーブルの内容については第5講

を参照せよ．

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

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

6.3.1. Q1. 副問い合わせ（1/2）

populationテーブルにおいて調査年度が2021時点の小学校児童数上位20件のレコードを求め，それらとactivityテーブルの内容を結合した内容を表示するSQL文を書け．

%%sql

6.3.2. Q2. 副問い合わせ（2/2）

activityテーブルにおいて「ボランティア」の値が上位10件以内に入る都道府県を求めるSQL文を考えよ． その上で，populationテーブルを用いて，「ボランティア」の値が上位10件以内に入る各都道府県の調査期間（2020および2021）における平均総人口を求めるSQL文を書け．

%%sql