【備忘録】Snowflake SQL Tips集

はじめに

Snowflake上に複数のデータマートの作成をしました。その過程で「これは他の案件でも使える」と感じた関数・構造・パターンを、備忘録として残します。

• 想定読者: SELECT / JOIN / GROUP BY / CASE は一通り書けるようになった方。
• 方言: Snowflake（DATEADD・EQUAL_NULL・UNPIVOT などはSnowflake固有の書き方を含みます）。

Tip 1: 会計年度を「月」から求める

困りごと: 4月始まりの会計年度など、暦の年（2024年）と会計年度（2023年度）がズレる。1〜3月だけ前年度にしたい。

YYYYMM は6桁の文字列なので、SUBSTRING で「年」と「月」を切り出して CASE で判定するだけです。

CASE
    WHEN TO_NUMBER(SUBSTRING("対象年月", 5, 2)) >= 4       -- 月が4〜12月なら
        THEN TO_NUMBER(SUBSTRING("対象年月", 1, 4))        -- その年がそのまま年度
    ELSE TO_NUMBER(SUBSTRING("対象年月", 1, 4)) - 1        -- 1〜3月は前年度
END AS "年度"

ポイント

• SUBSTRING("対象年月", 5, 2) は「5文字目から2文字」＝月の部分。SUBSTRING(..., 1, 4) は年の部分。
• 締め月が変わっても、しきい値 >= 4 を直すだけで対応できる。
• BIで「年度」で絞りたいことは多いので、この列を1本用意しておくと後がラク。

Tip 2: 「有効期間内のものだけ残す」は EXISTS ＋ 番兵値

困りごと: 取引明細のうち、「その時点で有効だった会員」の分だけ残したい。ふつうに JOIN すると、マスタ側が1対多のときに行が増えてしまう。

こういう「マスタ側に条件を満たす行があるものだけ残す」ときは、JOIN ではなく EXISTS が向いています。EXISTS は「内側の条件を満たす行が“あるか / ないか”」だけを見るので、行数が増えません。

FROM "取引明細" A
WHERE EXISTS (
    SELECT 1
    FROM "会員マスタ" B
    WHERE A."会員ID" = B."会員ID"                    -- 外側の行と突き合わせ（相関サブクエリ）
      AND TO_NUMBER(A."対象年月日")
          BETWEEN B."加入日"
              AND COALESCE(B."退会日", 99991231)      -- 未退会は「未来の日付」とみなす
)

ポイント

• EXISTS (SELECT 1 ...) は「存在チェック」。中身のSELECTは何を返してもよく、慣習で 1 を書く。
• 退会日が NULL（＝まだ退会していない現役会員）は、そのままだと BETWEEN の上限に使えない。そこで COALESCE("退会日", 99991231) として「ありえない未来の日付」を入れておく。この“ありえない値”を 番兵値（sentinel） と呼び、NULLの特別扱いを避けるための定番テクです。

Tip 3: 前年同月比は「前年を+1年ずらして」結合する

困りごと: 当月と前年同月を横に並べたい。でも「2024/05 と 2023/05」をどうやってキーで繋ぐ？

当年ぶんと前年ぶんを別々のCTEにして、前年側の年月に+1年してから当年と突き合わせます（2023/05 を +1年して 2024/05 にすれば、当年の 2024/05 と一致する）。

FROM current_month A                       -- 当年
LEFT JOIN zennen_month B                   -- 前年
    ON A."店舗コード" = B."店舗コード"
   AND A."カテゴリ"   = B."カテゴリ"
   AND A."対象年月" = TO_CHAR(
           DATEADD(YEAR, 1, TO_DATE(B."対象年月" || '01', 'YYYYMMDD')),  -- 前年を+1年
           'YYYYMM'
       )

ポイント

• TO_DATE(B."対象年月" || '01', 'YYYYMMDD') は「YYYYMM の後ろに 01 を足して日付にする」＝月初に変換。日付にしないと DATEADD で足し算できないため。
• 当年を主（FROM）、前年を LEFT JOIN にすると、前年実績がない月は自動で NULL になり、比較のときに扱いやすい。

別パターンとしては、当年を主（FROM）、前年をINNAR JOIN、の場合あり。
前年実績のない月は、NULLにするか否かで選択。

Tip 4: ちょっとした対応表は VALUES 句でその場に書く

困りごと: コード→名称のような小さな変換表のために、わざわざテーブルを1個作るのは大げさ。

VALUES を使うと、SQLの中に小さな表を直接埋め込めます。

category_master AS (
    SELECT * FROM VALUES
        ('C01', '食品'),
        ('C02', '日用品'),
        ('C03', '衣料')
    AS T("コード", "名称")   -- 列名をここで付ける
)

ポイント

• 管理するテーブルを増やさずに、対応表をSQL内に閉じ込められる。
• 「区分の全パターン（母集合）」を作る用途にも使える（次のTip 6で使います）。

Tip 5: 「実績ゼロの行」も出す — 骨組みを作ってから実績を埋める

困りごと: 集計すると、売上が0の月・店舗は行ごと消えてしまう。BIのグラフで歯抜けになる。

先に「全部の組み合わせ（＝骨組み）」を作り、そこへ実績を後付けで貼り、無い所は0で埋めます。全組み合わせは CROSS JOIN（総当たり結合）で作れます。
その総当たりした骨組みに対して、実績のあるデータをLEFT JOINしてあげることで、実績ゼロの行を含めた全体の集計が可能になる。

all_data AS (
    -- 全月 × 全店舗 × 全カテゴリ をぜんぶ作る
    SELECT A."対象年月", B."店舗コード", C."カテゴリ"
    FROM   months            AS A
    CROSS JOIN store_master  AS B
    CROSS JOIN category_master AS C
)
SELECT
    A."対象年月", A."店舗コード", A."カテゴリ",
    COALESCE(B."数量", 0) AS "数量"   -- 実績が無ければ 0
