Tables: Window Functions vs Group By#

Hello, everyone! This week, I want to dive into “window” and “group by.” What’s the difference? When should you use one over the other? Let’s take a look.

Theoretical Comparison#

Both window and group by functions are used to perform operations across a subset of rows of a table. These rows are subsetted based on a unique grouping of values within a column.

For example, the following table has 3 columns: 'sensor', 'date', and 'temperature'. These represent temperatures collected by sensors (presumably in different locations) across multiple days.

import pandas as pd
import pyarrow as pa
from numpy.random import default_rng
rng = default_rng(8)

df = (
    pd.MultiIndex.from_product(
        iterables=[
            ['a', 'b'],
            pd.date_range('2000-01-01', periods=10, tz='US/Pacific')
        ],
        names=['sensor', 'date']
    )
    .to_frame(index=False)
    .assign(
        temperature=lambda d: rng.normal(70, scale=8, size=len(d)).astype(int)
    )
    .sample(n=10, random_state=rng)
    .sort_values(by=['sensor', 'date'])
    .reset_index(drop=True)
    .astype({
        'date': pd.ArrowDtype(pa.date64()),
    })
)

df
sensor date temperature
0 a 2000-01-01 56
1 a 2000-01-03 59
2 a 2000-01-06 68
3 a 2000-01-07 62
4 b 2000-01-01 76
5 b 2000-01-03 76
6 b 2000-01-05 64
7 b 2000-01-07 69
8 b 2000-01-08 81
9 b 2000-01-10 67

Examining the table above, we can conclude that

  • The total DataFrame has N rows (10 rows)

  • There exist G groups of unique sensors (2 unique sensors)

N×G represents the DataFrame in its entirety, denoting N rows with G unique groups.

I’ll be using this N and G syntax to explain how these transformations mediate the shape of the resultant table.

For these conceptual sections, I’ll be using SQL via DuckDB to showcase the operations before diving into both pandas and Polars.

Grouped Aggregations#

N×G → G

Grouped aggregations take our N×G table and output a table of shape G. This means that, after our operation, we will have as many rows in the result as we do unique groups (e.g., one row per unique group).

import duckdb

duckdb.sql('''
    select
        sensor,
        avg(temperature) as avg_temp
    from df
    group by sensor
    order by sensor
''')
┌─────────┬───────────────────┐
│ sensor  │     avg_temp      │
│ varchar │      double       │
├─────────┼───────────────────┤
│ a       │             61.25 │
│ b       │ 72.16666666666667 │
└─────────┴───────────────────┘

Window Functions (Partitioned Aggregations)#

N×G → N×G|N

Window functions are designed to take a group of rows and output a result that is the same shape as its input. In this instance, let’s take the aggregated maximum temperature from each grouping and map it back to our original DataFrame. This is useful if we want to broadcast some aggregate operation from a group back to each individual row.

For example, we can calculate the temperature deviation, “How far away is each observed temperature reading from the average temperature for that group?”

One (subpar) way to organize this query is to perform a group by aggregation and join the aggregated table back to the original on the grouping column.

# Bad approach

duckdb.sql('''
    with agg as (
        select
            sensor, 
            round(avg(temperature), 2) as temp_avg
        from df
        group by sensor
    )
    
    select 
        df.*, 
        agg.temp_avg,
        round(df.temperature - agg.temp_avg, 2) as temp_deviation,
    from df
    join agg on df.sensor = agg.sensor
    order by df.sensor
''')
┌─────────┬────────────┬─────────────┬──────────┬────────────────┐
│ sensor  │    date    │ temperature │ temp_avg │ temp_deviation │
│ varchar │    date    │    int64    │  double  │     double     │
├─────────┼────────────┼─────────────┼──────────┼────────────────┤
│ a       │ 2000-01-01 │          56 │    61.25 │          -5.25 │
│ a       │ 2000-01-03 │          59 │    61.25 │          -2.25 │
│ a       │ 2000-01-06 │          68 │    61.25 │           6.75 │
│ a       │ 2000-01-07 │          62 │    61.25 │           0.75 │
│ b       │ 2000-01-01 │          76 │    72.17 │           3.83 │
│ b       │ 2000-01-03 │          76 │    72.17 │           3.83 │
│ b       │ 2000-01-05 │          64 │    72.17 │          -8.17 │
│ b       │ 2000-01-07 │          69 │    72.17 │          -3.17 │
│ b       │ 2000-01-08 │          81 │    72.17 │           8.83 │
│ b       │ 2000-01-10 │          67 │    72.17 │          -5.17 │
├─────────┴────────────┴─────────────┴──────────┴────────────────┤
│ 10 rows                                              5 columns │
└────────────────────────────────────────────────────────────────┘

