普段のデータ分析の中で、筆者はSQLを使う機会が多いのですが、関数を知っていないと遠回りをしてしまい、かつコードが長くなってしまうことがあります。
今回は、SQLで覚えておくと便利そうな関数をピックアップしてみました。PostgreSQL、MySQL、SQL Server、RedShift、BigQuery、Snowflakeなどで違いが出てきますので網羅はできていません。似た関数がないかどうかは検索をお願いします。
覚えておくと便利なSQL関数
- 欠損値の置き換え(0以外の文字列も可)
COALESCE(VAL, 0) - 最大/最小を見つける
GREATEST(V1, V2, V3, V4)
LEAST(V1, V2, V3, V4) - ゼロ除算の回避
col1 / NULLIF(col2, 0) - 二乗平均平方根(RMS)
SQRT(POWER(X1 - X2, 2)) AS RMS - ユークリッド距離
SQRT(POWER(X1 - X2, 2)) + SQRT(POWER(Y1 - Y2, 2)) AS DIST - 登録ユーザの年齢を計算する
AGE(register_date) - 行を集約してカンマ区切り
string_agg(val, ',') # PostgreSQL/BigQuery
listagg(val, ',') # RedShift
concat_ws(val, ',') # Hive/SparkSQL - 疑似テーブルの作成
WITH
TB1(col1, col2) AS (
VALUES
(1, 'aaa')
, (2, 'bbb')
, (3, 'ccc')
)
SELECT * FROM TB1 - 集約時に総計も合わせて出力。検算時に便利。
総計のみならば、カッコは二重で使用。
GROUP BY ROLLUP( (col1, col2, col3) ) - 集約時に全パターンの組み合わせを出力
GROUP BY CUBE( (col1, col2, col3) ) - 符号を出力(プラスは 1, イコールは 0, マイナスは -1)
SIGN(col) - ウィンドウの指定
ROWS BETWEEN
-- 最初の行から現在の行まで
rows between unbounded preceding and current row
-- 1つ前の行から1つの後の行まで
rows between 1 preceding and 1 following
-- 1つ前の行から1つ前の行まで(つまり1つ前の行)
rows between 1 preceding and 1 preceding
指定方法
・UNBOUNDED PRECEDING 先頭の行(終了点では使えない)
・UNBOUNDED FOLLOWING 末尾の行(開始点では使えない)
・CURRENT ROW 現在行
・<n> PRECEDING 現在行より<n>行前、RANGEの場合は<n>値前
・<n> FOLLOWING 現在行より<n>行後、RANGEの場合は<n>値後 -
ひとつ前のレコード (NULL値の置換要)
COALESCE(
LAG(col1) OVER (PARTITION BY col2 ORDER BY col3)
, 'not found') -
ふたつ後のレコード
LEAD(col1, 2) OVER (PARTITION BY col2 ORDER BY col3) - 固定文字数で文字埋め
LPAD(col, 5, '0') # 左埋め
RPAD(col, 5, '0') # 右埋め
参考:祝日情報の取得に使えるサービス
- 祝日情報(カレンダーサービス)
http://calendar-service.net/