複数の問い合わせ

SQL入門の7回目はサブクエリ・副問合せについて説明します。実際の現場では、複数のテーブルを使って、それらの集計した結果をまた別の集計で使用する、そんなことを繰り返すことが少なくありません。ここでは、そのように複数のSQLを組み合わせる手法について説明します。

本記事はFuture Coders独自教材からの引用です。

サブクエリ（副問い合わせ）

サブクエリとは、selectで抽出した結果をさらに、別の問い合わせで使用することをいいます。

主に3つの使い方があります。

selectで１つの値を取り出す場合
selectで複数の値を取り出す場合
selectで表を取り出す場合

１つの値を取り出した場合、その値を条件比較などで使用できます。複数の列を取り出した場合、その値をin演算子で使用したりすることが一般的です。表を取り出した場合、その表に対してさらにselectで抽出したりすることができます。

よく利用されるケースのイメージを以下に示します。

左図 … サブクエリで単一の値が得られた場合、その値を条件比較で使用します。
中図 … サブクエリで複数の値が得られた場合、IN演算子での条件比較で使用します。
右図 … サブクエリで表形式のデータが得られた場合、from句で指定することで、そこからさらに絞り込むことができます。

実際の使い方を見ながら慣れてゆきましょう。

単一の値のケース

以下はもっとも人口の多い都市を含む国を選択するSQLです。

select*from `sample.country`
where Code = (
  select CountryCode FROM `sample.city` 
  orderby Population desc limit 1
)

このような副問い合わせを解読する場合は、内側から読んでゆくのがコツです。内側のSQLは以下の通りです。

select CountryCode FROM `sample.city` 
  orderby Population desc limit 1

cityテーブルから人口の多い順に並べ、先頭の1つのレコードのみ取り出し、そのCountryCode値を返しています。上記SQLを実行すると”IND”という値が得られます。

その結果を使って、外側のSQLを実行します。つまり、以下のSQLを実行しているのと同じことになります。

select*from `sample.country`
where Code = "IND"

結果として以下のようなデータが得られます。インドに関する情報が抽出されています。

複数の値のケース

1992年以降に独立した国で話されている言語をしらべてみます。SQLは以下のようになります。

select CountryCode, languagefrom `sample.countrylanguage`
where  CountryCode in (
  SELECT Code FROM `sample.country` where safe_cast(IndepYear asint) >1992
)
orderby CountryCode

以下のように23件がヒットします。

この場合も内側から見てゆきます。副問い合わせは以下のSQLです。

SELECT Code FROM `sample.country` where safe_cast(IndepYear asint) >1992

実行結果は(“ERI”, “CZE”, “SVK”, “PLW”)の4つの値となります。これを外側のSQLにおいて

where CountryCode in ("ERI", "CZE", "SVK", "PLW")

と実行していることになります。

表形式データのケース

countryテーブルからAsiaの国を列挙し、そのCodeと等しい値を持つレコードをcityテーブルから抽出し、寿命順に並べたサンプルです。

SELECT
  C.Name, A.Name,
  LifeExpectancy
FROM
  sample.city AS C
JOIN (
  SELECT*FROM `sample.country`
  WHERE Continent = "Asia"
) AS A ON C.CountryCode = A.Code
ORDERBY LifeExpectancy desc

少し複雑に見えるかもしれませんが、これも内側の副問い合わせから見てゆくと、理解しやすくなります。

内側のSQLは以下の通りです。

SELECT*FROM `sample.country`
  WHERE Continent = "Asia"

countryテーブルからContinent列の値がAsiaのレコードを抽出しています。51件がヒットします。結果は以下のようになります。

この得られた結果とcityテーブルをJOINしています。JOINしている箇所を抜き出すと以下のようになります。

SELECT ...
FROM sample.city AS C
JOIN (サブクエリ) AS A ON C.CountryCode = A.Code

cityテーブルにC、サブクエリテーブルにAという別名をつけて、C.CountryCode = A.CodeでJOINしています。

最終的な結果は以下の通りです。日本の各都市で寿命の上位を独占していることが分かります。

