Like the Aggregate function, the WINDOW Function also aggregates the values in the particular window (or, set of rows ) and it returns the aggregated values for each row. These functions use the OVER () clause to define the window mentioned in the query. The OVER clause partitions the rows to form a set of rows whereas it also defines the order of rows within the partition.
- Syntax of WINDOW Function –
SELECT columnA WINDOW FUNCTION (columnB) OVER([PARTITION BY columnA] [ORDER BY columnC]) AS new_column FROM table_name;
- NOTE –
- In the above syntax, the WINDOW FUNCTION can be any aggregate or ranking function.
- “columnA” represents which column is to be selected.
- “columnB” with the window function represents which column is associated with the function.
- “columnC” represents the column on basis of which the partition will be done.
AGGREGATE WINDOW Function
As discussed earlier, the aggregate functions such as SUM(), COUNT(), AVERAGE(), etc. are applied over a set of rows then these are known as the AGGREGATE WINDOW Function.
DEMO TABLE –
Product Table
| Category | Region | City | Country | Sales | Customer_name |
| Office Supplies | Central | Chicago | United States | 15.696 | Suzanne McNair |
| Technology | East | New York City | United States | 539.97 | Amy Hunt |
| Office Supplies | East | New York City | United States | 22.58 | Amy Hunt |
| Office Supplies | East | Dover | United States | 16.52 | Emily Phan |
| Office Supplies | East | Dover | United States | 671.94 | Emily Phan |
| Technology | West | Aurora | United States | 169.064 | Tracy Poddar |
| Office Supplies | West | Aurora | United States | 168.624 | Tracy Poddar |
| Office Supplies | East | Long Beach | United States | 19.44 | Anthony Rawles |
| Office Supplies | West | Pasadena | United States | 31.92 | Helen Andreada |
| Furniture | West | Pasadena | United States | 433.568 | Helen Andreada |
Example – Consider the above Product table of the Customer database and evaluate the Sum of Sales for each Category.
SELECT Category,Region,City,[Country/Region],[Customer Name], SUM(Sales) OVER (PARTITION BY Category ) AS Total_sales FROM [dbo].[Products];
The output after executing the above statement is as shown –
| Category | Region | City | Country | Customer_ID | Customer_name | Total_sales |
| Office Supplies | Central | Chicago | United States | SM-20950 | Suzanne McNair | 946.72 |
| Office Supplies | East | New York City | United States | AH-10465 | Amy Hunt | 946.72 |
| Office Supplies | East | Dover | United States | EP-13915 | Emily Phan | 946.72 |
| Office Supplies | East | Dover | United States | EP-13915 | Emily Phan | 946.72 |
| Office Supplies | West | Aurora | United States | TP-21565 | Tracy Poddar | 946.72 |
| Office Supplies | East | Long Beach | United States | AR-10825 | Anthony Rawles | 946.72 |
| Office Supplies | West | Pasadena | United States | HA-14920 | Helen Andreada | 946.72 |
| Technology | East | New York City | United States | AH-10465 | Amy Hunt | 709.034 |
| Technology | West | Aurora | United States | TP-21565 | Tracy Poddar | 709.034 |
| Furniture | West | Pasadena | United States | HA-14920 | Helen Andreada | 433.568 |
As shown in the above query, the sum of sales within each category is displayed in the column Total_sales.
RANKING WINDOW Function
The Ranking Window function also aggregates the set of values or a particular window and returns the aggregated value for each row but unlike the aggregated function, it returns the rank for each row in the result set based on the specified values in those rows.
Below are some of the Ranking Window functions –
1. RANK () – The RANK() function assigns a unique rank for each row in the result set. If two records have the same value then it will assign the same rank by skipping the next rank in the sequence.
2. ROW_NUMBER () – It assigns a unique sequential rank to each row in the result set. The row_number will be reset with each partition defined by the PARTITION clause.
3. DENSE_RANK () – It assigns a unique rank to each row in the result set. It is identical to the RANK() but does not skip any rank. If two records have the same value then it will assign the same rank. The next ranking sequence number will be assigned to the following rows.
Other SQL topics to check out:
- What is RDBMS (Relational Database Management System)?
- SQL SELECT Statement
- SQL INSERT INTO SELECT Statement
- SQL ALL Operator
- SQL WHERE Clause
- SQL AND Operator
- SQL OR Operator
Follow us on Twitter, Facebook, Linkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.
If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.
