【Snowflake】分析・集計に役立つSQLの関数一覧!

分析

Snowflakeで分析・集計に役立つSQLの関数一覧をまとめました。

分析業務に必要な基本的な集計関数はもれなく存在しますが、AUC、AR、エントロピー、カイ2乗値等の統計値を算出する関数は、組み込み関数にはなく、ユーザー定義関数(UDF)を利用して、Pythonで記述する必要があります。

また、便利な関数を把握し利用することで、SQLを完結に記載でき、プログラムの可読性の向上や、修正の際のミス低減につながります。

行の集計関数

テーブルの行を集計する関数です。Excelでも似たような関数があるので理解しやすい関数が多いです。

関数説明・利用方法
countレコード数をカウント。select count(*) from ~
sum合計値を算出。case when 句と組み合わせると柔軟な集計が可能になります。
select sum(変数) from ~
select sum( case when 条件 then 変数 else 変数 end ) from ~
avg平均値を算出。select avg(変数) from ~
min最小値を算出。select min(変数) from ~
max最大値を算出。select max(変数) from ~
median中央値を算出。select median(変数) from ~
※avgとセットで使って、対象の分布を推測。
stddev_samp標準偏差を算出。select stddev_samp(変数) from ~
※avgとセットで使って、対象の分布を推測。

列項目の関数

テーブルの列項目を計算、集計する関数です。レコード内の列の計算は、分析用の指標を作る際によく使いします。また、div0やtry_to_numberは計算エラーを気にせずSQLを記載できるので便利です。

関数説明・利用方法
GREATEST与えられた変数の中の最大値を返す。select GREATEST(変数1,変数2・・) from ~。
LEAST与えられた変数の中の最小値を返す。select LEAST(変数1,変数2・・) from ~
COALESCE与えられた変数の中でnullでない最初の変数を返す。select coalesce(変数1,変数2,0) from ~; ※この例は変数1、変数2がnullの場合0を返す。
div0割り算関数。通常除数が0の場合エラーとなりますが、この関数の場合エラーではなく0を返す。select (変数1,変数2) from ~
try_to_number文字列を数字に変換する関数。通常変換できない場合エラーとなるが、この関数はエラーではなくnullを返す。 select try_to_number(変数1) from ~
ZEROIFNULL引数がnullの場合、0を返します。それ以外の場合は引数を返します。select ZEROIFNULL(変数) from ~

日付関連の関数

日付型項目への変換、日付計算を行う関数です。分析では時間変動による数字の変化をみることが多く、日付計算の習得は必須です。

関数説明・利用方法
DATE_FROM_PARTS
年、月、日を指定してDate型変数を作成。select DATE_FROM_PARTS( <year>, <month>, <day> ) from ~;
dateadd
日付計算関数。第1引数を変えることで年、月、日のいずれを計算対象とするか指定、第2に引数で増減させる数字を指定。※例えば 第一引数が年でnが1の場合1年後の日付を計算する。
年の増減: select dateadd(year,n,変数1)
月の増減: select dateadd(month,n,変数1)
日の増減: select dateadd(day,n,変数1)
datediff
日付間の差を算出する日付計算関数。第1引数を変えることで年、月、日いずれの差を算出するか指定。※例えば第一引数が年の場合、変数1と変数2の年の差を算出。
年の差:datediff(year,変数1,変数2)
月の差:datediff(month,変数1,変数2)
日の差:datediff(day,変数1,変数2)
TO_CHAR入力項目を文字列に変換する関数。分析としては主に日付型を日付文字列に変換する際に利用。
to_char(変数1,’YYYYMMDD’) ※例 20241114という文字列に変換
to_char(変数1,’YYYY-MM’) ※例 2024-11という文字列に変換

Snowflake独自の関数

集計関数ではないですが、Snowflake独自関数です。同じような集計結果をまとめたりするのに便利な関数です。使い方は別途記事にします。

関数説明・利用方法
last_query_id最後に実行されたクエリIDを返す。分析としての主な利用用途としては、ストアドプロシージャーの中で last_query_id()を実行することが多い。
RESULT_SCAN指定したクエリIDの実行結果(24時間以内の実行結果・ログ)を返す。SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))

コメント

タイトルとURLをコピーしました