❄️

データアナリスト向け Snowflake 便利なSQL Tips集①

Taro_CCCMK

2025/04/14に公開

はじめに：Copilot時代に必要なSQL基礎力

データ分析の民主化が進む現代、SQLはあらゆる職種で必須のスキルとなりました。
特にSnowflakeのようなクラウドデータプラットフォームでは、次の変化が起きています。

SQL利用の多様化：BIツールに依存しないdbtやRedashやStreamlitなどの直接分析の増加
AI連携の一般化：Copilotによる自然言語からのクエリ生成

またSnowflake Copilotなどの生成AIによるText2SQLにシフトしていく中で、AIが生成するSQLを正しく評価・修正するためには、以下の要素が不可欠です。

関数の挙動理解：例えばEXCLUDEやQUALIFYなど関数機能の特性やクエリ挙動の理解
結果の検証力：想定外の動作を発見するデータ感覚

Copilotは強力な支援ツールですが、生成クエリの妥当性を判断するには人間の知見が必要です。それぞれの関数の意図を理解しないままクエリを実行していると、集計結果が正しいかどうか分からなくなるかもしれません。

本記事を読む事で、Copilot時代においても最新の関数や構文を知り、正しい分析を導くための知識の習得の一助になればと思います。

また各関数を試すためにSnowflakeにプリセットされているサンプルデータである、みんな大好きTPC-Hデータを利用して便利な関数や構文をまとめていますので、そのまま実行が可能な内容となっています。

TPC-Hデータを使った10の分析シナリオ

基本編：クエリ効率化の必須テクニック

シナリオ1: SELECT * EXCLUDEで列選択を最適化

目的：特定の項目だけを除外するEXCLUDE関数により、項目を列挙しなくてよい可読性の高い構文を作成する

-- 注文テーブルから不要なコメント列を除外
SELECT * EXCLUDE (o_comment, o_clerk)
FROM snowflake_sample_data.tpch_sf1.orders
LIMIT 10;

実行結果の特徴
15列ある注文テーブルから2列を除外し、13列を表示。除外した項目の可読性を高める。

良く使う使い方
個人識別子だけを暗号化して、それ以外はそのまま連携したいときなどに大変便利です。

SELECT 
    ENCRYPT_CUSTOMER_ID(c_custkey) as CUSTOMER_ID,
    K.* EXCLUDE(c_custkey)
FROM snowflake_sample_data.tpch_sf1.customer;

シナリオ2: GROUP BY ALLで集計を簡素化

目的：各項目の指定を省略し、シンプルな構文で集計を実行できる

-- 日付と優先度で自動グループ化
SELECT 
  o_orderdate, 
  o_orderpriority,
  COUNT(*) AS order_count,
  AVG(o_totalprice)::DECIMAL(10,2) AS avg_price
FROM snowflake_sample_data.tpch_sf1.orders
GROUP BY ALL;

--実行結果例
o_orderdate | o_orderpriority | order_count | avg_price
------------|-----------------|-------------|----------
1992-01-01  | 1-URGENT        | 150         | 12,345.67

シナリオ3: QUALIFYでウィンドウ関数をフィルタリング

目的：指定されたディメンジョンの任意の順位値を集計する

-- 顧客ごとの最高額注文を抽出（従来の良くあるWITH句）
WITH ranked_orders AS (
  SELECT 
    o_custkey,
    o_orderdate,
    o_totalprice,
    RANK() OVER (PARTITION BY o_custkey ORDER BY o_totalprice DESC) AS price_rank
  FROM snowflake_sample_data.tpch_sf1.orders
)
SELECT *
FROM ranked_orders
WHERE price_rank = 1;

-- 顧客ごとの最高額注文を抽出（QUALIFY）
SELECT 
  o_custkey,
  o_orderdate,
  o_totalprice,
  RANK() OVER (PARTITION BY o_custkey ORDER BY o_totalprice DESC) AS price_rank
FROM snowflake_sample_data.tpch_sf1.orders
QUALIFY price_rank = 1;

処理性能比較
処理時間の差はほぼないのでSQLとしての構文量の少なさや可読性の高さがメリット

シナリオ4: LISTAGG: データの連結

目的：カテゴリごとの値を1行にまとめたい場合や複数行データを1つのセルに表示する

-- 顧客ごとの注文IDを連結
SELECT 
  o_custkey,
  LISTAGG(o_orderkey, ', ') WITHIN GROUP (ORDER BY o_orderdate) AS order_list
FROM snowflake_sample_data.tpch_sf1.orders
GROUP BY o_custkey;

-- 結果はちょっと簡略化しています
o_custkey | order_list
----------|---------------------
123       | 1, 5, 9, 12, 15
456       | 2, 6, 8, 11, 14
789       | 3, 4, 7, 10, 13

メリット:

データの可視化やレポート作成時に便利。
グループ化されたデータを簡単に確認可能。

中級編：多次元分析の実践技法

シナリオ5: ROLLUPで階層集計

目的：明細と小計と総合計を出すような分析においてシンプルな構文で集計を実行できる

