Agile育成ブログ
未来を変える喜びを
未分類

Snowflakeにおける関数

  /

1. スカラー関数 (Scalar Functions)

スカラー関数とは、「1つの入力（または1行のデータ）に対して、1つの値を返す」関数の総称です。
「スカラー（Scalar）」という言葉には「単一の」という意味があります。先ほど紹介した IFF、LPAD、後述する CONCAT なども、すべてこの「スカラー関数」という大きなカテゴリーに含まれます。

IFF関数

  • 特徴: 引数（カッコの中）が不要なものが多いです。
  • 代表例:
    • CURRENT_ROLE(): 現在使用しているロール名。
    • CURRENT_WAREHOUSE(): 使用中の仮想ウェアハウス名。
    • CURRENT_TIMESTAMP(): 現在の日時。

5. LPAD関数

文字列の左側を特定の文字で埋めて、指定した長さにする関数です（Left Pad）。

実行前

p_partkey
13
2149
373155

実行後

p_partkey
10000000003
20000000149
30000073155
  • 構文: LPAD(元の文字列, 全体の長さ, 埋める文字)
  • 例: 商品コードなどを5桁に揃えたい時
    • LPAD('123', 5, '0') → 結果: '00123'
  • 対義語: 右側を埋める RPAD もあります。

1. テーブル関数 (Table Functions)

通常の関数が「1つの値（スカラー）」を返すのに対し、「表（複数行・複数列）」を結果として返す関数です。

  • 特徴: FROM 句の中で TABLE() というキーワードと一緒に使われることが多いです。
  • 代表例:
    • FLATTEN: JSONなどの半構造化データをバラして行に展開する。
    • RESULT_SCAN: 直前に実行したクエリの結果をテーブルとして読み込む。
    • SPLIT_TO_TABLE: 文字列を区切り文字で分割して行にする。

2. ウィンドウ関数 (Window Functions)

行と行の「関係性」を計算するための関数です。テーブル全体を集計して1行にまとめるのではなく、各行を維持したまま、周囲のデータに基づいた計算結果を付与します。

  • 構文: OVER (PARTITION BY ... ORDER BY ...) を伴います。
  • 代表例:
    • RANK() / ROW_NUMBER(): 順位や行番号をつける。
    • SUM(sales) OVER(...): 累計売上を出す。
    • LAG() / LEAD(): 1行前や1行後の値を取得する。

3. コンテキスト関数 (Context Functions)

「今、誰が、どの環境で実行しているか」という、**現在のセッションの状態（コンテキスト）**を返す関数です。

Snowflake特有の便利な関数で、「簡略版のIF-THEN-ELSE」です。

  • 構文: IFF(条件, 真の場合の値, 偽の場合の値)
  • メリット: 標準SQLの CASE WHEN... よりも短く書けます。
    • 例: IFF(score >= 80, '合格', '不合格')
    • （ExcelのIF関数と同じ感覚で使えます）

6. 集計関数 (Aggregate Functions)

複数の行をまとめて、1つの統計的な値を出す関数です。

  • 特徴: GROUP BY と組み合わせて使われるのが一般的です。
  • 代表例:
    • COUNT(): 行数を数える。
    • SUM(): 合計を出す。
    • AVG(): 平均を出す。
    • LISTAGG(): 複数の行にある文字列をカンマなどで繋げて1つの文字列にする。

TRUNC関数

TRUNC（トランク）は、「切り捨て（Truncate）」を行う関数です。日付や数値に対してよく使われます。

日付に使う場合（一番多いパターン）

時刻の情報（時・分・秒）を切り捨てて、「日付のみ」の状態にします。

  • TRUNC(2023-10-01 15:30:45) → 2023-10-01 00:00:00

数値に使う場合

指定した桁数で切り捨てます。

  • TRUNC(123.456, 1) → 123.4
  • TRUNC(123.456, 0) → 123

SQLでの具体例

「2023年10月1日のデータだけ欲しい」という時、ついつい TRUNC を使いたくなりますが、パフォーマンスの差は歴然です。

