bigquery

SQL入門 – 2)select

kenichiro

SQL入門の第2回はselect文です。SQLの最初は直感的でわかりやすいselect命令から始めるとよいでしょう。単に列を抽出するところから、条件による絞り込みや並べ替えなどいろいろなケースを見てゆきましょう。

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

値の参照

式

文法は以下の通りです。複数の式がある場合は、カンマで区切ります。

select 式

いろいろと試してみましょう。

https://console.cloud.google.com/ からGCPのコンソールを開き、左のメニューからBigQueryを選択します。意図したプロジェクトが選択されているか画面左上にあるプルダウンメニューを確認してください。右側のペインの＋アイコン「クエリを新規作成」をクリックして作業用のエディタを開きます。

image 15
SQL入門 – 2)select 22

select1

以下のように入力して、「実行」ボタンを押下してください。計算結果の30が画面下部に表示されます。

select10*3
image 16
SQL入門 – 2)select 23

select2

BigQuery組み込み関数を実行することも可能です。複数の値を出力する場合はカンマで区切ります。以下は現在の日付と時刻を表示する例です。

selectcurrent_date(), current_time("Asia/Tokyo")
image 17
SQL入門 – 2)select 24

select3

  • 関数：current_date(tizezone)
  • 概要：現在の日付を返す
  • 引数：タイムゾーン（省略可）
  • 関数：current_time(tizezone)
  • 概要：現在の時刻を返す
  • 引数：タイムゾーン（省略可）

タイムゾーンは地名で指定します。指定可能なタイムゾーンの一覧は以下のURLを参照してください。

https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

出力結果をみるとf0_, f1_と列名が記載されています。これはデフォルトの列名で、列名を明示しなかったときに自動で割り当てられる値です。列名を指定する場合には、以下のように記述します。

select 式1as 列名1, 式2as 列名2, ...

このasは省略することも可能です。以下のように記述しても同じ結果となります。

select 式1 列名1, 式2 列名2, ...

以下のように記述すると、結果に列名が表示されることを確認できます。

selectcurrent_date() asdate, current_time("Asia/Tokyo") astime
image 18
SQL入門 – 2)select 25

select4

テーブル

一般的にはテーブルから値を取得することが多いでしょう。その場合は以下のように記述します。

select 列名 from テーブル

複数の列がある場合はカンマで区切ります。列名に＊と記述すればすべての列が選択されます。

selectを実行するまえにテーブルの概要をみてみましょう。エクスプローラーからsample/countriesを選択してください。

select5

image 19
SQL入門 – 2)select 26

テーブルを選択すると、画面右側に概要が表示されます。

  • スキーマ　＝　テーブルの各列のデータ型などを確認できます。
  • 詳細 = テーブルの作成日や更新日、サイズなどの情報を確認できます。
  • プレビュー = テーブルの中のデータを確認できます。ここで見た内容は課金対象になりません。
  • クエリ = このテーブルを対象としてクエリを実行する画面を開きます。

クエリをクリックして、編集画面をひらきます。新しいタブでも分割タブでも、使いやすい方を選んでください。

対象となるテーブルを選択してからクエリをクリックしたので、以下のような命令がデフォルトで挿入されています。

SELECTFROM `sample.countries` LIMIT 1000

select命令の直後に * を挿入して実行してください。テーブルの中身が表形式で表示されます。

image 20
SQL入門 – 2)select 27

select6

演習

ex-select1.py

セットアップでアップロードした全てのテーブルについて以下の作業をおこなってください。

  • スキーマの確認
  • テーブルの概要の確認
  • プレビューでのデータ確認
  • クエリを使ってのデータの確認

where句による条件指定

select命令では、条件を指定して必要な行のみを抽出できます。条件の指定はwhere句を指定します。以下のように記述します。

select 式 from テーブル where 条件

具体例をみてみましょう。peopleテーブルから男性だけを選ぶ例です。

SELECT*FROM `sample.people` where gender = "male"
image 21
SQL入門 – 2)select 28

select7

以下はpeopleテーブルから年齢(dob_age)が60より大きい人を検索する例です。

SELECT*FROM `sample.people` where dob_age >60

性別（gender）列は文字列（STRING）なので、条件を指定するときに値を”もしくは’で囲みます。 一方、年齢(dob_age)列は、整数型(INTEGER)なので、条件の指定には数値を指定します。

このように条件を指定する場合、データ型を意識することが大切です。テーブルのスキーマをみて確認してください。

image 22
SQL入門 – 2)select 29

select8

比較演算子

主な比較演算子を以下に列挙します。

演算子意味例
=等しいage = 10
<より小さいage < 10
>より大きいage > 10
<=以下age <= 10
>=以上age >= 10
!=, <>等しくないage != 10

