Complete Guide to Analytic Functions in SQL Server
#SQLServer #SQL #SQLAnalytics #SQLTutorial #WindowFunctions #SQLServerTutorial #DataAnalysis #RankingFunctions #ValueFunctions #StatisticalFunctions #Database #SQLQueries #LearnSQL #SQLServerFunctions #DataScience #TechTutorials #Programming #DatabaseManagement #sqltips
#SQLServerTraining #SQLServer2019 #SQLServer2022 #DatabaseDevelopment #SQLExpert #SQLLearning #AdvancedSQL #TechEducation #SQLFunctions #DataEngineering #CodingTutorials #SQLServerPerformance #DatabaseTips #SQLServerQueries #SQLServerAnalysis #ITTraining #SQLDatabase #Analytics #DataSkills #SQLServerBasics #panchayatonprime #roastbattle #panchayat
SQL Server Analytic Functions
------------------------------------------------------------------------------
Ranking Functions
Ranking functions are used to assign a rank to each row within the partition of a result set. They are useful for creating rankings, orderings, and distributions.
ROW_NUMBER():
Description: Assigns a unique sequential integer to rows within a partition of a result set, starting at 1 for the first row in each partition.
Example Usage:
SELECT
employee_id,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;
RANK():
Description: Assigns a rank to each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before it. Rows with equal values receive the same rank, with gaps in the ranking for subsequent ranks.
Example Usage:
SELECT
employee_id,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;
DENSE_RANK()
Description: Similar to RANK(), but DENSE_RANK() does not leave gaps in the ranking sequence when there are ties. Rows with equal values receive the same rank, but the next rank follows immediately.
Example Usage:
SELECT
employee_id,
salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM employees;
NTILE()
Description: Distributes the rows in an ordered partition into a specified number of groups, or "tiles". Each group is assigned a number, starting at one.
Example Usage:
SELECT
employee_id,
salary,
NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC) AS tile
FROM employees;
Value Functions
Value functions provide access to the value of a row at a specific physical offset from the current row within the result set. They are often used for comparison and trend analysis.
LAG():
Description: Provides access to a row at a given physical offset that comes before the current row within the result set.
Example Usage:
SELECT
employee_id,
salary,
LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary
FROM employees;
LEAD():
Description: Provides access to a row at a given physical offset that follows the current row within the result set.
Example Usage:
SELECT
employee_id,
salary,
LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS next_salary
FROM employees;
FIRST_VALUE():
Description: Returns the first value in an ordered set of values within the partition.
Example Usage:
SELECT
employee_id,
salary,
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary
FROM employees;
LAST_VALUE():
Description: Returns the last value in an ordered set of values within the partition.
Example Usage:
SELECT
employee_id,
salary,
LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary ASC) AS lowest_salary
FROM employees;
PERCENT_RANK():
Description: Calculates the relative rank of a row within a partition as a percentage of the total number of rows in the partition. The result is a value between 0 and 1.
Example Usage:
SELECT
employee_id,
salary,
PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS percent_rank
FROM employees;
CUME_DIST():
Description: Calculates the cumulative distribution of a value in a group of values. It represents the proportion of rows with a value less than or equal to the current row's value.
Example Usage:
SELECT
employee_id,
salary,
CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary DESC) AS cume_dist
FROM employees;
PERCENTILE_CONT():
Description: Computes a specific percentile for a continuous distribution of values in a group. It interpolates between values if the specified percentile falls between them.
Example Usage:
SELECT
employee_id,
salary,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id) AS median_salary
FROM employees;
Chapter :
00:00 CUME_DIST()
08:46 PERCENT_RANK()
18:13 FIRST_VALUE()
26:55 LAST_VALUE()
33:15 LED()
41:40 LAG()
46:12 PERCENTILE_CONT()
55:40 PERCENTILE DISC()
01:07:16 ROW_NUMBER()
01:09:46 RANK()
01:12:33 DENSE RANK()
01:14:57 NTILE()
Thank you for watching this video
EVERYDAY BE CODING
On this page of the site you can watch the video online Complete Guide to Analytic Functions in SQL Server with a duration of hours minute second in good quality, which was uploaded by the user Everyday Be Coding 26 May 2024, share the link with friends and acquaintances, this video has already been watched 2,063 times on youtube and it was liked by 44 viewers. Enjoy your viewing!
