SQLチートシート(Snowflake SQL)

初めに

最近Snowflakeを触り始めまして， SQLを一から学んでます．
その中で得られたこと，よく使うテンプレを以下にまとめていきます．
備忘録的な意味合いが強いですが，役立つものがありましたら何よりです．
（Snowflake上での動作は確認しています．）

▼リンク
Snowflake公式ドキュメンテーション
Snowflake公式ドキュメンテーション:関数リファレンス

基本

構文の順序　

公式ドキュメント:クエリ構文

よく使うもの

WITH
SELECT <列名>
FROM <テーブル名>
WHERE <列名> = <値> 
GROUP BY <列名>
ORDER BY <列名> DESC/ASC
LIMIT <値>
;
-- コメント

• WITH : テーブルを複数回の手順で加工したい際に，中継テーブルを定義するために使える．
• SELECT : 必須な句． ↓のFROMで指定するテーブルの列のうち，扱いたいものを指定．
  SELECT * とすることで全ての列を指定可能．また，関数を指定可能
• FROM : 扱うテーブルを指定
• WHERE : 列名とその値を指定することで，列の値に対して条件を満たすデータのみを取得できる．
• GROUP BY : 指定した列の値が同じデータは，まとめて一つのデータとする（まとめる方法はSELECT中で各行ごとに指定）
• ORDER BY : 指定した列の降順，昇順で並び替える．（複数の列を指定可能．左に書いたものほど優先される）
• LIMIT : 指定した数だけの行を表示させる．（python pandasの.head(<値>)と同じ）
• ; : 文の最後につける．

テンプレ(中身の確認)

上から5つ確認する

SELECT <列名>
FROM <テーブル名>
LIMIT 5;

列の値の種類数を確認する

SELECT COUNT(DISTINCT <列名>)
FROM <テーブル名>;
-- 列がりんご，もも，バナナで構成されているなら，3という値が得られる．

列の値の種類を確認する

SELECT <列名>
FROM <テーブル名>
GROUP BY <列名>;
-- 列がりんご，もも，バナナで構成されているなら，「りんご，もも，バナナ」が得られる．

SELECT array_agg(DISTINCT <列名>)
FROM <テーブル名>;
-- ↑でも同じ情報が得られる．　

列の値それぞれが，どの程度の頻度で出てくるか確認する

SELECT <列名>, COUNT(<列名>)
FROM <テーブル名>
GROUP BY <列名>;
-- 列の中にりんごが3回，ももが5回，バナナが7回出てくるなら, 
-- りんご:3, もも:5, バナナ:7 という風に得られる．

SELECT approx_top_k(<列名>, <値1>)
FROM <テーブル名>;
-- ↑でも同じ情報が得られる．
-- <値1>で指定した数だけ，頻度の多いものから表示される．（値1が2なら，↑のりんご:3は表示されない）

列1と列2の値が1対1で対応しているかどうかを確認する

SELECT <列1>, COUNT(DISTINCT <列2>) AS <列2の名前>
FROM <テーブル名>
GROUP BY <列1>
ORDER BY <列2の名前> DESC 
LIMIT 5;
-- 1対1で対応していない場合，表示される値は1より大きい値となる．
-- その中身も確認したいときは，array_agg(DISTINCT <列2>)をSELECTのところに追加

