如何在雪花中使用 SQL 用户定义函数? [英] how to use SQL user defined function in snowflake?

问题描述

我只是在研究如何在雪花中使用SQL.截图如下:

I am just studying how to use SQL in snowflake. Here is a snapshot:

这是这里使用的代码:

use schema SNOWFLAKE_SAMPLE_DATA.TPCH_SF1;
--use schema SNOWFLAKE_SAMPLE_DATA.TPCH_SF10;

select *
from LINEITEM
limit 200

您可以看到该表包含两个字段:L_LINENUMBER、L_QUANTITY.现在我想尝试一个用户定义的函数，它可以做到:

You can see the table includes two feilds: L_LINENUMBER, L_QUANTITY. Now I want to try a user defined function, which can do:

1. 使用 L_LINENUMBER、L_QUANTITY 作为传入函数的两个参数，
2. 计算 L_LINENUMBER1=L_LINENUMBER+1，并且 L_QUANTITY1=mean(L_QUANTITY).
3. 将两个新字段(L_LINENUMBER1、L_QUANTITY1)连接到原始表(LINEITEM)

如何使用创建函数来做到这一点.我已经阅读了很多关于创建函数的例子.但我就是不明白这一点.也许是因为我不擅长 SQL.那么，谁能给我一个包含所有细节的综合示例?

how to use create function to do this. I have read a lot of examples regarding create function. But I just cannot get the point. Maybe because I am not good at SQL. So, could anyone give me a comprehensive example with all the details?

推荐答案

我知道您的问题是关于 UDF，但是在这里使用 UDF 是过度的.

I understand that you question is about UDFs, but using UDFs for your purpose here is overkill.

您可以使用以下语句增加表中的属性.

You can increment an attribute in a table using the following statement.

SELECT
    L_LINENUMBER+1 as L_LINENUMBER1
FROM LINEITEM;

要计算表中属性的平均值，您应该了解这是一个聚合函数，只有在与 group by 语句结合使用时才有意义.您的数据示例如下所示.

To calculate the mean of an attribute in a table, you should understand that this is an aggregate function which only makes sense when used in conjunction with a group by statement. An example with your data is shown below.

SELECT
    AVG(L_QUANTITY) AS L_QUANTITY1
FROM LINEITEM
GROUP BY L_ORDERKEY;

由于您的问题最初是关于 UDF 并且您似乎正在关注 Snowflake 的示例数据，因此他们提供的示例是以下 UDF，它接受开尔文温度并将其转换为华氏度(从定义中您可以看到它可以应用于数字类型的任何属性).

Since your question was originally on UDFs and you seem to be following with Snowflake's sample data, the example that they provide is the following UDF which accepts a temperature in Kelvin and converts it to Fahrenheit (from the definition you can see that it can be applied to any attribute of the number type).

CREATE OR REPLACE FUNCTION 
  UTIL_DB.PUBLIC.convert_fahrenheit( t NUMBER)
  RETURNS NUMBER
  COMMENT='Convert from Kelvin from Fahrenheit'
  AS '(t - 273.15) * 1.8000 + 32.00';

这篇关于如何在雪花中使用 SQL 用户定义函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！