SQL UDTFs (ユーザー定義のテーブル関数)

UDTF は、単一のスカラー値ではなく行のセットを返し、クエリの FROM 句でアクセスできます。Snowflakeは SQL と JavaScript UDTFsの両方をサポートしています。こトピックでは、 SQL UDTFsについて説明します。

JavaScript UDTFsについては、 JavaScript UDTFs (ユーザー定義のテーブル関数) をご参照ください。

このトピックの内容:

構文

CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
  RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
  AS '<sql_expression>'

SQL UDTFsを含むすべての UDFsの一般的な構文の詳細については、 CREATE FUNCTION をご参照ください。

引数

名前

これは、 識別子の要件 で説明されているルールに従う有効なデータベースオブジェクト名である必要があります。

引数

これは、列名、リテラル、または単一の値に評価できる式などの式でなければなりません。通常、関数は1つの引数を取ります。これは列名です。たとえば、1つ以上の列名、または列名と1つ以上リテラル値など、1つ以上の値を渡すことができます。

定数を渡すことも、値をまったく渡さないことも可能です。ただし、ほとんどの場合、入力が毎回同じであれば、出力は毎回同じです。

出力列名

出力列の名前です。少なくとも1つの出力列が必要です。

出力列型

出力列のデータ型です。

SQL式

これは、テーブルを返す有効な SQL 式またはステートメントである必要があります。つまり、各行に1つ以上の列があるゼロ以上の行です。出力は、 RETURNS 句で指定された数およびデータ型と一致する必要があります。

使用上の注意

  • SQL UDTF の本体(別名「定義」)は SELECT 式でなければなりません。

  • RETURNS 句で指定された戻り値の型は、表形式の結果の列の名前と型を決定し、関数本体の SELECT ステートメントの対応する位置の式の型と一致する必要があります。

  • SQL式 を囲む区切り文字は通常一重引用符ですが、2つ以上のドル記号(「$$」)の文字列などの代替区切り文字を使用できます。終了区切り文字は、開始区切り文字と一致する必要があります。 SQL式 に一重引用符が含まれる場合、ドル記号の文字列は便利です。二重ドル記号を使用した例は、以下の例のセクションに含まれています。

  • SQL UDTF は通常、クエリの FROM 句で TABLE キーワードを使用して呼び出されます。 UDTF で定義された列は、通常のテーブル列を使用できる場所であればどこにでも表示できます。

サンプル SQL UDTFs

基本的な例

これは人為的に簡単にした UDTF の例で、出力をハードコードします。

CREATE FUNCTION t()
    RETURNS TABLE(msg VARCHAR)
    AS
    $$
        SELECT 'Hello'
        UNION
        SELECT 'World'
    $$;
SELECT msg 
    FROM TABLE(t())
    ORDER BY msg;
+-------+
| MSG   |
|-------|
| Hello |
| World |
+-------+

これは、 UDTF の別の基本的な例です。テーブルをクエリし、そのテーブルから2つの列を返します。

create or replace table orders (
    product_id varchar, 
    quantity_sold numeric(11, 2)
    );

insert into orders (product_id, quantity_sold) values 
    ('compostable bags', 2000),
    ('re-usable cups',  1000);
create or replace function orders_for_product(PROD_ID varchar)
    returns table (Product_ID varchar, Quantity_Sold numeric(11, 2))
    as
    $$
        select product_ID, quantity_sold 
            from orders 
            where product_ID = PROD_ID
    $$
    ;
select product_id, quantity_sold
    from table(orders_for_product('compostable bags'))
    order by product_id;
+------------------+---------------+
| PRODUCT_ID       | QUANTITY_SOLD |
|------------------+---------------|
| compostable bags |       2000.00 |
+------------------+---------------+

この同じ機能は、ビューを使用して実装することもできます。

結合の例

指定したユーザー ID の国情報(COUNTRY_CODE および COUNTRY_NAME)を返す SQL UDTF を作成し、使用します。

create or replace table countries (country_code char(2), country_name varchar);
insert into countries (country_code, country_name) values 
    ('FR', 'FRANCE'),
    ('US', 'UNITED STATES'),
    ('SP', 'SPAIN');

create or replace table user_addresses (user_ID integer, country_code char(2));
insert into user_addresses (user_id, country_code) values 
    (100, 'SP'),
    (123, 'FR'),
    (123, 'US');
CREATE OR REPLACE FUNCTION get_countries_for_user ( id number )
  RETURNS TABLE (country_code char, country_name varchar)
  AS 'select distinct c.country_code, c.country_name
      from user_addresses a, countries c
      where a.user_id = id
      and c.country_code = a.country_code';
select *
    from table(get_countries_for_user(123)) cc
    where cc.country_code in ('US','FR','CA')
    order by country_code;
+--------------+---------------+
| COUNTRY_CODE | COUNTRY_NAME  |
|--------------+---------------|
| FR           | FRANCE        |
| US           | UNITED STATES |
+--------------+---------------+

指定した年のお気に入りの色を返す SQL UDTF を作成します。

create or replace table favorite_years as
    select 2016 year
    UNION ALL
    select 2017
    UNION ALL
    select 2018
    UNION ALL
    select 2019;

 create or replace table colors as
    select 2017 year, 'red' color, true favorite
    UNION ALL
    select 2017 year, 'orange' color, true favorite
    UNION ALL
    select 2017 year, 'green' color, false favorite
    UNION ALL
    select 2018 year, 'blue' color, true favorite
    UNION ALL
    select 2018 year, 'violet' color, true favorite
    UNION ALL
    select 2018 year, 'brown' color, false favorite;

create or replace table fashion as
    select 2017 year, 'red' fashion_color
    UNION ALL
    select 2018 year, 'black' fashion_color
    UNION ALL
    select 2019 year, 'orange' fashion_color;
create or replace function favorite_colors(the_year int)
    returns table(color string) as
    'select color from colors where year=the_year and favorite=true';

クエリで UDTF を使用します。

select * from table(favorite_colors(2017));
+--------+
| COLOR  |
|--------|
| red    |
| orange |
+--------+

別のテーブルとの結合で UDTF を使用します。テーブルの結合列が引数として関数に渡されることに注意してください。

select * 
    from favorite_years y join table(favorite_colors(y.year)) c;
+------+--------+
| YEAR | COLOR  |
|------+--------|
| 2017 | red    |
| 2017 | orange |
| 2018 | blue   |
| 2018 | violet |
+------+--------+

追加の述部には、 ON ではなく WHERE 句を使用します。

select * 
    from fashion f join table(favorite_colors(f.year)) fav
    where fav.color = f.fashion_color ;
+------+---------------+-------+
| YEAR | FASHION_COLOR | COLOR |
|------+---------------+-------|
| 2017 | red           | red   |
+------+---------------+-------+

結合式で定数とともに UDTF を使用します。追加の結合条件には、 ON ではなく WHERE 句を使用する必要があることに注意してください。

select fav.color as favorite_2017, f.*
    from fashion f JOIN table(favorite_colors(2017)) fav
    where fav.color = f.fashion_color;
+---------------+------+---------------+
| FAVORITE_2017 | YEAR | FASHION_COLOR |
|---------------+------+---------------|
| red           | 2017 | red           |
| orange        | 2019 | orange        |
+---------------+------+---------------+