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