20年ぶりにSQLを書いたので重要だった点をまとめる
SQLを書くときは、まず日本語で意味の通るよう、どう分析するかの文章を作ろう。
できればポンチ絵などもあると良いと思う。ER図？知らない子だね。
今回は環境としてPresto ベースの Amazon Athena を利用。
書きかけなのであとでもう少し書くかも。
SELECT句
基本形は下記。
SELECTで表示したいフィールドを宣言。
FROMでフィールドを抽出するデータベースを宣言。
複数のDBと接続する場合はJOIN。
WHEREで条件を絞り込む。
GROUP BYでグループ化。
SELECT [フィールドA],[フィールドB],[フィールドC]
FROM [データベース名]
JOIN [結合するフィールド名] ON [フィールドA].[データベースA] = [フィールドB].[データベースB]
WHERE [条件式]
GROUP BY [フィールドC]
WITH句
さまざまなクエリを連携させて利用することができる。
サブクエリ
クエリのなかにクエリを書くことができる。
いまどきはwith句を使うのであまり使われないらしい。昔はそんなもの無かった。
Window関数
PARTITION BY
[]
配列から任意の列を取り出すことができる。
下記、arrayの1にreferrer値が入っていて、そこだけ取り出して使いたい場合。
SELECT array[1] AS referrer
AS句
テーブルやフィールドに別名を付けることができる
SELECT date_parse(TIME, '%Y-%m-%d %H:%i:%s') AS datetime,
DATE_PARCE
日付文字列を日付形式に変換する
DATE_PARCE(TIME, '%Y-%m-%d %H:%i:%s') AS datetime
フィールドに配列が含まれている場合
VIEWという配列の1つ目の中にURLが入っている場合
VIEW[1] AS URL
のように記述すればその配列だけを取り出すことができる。
SUBSTR句
文字数を絞り込むことができる
たとえば、日付と時間が含まれているdtフィールドに対して
WHERE SUBSTR(dt, 1, 7) >= '2021-06'
のように記述することで、2021-06以降のデータのみを取り出す、という指定を行うことができる。
LIKE句
文字列の検索。
下記のように記述することで、hogehogeという文字列が入っているURLだけを抽出できる。
where URL NOT LIKE '%hogehoge%'
IF文
どのDBを使うかで書き方が大きく異なる。
Prestoの場合は下記
IF(col1 = value1,
true_result,
false_result)
JOIN文
表と表を結合する。
結合の方法について、
内部結合（INNER JOIN）
左外部結合（LEFT OUTER JOIN）
右外部結合（RIGHT OUTER JOIN）
完全外部結合（FULL OUTER JOIN）
交差結合（CROSS JOIN）
がある。
下記が図もあって分かりやすかった。
3つのテーブルをJOINする
SELECT ...
FROM t1
JOIN t2
ON t1.hoge = t2.foo
JOIN t3
ON t2.foo = t3.bar
FROM ReferList
JOIN AgeCheck ON ReferList.session = AgeCheck.session
JOIN MaxList ON ReferList.session =MaxList.session
AND ReferList.datetime = MaxList.datetime
WHERE ReferList.datetime <= AgeCheck.datetime )
SQLのコメントアウト
-- ここにコメント文を書きます
/*
複数行の場合は
こう書きます。
*/
Discussion
SQL文の予約後は基本すべて大文字にしましょう。
例：with句 → WITH句
Yes,Sir!!