CODEWITHSUNDEEP
sqlfunctionuser-defined-functionssnowflake-cloud-data-platform
Feng Chen
Feng Chen

Reputation: 2253

how to use SQL user defined function in snowflake?

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

enter image description here

And this is the code used in here:

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

select *
from LINEITEM
limit 200

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. use L_LINENUMBER, L_QUANTITY as two parameters transferred into the function,
  2. calculate L_LINENUMBER1=L_LINENUMBER+1, and L_QUANTITY1=mean(L_QUANTITY).
  3. join the two new fields (L_LINENUMBER1, L_QUANTITY1) to the original table (LINEITEM)

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?

Upvotes: 0

Views: 2339

Answers (1)

Joshua Howard
Joshua Howard

Reputation: 916

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;

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;

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';

Upvotes: 2

Related Questions