SQL。言わずとしれたデータベース操作言語。今やプログラマー必修です。
SQLの中でも、最も重要、かつ難解なのが、SELECT文。
ですが、経験豊富なプログラマーでも、SELECTを苦手としている人はけっこう多い。
理解するポイントはこれ。「SELECTは、表から表へ変換する操作」。
そしてその操作は、大きく４つの要素に分類されます。どのような操作なのか、見ていきましょうを解説。
SQL SELECTが持つ意味
SQLとは、データベース操作言語です。そしてSELECTは、データベースからデータを取得するという、最も重要な操作です。
SELECTは「表形式のデータベースを操作する」
SELECTで検索する対象とするのは、表形式のデータです。
たとえば以下は、国の情報を管理するデータ。国コード、国名、地域、のデータを管理する、countryテーブルです。
SELECTは「表から表に変換する」操作
SELECT操作は、「データベースの検索や抽出を行う」こと。
この説明、間違いではないですが、あまりイメージが湧きにくい。
SELECTを理解するためには、このイメージが大事。
例をあげましょう。東アジアの国名一覧を取得する操作を考えます。
SELECT name, region FROM country WHERE region = 'Eastern Asia'
これは、「国の一覧表」という表から、「東アジアの国名表」という表に変換する操作、と言い換えることができますね。
SELECTで表を変換する４つの要素
ある表を、別の表に変換する。その操作内容は、大きく４つの操作に分類できます。
- 射影（projection） 欲しい列を持ってくる
- 選択（selection） 条件に合うデータだけ抽出する
- 集約（aggregation）計算する 列を計算してまとめる
- 結合（join） 複数の表を合わせる
複雑なSELECT文も、結局は、この４つの操作の組み合わせで構成されているのです。
射影：欲しい列を持ってくる
まずは基本の、射影操作。表の中から、欲しい列を「取り出す」操作です。
SELECTに続けて、欲しい列を指定します。
SELECT name, region
FROM country
;
これは分かりやすいでしょう。
選択：条件に合う行だけ抽出する
次は、欲しいデータの条件を与えて、そのデータだけを「選択する」。
選択する条件はWHEREで指定します。
SELECT code, name, region
FROM country
WHERE region = 'Eastern Asia'
;
これも見た目で分かりやすいでしょう。
集約：列を計算してまとめる
続いて「集約」。列の値を計算してひとつにまとめる操作です。
集約関数と呼ばれる計算が、これに該当します。
- COUNT：個数を計算
- SUM：合計を計算
- MAX：最大値を計算
- MIN：最大値を計算
- AVG：平均値を計算
計算の意味は自明。ですが、SQL的にはちょっとイメージを変えておきましょう。
集約関数は、「１列分の表を、１つのデータにまとめた表に、変換する」と捉えます。
cityテーブルから、最大の人口を取り出す例です。「人口」という１列の表を、「最大人口」という１つのデータにまとめた表に、変換します。
SELECT max(population)
FROM city
;
「列を縮めて集約する」イメージですね。
ただ実際に使うときは、「国ごとに最大人口を集計する」ほうが使いやすいでしょう。
といって、単純にこう書いても、エラーになります。
SELECT countrycode, max(population)
FROM city
;
--ERROR: column "city.countrycode" must appear in the GROUP BY clause or be used in an aggregate functionここで使うのがGROUP BY。どの単位で集約するか指定します。
SELECT countrycode, max(population)
FROM city
GROUP BY countrycode
;
国ごとの集約した結果が、得られました。
結合：複数の表を合わせる
SELECTの中で一番難しいのがこれ。２つの表を「結合する」。
以下の、countryテーブル、cityテーブル、この２つを結合してみましょう。
難しそうな感じがする結合操作。
でも実は、イメージはけっこう単純。
２つの表の、すべてのデータを組み合わせて、１つの表にする。それが、結合操作の実体です。
結合した後は、こんな感じの１つの表にまとまります。
結合してできた表は、かなりのデータ数になります。例えばcountryテーブルが200行、cityテーブルが500行なら、結合後のテーブルは200 ✕ 500 = 100,0000行になります。
でも、できた表を見ると、明らかに要らなそうなデータがあります。
例えば、国名がJapanで、都市名がNewYork。こんなデータは意味がない。
こうみると、役に立つのは、countryのcodeと、cityのcountrycodeが一致するデータだけ。当然といえば当然。
この条件も踏まえて、結合操作をSELECT文で書いてみましょう。
結合する表をINNER JOIN、条件をONで指定します。
SELECT country.name, city.name
FROM country
INNER JOIN city
ON country.code = city.countrycode
;
必要なデータだけ残った状態で、２つの表を結合できました。
高度なSELECTに挑戦しよう
SELECTに登場する、「射影」「選択」「集約」「結合」の４つの操作。
この４つの操作を組み合わせることで、複雑な問い合わせにも対応することができます。
４つの操作をすべて使った、応用問題にチャレンジしてみましょう。
応用問題のお題
使う表は、先ほども出てきた、countryテーブルとcityテーブル。
このテーブルから、以下条件で検索します。
欲しい結果は、このような表になるはず。
この表を作り出す、SELECT文を作ります。
最初に、SELECT文の完成形をお見せしておきましょう。こんな感じになります。
SELECT country.name, maxpopucityname.name, maxpopucityname.maxpopu
FROM country
INNER JOIN (
SELECT city.countrycode, city.name, maxpopucity.maxpopu
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucity
ON city.countrycode = maxpopucity.countrycode
AND city.population = maxpopucity.maxpopu
) AS maxpopucityname
ON country.code = maxpopucityname.countrycode
WHERE country.region = 'Eastern Asia'
;だいぶ難解ですね。でも大丈夫。
４つの操作を、順番に紐解いていけば分かります。SELECT文が出来る過程を、見ていきましょう。
STEP1：最大の人口を計算する
まずはとにかく、最大人口を計算しないといけません。
「集約」ですね。先ほども出てきたやつです。国ごとの最大人口を計算します。
SELECT countrycode, max(population)
FROM city
GROUP BY countrycode
;
この結果をもとにして、SQLを改造していきましょう。
STEP2：最大の人口を持つ都市名を付け加える
これだとまだ、都市名が表示できていません。都市名も一緒に表示しましょう。
都市名が入っているのは、cityテーブルです。こう考えます。
先ほど計算した最大人口の表」と「cityの表」を、「結合」する
さきほどの表に、maxpopucityという名前をつけ、INNER JOINで結合します。
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucity
ON city.countrycode = maxpopucity.countrycode
AND city.population = maxpopucity.maxpopu
INNER JOINで指定しているのが、「テーブル名」でなく、「SELECT文」であることに注目です。
SELECTが、「表から表に変換する操作」。だからSELECTでできた結果も「表」。
なので、その「SELECTでできた表」を「他のテーブル」と結合する、ということもできるのです。
結合条件は、国名コードが同じ、かつ、人口が同じ、です。
これで、その国の最大人口を持つ都市、という表が得られました。
STEP3：国名を付け加える
つぎはこれに、国名も加えます。国名が入っているのは、countryテーブル。
要領は先ほどと同じですね。countryテーブルと、先ほどの表を、「結合」する。
結合条件は、国名コードが同じ、ですね。
SELECT country.name, country.region, maxpopucityname.name, maxpopucityname.maxpopu
FROM country
INNER JOIN (
SELECT city.countrycode, city.name, maxpopucity.maxpopu
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucity
ON city.countrycode = maxpopucity.countrycode
AND city.population = maxpopucity.maxpopu
) AS maxpopucityname
ON country.code = maxpopucityname.countrycode
最終形にだいぶ近づきました。あと一歩です。
東アジアに絞る
最後の条件です。東アジア(Eastern Asia)のデータだけにします。
「選択」ですね。WHEREで、regionを絞る条件を入れます。（また選択からは、regionを外しておきます）
SELECT country.name, maxpopucityname.name, maxpopucityname.maxpopu
FROM country
INNER JOIN (
SELECT city.countrycode, city.name, maxpopucity.maxpopu
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucity
ON city.countrycode = maxpopucity.countrycode
AND city.population = maxpopucity.maxpopu
) AS maxpopucityname
ON country.code = maxpopucityname.countrycode
WHERE country.region = 'Eastern Asia'
;
これで、最終結果の表になりました。
完成！
これで、SELECT文の完成です。
結果を改めて確認しましょう。
SELECT country.name, maxpopucityname.name, maxpopucityname.maxpopu
FROM country
INNER JOIN (
SELECT city.countrycode, city.name, maxpopucity.maxpopu
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucity
ON city.countrycode = maxpopucity.countrycode
AND city.population = maxpopucity.maxpopu
) AS maxpopucityname
ON country.code = maxpopucityname.countrycode
WHERE country.region = 'Eastern Asia'
;
SELECT文は複雑ですが、４つの操作の組み合わせを考えると、表が変換される過程が見えてきますね。
注意：SELECTの書き方はひとつじゃない
応用問題を解いて、SELECT文を作りました。
ただ実は、答えとなるSELECT文は、これが唯一の答えではありません。
例えば、こう書いても、同じ表が出力できます。
SELECT name,
(SELECT name
FROM city
WHERE country.code = city.countrycode AND city.population = (
SELECT max(population)
FROM city
WHERE city.countrycode = country.code
)
) AS cityname
,
(SELECT max(population)
FROM city
WHERE country.code = city.countrycode
) AS maxpopu
FROM country
WHERE country.region='Eastern Asia'
;特に、「結合」を伴うSELECTの場合、これは覚えておきましょう。
- SELECTは、同じ結果が出すにも、複数の書き方ができるときがある
- ただし、書き方によって検索速度に大きな差がでる場合がある
検索速度が遅い…と感じたときは、４つの操作を確認し、ボトルネックを特定することが大事です。
まとめ
SELECTが何をしているのか混乱してきたら、表から表へ次々に変換していく、と考えれば、イメージしやすいです。
SELECTは、上達すれば、かなり複雑な条件でも検索できるように書けます。
ただその発想は、普通の手続き型プログラミングとは異なる独自の発想力が必要です。
まるでパズルのような複雑さ。うまくSELECT文を書けるようになるためには、いろいろな例題に触れることが大事です。
クラウドやマイクロサービスといった技術が普及していくなか、データベースを操作する技術は、ますます重要性が増してきています。
SELECTをマスターして、データベースエンジニアとしてのスキルアップを目指しましょう。
コメント