Window Function Types

Types for Window Functions:

• CUME_DIST: 
• Calculates the cumulative distribution of a value in a set of values.
• DENSE_RANK: 
• Assigns a rank to every row within its partition based on the ORDER BY clause. 
• It assigns the same rank to the rows with equal values. 
• If two or more rows have the same rank, then there will be no gaps in the sequence of ranked values.
• RANK:
• Similar to the DENSE_RANK() function except that there are gaps in the sequence of ranked values when two or more rows have the same rank.
• ROW_NUMBER:
• Assigns a sequential integer to every row within its partition
• FIRST_VALUE:
• Returns the value of the specified expression with respect to the first row in the window frame.
• LAST_VALUE:
• Returns the value of the specified expression with respect to the last row in the window frame.
• Default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• LAG:
• It allows you to look back a number of rows and access data of that row from the current row.
• Returns the value of the Nth row before the current row in a partition. 
• It returns NULL if no preceding row exists.
• It can finds the order date and the previous order date of each customer
• LEAD:
• It is very useful for calculating the difference between the current row and the subsequent row within the same result set.
• Returns the value of the Nth row after the current row in a partition.
• It returns NULL if no subsequent row exists.
• It can finds the order date and the next order date of each customer
• NTILE:
• Distributes the rows for each window partition into a specified number of ranked groups(buckets).
• PERCENT_RANK:
• It ranges from zero to one
• Calculates the percentile rank of a row in a partition or result set “Formula: (rank - 1) / (total_rows - 1)”

SQL CODE

************Create data Set******************/

CREATE TABLE sales(

    sales_employee VARCHAR(50) , fiscal_year INT ,

    sale DECIMAL(14,2)

);

 

INSERT INTO sales(sales_employee,fiscal_year,sale)

VALUES

('Bob',2016,100), ('Bob',2017,150), ('Bob',2018,200),

('Alice',2016,150), ('Alice',2017,100), ('Alice',2018,200),

('John',2016,200), ('John',2017,150), ('John',2018,250);

Select * from sales

/******************************/   

RANK() - Functions:

• The RANK() function is a window function could be used in SQL Server to calculate a rank for each row within a partition of a result set. 
• The same rank is assigned to the rows in a partition which have the same values.
•  The ranks may not be consecutive in the RANK() function as it adds the number of repeated rows to the repeated rank to calculate the rank of the next row. 
• It specify rank for each row in the result set.
• Syntax: Select *, Rank()over(order by <columnname>) as ‘col_Rank’ From Tablename

DENSE_RANK () - Function:

• Dense Rank analytical function is same as rank but it has assigned the consecutive rank for Tie values in the table.
• It overcomes disadvantage of rank function in case of duplicate data
• Syntax:  Select *, Dense_Rank()over(order by <columnname>) as ‘col_denRank’ From Tablename