-- 従来手法：国×年月で集計し、国別および全体を出すような階層的集計（良くあるWITH句）
WITH sales_data AS (
  SELECT 
    c.c_nationkey,
    DATE_TRUNC('month', o.o_orderdate) AS order_month,
    o.o_totalprice::DECIMAL(15,2) AS total_price
  FROM 
    snowflake_sample_data.tpch_sf1.orders AS o
    JOIN snowflake_sample_data.tpch_sf1.customer AS c
      ON o.o_custkey = c.c_custkey
)
SELECT
  c_nationkey,
  order_month,
  SUM(total_price) AS total_sales
FROM sales_data
GROUP BY GROUPING SETS (
  (c_nationkey, order_month),  -- 国別×月次
  (c_nationkey),               -- 国別
  ()                           -- 全体
)
ORDER BY c_nationkey NULLS LAST, order_month NULLS LAST;

-- ROLLUPを利用した構文（スッキリ！）
SELECT 
  c.c_nationkey,
  DATE_TRUNC('month', o.o_orderdate) AS order_month,
  SUM(o.o_totalprice)::DECIMAL(15,2) AS total_sales
FROM 
  snowflake_sample_data.tpch_sf1.orders AS o
JOIN 
  snowflake_sample_data.tpch_sf1.customer AS c
  ON o.o_custkey = c.c_custkey 
GROUP BY ROLLUP (c.c_nationkey, order_month)
ORDER BY c.c_nationkey, order_month;

処理性能比較
処理時間の差はほぼないのでSQLとしての構文量の少なさや可読性の高さがメリット

シナリオ6: GROUPING SETSで柔軟集計

目的：複数の項目を同時に集計したい場合にシンプルな構文でクエリを作成できる

-- 従来手法：担当者別とステータス別を同時集計（良くあるWITH句）
WITH 
PriorityCount AS (
  SELECT 
    o_orderpriority AS priority,
    COUNT(*) AS order_count
  FROM snowflake_sample_data.tpch_sf1.orders
  GROUP BY o_orderpriority
),
StatusCount AS (
  SELECT 
    o_orderstatus AS status,
    COUNT(*) AS order_count
  FROM snowflake_sample_data.tpch_sf1.orders
  GROUP BY o_orderstatus
),
TotalCount AS (
  SELECT 
    COUNT(*) AS order_count
  FROM snowflake_sample_data.tpch_sf1.orders
)
SELECT 
  priority,
  NULL AS status,
  order_count,
  0 AS priority_grouping,
  1 AS status_grouping
FROM PriorityCount
UNION ALL
SELECT 
  NULL AS priority,
  status,
  order_count,
  1 AS priority_grouping,
  0 AS status_grouping
FROM StatusCount
UNION ALL
SELECT 
  NULL AS priority,
  NULL AS status,
  order_count,
  1 AS priority_grouping,
  1 AS status_grouping
FROM TotalCount
ORDER BY priority, status;

-- GROUPING SETSを利用した構文（スッキリ！！）
SELECT 
  o_clerk,
  o_orderstatus,
  COUNT(*) AS order_count
FROM snowflake_sample_data.tpch_sf1.orders
GROUP BY GROUPING SETS ((o_clerk), (o_orderstatus));

-- 実行結果例  
o_clerk   | o_orderstatus | order_count
----------|---------------|-------------
Clerk#001 | NULL          | 120
NULL      | F             | 45,000

処理性能比較
処理時間の差はほぼないのでSQLとしての構文量の少なさや可読性の高さがメリット

時系列分析編

シナリオ7: DATE_FROM_PARTSで動的日付生成

DATE_FROM_PARTSは動的な日付生成するとともに不正な日付の自動調整という強力な機能を有しており、また日付操作の柔軟性が高く、例えば四半期ごとといった日付を生成できる関数です。

-- 従来手法：四半期ごとの開始日付を取得（CASE文でゴリゴリに実装する）
SELECT DISTINCT
  TO_DATE(
    CONCAT(
      YEAR(o_orderdate), '-',
      CASE QUARTER(o_orderdate)
        WHEN 1 THEN '01'
        WHEN 2 THEN '04'
        WHEN 3 THEN '07'
        WHEN 4 THEN '10'
      END, '-01'
    )
  ) AS quarter_start_date
FROM snowflake_sample_data.tpch_sf1.orders
ORDER BY quarter_start_date;

-- DATE_FROM_PARTS（短い構文で実装可能）
SELECT distinct
  DATE_FROM_PARTS(
    YEAR(o_orderdate),
    (QUARTER(o_orderdate) - 1) * 3 + 1,
    1
  ) AS quarter_start_date
FROM snowflake_sample_data.tpch_sf1.orders
order by 1;

-- 出力結果例
QUARTER_START_DATE
1992-01-01
1992-04-01
1992-07-01
1992-10-01
1993-01-01

-- 不正な日付を推定される正しい日付に自動調整
-- 存在しない2月30日
SELECT DATE_FROM_PARTS(2025, 2, 30) AS valid_date;

VALID_DATE
2025-03-02

-- 存在しない13月51日
SELECT DATE_FROM_PARTS(2025, 13, 51) AS valid_date;
VALID_DATE
2026-02-20

