seon99il

Database

SQL PARTITION BY Demystified: The Hidden Mechanics of Window Frames

3 min read

When performing aggregations and analysis in SQL, window functions often appear alongside GROUP BY. In particular, PARTITION BY allows you to divide data by specific columns and perform calculations per partition, and it works fundamentally differently from standard GROUP BY.

Schema: sales table

The sales table contains sales information:

-- sales Table

+--+----+----------+------+
|id|type|month     |amount|
+--+----+----------+------+
|1 |A   |2025-01-01|100   |
|2 |A   |2025-02-01|150   |
|3 |A   |2025-03-01|120   |
|4 |A   |2025-04-01|180   |
|5 |B   |2025-01-01|90    |
|6 |B   |2025-02-01|130   |
|7 |B   |2025-03-01|200   |
|8 |B   |2025-04-01|170   |
+--+----+----------+------+
  • id: unique row identifier
  • type: product type
  • month: sales month
  • amount: sales amount

PARTITION BY

PARTITION BY is used in window functions to divide data by a column so that calculations can be performed for each partition.

How it works

SELECT
    s.month,
    SUM(s.amount) OVER (PARTITION BY month) AS total_amount
FROM sales AS s;

-- Result
+----------+------------+
|month     |total_amount|
+----------+------------+
|2025-01-01|190         |
|2025-01-01|190         |
|2025-02-01|280         |
|2025-02-01|280         |
|2025-03-01|320         |
|2025-03-01|320         |
|2025-04-01|350         |
|2025-04-01|350         |
+----------+------------+
  • PARTITION BY month divides the data by month and repeats the total within each partition.
  • Important: All rows are preserved; no grouping is performed.

In contrast, GROUP BY combines rows into a single row per group, and individual row details are lost:

-- Group By Result
| month      | total_amount |
| ---------- | ------------ |
| 2025-01-01 | 190          |
| 2025-02-01 | 280          |
| 2025-03-01 | 320          |
| 2025-04-01 | 350          |

Syntax and Window Frames

Although not explicitly shown, PARTITION BY can be combined with the ROWS/RANGE window frames:

<window_function> OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    { ROWS | RANGE | GROUPS } BETWEEN <frame_start> AND <frame_end>
)
  • PARTITION BY: grouping unit
  • ORDER BY: frame ordering
  • ROWS / RANGE / GROUPS: frame type
  • frame_start / frame_end: frame boundaries

Frame options:

  • UNBOUNDED PRECEDING: from the first row of the partition
  • n PRECEDING: n rows before the current row
  • CURRENT ROW: current row
  • n FOLLOWING: n rows after the current row
  • UNBOUNDED FOLLOWING: until the last row of the partition

PARTITION BY: Window Frame

In window functions, you can define a frame to specify the range of rows over which calculations are performed. A frame can be one of ROWS, RANGE, or GROUPS, and calculations are performed according to the partition and ordering criteria.

SELECT s.month,
       SUM(s.amount) OVER (PARTITION BY month
                            RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )  AS total_amount
FROM sales AS s

This query calculates total_amount as the sum from the first row of each month frame up to the current row.

However, there is a subtle point. If you divide the sales table by month and sum from the first row up to the current row, you might expect the following result:

-- sales table
+--+----+----------+------+
|id|type|month     |amount|
+--+----+----------+------+
|1 |A   |2025-01-01|100   |
...
|5 |B   |2025-01-01|90    |
|8 |B   |2025-04-01|170   |
+--+----+----------+------+

-- Query
SELECT s.month,
       SUM(s.amount) OVER (PARTITION BY month
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_amount
FROM sales AS s

-- Result
+----------+------------+
|month     |total_amount|
+----------+------------+
|2025-01-01|100         |
|2025-01-01|190         |
+----------+------------+

PARTITION BY: ROWS, RANGE

  • ROWS calculates row by row, so 100 and 190 are computed separately even though they have the same month.
  • RANGE treats rows with the same value in the window frame as a single unit. For example, both 100 and 90 are considered as one, so total_amount = 190 for both rows.

Example: Implementing LAG

The LAG function retrieves the value of a column from a previous row:

LAG(<column>, <offset>, <default_value>)
OVER (PARTITION BY <partition_column> ORDER BY <order_column>)

You can achieve this without LAG by using a window frame:

SELECT
    id,
    type,
    month,
    amount,
    SUM(amount) OVER (
        PARTITION BY type
        ORDER BY month
        ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
    ) AS prev_amount
FROM sales;

-- Result
+--+----+----------+------+-----------+
|id|type|month     |amount|prev_amount|
+--+----+----------+------+-----------+
|1 |A   |2025-01-01|100   |null       |
|2 |A   |2025-02-01|150   |100        |
|3 |A   |2025-03-01|120   |150        |
|4 |A   |2025-04-01|180   |120        |
|5 |B   |2025-01-01|90    |null       |
|6 |B   |2025-02-01|130   |90         |
|7 |B   |2025-03-01|200   |130        |
|8 |B   |2025-04-01|170   |200        |
+--+----+----------+------+-----------+
  • The window frame is set to reference only 1 row preceding (ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING).
  • For the first row, NULL is returned because there is no previous row.
  • Calculations are independent per partition (type).

By leveraging this approach, you can customize queries to perform various row-level calculations using window frames.

Previous posts

View all posts →

Comments