他にも演算子がありますが、都度必要に応じて説明をしてゆきます。

AND, OR, NOT

複数の条件を指定する場合はANDとORを、否定をする場合はNOTを使用します。

  • 条件1 and 条件2 = 且つ
  • 条件1 or 条件2 = もしくは
  • not 条件1 = 条件1ではない

以下は、60歳より高齢の男性を検索する例です。条件１＝60歳より高齢、条件2＝男性、これらの条件をANDで結合しています。

SELECT*FROM `sample.people` where dob_age >60and gender="male"

ANDとORを組み合わせるときは、ANDのほうが優先順位が高いことに注意が必要です。これは足し算と掛け算の優先順位と同じように考えてください。

2 + 2 * 4 = 2 + (2 * 4) = 10

例えば以下の例は「男性」もしくは「女性で50才以上」という条件になります。

SELECT*FROM `sample.people` where gender="male" or gender="female" and dob_age >=50

明示的に優先順位を表したい場合、（）を使ってください。以下のSQLは上と同じです。189件ヒットします。

SELECT*FROM `sample.people` where gender="male" or (gender="female" and dob_age >=50)

以下のように優先順位を変えると「男性か女性か」かつ「年齢が50歳以上」となり、検索結果は132件になります。

SELECT*FROM `sample.people` where (gender="male" or gender="female") and dob_age >=50
image 23
SQL入門 – 2)select 30

select9

演習

ex-select2.py

peopleテーブルで年齢(dob_age)が30歳未満のレコード数を数えてください。48件のレコードが抽出されます。

ex-select3.py

peopleテーブルでfirst_nameがDavidの人を検索してください。2件のレコードが抽出されます。

ex-select4.py

peopleテーブルで誕生日が2000年1月1日以降の人を検索してください。9件のレコードが抽出されます。

ex-select5.py

peopleテーブルで氏名（name_first, name_last）の列だけを抽出し、genderがmaleのみ検索してください。136件のレコードが抽出されます。

ex-select6.py

peopleテーブルで、年齢が30歳未満の男性を検索してください。24件のレコードが抽出されます。

ex-select7.py

peopleテーブルで、名前(name_first)がEthan, David, Roy, Tomを検索してください。7件のレコードが抽出されます。

ex-select8.py

peopleテーブルで、名前(name_first)がEthan, David, Roy, Tomのどれかで、年齢が50才以上を検索してください。3件のレコードが抽出されます。

解答例{.answer}

ex-select2.py

SELECT name_first, dob_age, gender FROM `sample.people` 
    where dob_age <30

ex-select3.py

SELECT*FROM `sample.people` where name_first="David"

ex-select4.py

SELECT*FROM `sample.people` 
    where dob_date > "2000-01-01"

ex-select5.py

SELECT name_first,name_last FROM `sample.people` 
    where gender = "male"

ex-select6.py

SELECT*FROM `sample.people` 
    where gender = "male" and dob_age <=30

ex-select7.py

SELECT*FROM `sample.people` where 
    name_first="Ethan" or name_first="David" or 
    name_first="Roy" or name_first="Tom"

ex-select8.py

SELECT*FROM `sample.people` where 
    (name_first="Ethan" or name_first="David" or 
    　name_first="Roy" or name_first="Tom") and dob_age >=50

便利な条件式

whereを使用すれば条件を指定して検索できます。ここではより便利に検索をおこなうための条件の書き方をご紹介します。

BETWEEN

2つの条件をandで指定することで値が特定の範囲にあるという条件を指定できます。以下は20才以上40歳以下という条件です。

where20<= age and age <=40

このように範囲を指定する場合にはbetween命令を使用して以下のように記述できます。

列名 between 以上の値 and 以下の値

以下はpeopleテーブルで20才以上40才以下を検索する例です。85件ヒットします。

SELECT*FROM `sample.people` 
    where dob_age between20and40

以下のように記述しても同じ結果が得られます。

SELECT*FROM `sample.people` 
    where20<= dob_age and dob_age <=40

ここでbetweenは「以上～以下」であり、両方の値を含みます。未満ではないことに注意してください。

IN 演算子

名前がDavid, Roy, Tomのどれか、というように複数の値のなかでどれかに一致するか、このような条件はorを組み合わせて記述できます。 以下のSQLは4件ヒットします。

SELECT*FROM `sample.people` 
    where name_first="David" or name_first="Roy" or name_first="Tom"

IN演算子は複数の値のどれかに合致するか調べるときに便利です。

列名 IN (値1, 値2, ...値N)

先ほどの例は以下のように書き換えることができます。

SELECT*FROM `sample.people` 
    where name_first in ("David", "Roy", "Tom")