演習

ex-subquery1.py

GNPが最大の国で話されている言語を多い順に列挙してください。GNPの列には文字も含まれています。safe_castで数値に変換してから並べ替えが必要なことに注意してください。12の言語がヒットします。

ex-subquery2.py

Spanishを話している国で、Populationが大きい順に並べてください。Populationのデータ型がStringなので、並べ替えるには数値へ変換することがあることに注意してください。28レコードで最大がUSA,次点がMexicoとなります。

ex-subquery3.py

Africa大陸にある国で、話されている言語の種類を列挙し、そのレコード数が多い順に上位10個をリストアップしてください。

解答例

ex-subquery1.py

selectLanguagefrom sample.countrylanguage
where countrycode = (
  SELECT Code FROM `sample.country` 
  orderby safe_cast(GNP as float64) desc limit 1
) 
orderby Percentage desc

ex-subquery2.py

select Code, Name, Population from `sample.country`
where Code in (
  SELECT CountryCode FROM `sample.countrylanguage` wherelanguage= "Spanish"
)
orderbycast(Population as float64) desc

ex-subquery3.py

selectlanguage, count(*) as count from `sample.countrylanguage` as L
join (
  SELECT*FROM `sample.country` 
  where Continent = "Africa"
) as C on C.code = L.CountryCode
groupbylanguageorderby count desc
limit 10

Withを使った問い合わせの連結

サブクエリを使うと、問い合わせの結果を別の問い合わせで利用することができました。しかしながら、問い合わせを連鎖させると、階層構造が深くなってSQLが読みにくくなったりすることがあります。

そのような時にはWith句をつかうと読みやすくなるかもしれません。サブクエリを使った場合と、With句を使った場合のイメージを以下に示します。

select句を使った場合、その入れ子の階層が深くなってゆきます。内側からひも解いて理解してゆく必要があります。一方、with句を使用した場合、上から順番にテーブルを加工してゆくイメージとなります。テーブルを加工してゆく途中経過に応じて、上から順番に処理してゆくため、その処理の段数が増えたときに、副問い合わせよりも一般的に読みやすくなります。

With句の文法は以下の通りです。

with 
    テーブル名1as (Selectなどのテーブル式1),
    テーブル名2as (Selectなどのテーブル式2),
    ....
    テーブル名N as (Selectなどのテーブル式N)
select ...

Withを使ったサンプルはBigQueryのリファレンスでも頻繁に利用されます。以下は条件式のところに記載されているサンプルです。

https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions?hl=ja

WITH Numbers AS (
  SELECT90as A, 2as B UNIONALLSELECT50, 8UNIONALLSELECT60, 6UNIONALLSELECT50, 10
)
SELECT
  A,
  B,
  CASE A
    WHEN90THEN'red'WHEN50THEN'blue'ELSE'green'ENDASresultFROM Numbers

With句の中ではSelect命令をUNIONでつなげることで仮想的なテーブルを作成しています。そのテーブルにはAとBの2列がありますが、そのA列に対してCASE句を適用することでresult列を生成しています。

具体例をみてみましょう。副問い合わせで表形式のデータを扱ったときのサンプルをwith句を使って記述してみます。

単一の値のケース

以下はサブクエリの最初の例をwithを使って書き換えた例です。

with Codes as (
  select CountryCode FROM `sample.city` 
  orderby Population desc limit 1
)
select*from `sample.country`
where Code = (select CountryCode from Codes)

Codesには単なる値”IND”が格納されます。このまま、Where句の値としてしようすることはできないので、Select句のfromの後ろに配置しています。

複数の値のケース

以下はサブクエリの2番目の例をwithを使って書き換えた例です。

with Countries as (
  SELECT Code FROM `sample.country` where safe_cast(IndepYear asint) >1992
)
select CountryCode, languagefrom `sample.countrylanguage`
where CountryCode in (select*from Countries)
orderby CountryCode

