弊社ではLINE関連のサービスを展開しており、LINEから得られるデータをTreasureDataに蓄積し、検索をしています。TreasureDataからのデータ検索ではSQLを使用することが多いです。
今回はこのSQLについて解説していきます。

私はデータエンジニアとして勤務しています。元々はPython+Pandasが大好きで、TreasureDataを触る前は「SQLよりPandasでデータを触りたい！」と考えていましたが、今ではSQLに慣れてきました。
SQLに苦手意識や不安があっても、本記事の基本をおさえておけば最低限のSQLは書けるようになると思います。

SQLは基本的に、"「何を」「どこから」「どうやって」取得するか"を記述します。
サンプルデータを用いながら解説します。

サンプルデータ

今回はサンプルとして、TreasureDataに用意されているnasdaqのデータセットを用いて解説します。

Symbol	open	volume	high	low	close	time
TGLS	11.17	5100	11.38	11.17	11.3	1409241600
VVUS	4.19	909138	4.2	4.12	4.14	1409241600
SYBT	30.03	16686	30.11	29.85	29.93	1409241600
VONG	95.33	15103	95.56	95.31	95.539	1409241600
TRVN	6.18	27295	6.27	5.88	6.14	1409241600
...	...	...	...	...	...	...

SQLの基本形

基本的に、SQLでは以下3つのキーワードを覚えれば実行が可能です。

SELECT
FROM
WHERE

「何を」取得するかを表すSELECT

SQLのSELECTは、データベースのテーブルから「何を」取得するかを表します。
例えば、サンプルからopen(市場が開いた時の価格)を取得したい場合は、以下のようになります。

SELECT
    open
FROM
    nasdaq
;

open
1.31
52.74
10.56
7.35
27.16

複数の列(openとcloseなど)を取得したい場合は、SELECTに取得したい列を羅列します。

SELECT
    open
    , close
FROM
    nasdaq
;

open	close
25.454	25.454
2.16	2.16
5.54	5.53
24.32	24.21
16.48	16.99

2列目以降で先頭にカンマ(,)をつけているのは、取得したい列が不要になった場合、行ごと消せばよくなるためです。

「どこから」取得するかを表すFROM

SQLのFROMは、「どこから」値を取得するかを表します。
SELECTの例と同じですが、以下のようになります。

SELECT
    open
FROM
    nasdaq
;

上の例ではテーブルのみを記述していますが、データベース名を指定して、DB名.テーブル名とすることもできます。

SELECT
    open
FROM
    sample_datasets.nasdaq
;

「どうやって」取得するかを表すWHERE

SQLのWHEREは、「どうやって」値を取得するかを表します。

以下はopenよりcloseが高くなっている銘柄を抽出する例です。

SELECT
    open
    , close
FROM
    nasdaq
WHERE
    open < close
;

open	close
4.09	4.11
10.95	10.97
19.35	19.51
15.55	15.62
5.03	5.15

他のプログラミング言語とは異なる点がいくつかあります。

A = Bとイコールが1つ
文字列は'でのみ表される

イコールが1つ

PythonなどでAとBが同じであることを表す際は、A==Bのようにイコールを2つ連ねます。イコールを1つにしてA=Bと書くとAにBを代入することを表します。
しかし、SQLではA=BでAとBが同じだということを表します。イコールが1つになる点は注意が必要です！

イコールが1つの場合

SELECT
    open
FROM
    nasdaq
WHERE
    open = close
;

イコールが2個の場合（エラーとなる）

SELECT
    open

FROM
    nasdaq
WHERE
    open == close
;

比較演算子

open=closeなど2つの値を比較する=のことを、比較演算子と呼びます。
以下が、SQLで使用することの出来る比較演算子の例です。

A < B
A > B
A <= B
A >= B
A <> B
- AとBが異なる
A IS NULL
- Aが空

文字列は`'`でのみ表される

Pythonでは文字列を表すのに'や"が使われ、C言語などでは1文字は'、2文字以上は"で文字列を表します。

TreasureDataで用いるSQLでは、"を使うことができず、'のみで文字列を表します。
（データベース製品によっては、"が許容されている場合もあります。）

'を使用した場合

SELECT
    open
    , symbol
FROM
    nasdaq
WHERE
    symbol = 'LOCM7'
;

"を使用した場合（エラーとなる。）

SELECT
    open
    , symbol
FROM
    nasdaq
WHERE
    symbol = "LOCM7"
;

その他でよく使用するもの

データの順序を並び替えるORDER BY

ORDER BYは並び替えをするものです。
Prestoなどの一部の分散型エンジンでは、正しく並び替えされなかったり、速度が低下したりするので注意が必要です。