IS NULL, IS NOT NULL

列によっては値が入っていないことがあります。このとき値はNULLとなります。NULLと比較する場合は=などの演算子を使用することはできません。例えば以下のSQLはエラーとなります。

SELECT*FROM `sample.people` 
    where id_value =null

このようにnullか、もしくはnullでないか、をしらべるときに以下の演算子を使用します。

  • IS NULL = NULLか
  • IS NOT NULL = NULLでないか

前の例を書き換えると以下のようになります。54件ヒットします。

SELECT*FROM `sample.people` where id_value isnull

逆に「nullでない」という条件は以下のように記述します。196件ヒットします。

SELECT*FROM `sample.people` where id_value isnotnull

54 + 196 = 250となります。nullか、nullでないか、それぞれの検索結果を合計すると全てのレコードが検索出来ていることがわかります。

LIKE

文字列を含む列から、パターンと一致するかチェックするときに使います。例えば、以下のような場合です。

  • 田中、田島、田村のように”田”から始まる苗字を探したい場合
  • 京子、良子、恵理子のように”子”で終わる名前を探したい場合
  • 〇△株式会社、のように”株式会社”で終わる社名を探したい場合
where LIKE 列名 "パターン"

パターンには以下の文字を含めることができます。

  • % = 任意の数の文字と一致する
  • _ = 任意の単一の文字と一致する

以下はjohnから始まるメールアドレスを検索する例です。

SELECT email FROM `sample.people` where email like "john%"

以下の2件がヒットします。

john.robinson@example.com
johnny.burton@example.com

“john%”はjohnから始まり、そのあとは任意の文字でよいということを意味します。

以下は、name_lastが”son”で終る人を検索する例です。

SELECT name_last, email FROM `sample.people` 
    where name_last like "%son"

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

1	Robinson    john.robinson@example.com
2	Anderson    fletcher.anderson@example.com
...

以下電話番号の真ん中の3桁が8から始まるレコードを検索する例です。

SELECT phone FROM `sample.people` where phone like "(___)-8__-%"

以下の5件がヒットします。

1	(854)-807-7689
2	(662)-821-4634
3	(086)-854-9125
4	(816)-829-1055
5	(936)-886-2368

このように、文字列の中で％や＿を適宜挿入することで、前方一致、後方一致、特定の文字が一致などの条件で検索ができます。

演習

ex-select9.py

peopleテーブルで、年齢が20歳以上、30歳以下の人を検索してください。between演算子を使用してください。50件がヒットします。

ex-select10.py

peopleテーブルで、誕生日が1990年代の人を検索してください。between演算子を使用してください。50件がヒットします。

ex-select11.py

peopleテーブルで、nationalityが1, 3, 5のいずれかである人を検索してください。IN演算子を使用してください。108件がヒットします。

ex-select12.py

peopleテーブルで、id_nameがnullの人を検索してください。54件がヒットします。

ex-select13.py

peopleテーブルで、id_valueがnullでない人を検索してください。196件がヒットします。

ex-select14.py

peopleテーブルで、名前(name_first)が”A”から始まる人を検索してください。41件がヒットします。

ex-select15.py

peopleテーブルで、名前(name_first)が”r”で終る人を検索してください。19件がヒットします。

ex-select16.py

peopleテーブルで、名前(name_first)が4文字の人を検索してください。39件がヒットします。

ex-select17.py

peopleテーブルで、電話番号の末尾が2か4の人を検索してください。51件がヒットします。

解答例

ex-select9.py

SELECT*FROM `sample.people` where 
    dob_age between20and30

ex-select10.py

SELECT*FROM `sample.people` where 
    dob_date between "1990-01-01" and "1999-12-31"

ex-select11.py

SELECT*FROM `sample.people` where 
    nationality in (1,3,5)

ex-select12.py

SELECT*FROM `sample.people` where 
    id_name isnull

ex-select13.py

SELECT*FROM `sample.people` where 
    id_value isnotnull

ex-select14.py

SELECT*FROM `sample.people` where 
    name_first like "A%"

ex-select15.py

SELECT*FROM `sample.people` where 
    name_first like "%r"

ex-select16.py

SELECT*FROM `sample.people` where 
    name_first like "____"

ex-select17.py

SELECT*FROM `sample.people` where 
    phone like "%2" or phone like "%4"

LIMIT

出力するレコードの数を制限する場合には、LIMIT句を使用します。以下のようにSQL命令の最後に”LIMIT 個数”と記述します。

SQLの命令 ... LIMIT 制限する個数

以下はpeopleテーブルから10件出力する例です。

SELECT*FROM `sample.people` limit 10

行を制限しても検索に掛かるコストは変化しません。

