Snowflake スクリプトによる手続き型ロジックを含む SQL UDF を定義できるようになりました #SnowflakeDB

CREATE OR REPLACE TABLE TEST_TABLE (
    ID INT,
    NAME VARCHAR(255)
);
INSERT INTO TEST_TABLE (ID, NAME)
VALUES
    (1, 'John'),
    (2, 'Jane'),
    (3, 'Bob');

CREATE OR REPLACE function check_dept_udf_script(department_id INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
  IF (department_id < 3) THEN
    RETURN 'Engineering';
  ELSEIF (department_id = 3) THEN
    RETURN 'Tool Design';
  ELSE
    RETURN 'Marketing';
  END IF;
END;

--従来の方法
CREATE OR REPLACE FUNCTION check_dept_udf_case(department_id INTEGER)
RETURNS VARCHAR
AS
$$
  CASE
    WHEN department_id < 3 THEN 'Engineering'
    WHEN department_id = 3 THEN 'Tool Design'
    ELSE 'Marketing'
  END
$$;

>SELECT
     *
     ,check_dept_udf_script(id) AS checked_dept_script
     ,check_dept_udf_case(id) AS checked_dept_case
 FROM test_table;
+----+------+---------------------+-------------------+
| ID | NAME | CHECKED_DEPT_SCRIPT | CHECKED_DEPT_CASE |
|----+------+---------------------+-------------------|
|  1 | John | Engineering         | Engineering       |
|  2 | Jane | Engineering         | Engineering       |
|  3 | Bob  | Tool Design         | Tool Design       |
+----+------+---------------------+-------------------+

CREATE OR REPLACE TABLE purchases (
    purchase_id INT,
    member_tier VARCHAR(50),
    purchase_amount NUMBER(10, 2)
);

INSERT INTO purchases (purchase_id, member_tier, purchase_amount)
VALUES
    (1, 'Bronze', 50.00),
    (2, 'Silver', 150.00),
    (3, 'Gold', 300.00),
    (4, 'Bronze', 250.00);

CREATE OR REPLACE FUNCTION calculate_discount_script(
    purchase_amount NUMBER(10, 2),
    member_tier VARCHAR(50)
)
RETURNS NUMBER(10, 2)
LANGUAGE SQL
AS
  DECLARE
    final_amount NUMBER(10, 2) := purchase_amount;
  BEGIN
    -- STEP 1: 購入金額に応じた割引率を決定
    LET purchase_multiplier NUMBER(10, 2) := 1.0;

    IF (:purchase_amount < 100) THEN
      purchase_multiplier := 0.95;
    ELSEIF (:purchase_amount < 200) THEN
      purchase_multiplier := 0.90;
    ELSE
      purchase_multiplier := 0.85;
    END IF;

    -- STEP 2: 会員ランクに応じた追加割引率を決定
    LET member_rank_multiplier NUMBER(10, 2) := 1.0;

    IF (:member_tier = 'Silver') THEN
      member_rank_multiplier := 0.98;
    ELSEIF (:member_tier = 'Gold') THEN
      member_rank_multiplier := 0.95;
    ELSE
      member_rank_multiplier := 1.0; -- 割引なし
    END IF;

    -- STEP 3: 最終的な金額を計算
    final_amount := final_amount * purchase_multiplier * member_rank_multiplier;

    RETURN final_amount;
  END;
;

--従来の方法
CREATE OR REPLACE FUNCTION calculate_discount_case(
    purchase_amount NUMBER(10, 2),
    member_tier VARCHAR(50)
)
RETURNS NUMBER(10, 2)
AS
$$
  purchase_amount *
  (
      (CASE
          WHEN purchase_amount < 100 THEN 0.95
          WHEN purchase_amount < 200 THEN 0.90
          ELSE 0.85
      END) *
      (CASE
          WHEN member_tier = 'Silver' THEN 0.98
          WHEN member_tier = 'Gold' THEN 0.95
          ELSE 1.0
      END)
  )
$$;

>SELECT
    *
    ,calculate_discount_script(purchase_amount, member_tier) AS final_price_script
    ,calculate_discount_case(purchase_amount, member_tier) AS final_price_case
FROM purchases;
+-------------+-------------+-----------------+--------------------+------------------+
| PURCHASE_ID | MEMBER_TIER | PURCHASE_AMOUNT | FINAL_PRICE_SCRIPT | FINAL_PRICE_CASE |
|-------------+-------------+-----------------+--------------------+------------------|
|           1 | Bronze      |           50.00 |              47.50 |        47.500000 |
|           2 | Silver      |          150.00 |             132.30 |       132.300000 |
|           3 | Gold        |          300.00 |             242.25 |       242.250000 |
|           4 | Bronze      |          250.00 |             212.50 |       212.500000 |
+-------------+-------------+-----------------+--------------------+------------------+

--母音をかぞえるUDFを定義
CREATE OR REPLACE FUNCTION count_vowels(input_string VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
$$
DECLARE
  -- 母音の数を数えるための変数
  vowel_count INTEGER DEFAULT 0;

  -- ループの回数を格納する変数
  cnt INTEGER;

  -- ループの各イテレーションで使用する一時変数
  current_char VARCHAR;
BEGIN
  -- 文字列の長さを変数に代入
  cnt := LENGTH(:input_string);

  -- 文字列の長さ分ループ
  FOR i IN 1 TO cnt DO
    current_char := SUBSTR(:input_string, i, 1);

    -- 現在の文字が母音かチェック
    IF (current_char IN ('a', 'e', 'i', 'o', 'u', 'A', 'E', 'I', 'O', 'U')) THEN
      vowel_count := vowel_count + 1;
    END IF;
  END FOR;

  RETURN vowel_count;
END;
$$
;

>WITH sample_data AS (
    SELECT 'Hello Snowflake' AS my_string
    UNION ALL SELECT 'UDF is useful'
    UNION ALL SELECT 'Python'
    UNION ALL SELECT 'aeiou'
)
SELECT
    my_string,
    count_vowels(my_string) AS vowel_count
    ,REGEXP_COUNT(my_string, '[aeiouAEIOU]')
FROM
    sample_data;
+-----------------+-------------+-----------------------------------------+
| MY_STRING       | VOWEL_COUNT | REGEXP_COUNT(MY_STRING, '[AEIOUAEIOU]') |
|-----------------+-------------+-----------------------------------------|
| Hello Snowflake |           5 |                                       5 |
| UDF is useful   |           5 |                                       5 |
| Python          |           1 |                                       1 |
| aeiou           |           5 |                                       5 |
+-----------------+-------------+-----------------------------------------+