シナリオ8: LAG/LEADで前月比分析

LAG：同じ結果セットの前の行のデータにアクセスする
LEAD：同じ結果セットの後続の行のデータにアクセスする
時系列などで前後の比較を横並びで分析する事などに利用できる関数です

-- 従来手法：今月の売上と前月の売上を結合して前月比分析を行う（外部結合などで集計）

WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', o_orderdate) AS month,
    SUM(o_totalprice)::DECIMAL(15,2) AS sales,
    ROW_NUMBER() OVER (ORDER BY DATE_TRUNC('month', o_orderdate)) AS rn
  FROM snowflake_sample_data.tpch_sf1.orders
  GROUP BY 1
)
SELECT
  curr.month,
  curr.sales,
  prev.sales AS prev_month,
  (curr.sales - prev.sales) / prev.sales * 100 AS growth_rate
FROM monthly_sales curr
LEFT JOIN monthly_sales prev
  ON curr.rn = prev.rn + 1;

-- LAGを使った構文（前月を横並びにする指示が簡単に）
WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', o_orderdate) AS month,
    SUM(o_totalprice)::DECIMAL(15,2) AS sales
  FROM snowflake_sample_data.tpch_sf1.orders
  GROUP BY 1
)
SELECT
  month,
  sales,
  LAG(sales) OVER (ORDER BY month) AS prev_month,
  (sales - LAG(sales) OVER (ORDER BY month)) / LAG(sales) OVER (ORDER BY month) * 100 AS growth_rate
FROM monthly_sales;

上級編：複雑な分析要件への対応

シナリオ9: パーティション分割したウィンドウ関数

目的：ウィンドウ関数を用いることで可読性の高いクエリを作成する

-- 従来手法：顧客別の累積売上計算（副問合せで取得）
SELECT 
    o1.o_custkey,
    o1.o_orderdate,
    o1.o_totalprice,
    (
        SELECT SUM(o2.o_totalprice) 
        FROM snowflake_sample_data.tpch_sf1.orders o2
        WHERE o2.o_custkey = o1.o_custkey
          AND o2.o_orderdate <= o1.o_orderdate
    )::DECIMAL(15,2) AS cumulative_sales
FROM snowflake_sample_data.tpch_sf1.orders o1
ORDER BY o1.o_custkey, o1.o_orderdate;

-- PARTITIONを利用（スッキリ！！）
SELECT
  o_custkey,
  o_orderdate,
  o_totalprice,
  SUM(o_totalprice) OVER (
    PARTITION BY o_custkey 
    ORDER BY o_orderdate 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )::DECIMAL(15,2) AS cumulative_sales
FROM snowflake_sample_data.tpch_sf1.orders;

シナリオ10: PIVOTで横展開

クエリ結果の縦横を入れ替えて並べて比較したい細に便利な関数です

-- 従来手法：月別売上をステータス別に横軸で表示する（CASE文でゴリゴリに実装する）
SELECT
  DATE_TRUNC('month', o_orderdate) AS month,
  SUM(CASE WHEN o_orderstatus = 'F' THEN o_totalprice ELSE 0 END) AS "F",
  SUM(CASE WHEN o_orderstatus = 'O' THEN o_totalprice ELSE 0 END) AS "O",
  SUM(CASE WHEN o_orderstatus = 'P' THEN o_totalprice ELSE 0 END) AS "P"
FROM snowflake_sample_data.tpch_sf1.orders
GROUP BY DATE_TRUNC('month', o_orderdate)
ORDER BY month;

-- PIVOTによる構文（実装量が大幅に削減！！）
SELECT *
FROM (
  SELECT 
    DATE_TRUNC('month', o_orderdate) AS month,
    o_orderstatus,
    o_totalprice
  FROM snowflake_sample_data.tpch_sf1.orders
)
PIVOT(
  SUM(o_totalprice) 
  FOR o_orderstatus IN ('F', 'O', 'P')
)
ORDER BY month;

おわりに：データドリブン意思決定を支えるSQLスキルの進化

自分も古い人間の一人だなと思っていますが、「データを語る前に、まずSQLを制す」という時代は終わりつつあるなと考えています。Copilot時代において全てのデータ分析をAIに任せる事もいずれ来るかもしれません。でもそれまでの間、またその時代においても、正しい分析を担保するために必要な知識として最新のSQLテクニックは知っておくべきだと考えています。今回紹介したのはそのSQL関数のほんの一部ですが、これらを学ぶことにより、皆さんの明日からの分析作業が、より知的で楽しいものになると幸いです。

Snowflake Data HeroesPublication

Snowlfake データクラウドのユーザ会 SnowVillage のメンバーで運営しています。 Publication参加方法はこちらをご参照ください。 zenn.dev/dataheroes/articles/db5da0959b4bdd

Discussion

Yakke

非常に有益な記事だと思いました！
ROLLUPは使ったことなかったので使いたいと思います

Taro_CCCMK

お役に立ててよかったです！
皆さんの👍の数で意外と知られてないってことが分かりました😅

ログインするとコメントできます