テンプレ(データの抽出）

列1の値が特定の<値>のもののみ抽出

SELECT <列1>, <列2>
FROM <テーブル名>
WHERE <列1> = <値>;
-- 数値データなら不等号で値を指定可能
-- != を使用すれば、指定の値以外のものを指定できる。
-- ※値がnullのものを指定する場合は，　WHERE <列1> IS NULL
-- (WHERE <列1> = NULLとすると， 空のデータでなく，　NULLという文字列を探す

列1の値が特定の<値>を含むもののみ抽出（部分一致）

SELECT <列1>, <列2>
FROM <テーブル名>
WHERE <列1> IS LIKE '%<値>%';
-- %がワイルドカードの役割をしている。
-- そのため、'%<値>'で、<値>で終わるもの、'<値>%'で、<値>で始まるもののみにしぼり込める。
-- この場合も、IS NOT LIKEとすることで、じゃないほうを抽出できる。

列1の値でデータを集計．列2のデータは列1の値ごとに和をとる

SELECT <列1>, SUM(<列2>)
FROM <テーブル名>
GROUP BY <列1>;
-- GROUP BYは複数の列指定可能． 

SUMの代わりはMAXでも，MINでも．公式ドキュメント:集計関数

列1の値でデータを集計。列2のデータは、列1の値ごとに、列3の値が一番大きい行の値を取ってくる。（個人的イチオシ集計関数）

SELECT <列1>, MAX_BY(<列2>, <列3>)
FROM <テーブル名>
GROUP BY <列1>;
-- 割りと重宝する。
-- <列1> 果物、<列2> 収穫量、<列3> 日付で、
-- 「各果物ごとに、一番収穫量が多かった日の収穫量を取得」みたいなことができる。

列1の値でデータを集計．列2のデータが列1の値ごとに数値計算できない場合

SELECT <列1>, array_unique_agg(<列2>)
FROM <テーブル名>
GROUP BY <列1>;
-- 列2が文字だったりで数値計算できない場合，中身の要素をリスト(配列)としてまとめてしまう．

SELECT <列1>, approx_top_k(<列2>, <値1>)
FROM <テーブル名>
GROUP BY <列1>;
-- こちらは，中身の要素と出てきた回数をリスト(配列)としてまとめる．
-- <値1>で指定した数だけ，頻度の多いものから表示される．

列1の値でデータを集計．列2のデータが列1の値と1対1の場合

SELECT <列1>, any_value(<列2>)
FROM <テーブル名>
GROUP BY <列1>;

テンプレ(データの結合)

Table1とTable2に，互いのデータを1対1で紐づけるIDのような列がある場合

SELECT <Table1の列名>, <Table2の列名>
FROM <Table1の名前>
(INNER/FULL OUTER) JOIN <Table2の名前>　
ON <Table1の名前>.<Table1のIDの列名> = <Table2の名前>.<Table2のIDの列名>

-- INNER JOIN:内部結合.IDが一致しないものは除外する．
-- FULL OUTER JOIN:外部結合．IDが一致しないものは，存在しない部分のデータをnullとして表示する

-- 追記
ON 
<Table1の名前>.<列名1> = <Table2の名前>.<列名1> AND
<Table1の名前>.<列名2> = <Table2の名前>.<列名2>
--のように，複数条件の指定が可能．

JOINに関しての詳細はこちら→ JOIN : 公式ドキュメント

※列同士の値をからIDの代わりとなる列を作成し，結合するコードを記載していましたが，↑の複数条件指定する方法で事足りるため，以下の▶︎に折りたたみました．

Table1とTable2に，互いのデータを1対1で紐づけるIDがないため，IDの代わりとなるものを列の値から作り出したい場合

Table1とTable2に，互いのデータを1対1で紐づけるIDがないため，IDの代わりとなるものを列の値から作り出したい場合

WITH 
-- 1つ目のテーブルの定義
<Table1の新しい名前> AS (
SELECT 
CONCAT_WS('-', <結合したい列名1>, <結合したい列名2>)　AS <新しい列名1-1> ,
<Table1の列名> AS <新しい列名1-2> 
FROM <Table1の名前>), 

-- 2つ目のテーブルの定義
<Table2の新しい名前> AS (
SELECT 
CONCAT_WS('-', <結合したい列名1>, <結合したい列名2>) AS <新しい列名2-1> ,
<Table1の列名> AS <新しい列名2-2> 
FROM <Table1の名前>)
-- ↑で定義したTableの各列から新しいテーブルを定義

SELECT
<Table1の新しい名前>．<Table1の新しい列名> 
FROM <Table1の新しい名前>
(INNER/FUUL OUTER) JOIN <Table2の新しい名前> 
ON <Table1の新しい名前>．<新しい列名1-1> = <Table1の新しい名前>．<新しい列名2-1>;

Table1とTable2の構成が同じで，ひとまとめにしてしまいたい場合（縦に結合）

SELECT <Table1の列名>, <Table1の列名>
FROM <Table1の名前>
UNION
SELECT <Table2の列名>, <Table2の列名>
FROM <Table2の名前>

-- UNION:重複データは除外
-- UNION ALL:重複データを除外しない
-- ※SELECTの後に続く列名の数は同じである必要がある．
-- ※列名は関係なく，SELECT以下に書かれた順番に1番目の列同士，2番目の列同士が縦に繋がる

テンプレ(列の加工)

列の値を10倍

SELECT <列名> * 10
FROM <テーブル名>;

列同士の差を確認する

SELECT <列1> - <列2>
FROM <テーブル名>;

列に対しての計算は加減乗除は，おなじみの + - * / で可能→ 算術演算子 : 公式ドキュメント

列同士の割り算をしたいが，分母にしたい列に0が含まれる

SELECT div0(<分子の列名>, <分母の列名>)
FROM <テーブル名>;

-- 0で割る場合は，答えが0になる．
-- div0null という0で割るときも，nullで割るときもエラーを出さずに0を返してくれる関数もあり

丸めたり，切り捨てたり．指数対数三角関数と色々できます→数値関数 : 公式ドキュメント

場合分けを行いたい（CASE文）（他の言語で言うところのif文）

SELECT
CASE
    WHEN <列名1> < 18 THEN '子供'
    WHEN <列名1> < 65 THEN '大人'
    ELSE 'お年寄り'
END
FROM <テーブル名>

--条件が1つしかない（子供と大人の2分岐）なら以下でも可能
SELECT
IFF(<列名> < 18, '子供', '大人')
FROM <テーブル名>

その他

GROUP_BYを使わないデータの集計（ウィンドウ関数）

列3の値で列2を集計．その結果を集約していない状態の列1と並べて表示．

SELECT 
<列1>, 
max(<列2>)　OVER (PARTITION BY <列3>)
FROM <テーブル名>; 

ウィンドウ関数について，また，ウィンドウ関数でできることに関しては，こちらの記事が詳しいです．
window関数を使いこなす ~分析のためのSQL~
公式の解説はこちら→公式ドキュメント：ウインドウ関数の使用
max以外にも使用可能な関数はこちら→公式ドキュメント：ウィンドウ関数

UNIXTIMEを日本時間に変更

列1(unixtimeが格納されている)を，日本時間に変更して表示．

SELECT 
<列1>, 
to_timestamp_ntz(<列1>)
dateadd(hour, 9, to_timestamp_ntz(<列1>))
FROM <テーブル名>; 

-- to_timestamp_ntz関数で，unixtimeを世界標準時に変換
-- dateadd(hour, 9, <引数>)の関数で，日本時間に合わせるよう時差9時間分を足し算．

SELECT
<列1>, 
convert_timezone('Asia/Tokyo', cast(<列1> as varchar))
FROM <テーブル名>; 

-- こちらのように，'Asia/Tokyo'とtimezoneを指定して変更することも可能．
-- <列1>のデータ型がvarcharでない（Numberなど）場合エラーとなるため，
-- ↑のように，cast()を使って，データ型を変更する必要があるため注意

WITH：大きなデータを、一旦中くらいにしてから加工（2stepの操作）

列1の値でGROUPBYする。その後、最大値を取ることで集約した列2の値が3より大きいもののみ表示合。

WITH middle_table AS (
SELECT
<列1>
MAX<列2> AS <新しい列2の名前>
FROM
<大きいテーブルの名前>
GROUP BY <列1>
)
SELECT
<列1>
FROM middle_table
WHERE <新しい列2の名前> > 3

--3step, 4stepの操作をするには
WITH <テーブル1> AS (
<省略>
), <テーブル2> AS (
<省略>
). <テーブル3> AS (
<省略>
)
-- というように続けられる。

見た目的に、上から実行しているように見えるが、実は違う。
非常に簡潔でわかりやすい記事がこちら↓
SQL記述者全員が理解すべきSELECT文の論理的な処理順序のお話

そのため、以下の例のように、SELECT内で列を加工して、その加工した列を頼りに、WHEREで限定することはできないので、WITHを使って2段階の工程に分ける必要がある。
（と思ってます。WITH使わなくて良い方法があれば教えてください。）

ダメな例。SELECT内で定義した列はWHERE文に使えないので、上のようにWITH文を使って2回目の工程で絞り込もう

SELECT
<列1>
MAX<列2> AS <新しい列2の名前>
FROM
<大きいテーブルの名前>
WHERE <新しい列2の名前> > 3
GROUP BY <列1>

--列1の値を使ってデータを集約したとき、
--列2の最大値がどうなるかで、表示する列1の値を絞り込みたいとき

--これでは、「<新しい列2の名前>？ 知らんがそんな名前の列」と怒られてしまうので注意
-- 上の操作をWITHの中に書いて、その後ろの工程でWHEREを使って絞り込む

更新履歴
3/16 : union, window関数, 日本時間に関して追加しました
3/23 : 日本時間に関して，タイムゾーンを直接指定する方法を追記しました．
4/11 : 列の加工の項目を追加しました．
5/9 : テーブルの結合に，複数のID（キー）を指定する方法を追加しました．
2024
3/9 : CASEによる条件分岐、WITHに関して追加しました。

適宜追加していきます．