はじめに
1969年、E.F.コッドは関係モデルの礎となる論文「Derivability, Redundancy, and Consistency of Relations Stored in Large Data Banks」を発表しました。
「データベースに格納されたデータを「命題」に見立て、述語論理を用いてデータにアクセスする」という斬新な発想により、コッドはユーザからポインタを隠蔽し、直感的な記述によるデータベース操作を可能にしました。
SQLの背景に横たわる理論も非常に面白いですが、本記事では問題形式でSQLの実際の使い方に焦点を当てていきます。
なお、今回の問題はミックさんの著書「達人に学ぶSQL徹底指南書」の例と練習問題を全て解き、面白かったものについて少し形を変えたものです。
集合指向に沿ってクエリを組み立てられるようになることを目標に問題を選びました。
なお、上記の本は6問と言わず、200を超えるサンプルコードに練習問題25問が付いた充実のボリュームなので、興味のある方は是非読んでみて下さい。(今は第2版が出てるのでこちらの方が良いかもしれません。)
SQLプログラミング作法
個人的には他の人に読みやすく、理解しやすく書けていれば、細かいお作法はあまり気にしなくてよいと考えているため、一旦細かい取り決めは置いておいて、可読性のために最低限抑えなければならないポイントだけを簡単に挙げます。
(コーディング・スタイルの詳細を知りたい方はこちらを参考にして下さい。)
①インデントを使う。
②1行が長くなりすぎないように改行する。特にFROM句やWHERE句など明確な役割があるものの前では改行する。
③必要があればコメントを付ける。
④できれば標準SQLで書く。
とりあえず、最低限守らなければならないのはこれぐらいだと思います。それから、書き方の作法とは少し違った話ですが、意味のない表名や列名は付けないように注意しましょう。
④について少しだけ補足。
SQLはDBMS(有名どころだと、Oracle、MySQL、PostgreSQL、SQL Server)による独自の書き方(方言)が多いため、DBMS間の移植性が低いです。そのため、出来ることなら実装依存の関数や演算子を使うのは避けることが望ましいです。
本記事ではSQL Serverを用いていますが、出来るだけ標準的な(どのDB製品でも使用可能な)書き方を心掛けます。
では、面倒なルールの話はこれぐらいにして、問題演習に移りましょう。
実際に問題を解いて練習したい方のために、問題に出てくるテーブルを作成する文を用意したので、ご自由にお使い下さい。
問1
CREATE TABLE Population (
prefecture NVARCHAR(4),
sex NCHAR(1),
population INTEGER
)
INSERT INTO Population VALUES
('北海道','男',2537089),
('北海道','女',2844644),
('青森県','男',614694),
('青森県','女',693571),
('岩手県','男',615584),
('岩手県','女',664010),
('宮城県','男',1140167),
('宮城県','女',1193732),
('秋田県','男',480336),
('秋田県','女',542783),
('山形県','男',540226),
('山形県','女',583665),
('福島県','男',945660),
('福島県','女',968379),
('茨城県','男',1453594),
('茨城県','女',1463382),
('栃木県','男',981626),
('栃木県','女',992629),
('群馬県','男',973283),
('群馬県','女',999832),
('埼玉県','男',3628418),
('埼玉県','女',3638116),
('千葉県','男',3095860),
('千葉県','女',3126806),
('東京都','男',6666690),
('東京都','女',6848581),
('神奈川県','男',4558978),
('神奈川県','女',4567236),
('新潟県','男',1115413),
('新潟県','女',1188851),
('富山県','男',515147),
('富山県','女',551181),
('石川県','男',558589),
('石川県','女',595419),
('福井県','男',381474),
('福井県','女',405266),
('山梨県','男',408327),
('山梨県','女',426603),
('長野県','男',1022129),
('長野県','女',1076675),
('岐阜県','男',983850),
('岐阜県','女',1048053),
('静岡県','男',1820993),
('静岡県','女',1879312),
('愛知県','男',3740844),
('愛知県','女',3742284),
('三重県','男',883516),
('三重県','女',932349),
('滋賀県','男',696941),
('滋賀県','女',715975),
('京都府','男',1248972),
('京都府','女',1361381),
('大阪府','男',4256049),
('大阪府','女',4583420),
('兵庫県','男',2641561),
('兵庫県','女',2893239),
('奈良県','男',643946),
('奈良県','女',720370),
('和歌山県','男',453216),
('和歌山県','女',510363),
('鳥取県','男',273705),
('鳥取県','女',299736),
('島根県','男',333112),
('島根県','女',361240),
('岡山県','男',922226),
('岡山県','女',999299),
('広島県','男',1376211),
('広島県','女',1467779),
('山口県','男',665008),
('山口県','女',739721),
('徳島県','男',359790),
('徳島県','女',395943),
('香川県','男',472308),
('香川県','女',503955),
('愛媛県','男',654380),
('愛媛県','女',730882),
('高知県','男',342672),
('高知県','女',385604),
('福岡県','男',2410418),
('福岡県','女',2691138),
('佐賀県','男',393073),
('佐賀県','女',439759),
('長崎県','男',645763),
('長崎県','女',731424),
('熊本県','男',841046),
('熊本県','女',945124),
('大分県','男',551932),
('大分県','女',614406),
('宮崎県','男',519242),
('宮崎県','女',584827),
('鹿児島県','男',773061),
('鹿児島県','女',875116),
('沖縄県','男',704619),
('沖縄県','女',728947)
問2
CREATE TABLE Members (
member_id INTEGER,
name NVARCHAR(15)
)
INSERT INTO Members VALUES
(100001, '鈴木達郎'),
(100002, '竹山登'),
(100003, '伊藤浩平'),
(100005, '田中宏'),
(100006, '山本幸次'),
(100007, '中田翔'),
(100008, '佐藤由美'),
(100010, '小林百子')
問3, 問4
CREATE TABLE Employees (
name NVARCHAR(15),
income INTEGER
)
INSERT INTO Employees VALUES
('鈴木達郎',235000),
('竹山登',300000),
('伊藤浩平',195000),
('田中宏',220000),
('山本幸次',195000),
('中田翔',280000),
('佐藤由美',330000),
('小林百子',220000)
問5
CREATE TABLE TestScores (
student_id INTEGER,
subject NVARCHAR(8),
score SMALLINT
)
INSERT INTO TestScores VALUES
(1001,'算数',90),
(1001,'国語',60),
(1001,'英語',80),
(1002,'算数',40),
(1002,'国語',60),
(1002,'英語',60),
(1003,'算数',90),
(1003,'国語',40),
(1003,'英語',50),
(1004,'算数',80),
(1004,'国語',90),
(1004,'英語',90)
問6
CREATE TABLE Seats (
seat SMALLINT,
row_id CHAR(1),
status NCHAR(1)
)
INSERT INTO Seats VALUES
(1,'A','占'),
(2,'A','占'),
(3,'A','空'),
(4,'A','空'),
(5,'B','空'),
(6,'B','空'),
(7,'B','空'),
(8,'B','占'),
(9,'C','空'),
(10,'C','空'),
(11,'C','空'),
(12,'C','空'),
(13,'D','占'),
(14,'D','空'),
(15,'D','空'),
(16,'D','占')
練習問題
問1.集計と水平展開
Populationテーブルを使って、全国、関東地方、四国地方における男女それぞれの人口を集計した表を作ってください。(結果イメージ)
(解説)
SQL文の実行順序はその書き順とは異なり、次のような順になっています。
FROM(どのテーブルからデータを取るかを選ぶ)
→WHERE(取得したい行を選ぶ)
→GROUP BY(部分集合に集約する)
→HAVING(取得したい部分集合を選ぶ)
→SELECT(どの列がほしいかを選ぶ)
→ORDER BY(並び替える)
個人的にはこの順序に沿ってクエリを組み立てていくのが自然に考えやすくて好きなので、基本的にこの順序で考えていきます。
とりあえず、今回使うテーブルはPopulationです。
FROM Population
特に排除したい行はないのでWHERE句は不要です。
男女ごとの集計結果が知りたいので、行を男女ごとでまとめましょう。
GROUP BY sex
最後に必要なものだけをSELECTしていきます。
この際注意してほしいのが、GROUP BY句と併用する場合SELECT句に書けるものは以下の３つに限られているという点です。
1.GROUP BY句で指定した集約キー
2.集約関数
3.定数
例えば今のテーブルから人口(population)をSELECTすることはできません。考えてみれば当たり前ですが、男女ごとに集約した部分集合に対して人口と言われても、どの県の人口のことを指しているのか特定できないからです。
しかし「人口の合計値」や「人口の平均値」などの情報(統計的属性)は持っているので、これらは集約関数により記述することが可能です。
例えば四国の人口を調べたければ、具体的に「県名が'徳島'、'香川'、'愛媛'、'高知'であるものの人口の合計値」というように指定してやればSELECT可能です。
SELECT SUM(CASE WHEN prefecture IN ('徳島県', '香川県', '愛媛県', '高知県')
THEN population
ELSE 0 END) AS '四国'
解答は次のようになります。
SELECT SUM(population) AS '全国',
SUM(CASE WHEN prefecture IN ('茨城県', '栃木県', '群馬県', '埼玉県','千葉県', '東京都', '神奈川県')
THEN population
ELSE 0 END) AS '関東',
SUM(CASE WHEN prefecture IN ('徳島県', '香川県', '愛媛県', '高知県')
THEN population
ELSE 0 END) AS '四国'
FROM Population
GROUP BY sex
問2.自動採番
会員情報を管理するようなシステムでは、会員の新規登録時などに、会員IDを自動で割り振りたい場合があるでしょう。
各会員を識別するために会員IDは一意であるべきですから、登録済の会員IDと重複しないような最小の値を割り振るというルールにしましょう。
では、Membersテーブルから新規会員用のmember_idを採番してみて下さい。
(結果イメージ)
(解説)
難しいことはありませんが、実務に直結しそうな内容なので一度練習しておきましょう。(実際はシーケンスオブジェクトを利用して採番するケースがほとんどだと思いますが、ここでは触れません。)
まずは会員ID+1が登録済の会員IDと重複しない行だけを抽出します。
FROM Members
WHERE (member_id + 1) NOT IN (SELECT member_id
FROM Members)
今抽出した中から最小のものを選べばよいだけですから、解答は次の通りです。
SELECT MIN(member_id + 1) AS '新規会員番号'
FROM Members
WHERE (member_id + 1) NOT IN (SELECT member_id
FROM Members)
問3.最頻値
SQLは集合指向言語と呼ばれます。手続き型言語のようにデータを順序付けて扱う発想から頭を切り替え、集合を意識することでグッと理解が深まるでしょう。
では集合指向的な考え方の練習として、Employeesテーブルから収入の最頻値を求めて下さい。
(結果イメージ)
(解説)
収入ごとに部分集合に分け、要素の数が最大の部分集合の収入をSELECTすればよいです。
まず全集合(社員テーブル)を収入が等しいものどうしの部分集合に分割します。
FROM Employee
GROUP BY income
ここから要素数が最大の部分集合だけを抽出します。
部分集合に対する抽出条件はHAVING句を用いて記述できます。
要素数が最大のもの、すなわち他のどの部分集合の要素数よりも大きい要素数を持つ集合を抽出します。
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
FROM Employees
GROUP BY income)
ここから収入を選び取ればそれが求めたかった最頻値です。
収入でGROUP BYしていますから収入をSELECTすることは可能です。
GROUP BY句を使う場合にSELECT句に書けるものについては問1の解説を参照して下さい。
SELECT income AS '最頻値'
FROM Employees
GROUP BY income
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
FROM Employees
GROUP BY income)
問4.中央値
次に先ほど使った収入テーブルから、収入の中央値を求めて下さい。 実装依存の関数やORDER BYによる並び替えを使えば簡単に書けるかもしれませんが、ここではそれらを使わず、あくまで集合指向的な考え方で解いてみて下さい。(結果イメージ)
(解説)
少しテクニカルですが集合指向の考え方の練習になると思います。元の集合を、真ん中の値を含めて半分(収入が低い方の部分集合と収入が高い方の部分集合)に分割しどちらの集合にも含まれる要素を求めます。
ポイントはやはり、いかにして半分に切り分けるかでしょう。
まずは自己結合を行います。自己結合は非等値結合と組み合わせることで重複順列・順列・組み合わせなどを作ることができます。今のように同一テーブル同士での大小比較を行いたい場合にも有用であり、使いこなせば強力な武器になります。
FROM Employees E1 CROSS JOIN Employees E2
GROUP BY E1.income
このテーブルから中央値を取り出すにはどうすればよいでしょうか。
大小比較可能な重複を許したn個の要素
a_1, a_2, ・・・ , a_nを考えます。
この時、ある要素a_pについて
(a_p以上となる要素の個数) >= n/2 ・・・(A)
を満たすa_pの集合が下半分の集合になります。
同様に,
(a_p以下となる要素の個数) >= n/2 ・・・(B)
を満たすa_pの集合が上半分の集合になります。
また、
欲しいのはこれらを同時に満たすa_pですから、これをSQL文にそのまま翻訳してやると以下の通りです。
SELECT E1.income
FROM Employees AS E1 CROSS JOIN Employees AS E2
GROUP BY E1.income
HAVING SUM(CASE WHEN E1.income <= E2.income
THEN 1
ELSE 0 END ) >= COUNT(*)/2 --(A)
AND SUM(CASE WHEN E1.income >= E2.income
THEN 1
ELSE 0 END ) >= COUNT(*)/2 --(B)
ここで注意点ですが、全体の行数が奇数ならばE1.incomeは1つしか抽出されないので、中央値は問答無用で1つに定まりますが、全体の行数が偶数の場合はE1.incomeは2つ抽出される可能性があります。すなわち、(A),(B)をともに満たすようなE1.incomeが2つ存在するようなケースです。(本問のような場合)
このような場合はどうしたら良いかと言いますと、定義に乗っとればこれら2つの値の平均が中央値です。
よって偶数の場合も考慮した解答は次の通りになります。
SELECT AVG(income)
FROM (SELECT E1.income
FROM Employees AS E1 CROSS JOIN Employees AS E2
GROUP BY E1.income
HAVING SUM(CASE WHEN E1.income >= E2.income
THEN 1
ELSE 0 END ) >= COUNT(*)/2
AND SUM(CASE WHEN E1.income <= E2.income
THEN 1
ELSE 0 END ) >= COUNT(*)/2 ) AS TMP
問5.全称量化
TestScoresテーブルから 算数の点数が80点以上で、かつ、国語の点数が50点以上の生徒を選択して下さい。(結果イメージ)
(解説)
述語論理における2種類の基本的量化として、次の全称量化と存在量化があります
①「全称量化」⇒ 全てのxは条件Pを満たす
②「存在量化」⇒ 条件Pを満たすxが(少なくとも1つ)存在する
②はEXISTSを用いて記述できますが、①を実装した述語はSQLにはまだ存在しません。
では①はSQLでは表現できないのかと言うとそれは違って、実は①もEXISTSを使って実装できるのですが、それにはちょっとしたコツが必要になります。
まず、EXISTSの否定NOT EXISTSを使えば「条件Pを満たすxが1つも存在しない」ことを表現できます。
さらに条件の方も否定してやれば
「条件Pを満たさないxが一つも存在しない」となり、これは「全てのxは条件Pを満たす」ことと同じです。
Point
肯定⇔二重否定
今回の問題文の条件をそのまま(肯定的に)捉えれば、
「ある学生の全ての行について、教科が算数ならば80点以上であり、かつ、教科が国語ならば50点以上である。」
となります。これを二重否定の形で言い換えてやれば、
「ある学生の行について、教科が算数ならば80点未満であるか、または、教科が国語ならば50点未満であるような行は1つも存在しない」
となり、これはSQL文で記述することができます。
解答は次になります。
SELECT DISTINCT student_id
FROM TestScores TS1
WHERE NOT EXISTS ( --以下を満たす行が１つもない
SELECT *
FROM TestScores TS2
WHERE TS2.student_id = TS1.student_id
AND 1 = CASE WHEN subject = '算数' AND score < 80 --算数が80点未満
THEN 1
WHEN subject = '国語' AND score < 50 --国語が50点未満
THEN 1
ELSE 0 END )
さらに、これまで何度も登場しているGROUP BY句と組み合わせれば、「算数の点数が80点以上で、かつ、国語の点数が50点以上の生徒の中で最も合計点が高い生徒」なども自由自在に求められます。
SELECT student_id
FROM TestScores TS
GROUP BY student_id
HAVING SUM(score) >= ALL (SELECT SUM(score)
FROM TestScores TS1
WHERE NOT EXISTS ( --以下を満たす行が１つもない
SELECT *
FROM TestScores TS2
WHERE TS2.student_id = TS1.student_id
AND TS2.student_id = TS.student_id
AND 1 = CASE WHEN subject = '算数' AND score < 80 --算数が80点未満
THEN 1
WHEN subject = '国語' AND score < 50 --国語が50点未満
THEN 1
ELSE 0 END )
GROUP BY student_id )
問6.バスの座席
最後の問題は現実でもよくありそうなシチュエーションの問題です。 バスの座席を表したSeatsテーブルから、3人が同じ列に並んで座れるような連続した席の並びを、全て選んで下さい。ただし通路を挟むのはOKとします。 今までのまとめとして、問4で使った自己結合+HAVING句を用いる方法と、問5で解説した全称量化を使う方法の2通りを考えてみて下さい。 なおSeatsテーブルの状態は図に書くと次のようになっています。
(結果イメージ)
まずは自己結合+HAVING句を用いる方法からいきましょう。
今知りたいのは連続した3行の状態なので、次のようにして3行をひとまとめにします。
FROM Seats AS S1 CROSS JOIN Seats S2
WHERE S2.seat BETWEEN S1.seat AND S1.seat + 2
GROUP BY S1.seat
後は部分集合の全ての要素について、状態が空で、かつ、列番号が同じになることを書いてやればよいです。
各要素(=行)が特定の条件を満たす集合に含まれるかどうかはCASE式を用いて記述できます。(特性関数という。)
CASE式も非常に強力な武器ですので、ぜひ練習して使いこなしてください。
SELECT S1.seat AS '席1',
S1.seat + 1 AS '席2',
S1.seat + 2 AS '席3'
FROM Seats AS S1 CROSS JOIN Seats S2
WHERE S2.seat BETWEEN S1.seat AND S1.seat + 2
GROUP BY S1.seat
HAVING COUNT(*) = SUM(CASE WHEN S2.status = '空'
AND S1.row_id = S2.row_id
THEN 1
ELSE 0 END)
次に全称量化を使う方法です。
連続する3行に対して成り立ってほしい条件は「全ての行について状態が'空'であり、かつ、列番号が同じである」です。
これを二重否定してやれば、「状態が'空'でないか、または、列番号が異なるような行が一つも存在しない」となります。
前問、本問を通して少し全称量化のパターンが掴めてきたのではないかと思います。
SELECT seat AS '席1',
seat + 1 AS '席2',
seat + 2 AS '席3'
FROM Seats AS S1
WHERE NOT EXISTS ( --以下を満たす行が1つもない
SELECT *
FROM Seats AS S2
WHERE S2.seat BETWEEN S1.seat AND S1.seat + 2 --連続した3席
AND ( S2.status <> '空' --状態が'空'でない
OR S2.row_id <> S1.row_id)) --列番号が異なる