A simplified explanation of SQL Window function with visuals
A window function performs an operation across a set of rows in a table, also known as partitions (subgroups). It’s called window precisely because the set of rows is called window.
The above is textbook definition. Let me take a stab and explain it in group by terms… Think of it as a recursive
GROUP BY but in one single statement with the added functionality to create multiple subsets of the data. Not following? keep reading.
In this visualization, aggregation on the left is via
GROUP BY where it combines and then hides individual rows being aggregated. Whereas, Window Function, on the right, has access to individual rows and can add attributes from those rows into the result set.
Window function is powerful
This is an another compelling visual that shows how efficient and powerful a window function is compared
GROUP BY. Unlike
GROUP BY, Window functions can calculate running totals and moving averages via ranking and value functionality.
Window function vs GROUP BY Syntax
SQL Window function is different from a
Let’s say we need to get total salary by department. We use
GROUP BY statement to get this data.
sum(salary) AS salary
Let's now use the window function to get total salary by department.
SUM(salary) OVER (PARTITION BY department_id) AS salary
FROM employee_tableDEPARTMENT_ID SALARY
The above example shows how syntax varies between
GROUP BY and window function. Let’s deep-dive into the syntax elements of a window function. In a Window function, you have a function (e.g. COUNT, SUM, etc), PARTITION BY, ORDER BY and ROWS clauses. ORDER BY and ROWS elements are optional.
function (expression) OVER
( [ PARTITION BY expression_list ]
[ ORDER BY order_list ]
[ ROWS frame_clause ])
functiondefines the calculation to be performed.
OVERexpressions are required.
PARTITION BYclause subdivides the result set into partitions
ORDER BYsorts orders within each partition.
ROWSdefines the physical offset from current row.
This is another example using window function, which outputs day, daily revenue, and 3 day average, especially
ROWS PRECEDING part.
AVG('Daily Revenue') OVER(ORDER BY 'Day' ROWS 2 PRECEDING) AS '3 Day Average'
FROM 'Running Average Example';
Window Function Types
GROUP BY functionality only offers aggregate functions; whereas Window functions offer aggregate, ranking, and value functionalities.
SQL Window function is efficient and powerful. It not only offers
GROUP BY aggregate functionality but advanced analytics with ranking and value options. Window function is considered advanced SQL, so if you choose to learn , you are leveling up in the world of SQL. Here are some resources to get started.
Learning Resources for learning Window Function
Thanks for Reading!
If you like my work and want to support me…