Skip to content

Day 4: Advance SQL For Data Science

 Becoming Human: Artificial Intelligence Magazine – Medium This blog contains Window Ranking function in SQL like (Rank, Dense_Rank, Row_Number , Lead, Lag) .Rank()This RANK() function calculates a rank to each row within a partition of a result set.The Syntax of Rank() window function :-RANK() OVER ( [PARTITION BY partition_expression, … ] ORDER BY sort_expression [ASC | DESC], …)In PARTITION BY clause, it divides the rows of the result set partitions to which the rank() function is applied.In ORDER BY The clause specifies whether the row needs to be sorted into asc/dec order of the rows for each partition to which the Rank() function is applied.Here is the Example of Rank()This is the Employee Table with Name and Salary.We are applying Rank() on Salary Column: select *, rank() over (order by salary desc) rn from Employee e;The rank() function splits the salary column on the bases of descending order.Now we are applying Rank() with Partition by select *, rank() over (Partition by name order by salary desc) rn from Employee eIn this Rank() function splits on the partition of the name column with the salary column in descending order.DENSE_RANK()The DENSE_RANK() function returns consecutive rank values with each row in each partition receiving the same ranks if they have the same values.The Syntax of Dense_Rank() window function :-DENSE_RANK() OVER ( [PARTITION BY partition_expression, … ] ORDER BY sort_expression [ASC | DESC], …)We are applying Dense_Rank() on Salary Column: select *, dense_rank() over (order by salary desc) rn from Employee e;Now we are applying Dense_Rank() with Partition by: select *, dense_rank() over (Partition by name order by salary desc) rn from Employee eIn this Dense_Rank() function splits on the partition of the name column with the salary column in descending order.ROW_NUMBER()The ROW_NUMBER() is a simple window function that gives an integer row number to the corresponding row. The row number starts with 1 for the first row in each partition.The Syntax of Row_number() window function :-ROW_NUMBER() OVER ( [PARTITION BY partition_expression, … ] ORDER BY sort_expression [ASC | DESC], …)We are applying row_number() on Salary Column: select *, rank() over (order by salary desc) rn from Employee e;we are applying row_number() with Partition by: select *, rank() over (Partition by name order by salary desc) rn from Employee e;In this Row_Number() function splits on the partition of the name column with the salary column in descending order.LEAD() and LAG()LAG() and LEAD() are positional functions. These are window functions and are very useful in creating reports because they can refer to data from rows above or below the current rowThe LAG() function allows access to a value stored in a different row above the current row.Lag()Syntax of LAG():LAG(expression [,offset[,default_value]]) OVER(ORDER BY columns)Query: SELECT *, LAG(Salary) OVER(Partition By name ORDER BY Salary asc) as previous_sale_value FROM Employee;LEAD() is similar to LAG(). Whereas LAG() accesses a value stored in a row above, LEAD() accesses a value stored in a row below.Syntax of Lead():LEAD(expression [,offset[,default_value]]) OVER(ORDER BY columns)Query: SELECT *, LEAD(Salary) OVER(Partition By name ORDER BY Salary asc) as previous_sale_value FROM Employee;LEAD()NOTE: The PARITION BY clause is optional. If you omit it, the function will treat the whole result set as a single partition.Thankyou for reading it.=============================THE END==========================GitHub : Day 4 SessionPlease give it a star on github!!Reference :https://www.w3schools.com/sql/default.asphttps://www.geeksforgeeks.org/Hope you found it helpful! Thanks for reading!Follow me for more Data Science related posts!Let’s connect on LinkedIn!Day 4: Advance SQL For Data Science was originally published in Becoming Human: Artificial Intelligence Magazine on Medium, where people are continuing the conversation by highlighting and responding to this story. Read More 

Leave a Reply

Your email address will not be published. Required fields are marked *