Hi Community,
In this article, I listed 5 useful SQL functions with explanations and query examples 👇🏻
These 5 functions are
So Let us start with COALESCE function
#COALESCE
The COALESCE function evaluates a list of expressions in left-to-right order and returns the value of the first non-NULL expression. If all expressions evaluate to NULL, NULL is returned.
Following statement will return first not null value which is 'intersystems'
SELECT COALESCE(NULL, NULL, NULL,'intersystems', NULL,'sql')
Let us create below table for further example
CREATE TABLE EXPENSES(
TDATE DATE NOT NULL,
EXPENSE1 NUMBER NULL,
EXPENSE2 NUMBER NULL,
EXPENSE3 NUMBER NULL,
TTYPE CHAR(30) NULL)
Now let us insert some dummy data to test our function
INSERT INTO sqluser.expenses (tdate, expense1,expense2,expense3,ttype )
SELECT {d'2023-01-01'}, 500,400,NULL,'Present'
UNION ALL
SELECT {d'2023-01-01'}, NULL,50,30,'SuperMarket'
UNION ALL
SELECT {d'2023-01-01'}, NULL,NULL,30,'Clothes'
UNION ALL
SELECT {d'2023-01-02'}, NULL,50,30 ,'Present'
UNION ALL
SELECT {d'2023-01-02'}, 300,500,NULL,'SuperMarket'
UNION ALL
SELECT {d'2023-01-02'}, NULL,400,NULL,'Clothes'
UNION ALL
SELECT {d'2023-01-03'}, NULL,NULL,350 ,'Present'
UNION ALL
SELECT {d'2023-01-03'}, 500,NULL,NULL,'SuperMarket'
UNION ALL
SELECT {d'2023-01-04'}, 200,100,NULL,'Clothes'
UNION ALL
SELECT {d'2023-01-06'}, NULL,NULL,100,'SuperMarket'
UNION ALL
SELECT {d'2023-01-06'}, NULL,100,NULL,'Clothes' 
Now by using COALESCE function we will retrieve first not NULL value from expense1,expense2 and expense 3 columns
SELECT TDATE,
COALESCE(EXPENSE1,EXPENSE2,EXPENSE3),
TTYPE
FROM sqluser.expenses ORDER BY 2 
#RANK vs DENSE_RANK vs ROW_NUMBER functions
- RANK()— assigns a ranking integer to each row within the same window frame, starting with 1. Ranking integers can include duplicate values if multiple rows contain the same value for the window function field.
- ROW_NUMBER() — assigns a unique sequential integer to each row within the same window frame, starting with 1. If multiple rows contain the same value for the window function field, each row is assigned a unique sequential integer.
- DENSE_RANK() leaves no gaps after a duplicate rank.
In SQL, there’s several ways that you can assign a rank to a row, which we’ll dive into with an example. Consider once again the same example as before, but now we want to know what is the highest expenses.
We want to know where do I spend the most money. There are different ways to do it. We can use all ROW_NUMBER() , RANK() and DENSE_RANK() . We will order the previous table using all three functions and see what are the main differences between them using the following query:
Below is our query:
The main difference between al three functions is the way they deal with ties. We will further deep-dive their differences:
ROW_NUMBER()returns a unique number for each row starting at 1. When there are ties, it arbitrarily assigns a number if a second criteria is not defined.
RANK()returns a unique number for each row starting at 1, except for when there are ties, then it will assign the same number. As well, a gap will follow a duplicate rank.
DENSE_RANK() leaves no gaps after a duplicate rank.
#Calculating Running Totals
The running total is probably one of the most useful window functions especially when you want to visualize growth. Using a window function with SUM(), we can calculate a cumulative aggregation.
To do so, we just need to sum a variable using the aggregator SUM() but order this function using a TDATE column.
You can observe the corresponding query as follows:
As you can observe in the table above, now we have the accumulated aggregation of the amount of money spent as the date passes by.
Conclusion
SQL is great. Functions used above might be useful when dealing data analysis, data science, and any other data-related field.
This is why you should care to keep improving your SQL skills.
Thanks
Top comments (0)