SQL Window function vs Group by

Sundas Khalid

Jun 17, 2020·3 min read

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 GROUP BY.

Let’s say we need to get total salary by department. We use GROUP BY statement to get this data.

SELECT 
department_id,
sum(salary) AS salary
FROM employee_table
GROUP BY 
department_idDEPARTMENT_ID TOTAL_SALARY
------------- ------------
100        51608
30        24900
21           7000
20        19000
70        10000
90        58000
110        20308
50       156400
40         6500
80       304500
10         4400
60        28800

Let's now use the window function to get total salary by department.

SELECT
DISTINCT department_id,
SUM(salary) OVER (PARTITION BY department_id) AS salary
FROM employee_tableDEPARTMENT_ID SALARY
------------- ------------
30        24900
60        28800
90        58000
10         4400
100        51608
20        19000
50       156400
40         6500
80       304500
70        10000
110        20308

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 ])

• function defines the calculation to be performed.
• OVER expressions are required.
• PARTITION BY clause subdivides the result set into partitions
• ORDER BY sorts orders within each partition.
• ROWS defines 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.

SELECT 'Day', 
'Daily Revenue',
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.

Final thoughts

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

• Website: SQLzoo & Mode
• Books: a list of book recommendations for learning SQL
• Courses: Udemy SQL Bootcamp & Coursera Introduction to SQL
• Practice: Hackerank and Leetcode SQL questions

Thanks for Reading!

If you like my work and want to support me…

1. The BEST way to support me is by following me on Medium here.
2. Follow me on Instagram here. I post lots of updates and interesting stuff here!
3. Also, be one of the FIRST to subscribe to my new YouTube channel here!
4. Follow me on LinkedIn here.
5. Check out my website, sundaskhalid.com.