Sitemap
Learning SQL

Focused on helping you learn SQL

SQL Window Functions Visualized

6 min readJun 5, 2022

--

Press enter or click to view image in full size
The “demon” window function (credit here)

This is my first blog post, please do help by commenting your thoughts.

Recently I’ve been learning SQL in the last month, trying to hone in my skills to better myself in my data science journey.

And I came across the dreaded “SQL Window” or <Aggregate> OVER (PARTITION ... SORT BY ...) . A blog I read describe SQL’s window function as “A powerful tool that you love to hate”, showing how confusing it could be at time. Most of the tutorials though failed to visualize exactly how the window function work step by step and give the readers a feel to how the “window” is generated and moved around for each row. The data school’s tutorial does have some nice GIFs which inspired me to do a more complete visualization on the topic.

In summary, this post aims to give SQL users a visual sense to how exactly window functions’ window is constructed, moved and calculated

DATA DESCRIPTION

We’ll use an example table of work done per day for our illustrative purposes

  • Day: day of work
  • Start: work start time
  • Duration: hour of work done
Our data ❤

Quick review of window function

  1. Unlike normal aggregation functions with grouping, which only return one row per group, window functions return all rows, each having their calculated value based on their defined window
  2. Window funtion use OVER keyword and its arguments to define the calculation window
  3. There are three main parts of a window function in SQL:
  • Grouping: This defines the group that each row belongs to (PARTITION BY )
  • Order: This sort values within each group and make the window expands incrementally within each group ( ORDER BY)
  • Range: This is use to further define the window size, within each group ( ROWS or RANGE )

References if needed: 1 , 2

OVER (..)

“Over turns an aggregation function into a window function”

In this first example, we define a simple SUMwindow functionOVER durationcolumn with no arguments. Meaning every row’s window is the same and it is the whole table.

SELECT
day,
duration,
SUM(duration) OVER() AS total_duration
FROM
work;

Result for each row is basically sum of all rows

Result of sum for window over all rows

OVER (PARTITION BY ..)

Now, let’s spice it up with some grouping (or maybe I should use ‘partition’ to avoid confusion, but who cares, you get my point!)

The query below SUM duration of rows PARTITION by their start time. Do note that rows are re-ordered based on their grouping when you use PARTITION

SELECT
day,
start,
duration,
SUM(duration) OVER(PARTITION BY start) AS sum_duration_by_start
FROM
work;

So here, each row’s calculation window is exactly the size of their group (based on start column)

Window function with groups

It can be seen that all rows in the same group have the whole group as their window!

OVER (ORDER BY ..)

Having introduced PARTITION BY in the previous example, let us now look at ORDER BY

Get Trần Hoàng Long’s stories in your inbox

Join Medium for free to get updates from this writer.

Basically, from my intuition, there’s two things about ordering in window that you need to remember:

  1. Of course, ORDER BY will order rows within their respective group (we’ll see how ordering works with grouping in the next example)
  2. ORDER BY will also force the window to expand gradually (one row at a time) within each group.

In the query, we define no grouping (means the whole table is group for all rows) and the SUM expands in ORDER BY day.

SELECT
day,
duration,
SUM(duration) OVER(ORDER BY day) AS sum_duration_order_by_day
FROM
work;

In simple terms, we get a running sum calculation that just keep adding the current row to the result.

Running sumation using order by

OVER (PARTITION BY .. SORT BY ..)

Here comes the interesting part.

Try and visualize what the below query would do when I:

  • calculate windowed SUM
  • PARTITION BY start time
  • ORDER BY day
SELECT
day,
start,
duration,
SUM(duration) OVER(PARTITION BY start ORDER BY day)
AS sum_duration_sort_by_day
FROM
work;

Here’s the result:

Duration group by start with order by!

As we can see, the windows resets back to size 1 at the start of each group and incrementally expand its size by one for every next row in the group.

Some other different window sizes

I separate ROWS and RANGE into another section since they seems to be less popular and a bit more complex.

Moving aggregation with ROWS

In previous section, we’ve seen how window is defined based on grouping. But here (with or without grouping), ROWS and RANGE allows us to have a more fine grained control over window size.

Say we want to see how many work hours we’ve done for the last two days! This can be done by defining sum over a moving window of size 2.

SELECT
day,
duration,
SUM(duration) OVER(ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS moving_sum
FROM
work;

Result is a moving (or rolling) summation with window size 2.

Moving sum with window size 2

Crazy example with all PARTITION, ORDER, ROWS

Finally, why dont we put all of these three concept together and see how they ‘interact’?

Our query (a quite meaningless one), defines a windowed summation over (in this exact order):

  • First: group by start time
  • Second: order by day of work (in each group)
  • Third: Window size 2 including current row and one previous row (in the same group)
SELECT
day,
start,
duration,
SUM(duration) OVER(PARTITION BY start ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS crazy
FROM
work;

Pay very close attention to the tiny difference between this example and the example from section “OVER (PARTITION BY .. SORT BY ..)”

crazy example

Do notice how the grouping and the ordering still follows rules of PARTITION BY and ORDER BY like we’ve encounter in previous examples.

Though, how the window is constructed within each group is not incrementally expanding anymore but instead, it follows the defined constraint of ROWS , which specify that the window is of size two, including current row and one previous row (if any in the same group)

This can be seen at day 7 (row 4) where its window is of size 2 despite it being the last row in a group of size 3.

Conclusion

In this blog, I hope i’ve help you to have a better grasp of how window function works in SQL through the use of nice GIFs 😸

Thanks for reading ❤.

Go from SELECT * to interview-worthy project. Get our free 5-page guide.