Styjun

Shortest distance around a pyramid?

Does Ubuntu Server 18.04.2 LTS "track" users in any way?

Dropping outliers based on "2.5 times the RMSE"

Are randomly-generated passwords starting with "a" less secure?

Can I use "candidate" as a verb?

How can an advanced civilization forget how to manufacture its technology?

Was adding milk to tea started to reduce employee tea break time?

Why do players in the past play much longer tournaments than today's top players?

How to check the quality of an audio sample?

What is this welding tool I found in my attic?

Machine learning and operations research projects

Is a Lisp program in both prog-mode and lisp-mode?

SSH Jump and local command

How to know whether a Tamron lens is compatible with Canon EOS 60D?

Why isn't there research to build a standard lunar, or Martian mobility platform?

Why does Hellboy file down his horns?

Why are they 'nude photos'?

What is temperature on a quantum level

When did the Roman Empire fall according to contemporaries?

Serial communication between STM32F4 and PC -USART-

Referring to different instances of the same character in time travel

Keep milk (or milk alternative) for a day without a fridge

Can I play a first turn Simic Growth Chamber to have 3 mana available in the second turn?

Was the Ford Model T black because of the speed black paint dries?

how to use SQL user defined function in snowflake?

How can I prevent SQL injection in PHP?Add a column with a default value to an existing table in SQL ServerHow do JavaScript closures work?What's the difference between a method and a function?var functionName = function() vs function functionName() Set a default parameter value for a JavaScript functionSQL Server: How to Join to first rowHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

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

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?

sql function user-defined-functions snowflake

share|improve this question

asked Mar 26 at 4:02

Feng ChenFeng Chen

63811 gold badge77 silver badges2121 bronze badges

add a comment | 

0

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

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?

sql function user-defined-functions snowflake

share|improve this question

asked Mar 26 at 4:02

Feng ChenFeng Chen

63811 gold badge77 silver badges2121 bronze badges

add a comment | 

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

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?

sql function user-defined-functions snowflake

share|improve this question

asked Mar 26 at 4:02

Feng ChenFeng Chen

63811 gold badge77 silver badges2121 bronze badges

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

select *
from LINEITEM
limit 200

share|improve this question

asked Mar 26 at 4:02

Feng ChenFeng Chen

63811 gold badge77 silver badges2121 bronze badges

share|improve this question

asked Mar 26 at 4:02

Feng ChenFeng Chen

63811 gold badge77 silver badges2121 bronze badges

asked Mar 26 at 4:02

Feng ChenFeng Chen

63811 gold badge77 silver badges2121 bronze badges

asked Mar 26 at 4:02

Feng ChenFeng Chen

63811 gold badge77 silver badges2121 bronze badges

1 Answer
1

active

oldest

votes

1

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

share|improve this answer

answered Apr 17 at 19:27

Joshua HowardJoshua Howard

46311 gold badge77 silver badges1919 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55349660%2fhow-to-use-sql-user-defined-function-in-snowflake%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

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

share|improve this answer

answered Apr 17 at 19:27

Joshua HowardJoshua Howard

46311 gold badge77 silver badges1919 bronze badges

add a comment | 

1

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

share|improve this answer

answered Apr 17 at 19:27

Joshua HowardJoshua Howard

46311 gold badge77 silver badges1919 bronze badges

add a comment | 

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

share|improve this answer

answered Apr 17 at 19:27

Joshua HowardJoshua Howard

46311 gold badge77 silver badges1919 bronze badges

SELECT
 L_LINENUMBER+1 as L_LINENUMBER1
FROM LINEITEM;

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

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

share|improve this answer

answered Apr 17 at 19:27

Joshua HowardJoshua Howard

46311 gold badge77 silver badges1919 bronze badges

answered Apr 17 at 19:27

Joshua HowardJoshua Howard

46311 gold badge77 silver badges1919 bronze badges

answered Apr 17 at 19:27

Joshua HowardJoshua Howard

46311 gold badge77 silver badges1919 bronze badges