FROM all_data A
LEFT JOIN 実績 B
    ON  A."対象年月"   = B."対象年月"
    AND A."店舗コード" = B."店舗コード"
    AND A."カテゴリ"   = B."カテゴリ";

ポイント

• 骨組み（母集合）は DISTINCT した月や、マスタ、VALUES で用意する。
• 実績は LEFT JOIN して COALESCE(..., 0) で0埋め。「行が消える」問題がなくなる。

Tip 6: 横持ち⇔縦持ちの変換 — UNPIVOT と CASE 手動ピボット

「横持ち」＝項目が列方向に並ぶ形、「縦持ち」＝項目名と値が行方向に並ぶ形のことです。

困りごと（横→縦）: 項目1, 項目2, 項目3… と列が横に並んでいて、対応表と結合しづらい。

UNPIVOT で、横に並んだ列を「項目名」と「値」の2列に畳めます。空欄も残したいので INCLUDE NULLS を付けます。

SELECT "対象日", "店舗コード", "項目", "結果"
FROM   元データ
UNPIVOT INCLUDE NULLS (
    "結果" FOR "項目" IN (
        "項目1", "項目2", "項目3", "項目4"   -- 横に並んだ列を並べる
    )
)
-- 結果イメージ: 1行(項目1〜4) → 4行(項目名, 結果) に展開される

困りごと（縦→横）: 逆に、決済区分ごとの数量をBI用に横に並べたい。

決まった数の区分なら CASE + SUM で手作りできます（手動ピボット）。

SUM(CASE WHEN "決済区分" = '01' THEN "数量" ELSE 0 END) AS "現金",
SUM(CASE WHEN "決済区分" = '02' THEN "数量" ELSE 0 END) AS "電子マネー"

ポイント

• UNPIVOT INCLUDE NULLS を付けると、値が空（未入力）の項目も行として残せる。
• PIVOT 構文もあるが、CASE + SUM の方が条件を細かく書けて融通が利くことが多い。

Tip 7: NULLとゼロ除算でコケないための3関数

JOIN のキーに NULL が混じると、NULL = NULL は真にならないため結合が外れます。割り算も分母0で落ちます。Snowflakeの安全系関数で回避します。

-- NULL同士も一致とみなして結合する
ON EQUAL_NULL(A."区分A", B."区分A")
   AND EQUAL_NULL(A."カテゴリ", B."カテゴリ")

-- 実績が無ければ0にする
COALESCE(B."月毎数量", 0)

-- 分母が0でもエラーにせず0を返す
DIV0(A."会員数量", B."全体数量") AS "会員比率"

ポイント

• EQUAL_NULL(a, b) は「a = b、ただし両方NULLでも一致とみなす」。NULLを含むキーで結合するときに取りこぼしを防げる。
• 比率は a / b ではなく DIV0(a, b)。分母0のせいでクエリ全体が落ちる事故を防げる。

Tip 8: ウィンドウ関数で「上位N%」を取る

困りごと: 「切り口ごとに、上位25%の店舗だけ集計したい」。GROUP BY だと集約されてしまい、順位で絞れない。

ウィンドウ関数は、GROUP BY と違って行を潰さずに、行ごとに順位や件数を計算できる関数です。順位（ROW_NUMBER）と母数（COUNT(*) OVER）を出して、割合でしきい値を切ります。

rank_cnt AS (
    SELECT
        ...,
        ROW_NUMBER() OVER (
            PARTITION BY "対象年月", "カテゴリ"   -- この切り口ごとに
            ORDER BY "比率" DESC, "店舗コード"    -- 比率が高い順
        ) AS "行番号",
        COUNT(*) OVER (
            PARTITION BY "対象年月", "カテゴリ"   -- 同じ切り口の総件数
        ) AS "最大行数"
    FROM base
)
SELECT *
FROM rank_cnt
WHERE "行番号" <= TRUNC("最大行数" * 0.25)   -- 上位25%

ポイント

• PARTITION BY は「このまとまりごとに計算をリセット」の指定。GROUP BY の集約版と考えると分かりやすい。
• 上位N%は TRUNC(総件数 * 割合) でしきい値を作る。単に「各グループ1位」なら ROW_NUMBER() = 1（Tip 4 参照）。

小ネタ・ハマりどころ

• TABLE と VIEW の使い分け: 重い集計や、日次で積む履歴は CREATE OR REPLACE TABLE ... AS で実体化して速くする。BI表示用の最終加工は CREATE OR REPLACE VIEW にして常に最新を返す、という分担がやりやすい。
• 表示用の変換は最後に CASE: 結果コード OK/NG/NA → ○/×/‐、区分コード → ラベル、などの見た目の変換は、ビューの最終SELECTでまとめてやると管理しやすい。
• GROUP BY 1, 2, 3 の序数指定: ラクだけど、SELECTの列順を変えると壊れる。長いGROUP BYでは列名で書いた方が安全なこともある。
• 年月を数値で比べたいとき: TO_NUMBER(TO_CHAR(d, 'YYYYMM')) や TRUNC("YYYYMMDD" / 100) で YYYYMM 相当の数値が作れる。日付が数値型で入っているマスタと突き合わせるときに便利。

おわりに

snowflakeのSQLに触れられたことで、基礎的な部分から独創的な書き方まで幅広く学ぶことができました。
また、フォーマットやテンプレートがない状態の中で、「どの関数を使うべきか」「もっと効率化できないか」と試行錯誤しながら進められたことは、自分にとって非常に貴重な経験となりました。

本記事が、少しでも皆様の学びの助けになれば幸いです。