SQL: User Defined Functions

Functions in SQL work just like functions in most programming languages. If you aren’t familiar with a function, you should know that you are already using them without even knowing it.

Consider this for example:

Select Count(*)
From Table

COUNT() is a function. When you pass it rows from your query, it counts the rows and returns a value in the form of an integer. But COUNT() is a built in function, meaning it came as part of SQL Server, you did not have to create it.

SQL Server allows you the option of creating User Defined Functions, meaning functions you develop yourself. These are handy when you find yourself handling repeated tasks, such as date formatting or string manipulation. Instead of having to repeatedly code a complex command, you can just build a function once and then call on it whenever needed.

Let’s start with a basic example:

2018-04-03_14-23-36.png

Here I created a function called ADD_UP that accepts 2 numbers and outputs the SUM of the two numbers ( yes I know this already available as the built in function SUM(), but I want to start nice and easy)

Lets start by discussing the syntax. The basic syntax for creating a function is as follows:

CREATE FUNCTION name (@var data-type)
RETURNS data-type
AS
BEGIN
   RETURNS (some type of action)
END

In my example we are naming the function ADD_UP and supplying two integer variables: @NUM1 and @NUM2

CREATE FUNCTION ADD_UP (@NUM1 INT, @NUM2 INT)

Then we define the data-type our function will return. In this case, since we are adding 2 integers, our function will return an INT

RETURNS INT

Next we wrap out function in

AS
BEGIN
ENDS

Finally, we perform an action

RETURNS (@NUM1+ @NUM2)

Finally, when you want to call the Function, just use it in a select statement.

(**Note, user defined functions require you to use the schema prefix. Since I just used the default dbo schema, this example uses dbo.ADD_UP)

select dbo.ADD_UP(2,3) as ADDED

and as you see, we get 5 as our answer.

2018-04-03_14-23-36

Now, let’s try something different. Here we are going to work with a date function. In this example I built a function called MNTH that accepts one variable @DT – a date data-type and returns an Integer representing the month of the date passed to it.

Again, all I am really doing is duplicating the built-in function MONTH(), but I wanted to show different data-types

2018-04-03_14-32-54.png

(** getdate() is a built-in function that returns the current date. I ran this SQL on 4/2/2018, so it returns 4 as a result)

Now finally here is an example of how you might use a function in real life. Let’s say you have lots of reports that call for your date to be represented in MM-YYYY format. Instead of having to repeatedly type a complex date formatting, you can build it into a User Defined Function and pass a regular date to the function.

2018-04-03_14-34-36

If you are not familiar with cast(concat(month(@DT),’-‘,year(@DT))as varchar(8))) statement, I’ll break it down here:

Let’s go from the inside out:

concat is a string function meaning to concatenate or “string together” – so

concat(month(@DT),’-‘,year(@DT))

concat(4, ‘-‘, 2018)

4-2018

Cast allows us to convert the output of the concat statement into a string (varchar) data-type

cast(4-2018 as varchar(8)) = ‘4-2018’

Finally, if you want to find your functions after you create them, they are located under your database -> Programmability -> Functions

In this case, I only built Scalar-valued Functions, I’ll cover the other types in future lessons.

2018-04-03_14-39-21.png

 

Advertisements

Python: Fun with Central Tendency

Now numpy provides easy functions for finding central tendency – you can find them in my Numpy Part II lesson: Python: Numpy Part II.

But we have learned enough about Python so far, that maybe it would be more fun to build our own functions. In this lesson we are going to build our own statistics library with mean, median, mode, and quantile

Our Data

pythonCent

Mean

pythonCent1.jpg

or even easier:

pythonCent3

Median

Remember with median – if your data contains an odd number of elements (n%2==1), you just take the middle value. However, if your data contains and even number of elements (n%2==0) then you add the two middle numbers and divide by 2.

We handle that through the use of an if statement in our function.

pythonCent2

Mode

For mode, we want to find the most common element in the list. For this task, I will import Counter from collections.

d.most_common() returns each unique element and the number of times it appears

d.most_common(1) returns the

pythonCent4.jpg

or in function form:

pythonCent5.jpg

In my numpy part II lesson I use a more elegant solution, but I want you to see that there is always more than one way to do something in Python.

Quantile

Quantiles are cut points in set of data. They can represent the bottom ten percent of the data or the top 75% or any % from 0 to 100.

In my solution, I am adding a slicing argument to the sorted() function. Below shows all elements up to (but not including) index 4

pythonCent6

quantile function:

pythonCent7


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more Python content: Python

Python Functions

Working with functions will greatly improve your programming capabilities. You have already worked with a few functions: range(), float(). Now I am going to show you how to build your own.

One great thing about functions is they are reusable. Image you had written a complex formula that calculates final grade based on points earned for 25 tests and assignments. Would you want to have to rewrite that code every time you need it? No, it is much easier just to write it once and call it up when you need it.

Enough talk, let’s build our first function.

Syntax

def FUNCTION NAME (PARAMETERS):
“””DOCSTRING”””
COMMANDS
RETURN[EXPRESSION]

In the example below, we are not using Return yet. We will get to
that soon enough.

So let’s examine the function below:

  • def talkback (strg): = define a new function named talkback which takes one parameter – strg ** note strg is just a variable name I used. It could just as easily been x or y
  • “””this prints the string you submit””” = this is the docstring, we will cover this in a minute
  • print strg = this is the command portion of the function. This will print the parameter you pass to it.
  • ln[2]: talkback(“Print this line”) = this calls the function we had just created. It passes the value “Print this line” to strg, which the function then prints
  • help(talkback) — this displays the docstring from the function. Docstrings are instructions that help you to understand how the function works. This is especially important as your functions become more and more complex. Just think of docstrings as help functions.  ** note the triple quotes “”” — this allows you have multiple lines inside the quotes

pythonfunc

Now let’s look at a function using Return

This function, named summed(), is accepting 3 parameters. It then takes the values of the 3 passed parameters and adds them up (assigning them to “s“). Instead of printing the result out, like in the example above, it uses Return s to return the summed value.

The purpose of Return can be seen in ln[8]. As you can see, the summed() function is being used as a number in a division problem. We are able to do this, since the Return command effectively replaces the summed() function with a numeric value in the division equation.

pythonfunc2.jpg

Here is what happens if I try using Print instead of Return. Notice the division problem errors out.

pythonfunc3.jpg

Default Arguments and Named Arguments

Let’s look at this function below:

The first thing I want you to notice is the price = 50 in the parameters section. This is called a default argument. As you can see in ln[15], if I only pass a value for item, function prints out a price of 50 – the default value given.

Now look at ln[16]. When I pass a value for price, it overrides the default value of 50 and sets it to 25

Finally, look at ln[17]. This is naming my arguments. Notice the order of the arguments can be overridden if I pass arguments in the form of parameterName = Value.

pythonfunc4.jpg


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT

Follow this link for more Python content: Python