ORDER BYには昇順(小さい順)と降順(大きい順)があります。それぞれ、昇順はASC、降順はDESCで表されます。
以下が使用例です。

SELECT
    open
FROM
    nasdaq
ORDER BY open ASC
;

open
0.0
0.0001
0.0002
0.0003
0.00031
0.0004
0.0005
0.0006
0.0007
0.0008

DESC

SELECT
    open
FROM
    nasdaq
ORDER BY open DESC
;

open
199999.99
199999.99
199999.99
90002.76
39250.0
31500.0
26875.0
26750.0
25250.0
24750.0

ASCは省略することも可能です。省略した場合は、ASCが自動的に選択されます。

SELECT
    open
FROM
    nasdaq
ORDER BY open
;

列の値をグループ化するGROUP BY

各値ごとの代表値などを取得する際などに使用します。
代表値は主に以下のようなものです。

最大値 (MAX)
最小値 (MIN)
平均値 (AVG)
数値の個数 (COUNT)
文字列の個数 (COUNTA)
etc...

SELECT
    symbol
    , AVG(open) AS average
FROM
    nasdaq
GROUP BY symbol
;

symbol	average
GILT	95.44941903618907
FISI	18.606877227722762
FBSS	11.82426755629691
GMCR	15.53558756848667
FTLB	20.130534586466165
HAFC	64.3150636265515
FDEF	13.300355836612315
HBP	3.807672959889351
FBNK	13.952141911764702
HSIC	38.99376541211103
...	...

別名をつけるAS

ASは抽出結果や列名に名前をつけることができるものです。

下のように、1つの抽出した塊に対して検索をかける際によく使います。

(
    SELECT
        open
        , close
    FROM
        nasdaq
    WHERE
        open < close
) AS result

SELECT
    open
FROM
    result
 ;

また、集約関数やTreasureData独自の関数などを使用すると列名が変わってしまいます。その際、わかりやすい列名をつけるために使います。

SELECT
    symbol
    , AVG(open) AS average_open
FROM
    nasdaq
GROUP BY symbol
;

ASは一部で省略することも可能です。以下のように書かれることもあります。

SELECT
    open open_price
FROM
    nasdaq
;

重複を削除するDISTINCT

DBから値を抽出する際に重複して欲しくない場合があると思います。重複を削除するものがDISTINCTです。

DISTINCTは以下のようにSELECTの中で使用します。

SELECT
    DISTINCT symbol
FROM
    nasdaq
;

以下のようにDISTINCTの後ろに複数の列名を指定すると、列の組み合わせで重複が削除されます。

symbol	open
A	100
A	200
B	100
B	100

上記のテーブルに対して以下のようなSQLを実行すると、

SELECT
    DISTINCT
        symbol
        , open
 FROM
    nasdaq
;

下の結果が得られます。
(symbolのAは重複しているが、openとの組み合わせでは重複していない。)

symbol	open
A	100
A	200
B	100

弊社では、LINEの通知を送る際に対象となるUserIDをTreasureDataから抽出することが多くあります。
基本的には同じユーザーに何通もメッセージが送信されてしまわないよう、重複は許されないことが多く、DISTINCTを多用します。

TreasureData特有の仕様

全件抽出での速度

TreasureDataの検索処理は、PrestoかHiveで動かすことができ、弊社ではPrestoをメインで使用しています。
Prestoは列指向のエンジンであり、全件抽出は得意としていません。

列指向の例

TreasureDataでは、データを追加する際に追加する時刻(unixtime)が自動的にtime列として追加されます。time列がパーティションとして機能することで高速な検索が可能となっています。

TreasureDataには期間指定をする関数(TD_TIME_RANGE)が用意されています。
https://docs.treasuredata.com/display/public/PD/Leveraging+Time-Based+Partitioning

SELECT
    open
    , TD_TIME_FORMAT(time, 'yyyy-MM-dd HH:mm:ss', 'JST') AS time
FROM
    nasdaq
WHERE
  TD_TIME_RANGE(time, null, '2010-01-01', 'JST')
;

他にもLIMITで抽出する件数を制限することで速度が改善されます。
検証中などはLIMITで行数の制限をかけると、スキャン量も減り効率的になるかと思います。

SELECT
    open
FROM
    nasdaq
LIMIT 100 # 上から100件のみ抽出
;

おわりに

今回は、SQLの基礎について解説をしました。SQLの基本自体はそれほど難しくはないかと思います。
弊社ではビジネスサイドの人間でも基礎的なSQLを理解し、クエリを作成することができています。
この記事を機にSQLに興味を持ち、理解していくきっかけとなれば幸いです。

【初学者向け】誰でも理解できるSQLの基礎