Window functions perform calculations across related rows while preserving the individual rows in the result. They can be used after joining multiple tables.
Window functions are powerful for ranking, running totals, comparisons, and advanced business analysis.
Show each customer's orders along with the customer's total order value.
SELECT
c.CustomerName,
o.OrderID,
o.OrderAmount,
SUM(o.OrderAmount) OVER (
PARTITION BY c.CustomerID
) AS CustomerTotal
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID;Used in sales ranking, customer analysis, financial reporting, performance analysis, and trend calculations.
- Window functions preserve individual rows.
- JOIN provides related data.
- PARTITION BY defines the calculation group.
- Window functions can calculate totals and rankings.
- They are useful for advanced analytics.
Can window functions be used after a JOIN?
Yes. Window functions can calculate analytical values over rows returned by a JOIN.
How is a window function different from GROUP BY?
GROUP BY collapses rows into groups, while a window function calculates across rows while keeping the individual rows.