🦝

20年ぶりにSQLを書いたので重要だった点をまとめる

2021/07/01に公開
2

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の場合は下記
https://nontitle.xyz/archives/1009

IF(col1 = value1,
            true_result,
            false_result)

JOIN文

表と表を結合する。

結合の方法について、
内部結合(INNER JOIN)
左外部結合(LEFT OUTER JOIN)
右外部結合(RIGHT OUTER JOIN)
完全外部結合(FULL OUTER JOIN)
交差結合(CROSS JOIN)
がある。
下記が図もあって分かりやすかった。
https://mathwords.net/tablejoin

3つのテーブルをJOINする

https://otiai10.hatenablog.com/entry/2015/09/08/141229

SELECT ...
FROM t1
JOIN t2
ON t1.hoge = t2.foo
JOIN t3
ON t2.foo = t3.bar

https://qiita.com/shukan0728/items/d48936928e5ac7aaf7b2

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