書き方SQLイメージDBの作業
❌ 悪い例 (関数利用)WHERE TRUNC(created_at) = '2023-10-01'100万件あれば、100万回切り捨て計算をして比較する
⭕ 良い例 (範囲指定)WHERE created_at >= '2023-10-01'
AND created_at < '2023-10-02'		索引から「2023-10-01」の場所をサッと見つけて、そこだけ読み取る

ユーザー定義関数

Snowflakeには便利な標準関数がたくさん用意されていますが、「自分のビジネス特有の計算」や「複雑なロジックの使い回し」が必要になることもありますよね。

そんな時に役立つのが**ユーザー定義関数（UDF）**です。今回は、UDFの基礎から、パフォーマンスを最大化する使い分けまでを徹底解説します！

1. ユーザー定義関数（UDF）とは？

UDF（User-Defined Functions）とは、ユーザーが独自に作成できるカスタム関数のことです。一度作成すれば、標準の SUM() や UPPER() と同じように、SQLクエリの中で自由に呼び出すことができます。

なぜUDFを使うのか？

  • ロジックの再利用: 同じ計算式を何度も書く必要がなくなります。
  • クエリの簡素化: 数十行にわたる複雑な CASE 文を、1つの関数にスッキリまとめられます。
  • 他言語の活用: SQLでは難しい処理を、PythonやJavaScriptのライブラリを使って実行できます。

2. UDFの2つの「戻り値」による分類

UDFには、結果をどう返すかによって2つの種類があります。

① スカラーUDF (Scalar UDFs)

1つの入力に対して、1つの値を返します。

  • 例: 税込み金額の計算、文字列の特定の整形など。

② テーブルUDF (UDTFs: User-Defined Table Functions)

1つの入力に対して、**表（複数行・複数列）**を返します。

  • 例: 1つの文章を単語ごとにバラして行にする、日付範囲からカレンダーを生成するなど。

3. UDFで使える4つの言語

SnowflakeのUDFは、SQLだけでなく複数の言語で記述できるのが大きな特徴です。

言語特徴
SQL最もシンプルで高速。簡単な計算やロジック向き。
Python豊富なライブラリ（Pandasなど）が利用可能。データサイエンス向き。
JavaScript複雑な条件分岐や、動的なロジックが得意。
Java / Scala既存のエンタープライズ資産の移行や、高度な計算向き。

RETURNS TABLE (…)

普通の関数（スカラー関数）は「100」や「’SUCCESS’」といった1つの値を返しますが、この関数は指定した列構成を持つ「表そのもの」を返します。

RETURNS TABLE (customer_id number, first_name varchar, last_name varchar, region varchar, nation varchar)

引数

( ) の中にある region_nation は、ユーザーが入力する検索キーワードです。 この関数は賢い作りになっており、「国名（NATION）」を入れても「地域名（REGION）」を入れても、どちらでも反応するように設計されています。

FUNCTION get_customers_by_nation_region ( region_nation varchar )

呼び出し方

-- 「AMERICA」地域の顧客をすべて取得する場合
SELECT * FROM TABLE(get_customers_by_nation_region('AMERICA'));

-- 「FRANCE」国の顧客をすべて取得する場合
SELECT * FROM TABLE(get_customers_by_nation_region('FRANCE'));

複雑なJOINの隠蔽: 本来なら毎回3つのテーブルをJOINして WHERE を書かなければなりませんが、この関数を使えば SELECT * FROM TABLE(関数名('キーワード')) だけで済みます。

柔軟性: 「国」か「地域」かを気にせず、1つの入力欄（引数）で検索できるため、BIツールやアプリケーションから呼び出す際に非常に便利です。

セキュリティ: この関数を「セキュア（SECURE）」に設定すれば、ユーザーに対して元の基盤テーブル（CUSTOMER等）への直接アクセスを許可することなく、特定のフィルタ結果だけを公開することができます。

特定の顧客（customer_id）を指定すると、その人が過去に行った注文の中から、合計金額が高い上位10件を計算してリストアップする」
役割: 複雑な売上計算（単価 × 数量 － 割引 ＋ 税）を裏側で行い、結果を「注文ID、日付、総額」という使いやすい表の形で返します。
引数 (customer_id): 顧客のID（数値）を1つ渡すだけで、その人専用のランキングが生成されます。