While we manage to obtain the desired result, the approach is a bit clunky as it requires separating the aggregate operation and the rejoining. Instead, SQL provides a more concise syntax leading to better query optimization. For window functions, we can leave the group by clause behind, instead using an over (partition by …) clause inside of the column selection. This makes sense because the result of the window function will be the same size as all of the other columns, making them directly comparable with one another.

# Good approach

duckdb.sql('''
    select
        *,
        round(
            avg(temperature) over (partition by sensor), 2
        ) as temp_avg,
        round(temperature - temp_avg, 2) as temp_deviation,
    from df
    order by sensor, date
''')
┌─────────┬────────────┬─────────────┬──────────┬────────────────┐
│ sensor  │    date    │ temperature │ temp_avg │ temp_deviation │
│ varchar │    date    │    int64    │  double  │     double     │
├─────────┼────────────┼─────────────┼──────────┼────────────────┤
│ a       │ 2000-01-01 │          56 │    61.25 │          -5.25 │
│ a       │ 2000-01-03 │          59 │    61.25 │          -2.25 │
│ a       │ 2000-01-06 │          68 │    61.25 │           6.75 │
│ a       │ 2000-01-07 │          62 │    61.25 │           0.75 │
│ b       │ 2000-01-01 │          76 │    72.17 │           3.83 │
│ b       │ 2000-01-03 │          76 │    72.17 │           3.83 │
│ b       │ 2000-01-05 │          64 │    72.17 │          -8.17 │
│ b       │ 2000-01-07 │          69 │    72.17 │          -3.17 │
│ b       │ 2000-01-08 │          81 │    72.17 │           8.83 │
│ b       │ 2000-01-10 │          67 │    72.17 │          -5.17 │
├─────────┴────────────┴─────────────┴──────────┴────────────────┤
│ 10 rows                                              5 columns │
└────────────────────────────────────────────────────────────────┘

Window Functions (Running)#

While this is not a formal differentiation, I previously mentioned that we can supply an aggregation to our window operation. However, since we have access to all of the values within a partition (grouping), we are not limited to simple aggregate functions.

By specifying an order by clause within over (partition by order by …), we can alter the window of rows that SQL examines before calculating our specified aggregation. In this case, we can perform cumulative aggregations (or running aggregations). If we wanted to query the running maximum temperature encountered within our group, we can use the following query:

duckdb.sql('''
    select
        *,
        max(temperature) over (partition by sensor              )
            as temp_sensor_max,
            
        max(temperature) over (partition by sensor order by date) 
            as temp_sensor_max_running,
    from df
    order by sensor, date
''')
┌─────────┬────────────┬─────────────┬─────────────────┬─────────────────────────┐
│ sensor  │    date    │ temperature │ temp_sensor_max │ temp_sensor_max_running │
│ varchar │    date    │    int64    │      int64      │          int64          │
├─────────┼────────────┼─────────────┼─────────────────┼─────────────────────────┤
│ a       │ 2000-01-01 │          56 │              68 │                      56 │
│ a       │ 2000-01-03 │          59 │              68 │                      59 │
│ a       │ 2000-01-06 │          68 │              68 │                      68 │
│ a       │ 2000-01-07 │          62 │              68 │                      68 │
│ b       │ 2000-01-01 │          76 │              81 │                      76 │
│ b       │ 2000-01-03 │          76 │              81 │                      76 │
│ b       │ 2000-01-05 │          64 │              81 │                      76 │
│ b       │ 2000-01-07 │          69 │              81 │                      76 │
│ b       │ 2000-01-08 │          81 │              81 │                      81 │
│ b       │ 2000-01-10 │          67 │              81 │                      81 │
├─────────┴────────────┴─────────────┴─────────────────┴─────────────────────────┤
│ 10 rows                                                              5 columns │
└────────────────────────────────────────────────────────────────────────────────┘

Note that the passed aggregation for both selections was max(temperature). The difference here is the presence of the order by within our over clause. This part of the query was evaluated on every subset of rows starting with the first row of a partition, then the first two rows of a partition, then the first three rows, etc. until we have consumed all rows in the partition.

You might recognize this pattern as a “cumulative maximum,” and you can liken all specifications of over (… order by …) as cumulative operations.

Wrap-Up#

That’s all the time that we have today! Next week, we’ll dive into how we organize these same concepts using our Python DataFrame tools: pandas & Polars. In the meantime make sure to join our Discord to talk more about window operations!