サブクエリの内容を最初のWith句でCountriesというテーブルに格納しています。その内容を次のSelectのwhere句の中で使用しています。Countriesはテーブルの形なので、Whereのin演算子の直後に直接記述することはできません。そこで、(select * from Countries)と記述することで値の列に変換しています。

表形式データのケース

最後にサブクエリで表形式の値を扱った例「countryテーブルからAsiaの国を列挙し、そのCodeと等しい値を持つレコードをcityテーブルから抽出し、寿命順に並べたサンプル」をwithで書き換えてみます。

With Asians as (
  SELECT LifeExpectancy, Code, Name FROM `sample.country`
  WHERE Continent = "Asia"
), 
AsiaCities as (
  select 
    LifeExpectancy, 
    A.Name as CountryName, 
    C.Name as CityName 
  from Asians as A
  join `sample.city` as C on (A.Code = C.CountryCode)
)
select CityName, CountryName, LifeExpectancy from AsiaCities
orderby LifeExpectancy desc

最初のSelectではcountryテーブルからAsia大陸の国（寿命、Code、国名）を抽出してAsiansという名前で参照します。次に、cityテーブルとAsiansをjoinして、そこから寿命、都市名、国名を抽出してAsiaCitiesという名前で参照します。最終的に、AsiaCitiesというテーブルから3つの列を抽出しています。

処理が行われる順番にSQLが記述されているのため、慣れればWithを使った記述のほうが分かり易く感じるかもしれません。

演習

以下の演習は副問合わせではなく、with句を使用してください。

ex-with1.py

countryテーブルからNameが”Japan”のレコードを抽出し、そのCodeを使ってcityを検索してください。248件がヒットします。

ex-with2.py

countryテーブルからRegionが”Eastern Asia”のレコードを抽出し、そのCodeを使ってcityを検索してください。740件がヒットします。

ex-with3.py

cityテーブルからPopulationが1000000より大きい都市と、その都市を含む国を選択して、BigCitiesというテーブルにします。さらに、countryテーブルからその都市を含む国名とGDPを抽出してBigCountriesというテーブルにし、 BigCountriesから国名とGNPを列挙してください。その際にGNPの大きい順に10レコード抽出します。以下のようなテーブルを作成してください。

ex-with4.py

countryテーブルから寿命が60より大きい国を選びLongLifeContriesとし、さらにLongLifeContriesから人口Populationが10,000,000人より大きい国を選びBigLongLifeControesとし、 GNPの大きい順にならべてください。53件がヒットします。

解答例

ex-with1.py

with JpCode as (
  SELECT Code FROM `sample.country` where name = "Japan"
)
select*from `sample.city`
where CountryCode = (Select*from JpCode)

ex-with2.py

with Regions as (
  SELECT Code FROM `sample.country` where Region = "Eastern Asia"
)
select*from `sample.city`
where CountryCode in (Select*from Regions)

ex-with3.py

With BigCities as (
  SELECT CountryCode, Name, Population FROM `sample.city` where Population >1000000
), BigCountries as (
  Select Name, GNP from  `sample.country` where Code in (select CountryCode from BigCities)
)
select Name, cast(GNP as float64) GNP from BigCountries
orderby GNP desc limit 10

ex-with4.py

With LongLife as (
  SELECT*FROM `sample.country` where safe_cast(LifeExpectancy as float64) >60
),
BigLongLife as (
  select*from LongLife where safe_cast(Population as float64) >10000000
)
select  Name, Population, LifeExpectancy, cast(GNP as float64) GNP from BigLongLife
orderby GNP desc

SQL入門 – 7)サブクエリ・副問合せ

複数の問い合わせ

目次

サブクエリ（副問い合わせ）

単一の値のケース

複数の値のケース

表形式データのケース

演習

ex-subquery1.py

ex-subquery2.py

ex-subquery3.py

解答例

ex-subquery1.py

ex-subquery2.py

ex-subquery3.py

Withを使った問い合わせの連結

単一の値のケース

複数の値のケース

表形式データのケース

演習

ex-with1.py

ex-with2.py

ex-with3.py

ex-with4.py

解答例

ex-with1.py

ex-with2.py

ex-with3.py

ex-with4.py