複雑な計算式（net_line_item_total）

関数の中心にある以下の計算式は、ビジネスにおける「真の売上額」を算出しています。

(SUM(L_QUANTITY * P_RETAILPRICE * (1-L_DISCOUNT) * (1+L_TAX)))::NUMBER(18,2)

計算の順序:

  1. L_QUANTITY * P_RETAILPRICE: 数量 × 単価 で基本額を出す。
  2. * (1-L_DISCOUNT): そこから割引分を引く（例：割引率0.1なら0.9をかける）。
  3. * (1+L_TAX): さらに税金を加算する（例：税率0.1なら1.1をかける）。
  4. SUM(...): 1つの注文には複数の商品（明細）が含まれるため、それらを合計する。
  5. ::NUMBER(18,2): 最後に小数点第2位までの数値型に整形する。

条件絞り込み（WHERE句）の重要性

解説文にもある通り、返品された商品を除外しています。
意味: 返品済みフラグ（’R’）が立っているものは、売上に計上してはいけないので除外します。
結果: 「実際に購入が確定した有効な注文」のみを対象とした、正確なランキングになります。

WHERE L_RETURNFLAG <> 'R'

上位10件の抽出（TOP 10 と ORDER BY）

ORDER BY ... DESC: 計算した注文総額が高い順（降順）に並べ替えます。
TOP 10: 並べ替えた結果の先頭10行だけを切り出します。

SELECT TOP 10 ... ORDER BY net_line_item_total DESC

使い方（呼び出し例）

Noah Yamadaさんの顧客IDが 12345 だった場合、以下のように呼び出します。

-- Noah Yamadaの上位10件の注文を表示
SELECT * FROM TABLE(fetch_top_ten_orders(12345));

「スカラーSQL UDF（ユーザー定義関数）」です。

先ほどまでの「テーブル関数（UDTF）」が「表」を返していたのに対し、この関数は「1行のデータから1つの計算結果」だけを返します。Excelの数式を関数として登録するようなイメージです。

重要なポイントを整理して解説します。

1. この関数の役割：計算式の「部品化」

この関数 fetch_net_line_item_total は、ビジネスで頻繁に使う「正味の売上金額（税・割引込み）」の計算ロジックを一箇所にまとめたものです。

  • 入力: 数量、小売価格、割引率、税率の4つ。
  • 出力: 計算後の「金額（1つの数値）」だけを返します。

2. テーブル関数（UDTF）との違い

解説文にある通り、構造は似ていますが決定的な違いがあります。

特徴テーブル関数 (UDTF)スカラー関数 (UDF)
戻り値の定義RETURNS TABLE (...)RETURNS number(18,2)
戻ってくるもの複数行・複数列の「表」単一の「値」
呼び出し場所FROM TABLE(関数名(...))SELECT 関数名(...)

なぜわざわざ「スカラー関数」にするのか？

先ほど「上位10件の注文を出すテーブル関数」を見ましたが、その中にも同じ計算式が入っていましたよね。

-- テーブル関数の中に書いてあった計算式
(SUM(L_QUANTITY*P_RETAILPRICE*(1-L_DISCOUNT)*(1+L_TAX)))::NUMBER(18,2)

もしこの計算式（例えば税金の計算ルールなど）が変更になった場合、あちこちにこの式を直書きしていると、すべての関数やクエリを書き直さなければなりません。

しかし、今回のようにスカラー関数として独立させておけば、この関数の中身を1箇所直すだけですべてのクエリに反映されます。

使い方（呼び出し例）

スカラー関数は、SELECT 句の中で通常の列と同じように使います。

SELECT 
    L_ORDERKEY,
    L_LINENUMBER,
    -- 自作した関数を呼び出す
    fetch_net_line_item_total(L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX) AS net_total
FROM 
    SNOWBEARAIR_DB.PROMO_CATALOG_SALES.LINEITEM;
RELATED POST