SQL Tutorial - Window Functions - Ranking
30.1 هزار بار بازدید -
7 سال پیش
-
Another fantastic SQL Tutorial brought
Another fantastic SQL Tutorial brought to you by BeardedDev.
If you are new to working with Window Functions check out this video:
SQL Tutorial - Window Functions
T-SQL Querying
https://www.amazon.com/gp/product/073...
T-SQL Fundamentals
https://www.amazon.com/gp/product/150...
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
https://www.amazon.com/gp/product/073...
In this video we explore ranking functions available as part of Window Functions:
ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()
This tutorial shows an example of when to use each ranking function and the differences between them.
Window Functions were first introduced in SQL Server 2005 but further enhancements and support was added in SQL Server 2012.
We look at the OVER clause and PARTITION BY.
Window Functions can only be included within SELECT or ORDER BY clauses.
Functions Available:
Aggregate - COUNT, SUM, MIN, MAX, AVG
Ranking - ROW_NUMBER, RANK, DENSE_RANK, NTILE
Offset - FIRST_VALUE, LAST_VALUE, LEAD, LAG
Statistical - PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DIST
Windows Functions also have FRAMES
ROWS
RANGE
Window Functions are a powerful tool within SQL Server and I am excited to bring more videos and tutorials working with Window Functions in the future.
ROW_NUMBER - unique incrementing integers
RANK - same rank for same values
DENSE_RANK - same rank for same values
NTILE - assigns tile numbers based on number of tiles requested
SQL:
SELECT
Sales_Id
, Sales_Total
, ROW_NUMBER() OVER(ORDER BY Sales_Total DESC) AS rownum
, RANK() OVER(ORDER BY Sales_Total DESC) AS rnk
, DENSE_RANK() OVER(ORDER BY Sales_Total DESC) AS dense
, NTILE(3) OVER(ORDER BY Sales_Total DESC) AS ntle
FROM dbo.Sales_2
SELECT
Sales_Id
, NTILE(10) OVER(ORDER BY Sales_Total DESC) AS ntle
FROM dbo.Sales_2
If you are new to working with Window Functions check out this video:
SQL Tutorial - Window Functions
T-SQL Querying
https://www.amazon.com/gp/product/073...
T-SQL Fundamentals
https://www.amazon.com/gp/product/150...
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
https://www.amazon.com/gp/product/073...
In this video we explore ranking functions available as part of Window Functions:
ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()
This tutorial shows an example of when to use each ranking function and the differences between them.
Window Functions were first introduced in SQL Server 2005 but further enhancements and support was added in SQL Server 2012.
We look at the OVER clause and PARTITION BY.
Window Functions can only be included within SELECT or ORDER BY clauses.
Functions Available:
Aggregate - COUNT, SUM, MIN, MAX, AVG
Ranking - ROW_NUMBER, RANK, DENSE_RANK, NTILE
Offset - FIRST_VALUE, LAST_VALUE, LEAD, LAG
Statistical - PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DIST
Windows Functions also have FRAMES
ROWS
RANGE
Window Functions are a powerful tool within SQL Server and I am excited to bring more videos and tutorials working with Window Functions in the future.
ROW_NUMBER - unique incrementing integers
RANK - same rank for same values
DENSE_RANK - same rank for same values
NTILE - assigns tile numbers based on number of tiles requested
SQL:
SELECT
Sales_Id
, Sales_Total
, ROW_NUMBER() OVER(ORDER BY Sales_Total DESC) AS rownum
, RANK() OVER(ORDER BY Sales_Total DESC) AS rnk
, DENSE_RANK() OVER(ORDER BY Sales_Total DESC) AS dense
, NTILE(3) OVER(ORDER BY Sales_Total DESC) AS ntle
FROM dbo.Sales_2
SELECT
Sales_Id
, NTILE(10) OVER(ORDER BY Sales_Total DESC) AS ntle
FROM dbo.Sales_2
7 سال پیش
در تاریخ 1396/08/17 منتشر شده
است.
30,166
بـار بازدید شده