SQL UDFs

このトピックでは、SQL UDFs (ユーザー定義関数)に固有の概念と使用方法の詳細について説明します。

UDFsの概要、およびすべての型の UDFsに適用される情報については、 UDFsの概要 をご参照ください。

このトピックの内容:

一般的な使用法

SQL UDF は、任意の SQL 式を評価し、式の結果を返します。

関数定義は、スカラー(単一)値、またはテーブル関数として定義されている場合は行のセットを返す SQL 式です。円の面積を計算するスカラー UDF の基本的な例を次に示します。

CREATE FUNCTION area_of_circle(radius FLOAT)
  RETURNS FLOAT
  AS
  $$
    pi() * radius * radius
  $$
  ;
SELECT area_of_circle(1.0);

出力:

+---------------------+
| AREA_OF_CIRCLE(1.0) |
|---------------------|
|         3.141592654 |
+---------------------+

式はクエリ式(SELECT 式)にすることができます。例:

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold) FROM purchases
  $$
  ;

SQL UDFでクエリ式を使用する場合、クエリ式を終了するために UDF 本文内にセミコロンを含めないでください。

1つのクエリ式のみを含めることができます。式には UNION [ALL]を含めることができます。

注釈

UDF の本文には完全な SELECTステートメントを含めることができますが、 DDL ステートメントや SELECT以外の DML ステートメントを含めることはできません。

基本的な SQL スカラー UDF の例

この例では、数学定数piのハードコードされた近似を返します。

CREATE FUNCTION pi_udf()
  RETURNS FLOAT
  AS '3.141592654::FLOAT'
  ;
SELECT pi_udf();   

出力:

+-------------+
|    PI_UDF() |
|-------------|
| 3.141592654 |
+-------------+

一般的な SQL の例

この例では、クエリ式( SELECT ステートメント)を使用します。

使用するテーブルとデータを作成します。

CREATE TABLE purchases (number_sold INTEGER, wholesale_price NUMBER(7,2), retail_price NUMBER(7,2));
INSERT INTO purchases (number_sold, wholesale_price, retail_price) VALUES 
   (3,  10.00,  20.00),
   (5, 100.00, 200.00)
   ;

UDF を作成します。

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold) FROM purchases
  $$
  ;

クエリで UDF を呼び出します。

SELECT profit();

出力:

+----------+
| PROFIT() |
|----------|
|   530.00 |
+----------+

この例では、 WITH 句で UDF を使用します。

CREATE TABLE circles (diameter FLOAT);

INSERT INTO circles (diameter) VALUES
    (2.0),
    (4.0);

CREATE FUNCTION diameter_to_radius(f FLOAT) 
  RETURNS FLOAT
  AS 
  $$ f / 2 $$
  ;
WITH
    radii AS (SELECT diameter_to_radius(diameter) AS radius FROM circles)
  SELECT radius FROM radii
    ORDER BY radius
  ;

出力:

+--------+
| RADIUS |
|--------|
|      1 |
|      2 |
+--------+

この例では、 JOIN 操作を含むより複雑なクエリを使用します。

使用するテーブルとデータを作成します。

CREATE TABLE orders (product_ID varchar, quantity integer, price numeric(11, 2), buyer_info varchar);
CREATE TABLE inventory (product_ID varchar, quantity integer, price numeric(11, 2), vendor_info varchar);
INSERT INTO inventory (product_ID, quantity, price, vendor_info) VALUES 
  ('X24 Bicycle', 4, 1000.00, 'HelloVelo'),
  ('GreenStar Helmet', 8, 50.00, 'MellowVelo'),
  ('SoundFX', 5, 20.00, 'Annoying FX Corporation');
INSERT INTO orders (product_id, quantity, price, buyer_info) VALUES 
  ('X24 Bicycle', 1, 1500.00, 'Jennifer Juniper'),
  ('GreenStar Helmet', 1, 75.00, 'Donovan Liege'),
  ('GreenStar Helmet', 1, 75.00, 'Montgomery Python');

UDF を作成します。

CREATE FUNCTION store_profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
  SELECT SUM( (o.price - i.price) * o.quantity) 
    FROM orders AS o, inventory AS i 
    WHERE o.product_id = i.product_id
  $$
  ;

クエリで UDF を呼び出します。

