8.2 Window Functions

 In SQL Server, Window Functions are powerful tools for performing calculations across a specified range of rows related to the current row within the result set. They are used with the OVER clause and can be applied to aggregate functions and ranking functions. Let's explore some common window functions.

8.2.1 ROW_NUMBER()

The ROW_NUMBER() function assigns a unique integer to each row within a partition of the result set.

SELECT
    ProductID,
    ProductName,
    CategoryID,
    ROW_NUMBER() OVER (ORDER BY ProductID) AS RowNum
FROM
    Products;

ProductID	ProductName	CategoryID	RowNum
1	Product A	1	1
2	Product B	1	2
3	Product C	2	3
...	...	...	...

8.2.2 RANK() and DENSE_RANK()

The RANK() and DENSE_RANK() functions assign a ranking to each row based on the specified column order.

SELECT
    EmployeeID,
    FirstName,
    LastName,
    Salary,
    RANK() OVER (ORDER BY Salary DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM
    Employees;

EmployeeID	FirstName	LastName	Salary	Rank	DenseRank
1	John	Doe	60000	1	1
2	Jane	Smith	55000	2	2
3	Bob	Johnson	55000	2	2
...	...	...	...	...	...

8.2.3 LAG() and LEAD()

The LAG() and LEAD() functions allow you to access data from previous and subsequent rows in the result set.

SELECT
    OrderID,
    OrderDate,
    TotalAmount,
    LAG(TotalAmount) OVER (ORDER BY OrderDate) AS PrevAmount,
    LEAD(TotalAmount) OVER (ORDER BY OrderDate) AS NextAmount
FROM
    Orders;

OrderID	OrderDate	TotalAmount	PrevAmount	NextAmount
1	2023-01-01 08:00:00	100.00	NULL	150.00
2	2023-01-02 10:30:00	150.00	100.00	120.00
3	2023-01-03 12:45:00	120.00	150.00	NULL
...	...	...	...	...

8.2.4 SUM() Over a Partition

The SUM() function can be used over a specific partition of the result set.

SELECT
    CategoryID,
    ProductName,
    Price,
    SUM(Price) OVER (PARTITION BY CategoryID) AS CategoryTotal
FROM
    Products;

CategoryID	ProductName	Price	CategoryTotal
1	Product A	50.00	150.00
1	Product B	70.00	150.00
2	Product C	80.00	80.00
...	...	...	...

These examples showcase the versatility of window functions in SQL Server. Experiment with these functions to gain a deeper understanding of their capabilities.