SQL文の基本おさらい

こんにちは。
株式会社オプティマインドでソフトウェアエンジニアをしております鳥居と申します。
少し前から、オプティマインドではデータ分析のためにBigQueryを導入しています。
BigQueryでクエリを実行するためにはSQL文の基礎知識が必須ですが、私自身、業務でSQL文を書くのは今回が初めてになるため、改めて構文をおさらいしようと思います。

※主に分析目的で活用したいという理由から、更新（UPDATE）・挿入（INSERT）・削除（DELETE）の説明は省いています。
※勉強中のため、内容に誤りがあった場合は教えていただけますと幸いです。

最低限覚えておきたい記述ルール

• SELECTやFROMなどの大文字・小文字は区別されない。
• 文字列や日付はシングルクォート(')で囲む。数値は囲まない。

SELECTとFROM

SELECT *
FROM city

アスタリスク(*)は全ての列を意味する。
「city」というテーブルの全ての列を選択する。

WHEREで条件を指定する

SELECT *
FROM city
WHERE prefecture = 'aichi'

「prefecture」列に 'aichi' の値が入っているという条件で指定する。
愛知県に該当する行のみが抽出される。

比較演算子

=, >=, >, <=, <が使用できる。
等しくないことを意味する場合は、<>を用いる。

日付の記述例

WHERE timestamp <= '2022-09-25 00:00:00'

AND演算子、OR演算子

SELECT name, population
FROM city
WHERE prefecture = 'aichi'
AND population >= 100000

愛知県かつ人口が10万人以上に該当する行のみで、「name」「population」列に絞って選択する。

DISTINCTで重複を排除する

SELECT DISTINCT prefecture
FROM city
WHERE country = 'jpn'

日本に該当する行で、一意の都道府県を抽出する。
各行が、愛知県名古屋市、愛知県春日井市...のように同一の都道府県が複数行に渡るテーブルから、都道府県を一意に表示させたい場合に使える。

ORDER BYで並び替える

SELECT *
FROM city
WHERE country = 'jpn'
ORDER BY population DESC

日本に該当する行で、人口の多い順（＝降順）に並び替える。
昇順にしたいときは、ASCを用いる。
（ASCはデフォルト値であるため、省略することも可能）

覚えておきたい5つの集約関数

• COUNT: 行数をカウントする。
• SUM: 数値列のデータを合計する。
• AVG: 数値列のデータを平均する。
• MAX: 任意の列の最大値を出す。
• MIN: 任意の列の最小値を出す。

COUNTで行数をカウントする

SELECT COUNT(*)
FROM city
WHERE country = 'jpn'

「country」が 'jpn' に該当する行をカウントする。

MIN, MAX, SUM, AVGを出力する

SELECT MIN(population), MAX(population), SUM(population), AVG(population)
FROM city
WHERE country = 'jpn'

日本の人口の「最小値」「最大値」「合計値」「平均値」を求める。

GROUP_CONCATで複数行を1行に集約する

SELECT GROUP_CONCAT(name)
FROM city
WHERE country = 'jpn'
AND prefecture = 'aichi'

愛知県の都市を1行に集約する。
（結果は、1行の1つのセル内に「Nagoya,Kasugai,Nagakute...」と集約される形になる。）

GROUP_CONCATにDISTINCTキーワードを用いて、重複させずに集約する

SELECT GROUP_CONCAT(DISTINCT prefecture)
FROM city
WHERE country = 'jpn'

同一の都道府県が複数行に存在しているテーブルであっても、「Tokyo,Kanagawa,Osaka...」のように一意に集約できる。

GROUP_BYでグループを切り分ける

SELECT prefecture, COUNT(*)
FROM city
WHERE country = 'jpn'
GROUP BY prefecture

都道府県ごとにグループを切り分け、それぞれの都道府県に都市がいくつ登録されているのかをカウントする。

HAVINGで条件を指定してグループを切り分ける

SELECT prefecture, COUNT(*)
FROM city
WHERE country = 'jpn'
GROUP BY prefecture
HAVING COUNT(*) = 4

「都市が4つ」という条件で、都道府県ごとにグループを切り分ける。

WHERE COUNT(*) = 4は使えないため注意

COUNT等の集約関数を記述できるのは、SELECT, ORDER BY, HAVINGの3つだけ。

上記までに出てきた句の記述順序

必ずこの順番で記述する必要がある。

①SELECT
②FROM
③WHERE
④GROUP BY
⑤HAVING
⑥ORDER BY

INNER JOINで内部結合する

INNER JOIN の用途を理解するため、順を追って2つのクエリを実行する。

1つ目のクエリ

SELECT *
FROM countrylanguage
WHERE language = 'japanese'
ORDER BY percentage DESC

「countrylanguage」は、各国で使われている言語のテーブル。
世界の国のうち、日本語を使っている国を表示させる。

CountryCode	Language	IsOffiial	Percentage
JPN	Japanese	T	99.1
GUM	Japanese	F	2.0
BRA	Japanese	F	0.4
USA	Japanese	F	0.2

この結果として、該当する「countrycode」は、以下があることが分かった。

• JPN
• GUM
• BRA
• USA

しかし、上記のうち GUM, BRA はどこの国を指すのか分からない。

2つ目のクエリ

SELECT countrylanguage.*,country.name
FROM countrylanguage
INNER JOIN country
ON countrylanguage.countrycode = country.code
WHERE language = 'japanese'

この結果として、以下のようなテーブルが表示され、先にあった疑問が解決する。
SELECT で指定している通り、「countrylanguage」テーブルの全ての列と、「country」テーブルの「name」の列が結合されている。
結合の条件は、ON で指定している。これにより両テーブルで一致する行のみを表示させることができる。

CountryCode	Language	IsOfficial	Percentage	name
BRA	Japanese	F	0.4	Brazil
GUM	Japanese	F	2.0	Guam

内部結合と外部結合の違い

外部結合には、LEFT OUTER JOIN を用いる。（尚、LEFT JOINと書くことも可能）
ONで条件を指定した時に、1つ目のテーブルには存在するが2つ目に結合するテーブルは null値の場合でも、外部結合の場合は表示される。
一方、内部結合の場合は値が入っている行のみに絞って表示される。

付録: (BigQuery)よく使うJSON関数

BigQueryを利用するにあたって、独自に用意されているクエリ文を書く機会も多いため、今回は普段からよく使うJSON関数について纏めておきたいと思います。
中でも、特に基本の部分となる抽出関数に絞って説明します。

JSON_QUERY

WHERE JSON_QUERY(data, '$.test') = 'hello'

第1引数: JSON形式の文字列
　　　 （セル内にJSON形式の文字列データを持つテーブルのカラム名でOK）
第2引数: JSONの中で特定の調べたい値に該当するパス

返り値: JSON形式のSTRING（文字列、数値、ブール値、配列、オブジェクト）

JSON_VALUE

第1引数: JSON形式の文字列
第2引数: JSONの中で特定の調べたい値に該当するパス

返り値: STRING

JSON_QUERY と JSON_VALUE の違い

1. JSON_QUERY はJSON形式のSTRINGが返され、 JSON_VALUE はSTRINGが返される。

   SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') AS json_name,
     JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') AS scalar_name,
     JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') AS json_age,
     JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;
   
   +-----------+-------------+----------+------------+
   | json_name | scalar_name | json_age | scalar_age |
   +-----------+-------------+----------+------------+
   | "Jakob"   | Jakob       | "6"      | 6          |
   +-----------+-------------+----------+------------+
   

2. JSON_VALUE はSTRINGで返されるため、それに当てはまらない型（配列など）だった場合は NULL が返される。

   SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
     JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;
   
   +--------------------+------------+
   | json_query         | json_value |
   +--------------------+------------+
   | ["apple","banana"] | NULL       |
   +--------------------+------------+
   

JSON_QUERY_ARRAY

SELECT JSON_QUERY_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS json_array;

+---------------------------------+
| json_array                      |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

第1引数: JSON形式の文字列
第2引数: JSONの中で特定の調べたい値に該当するパス

返り値: JSON形式の配列

JSON_VALUE_ARRAY

SELECT JSON_VALUE_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

第1引数: JSON形式の文字列
第2引数: JSONの中で特定の調べたい値に該当するパス

返り値: STRINGが格納された配列

参考リンク

BigQuery - JSON関数

おわりに

最後までお読みいただき、ありがとうございました！
オプティマインドでは「多様性が進んだ世の中でも、全ての人に物が届く世界を持続可能にする」という物流業界の壮大な社会課題を解決すべく、 一緒に働く仲間を大募集中です。 少しでも興味が湧いた方は是非お気軽にカジュアル面談をお申し込みください！

https://recruit.optimind.tech/