SQL Window function vs Group by

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.

Image for post
Image for post

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.

Image for post
Image for post

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_id
DEPARTMENT_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_table
DEPARTMENT_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.

Image for post
Image for post

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

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.

Written by

I write about data science, diversity & lifestyle | currently at Google | more learning content at sundaskhalid.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store