なんやブログ|データ分析格闘記

データ分析を生業に、分析ナレッジや読書感想などを書くブログ。ミナミのデータ分析格闘ブログ

【SQL】 データ分析で覚えておくと便利な関数まとめ

f:id:bee5boo5bee:20210816173540p:plain

 

普段のデータ分析の中で、筆者は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')  # 右埋め

 

参考:祝日情報の取得に使えるサービス

プライバシーポリシー | 趣味のページ