目次 › 第4章 › 4-6

第4章 パフォーマンス最適化・クエリ・変換 / 想定学習時間:30〜40分 / 最終確認:2026年8月

4-6. 非構造化データとウィンドウ関数・集約

1. 非構造化データ:ステージとディレクトリテーブル

画像・PDF・音声のような非構造化データは、テーブルの行としてではなく、ステージ上のファイルとして扱います。Snowflake はステージにディレクトリテーブル(directory table)という仕組みを用意しており、有効化するとステージ内のファイル一覧をテーブルのようにSQLで照会できます。

-- ディレクトリテーブルを有効にしてステージを作成
CREATE STAGE doc_stage
  DIRECTORY = (ENABLE = TRUE)
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');

-- ステージ上のファイルのメタデータを一覧する
SELECT relative_path, size, last_modified, file_url
FROM DIRECTORY(@doc_stage);

ディレクトリテーブルが返すのはファイルのメタデータ(相対パス・サイズ・更新日時・ファイルURLなど)であり、ファイルの中身そのものではありません。中身へのアクセスは、次のURL関数で得たURLを通じて行います。なお、ステージへのファイル追加・削除を反映するには、ディレクトリテーブルのリフレッシュ(自動または ALTER STAGE ... REFRESH)が必要です。

2. 3種類のファイルURL関数(比較必須)

ステージ上のファイルへアクセスするURLを生成する関数は3つあり、「URLの寿命」と「アクセス時に必要な認証・権限」が異なります。この違いは試験の定番論点です。

3. ウィンドウ関数:OVER句の基本

ここからはクエリ・変換の道具に移ります。ウィンドウ関数は、GROUP BY のように行をまとめてしまわずに、各行を残したまま「行の集合(ウィンドウ)」に対する計算結果を各行に付与する関数です。基本構文は次のとおりです。

関数() OVER (
  PARTITION BY 区切りの列   -- 区切りごとに独立して計算(省略可)
  ORDER BY 並び順の列        -- ウィンドウ内の順序(関数によって必須)
)

SELECT
  month,
  amount,
  LAG(amount) OVER (ORDER BY month)              AS prev_amount,   -- 前月の値
  amount - LAG(amount) OVER (ORDER BY month)     AS diff,          -- 前月差
  AVG(amount) OVER (
    ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  )                                              AS moving_avg_3m  -- 3か月移動平均
FROM monthly_sales;

4. QUALIFY句:ウィンドウ関数の結果でフィルタする

「グループごとに最新の1行だけ欲しい」はデータ変換の定番要求ですが、WHERE句ではウィンドウ関数を使えません(WHEREはウィンドウ関数より先に評価されるため)。標準SQLではサブクエリで囲む必要がありますが、SnowflakeにはQUALIFY句があり、1段で書けます。

-- 顧客ごとに最新の注文1件だけを取り出す
SELECT customer_id, order_id, order_date
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id ORDER BY order_date DESC
) = 1;

評価順序のイメージは「WHERE が行に対するフィルタ、HAVING がグループに対するフィルタ、QUALIFY がウィンドウ関数の結果に対するフィルタ」です。QUALIFY はSnowflakeの特徴的な構文で、「ウィンドウ関数の結果で絞り込むにはどの句を使うか」という形で問われます。

5. 集約:GROUP BYとその拡張

基本の GROUP BY と、グループ化後の絞り込みである HAVING は前提知識として、Snowflake は小計・総計を一度に計算する拡張構文もサポートしています。詳細な構文を暗記する必要はなく、何が増えるかを押さえておけば十分です。

ROLLUP は「地域→商品」のような階層の小計、CUBE は全方向のクロス集計、GROUPING SETS は必要な集計単位だけを明示する、と整理しておきましょう。

練習問題