Aggregate Window Functions
Aggregate window functions refer to a variety of aggregate functions including SUM(), COUNT(), AVG(), MAX(), and MIN() that are applied to a certain window (set of data).
Let us take a look at an example in order to better grasp the concept of aggregate window functions.
Let’s say we want to find the average age of our male and female customers and order both classes by the number of orders they’ve made. However, we want to see only the CustomerID, Name, Sex, NumberOfItems, and the average age of each person. Then, the query would look like this:
1SELECT CustomerID, Name, Sex, NumberOfItems,
2AVG(Age) OVER (PARTITION BY Sex ORDER BY NumberOfItems) AS AvgAge
3FROM Table1The resulting table would look something like this:
As we can see, the average age of both sexes is calculated and displayed in the column named AvgAge. Moreover, both classes of customers are ordered by the number of their orders, respectively.
