MAGAZINE

ルーターマガジン

データ/フォーマット

次に身につけたい参照系クエリ入門

2019.04.05
Pocket

こんにちは。アルバイトのkondoです。
クローリングで集めたデータの調査で、SQLを使いデータベースへ問い合わせをすることがあります。
今回はクエリの中でも少し難し目の参照系のクエリ(GROUP BY句, JOIN句, EXISTS演算子)について、そのクエリの使い方を紹介します。

サンプルデータ

以下のサンプルデータを使って解説します。MySQLが配布しているサンプルデータです。
https://dev.mysql.com/doc/index-other.html

  • countriesテーブル
Code Name Continent Region Population
ABW Aruba North America Caribbean 103000
AFG Afghanistan Asia Southern and Central Asia 22720000
AGO Angola Africa Central Africa 12878000
AIA Anguilla North America Caribbean 8000
ALB Albania Europe Southern Europe 3401200
AND Andorra Europe Southern Europe 78000
ANT Netherlands Antilles North America Caribbean 217000
ARE United Arab Emirates Asia Middle East 2441000
ARG Argentina South America South America 37032000
ARM Armenia Asia Middle East 3520000

  • citiesテーブル
ID Name CountryCode District Population
1 Kabul AFG Kabol 1780000
2 Qandahar AFG Qandahar 237500
3 Herat AFG Herat 186800
4 Mazar-e-Sharif AFG Balkh 127800
5 Amsterdam NLD Noord-Holland 731200

SQLの説明

JOIN句

JOIN句は、2つのテーブルのレコードを繋げるクエリです。
countriesのテーブルとcitiesのテーブルを繋げたいとします。この2テーブルは国コード(countriesテーブルのCodeカラムとcitiesテーブルのCountryCodeカラム)でつながっているので、それが同じもの同士で結合させてあげます。

SELECT countries.Code, countries.Name, cities.Name FROM countries JOIN cities ON countries.Code = cities.CountryCode;
Code Name Name
ABW Aruba Oranjestad
AFG Afghanistan Kabul
AFG Afghanistan Qandahar
AFG Afghanistan Herat
AFG Afghanistan Mazar-e-Sharif
AGO Angola Luanda
AGO Angola Huambo
AGO Angola Lobito
AGO Angola Benguela

中央の列に国名が入り、右の列に都市名が入っていますね。countriesとcitiesの2テーブルがJOIN句で繋がり、1つのテーブルになりました。
FROM句で1つ目のテーブル名を指定した後、JOIN句に続いて2つ目のテーブル名を書き、その後のON句の後に結合する条件を書きます。
カラムを指定するときは、テーブル名.カラム名の書き方で指定してあげましょう。
親子関係の2テーブルのレコードを親子セットで1レコードとして扱う、といった使い方をよくします。

GROUP BY句

GROUP BY句は、レコードをグループ化する命令で、今までの参照クエリとは少し異なります。
カラムの値ごとにレコードを分け、その分けられたレコードたちをそれぞれ1つのまとまりとして扱います。
具体的な使い方としては、countriesテーブルのContinentカラムの値を全て見たいという時などに使います。

SELECT Continent FROM countries GROUP BY Continent;

このように、GROUP BY句に続いてカラム名を指定します。

Continent
Asia
Europe
North America
Africa
Oceania
Antarctica
South America

また、グループに対し集計関数を使ったり、グループ分けのカラムを複数指定することもできます。
集計関数countと、GROUP BY句にRegionカラムも指定してみます。

SELECT Continent, Region, count(*) FROM countries GROUP BY Continent, Region;
Continent Region count(*)
Asia Eastern Asia 8
Asia Middle East 18
Asia Southeast Asia 11
Asia Southern and Central Asia 14
Europe Baltic Countries 3
Europe British Islands 2
Europe Eastern Europe 10
Europe Nordic Countries 7

グループ化の基準列の値に条件をつけたいときは、HAVING句を後につけます。
countriesを独立年(IndepYear)でグループ化したうちの、独立年が1980年以降のグループのみを指定したいときはこう書きます。

SELECT IndepYear,count(IndepYear) FROM countries GROUP BY IndepYear HAVING IndepYear > 1980;
IndepYear count(IndepYear)
1981 2
1983 1
1984 1
1990 3
1991 18
1992 1
1993 3
1994 1

EXISTS演算子

EXISTS演算子は、EXISTS (サブクエリ)という書き方をし、サブクエリで検索した行があればTRUEを返す演算子です。
サブクエリはクエリの中に書くクエリのことで、このEXISTS演算子以外の場所にも書くことができ、複雑な問い合わせに使います。
EXISTS演算子の具体的な使い方は以下のような書き方をして使い、これは「国のうち、『国に紐付いた都市で人口が800万人以上のもの』があるもの」という意味です。

SELECT `Code`, `Name`, `Continent`, `Region`, `Population` FROM countries WHERE EXISTS (SELECT * FROM cities WHERE cities.CountryCode = countries.Code AND population > 8000000)
Code Name Continent Region Population
BRA Brazil South America South America 170115000
CHN China Asia Eastern Asia 1277558000
IDN Indonesia Asia Southeast Asia 212107000
IND India Asia Southern and Central Asia 1013662000
KOR South Korea Asia Eastern Asia 46844000
MEX Mexico North America Central America 98881000
PAK Pakistan Asia Southern and Central Asia 156483000
RUS Russian Federation Europe Eastern Europe 146934000
TUR Turkey Asia Middle East 66591000
USA United States North America North America 278357000

出力されたテーブルのPopulationカラムは国の人口です。出力されるのはメインクエリで指定した内容で、サブクエリはその条件指定に使われるだけ出力はされません。

最後に

SQLはとっつきにくい雰囲気を感じますが、ひとつひとつ身につけていけば覚える量はそこまで多くはありません。この記事がSQL入門のお役に立てば幸いです。

Pocket

CONTACT

お問い合わせ・ご依頼はこちらから