SELECT store_profit();

出力:

+----------------+
| STORE_PROFIT() |
|----------------|
|         550.00 |
+----------------+

トピック CREATE FUNCTION には追加の例が含まれています。

さまざまな句で UDFs を使用する

スカラー UDF は、スカラー式を使用できる任意の場所で使用できます。例:

-- ----- These examples show a UDF called from different clauses ----- --

select MyFunc(column1) from table1;

select * from table1 where column2 > MyFunc(column1);

UDFでの SQL 変数の使用

この例は、SQL 変数を設定し、UDF 内でその変数を使用する方法を示しています。

SET id_threshold = (SELECT COUNT(*) FROM table1) / 2;
CREATE OR REPLACE FUNCTION my_filter_function()
RETURNS TABLE (id int)
AS
$$
SELECT id FROM table1 WHERE id > $id_threshold
$$
;

トラブルシューティング

エラーメッセージ: Unsupported subquery type

原因

UDF にクエリ式が含まれている場合、UDF は サブクエリ として機能できます。サブクエリに列名が渡される場合、サブクエリは 相関サブクエリ として機能できます。相関サブクエリが相関サブクエリのSnowflakeルールに違反している場合、ユーザーはエラーメッセージ Unsupported subquery type を受け取ります。以下の例は、無効な相関サブクエリ、および UDF が同様の無効な相関サブクエリのように機能する方法を示しています。

テーブルのペアを作成し、それらにデータをロードします。

CREATE TABLE stores (store_ID INTEGER, city VARCHAR);
CREATE TABLE employees (employee_ID INTEGER, store_ID INTEGER, sales NUMERIC(10,2), 
    sales_date DATE);
INSERT INTO stores (store_ID, city) VALUES 
    (1, 'Winnipeg'),
    (2, 'Toronto');
INSERT INTO employees (employee_ID, store_ID, sales, sales_date) VALUES 
    (1001, 1, 9000.00, '2020-01-27'),
    (1002, 1, 2000.00, '2020-01-27'),
    (2001, 2, 6000.00, '2020-01-27'),
    (2002, 2, 4000.00, '2020-01-27'),
    (2002, 2, 5000.00, '2020-01-28')
    ;

次の SQL ステートメントには、Snowflakeルールに従わない相関サブクエリが含まれています。このコードは Unsupported subquery type エラーを引き起こします。

SELECT employee_ID,
       store_ID,
       (SELECT city FROM stores WHERE stores.store_ID = employees.store_ID)
    FROM employees;

以下のコードは、上に示したものと同様の相関サブクエリを作成する方法で、サブクエリのような UDF を作成して呼び出します。

CREATE FUNCTION subquery_like_udf(X INT)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS
    $$
        SELECT city FROM stores WHERE stores.store_ID = X
    $$;
SELECT employee_ID, subquery_like_udf_2(employees.store_ID)
    FROM employees;
解決策#1

UDF にクエリ式が含まれている場合は、 サブクエリ のルールに準拠した方法でのみ UDF を呼び出します。

たとえば、次のステートメントは、列名ではなく定数を使用して UDF を呼び出すため、UDF は相関サブクエリのように機能しません。

SELECT subquery_like_udf(1);
+----------------------+
| SUBQUERY_LIKE_UDF(1) |
|----------------------|
| Winnipeg             |
+----------------------+
解決策#2

場合によっては、同じ目的を別の方法で達成するために UDF を書き直すことができます。1つの行を返すように静的に決定できる場合は、相関サブクエリが許可されます。次の UDF は集計関数を使用しているため、1行のみを返します。

CREATE FUNCTION subquery_like_udf_2(X INT)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS
    $$
        SELECT ANY_VALUE(city) FROM stores WHERE stores.store_ID = X
    $$;
SELECT employee_ID, subquery_like_udf_2(employees.store_ID)
    FROM employees;
+-------------+-----------------------------------------+
| EMPLOYEE_ID | SUBQUERY_LIKE_UDF_2(EMPLOYEES.STORE_ID) |
|-------------+-----------------------------------------|
|        1001 | Winnipeg                                |
|        1002 | Winnipeg                                |
|        2001 | Toronto                                 |
|        2002 | Toronto                                 |
|        2002 | Toronto                                 |
+-------------+-----------------------------------------+