SQL Tutorial - Window Functions (Follow Along)
6.3 هزار بار بازدید -
4 سال پیش
-
Another video brought to you
Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis.
If you like the video you can support me on Patreon, Patreon: beardeddev
A lot of views have requested the ability to follow along with my tutorials particularly on a tutorial I have already on Window Functions, this video builds on the previous one by adding the ability to follow along with the tutorials with the code examples below.
The original video can be found here: SQL Tutorial - Window Functions
In this video we create a sample table and then use that data to cover different scenarios, first we take a long at using GROUP BY in SQL Server and understand the limitations. We then introduce a CTE to the query to allow us to join back to the original table to retrieve further columns that are not included in the GROUP BY. We discuss the problems with this approach, mainly that we have to join to the table multiple times, the query is also more verbose, the other downside is that we will need to create multiple CTEs or Derived Tables for each different aggregation we want to perform.
We then introduce Window Functions including how to use OVER and what PARTITION BY means, we also discuss how we can use different aggregate functions and that Window Functions are only allowed in SELECT or ORDER BY. We demonstrate different Window Functions using different PARTITION BY clause and even show a Window Function to calculate the grand total.
Lastly we demonstrate how we can use Window Functions in expressions to calculate percent of totals.
If you would like to follow along with this SQL tutorial then you can use the below SQL to create the necessary objects.
Please feel free to post any comments.
DROP TABLE IF EXISTS dbo.SalesDetails;
GO
CREATE TABLE dbo.SalesDetails
(
SalesDetailsId INT NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_SalesDetails_SalesDetailsId PRIMARY KEY (SalesDetailsId),
SalesId INT NOT NULL,
SalesDate DATE NOT NULL,
ProductId INT NOT NULL,
Price MONEY NOT NULL,
Quantity INT NOT NULL,
LineTotal AS Price * Quantity
);
INSERT INTO dbo.SalesDetails (SalesId, SalesDate, ProductId, Price, Quantity)
VALUES
(1, '20200105', 6, 5.99, 2),
(1, '20200105', 5, 4.50, 1),
(1, '20200105', 4, 17.99, 4),
(2, '20200107', 2, 2.99, 2),
(2, '20200107', 3, 11.40, 1),
(3, '20200107', 6, 5.99, 4),
(3, '20200107', 2, 2.99, 2),
(3, '20200107', 3, 11.40, 1),
(3, '20200107', 9, 6.29, 4),
(4, '20200108', 9, 6.29, 2),
(4, '20200108', 8, 23.10, 1),
(4, '20200108', 1, 13.25, 4),
(4, '20200108', 2, 2.99, 2),
(4, '20200108', 3, 11.40, 1),
(5, '20200110', 4, 17.99, 4),
(6, '20200110', 7, 19.00, 2),
(6, '20200110', 9, 6.29, 10);
WITH CTE AS
(
SELECT
SalesId,
SUM(LineTotal) AS SalesTotal
FROM dbo.SalesDetails
GROUP BY
SalesId
)
SELECT
SalesDetailsId,
A.SalesId,
SalesDate,
ProductId,
Price,
Quantity,
LineTotal,
SalesTotal
FROM dbo.SalesDetails AS A
INNER JOIN CTE AS B
ON A.SalesId = B.SalesId;
SELECT
SalesDetailsId,
SalesId,
SalesDate,
ProductId,
Price,
Quantity,
LineTotal,
SUM(LineTotal) OVER(PARTITION BY SalesId) AS SalesTotal,
COUNT(SalesDetailsId) OVER(PARTITION BY SalesId) AS SalesCount,
SUM(LineTotal) OVER(PARTITION BY SalesDate) AS DailyTotal,
SUM(LineTotal) OVER(PARTITION BY SalesDate, ProductId) AS DailyProductSales,
SUM(LineTotal) OVER() AS SalesGrandTotal,
100 * SUM(LineTotal) OVER(PARTITION BY SalesId) / SUM(LineTotal) OVER() AS pcttotal
FROM dbo.SalesDetails
ORDER BY SalesId
If you like the video you can support me on Patreon, Patreon: beardeddev
A lot of views have requested the ability to follow along with my tutorials particularly on a tutorial I have already on Window Functions, this video builds on the previous one by adding the ability to follow along with the tutorials with the code examples below.
The original video can be found here: SQL Tutorial - Window Functions
In this video we create a sample table and then use that data to cover different scenarios, first we take a long at using GROUP BY in SQL Server and understand the limitations. We then introduce a CTE to the query to allow us to join back to the original table to retrieve further columns that are not included in the GROUP BY. We discuss the problems with this approach, mainly that we have to join to the table multiple times, the query is also more verbose, the other downside is that we will need to create multiple CTEs or Derived Tables for each different aggregation we want to perform.
We then introduce Window Functions including how to use OVER and what PARTITION BY means, we also discuss how we can use different aggregate functions and that Window Functions are only allowed in SELECT or ORDER BY. We demonstrate different Window Functions using different PARTITION BY clause and even show a Window Function to calculate the grand total.
Lastly we demonstrate how we can use Window Functions in expressions to calculate percent of totals.
If you would like to follow along with this SQL tutorial then you can use the below SQL to create the necessary objects.
Please feel free to post any comments.
DROP TABLE IF EXISTS dbo.SalesDetails;
GO
CREATE TABLE dbo.SalesDetails
(
SalesDetailsId INT NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_SalesDetails_SalesDetailsId PRIMARY KEY (SalesDetailsId),
SalesId INT NOT NULL,
SalesDate DATE NOT NULL,
ProductId INT NOT NULL,
Price MONEY NOT NULL,
Quantity INT NOT NULL,
LineTotal AS Price * Quantity
);
INSERT INTO dbo.SalesDetails (SalesId, SalesDate, ProductId, Price, Quantity)
VALUES
(1, '20200105', 6, 5.99, 2),
(1, '20200105', 5, 4.50, 1),
(1, '20200105', 4, 17.99, 4),
(2, '20200107', 2, 2.99, 2),
(2, '20200107', 3, 11.40, 1),
(3, '20200107', 6, 5.99, 4),
(3, '20200107', 2, 2.99, 2),
(3, '20200107', 3, 11.40, 1),
(3, '20200107', 9, 6.29, 4),
(4, '20200108', 9, 6.29, 2),
(4, '20200108', 8, 23.10, 1),
(4, '20200108', 1, 13.25, 4),
(4, '20200108', 2, 2.99, 2),
(4, '20200108', 3, 11.40, 1),
(5, '20200110', 4, 17.99, 4),
(6, '20200110', 7, 19.00, 2),
(6, '20200110', 9, 6.29, 10);
WITH CTE AS
(
SELECT
SalesId,
SUM(LineTotal) AS SalesTotal
FROM dbo.SalesDetails
GROUP BY
SalesId
)
SELECT
SalesDetailsId,
A.SalesId,
SalesDate,
ProductId,
Price,
Quantity,
LineTotal,
SalesTotal
FROM dbo.SalesDetails AS A
INNER JOIN CTE AS B
ON A.SalesId = B.SalesId;
SELECT
SalesDetailsId,
SalesId,
SalesDate,
ProductId,
Price,
Quantity,
LineTotal,
SUM(LineTotal) OVER(PARTITION BY SalesId) AS SalesTotal,
COUNT(SalesDetailsId) OVER(PARTITION BY SalesId) AS SalesCount,
SUM(LineTotal) OVER(PARTITION BY SalesDate) AS DailyTotal,
SUM(LineTotal) OVER(PARTITION BY SalesDate, ProductId) AS DailyProductSales,
SUM(LineTotal) OVER() AS SalesGrandTotal,
100 * SUM(LineTotal) OVER(PARTITION BY SalesId) / SUM(LineTotal) OVER() AS pcttotal
FROM dbo.SalesDetails
ORDER BY SalesId
4 سال پیش
در تاریخ 1399/06/07 منتشر شده
است.
6,357
بـار بازدید شده