ちなみに、テーブルが巨大な場合には、サンプリングすることでコストを抑えることが可能です。以下のようにFROM句の後ろにTABLESAMPLE句を指定します。Nにはテーブルの何パーセントを処理するかという数値を指定します。

select ... from ... TABLESAMPLESYSTEM (N PERCENT)

以下はテーブルの1％だけを抽出する場合の記述方法です。

SELECT*FROM `sample.people` TABLESAMPLESYSTEM (1PERCENT)

小さなテーブルでは処理量は変化しません。巨大なテーブルを使用して、且つその一部分だけ見ればよい場合には、コストを抑えるために有効な命令です。

ORDER BY

行を並べる順序を指定します。

ORDER BY 列名（もしくは列番号） ASC  = 昇順
ORDER BY 列名（もしくは列番号） DESC = 降順

昇順のASCは省略できます。

以下は誕生日の昇順に並べる例です。最後に ASC を付けても同じ結果が得られます。

SELECT*FROM `sample.people` orderby dob_date

以下は誕生日の降順に並べる例です。

SELECT*FROM `sample.people` orderby dob_date desc

並べる順序は複数列を指定することも可能です。例えば、年齢dob_ageで整列したばあいには、同じ値のレコードが複数存在します。その中でname_firstのアルファベット順で並べる、という場合には整列条件を以下のように2つ指定します。

SELECT name_first, dob_age FROM `sample.people` 
    orderby dob_age, name_first
image 24
SQL入門 – 2)select 31

select10

年齢順でソートされ、同じ年齢では名前のアルファベット順に並んでいます。

それぞれの順序を指定する場合には、列名の後ろにASC/DESCを指定します。以下は、年齢の降順で並べ、同じ年齢はアルファベットの昇順で並べる例です。最後のascは省略しても同じ結果になります。

SELECT name_first, dob_age FROM `sample.people` 
    orderby dob_age desc, name_first asc

並べる順番の名前でなく、列番号で指定することも可能です。上記例は以下のように記述しても同じ結果が得られます。

SELECT name_first, dob_age FROM `sample.people` 
    orderby2desc, 1asc

dob_ageは2列名、name_firstは1列目なので、名前の代わりに列番号を指定しています。

DISTINCT

DISTINCTは重複を取り除き、一意の値のみ抽出するための命令です。

以下はgender列に取りうる値を出力します。

SELECTdistinct gender FROM `sample.people`

male, femaleのみが取りうる値であることが分かります。

複数の列をまとめて、それらの取りうる値を求めることもできます。以下はgenderとnationalityをまとめて、それらが重複しないような行を抽出します。

SELECTdistinct gender, nationality FROM `sample.people` orderby nationality

このようにdistinctは複数の行をまとめて集約する働きをするため、通常の列と混在させることは出来ません。

以下のような指定は文法エラーとなります。nationalityは国籍の列です。通常に出力すると250行になります。一方、distinct genderはgenderの取りうる値なのでmale, femaleの2行だけです。これらを混在して出力することができないためです。

SELECT nationality, distinct gender FROM `sample.people`

つまり、distinctを使用する場合は、列の先頭に”distinct”と記述し、その後に一意で求めたい列の組み合わせを記述することになります。

演習

ex-select18.py

peopleテーブルで20件表示してください。

ex-select19.py

peopleテーブルで誕生日が古い順に10件表示してください。

ex-select20.py

peopleテーブルで誕生日が新しい順に10件表示してください。

ex-select21.py

peopleテーブルでnationalityの取りうる値を一覧表示し、昇順に並べてください。

ex-select22.py

squirrel-dataテーブルで、Area_Nameの取りうる値を一覧表示してください。4件ヒットします。

ex-select23.py

squirrel-dataテーブルで、Area_NameとPark_Nameの組み合わせの取りうる値を一覧表示してください。20件ヒットします。

ex-select24.py

squirrel-dataテーブルで、Activitiesの取りうる値をアルファベット順に一覧表示してください。ただしnullは除くものとします。72件ヒットします。

解答例

ex-select18.py

SELECT*FROM `sample.people` limit 20

ex-select19.py

SELECT*FROM `sample.people` orderby dob_date limit 10

ex-select20.py

SELECT*FROM `sample.people` orderby dob_date desc limit 10

ex-select21.py

SELECTdistinct nationality FROM `sample.people` orderby nationality

ex-select22.py

SELECTdistinct Area_Name FROM `sample.squirrel-data`

ex-select23.py

SELECTdistinct Area_Name, Park_Name FROM `sample.squirrel-data`

ex-select24.py

SELECTdistinct Activities FROM `sample.squirrel-data` 
  where Activities isnotnullorderby Activities
Categories: Programming, SQL