SQL Tutorial - Window Functions - Calculate Running Totals, Averages

BeardedDev
BeardedDev
42 هزار بار بازدید - 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 learn how to use Window Functions to calculate running totals and running averages.  This video teaches about Window Frames:
Rows
Range

Preceding
Current Row
Following

Window Frames are a filtered portion of a partition.

Window Functions were first introduced in SQL Server 2005 but further enhancements and support was added in SQL Server 2012.

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.

SQL:
SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Running Total]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date

SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS [Running Total]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date

SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS [Running Total]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date

SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS [Running Total]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date

SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS [Running Total]
, CAST(AVG(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS DECIMAL(8, 2)) AS [Running Average]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date
7 سال پیش در تاریخ 1396/08/25 منتشر شده است.
42,012 بـار بازدید شده
... بیشتر