Welcome To TechBrothersIT: Snowflake SQL Tutorial 54 - Window Frames in Snowflake - Measure the Frame WITH the Frame

Snowflake SQL · Episode 54

In this post we are going to cover window frames in Snowflake.

A frame is the set of rows a window function is allowed to look at for the row it is currently on. It is not the whole partition and it is not the result. It is a window onto the partition, and it moves as the function walks down the rows.

Rather than draw that, we are going to measure it. Every query below puts COUNT(*), MIN() and MAX() over the same frame as the function we are watching, so the frame’s size and both of its ends arrive as columns we can read. Then we will grow a frame, slide it, run it backwards, empty it, and confine it to a partition.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 54 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's create some sample data
Let's write the frame out on a running total
Now let's measure the frame with the frame
Now let's make the frame stop growing
Now let's run the frame the other way
Can a frame be empty?
Can we run a frame backwards?
Now let's add a PARTITION BY
Let's finish with cumulative revenue by month

Let's create some sample data

Frames have been quietly present in this level since OVER() in Episode 46. This is the post that says what one is, and then watches it move.

Most of what follows runs against the shared EMPLOYEES table, ordered by EMP_ID. At the end we move to ORDERS for a cumulative revenue column. Paste the script below into a worksheet if you have not already set it up.

Sample data

-- Creates the EMPLOYEES table used by every episode in this series.
-- Part of the shared sample-data script; run it once.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE EMPLOYEES (
    EMP_ID          INT,
    FIRST_NAME      VARCHAR,
    LAST_NAME       VARCHAR,
    EMAIL           VARCHAR,
    DEPT_ID         INT,
    MANAGER_ID      INT,
    SALARY          NUMBER(10,2),
    COMMISSION_PCT  NUMBER(4,2),
    HIRE_DATE       DATE
);

INSERT INTO EMPLOYEES VALUES
    (1, 'Aisha',  'Khan',      'aisha.khan@example.com',      10,    6, 118000.00, NULL,  '2019-03-11'),
    (2, 'Marcus', 'Reid',      'marcus.reid@example.com',     10,    6,  96500.00, NULL,  '2021-07-01'),
    (3, 'Lena',   'Fischer',   'lena.fischer@example.com',    20,    6, 104000.00, 0.05,  '2018-01-22'),
    (4, 'Tomas',  'Novak',     'tomas.novak@example.com',     20,    3,  87250.00, NULL,  '2022-09-05'),
    (5, 'Priya',  'Raman',     'priya.raman@example.com',     30,    6,  96500.00, 0.10,  '2020-11-17'),
    (6, 'Daniel', 'O''Connor', 'daniel.oconnor@example.com',  10, NULL, 131000.00, NULL,  '2016-05-30'),
    (7, 'Sofia',  'Marino',    'sofia.marino@example.com',    30,    5,  71400.00, 0.08,  '2023-02-13'),
    (8, 'Grace',  'Lin',       'grace.lin@example.com',     NULL,    6,  92000.00, NULL,  '2021-04-19');

Now let's number those rows in the order every frame on this page will walk them.

Query — the rows in EMP_ID order

SELECT ROW_NUMBER() OVER (ORDER BY EMP_ID) AS RN, EMP_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 4 columns

RN	EMP_ID	FIRST_NAME	SALARY
1	1	Aisha	118000.00
2	2	Marcus	96500.00
3	3	Lena	104000.00
4	4	Tomas	87250.00
5	5	Priya	96500.00
6	6	Daniel	131000.00
7	7	Sofia	71400.00
8	8	Grace	92000.00

Eight rows. Notice that SALARY jumps about inside that order. The ordering column is EMP_ID, not the salary, and that makes it easy to tell a moving frame from a sorted one.

Let's write the frame out on a running total

A frame is the set of rows the function is allowed to look at, for the row it is currently on. It is not the partition and it is not the result.

Here is the running total from Episode 46, with its frame written out in full rather than left to the default.

Query

SELECT EMP_ID, FIRST_NAME, SALARY,
       SUM(SALARY) OVER (ORDER BY EMP_ID
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUNNING
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 4 columns

EMP_ID	FIRST_NAME	SALARY	RUNNING
1	Aisha	118000.00	118000.00
2	Marcus	96500.00	214500.00
3	Lena	104000.00	318500.00
4	Tomas	87250.00	405750.00
5	Priya	96500.00	502250.00
6	Daniel	131000.00	633250.00
7	Sofia	71400.00	704650.00
8	Grace	92000.00	796650.00

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the whole frame clause, and it has two halves. A start bound and an end bound.

UNBOUNDED PRECEDING means the first row of the partition. CURRENT ROW means this row. So on each row the frame reaches from the top of the partition down to wherever the function has got to, which is exactly what a running total is.

The five bounds

Those two are part of a short vocabulary, and every frame in this post is built out of it.

Bound	Where it sits
`UNBOUNDED PRECEDING`	The first row of the partition. Only valid as a start bound.
`n PRECEDING`	n rows back from the current row
`CURRENT ROW`	The row being computed
`n FOLLOWING`	n rows forward from the current row
`UNBOUNDED FOLLOWING`	The last row of the partition. Only valid as an end bound.

ROWS, deliberately

Every frame on this page is written with the keyword ROWS. There is a second keyword, RANGE, which takes the same bound words and can give a different answer. It is also what Snowflake uses when you write ORDER BY inside OVER and no frame at all. That is Episode 55, and it deserves its own page.

Now let's measure the frame with the frame

Here is the trick the rest of this post is built on, and it is the thing that makes frames stop being abstract.

Nothing says a frame may only have one function over it. So let's put the running total over the frame, and then COUNT(*), MIN(EMP_ID) and MAX(EMP_ID) over the same frame beside it. The frame’s size and both of its ends become columns in the grid.

Query

SELECT EMP_ID, FIRST_NAME, SALARY,
       SUM(SALARY)   OVER (ORDER BY EMP_ID
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUNNING,
       COUNT(*)      OVER (ORDER BY EMP_ID
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ROWS_IN_FRAME,
       MIN(EMP_ID)   OVER (ORDER BY EMP_ID
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FRAME_FROM,
       MAX(EMP_ID)   OVER (ORDER BY EMP_ID
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FRAME_TO
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 7 columns · scroll →

EMP_ID	FIRST_NAME	SALARY	RUNNING	ROWS_IN_FRAME	FRAME_FROM	FRAME_TO
1	Aisha	118000.00	118000.00	1	1	1
2	Marcus	96500.00	214500.00	2	1	2
3	Lena	104000.00	318500.00	3	1	3
4	Tomas	87250.00	405750.00	4	1	4
5	Priya	96500.00	502250.00	5	1	5
6	Daniel	131000.00	633250.00	6	1	6
7	Sofia	71400.00	704650.00	7	1	7
8	Grace	92000.00	796650.00	8	1	8

Read ROWS_IN_FRAME straight down. It climbs by one on every row, from one up to eight.

FRAME_FROM never moves off 1, because UNBOUNDED PRECEDING is pinned to the top of the partition. FRAME_TO follows the row down the table, because CURRENT ROW is pinned to the row.

That is a frame growing, and we did not have to take anyone’s word for it. One end is anchored, the other end is moving, and the size is the distance between them.

Why MIN and MAX of EMP_ID

MIN() and MAX() of the ordering column are what make this readable. They are the smallest and largest value the frame can see, and because the rows are walked in that order, those are its first and last row. Point them at any other column and you get an aggregate, not a frame boundary.

Now let's make the frame stop growing

Let's change the start bound from UNBOUNDED PRECEDING to 2 PRECEDING. The frame becomes this row plus the two rows above it, which is a trailing three-row window.

Query

SELECT EMP_ID, FIRST_NAME, SALARY,
       SUM(SALARY) OVER (ORDER BY EMP_ID ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS SUM_3,
       COUNT(*)    OVER (ORDER BY EMP_ID ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ROWS_IN_FRAME,
       MIN(EMP_ID) OVER (ORDER BY EMP_ID ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS FRAME_FROM,
       MAX(EMP_ID) OVER (ORDER BY EMP_ID ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS FRAME_TO
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 7 columns · scroll →

EMP_ID	FIRST_NAME	SALARY	SUM_3	ROWS_IN_FRAME	FRAME_FROM	FRAME_TO
1	Aisha	118000.00	118000.00	1	1	1
2	Marcus	96500.00	214500.00	2	1	2
3	Lena	104000.00	318500.00	3	1	3
4	Tomas	87250.00	287750.00	3	2	4
5	Priya	96500.00	287750.00	3	3	5
6	Daniel	131000.00	314750.00	3	4	6
7	Sofia	71400.00	298900.00	3	5	7
8	Grace	92000.00	294400.00	3	6	8

ROWS_IN_FRAME climbs to three and then stays there. FRAME_FROM is the column to watch. It sticks at 1 while the frame is still filling up, then starts climbing one row at a time.

Both ends are moving now, a fixed distance apart. That is a frame sliding rather than growing.

The first two rows are the exception, and they are worth saying out loud. There are not two rows above the first row, and there is only one above the second, so those two frames are short. A sliding window always starts short, and any average or sum computed over it is over fewer rows than we asked for.

Can a frame look forward as well as back?

It can. Nothing says the end bound has to be CURRENT ROW. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING is this row, the row above and the row below, which gives us a centred average.

Query

SELECT EMP_ID, FIRST_NAME, SALARY,
       AVG(SALARY) OVER (ORDER BY EMP_ID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS AVG_3,
       COUNT(*)    OVER (ORDER BY EMP_ID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS ROWS_IN_FRAME,
       MIN(EMP_ID) OVER (ORDER BY EMP_ID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS FRAME_FROM,
       MAX(EMP_ID) OVER (ORDER BY EMP_ID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS FRAME_TO
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 7 columns · scroll →

EMP_ID	FIRST_NAME	SALARY	AVG_3	ROWS_IN_FRAME	FRAME_FROM	FRAME_TO
1	Aisha	118000.00	107250.00000	2	1	2
2	Marcus	96500.00	106166.66666	3	1	3
3	Lena	104000.00	95916.66666	3	2	4
4	Tomas	87250.00	95916.66666	3	3	5
5	Priya	96500.00	104916.66666	3	4	6
6	Daniel	131000.00	99633.33333	3	5	7
7	Sofia	71400.00	98133.33333	3	6	8
8	Grace	92000.00	81700.00000	2	7	8

Look at the two ends of ROWS_IN_FRAME. The first row has nothing above it and the last row has nothing below it, so those two frames hold two rows rather than three. Their averages are over two values, not three. Every other row holds three.

The edges of a moving average

AVG() divides by the number of rows the frame actually contains, not by the number the frame clause asked for. That is the correct behaviour, and it is also the reason the first and last points of a centred moving average look wrong on a chart. Episode 56 does something about it.

Now let's run the frame the other way

Let's swap the two bounds over. CURRENT ROW as the start, UNBOUNDED FOLLOWING as the end. That gives us this row plus everything after it, which is a remaining total instead of a running one.

Query

SELECT EMP_ID, FIRST_NAME, SALARY,
       SUM(SALARY) OVER (ORDER BY EMP_ID
                         ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS REMAINING,
       SUM(SALARY) OVER (ORDER BY EMP_ID
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUNNING,
       SUM(SALARY) OVER () AS TOTAL
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 6 columns · scroll →

EMP_ID	FIRST_NAME	SALARY	REMAINING	RUNNING	TOTAL
1	Aisha	118000.00	796650.00	118000.00	796650.00
2	Marcus	96500.00	678650.00	214500.00	796650.00
3	Lena	104000.00	582150.00	318500.00	796650.00
4	Tomas	87250.00	478150.00	405750.00	796650.00
5	Priya	96500.00	390900.00	502250.00	796650.00
6	Daniel	131000.00	294400.00	633250.00	796650.00
7	Sofia	71400.00	163400.00	704650.00	796650.00
8	Grace	92000.00	92000.00	796650.00	796650.00

The first row’s REMAINING equals TOTAL, because the first row’s frame is the whole table. By the last row it is down to that row’s own salary, because nothing follows Grace.

RUNNING climbs while REMAINING falls, and the two cross somewhere in the middle of the grid.

An identity worth checking

Both of those frames contain the current row. So the current row’s salary is counted twice when we add the two columns together.

That gives us an arithmetic claim precise enough to test. RUNNING + REMAINING - SALARY should equal TOTAL on every row.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(RUNNING + REMAINING - SALARY = TOTAL) AS ROWS_WHERE_IT_HOLDS,
       COUNT_IF(RUNNING + REMAINING - SALARY <> TOTAL) AS ROWS_WHERE_IT_FAILS
FROM (
  SELECT SALARY,
         SUM(SALARY) OVER (ORDER BY EMP_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUNNING,
         SUM(SALARY) OVER (ORDER BY EMP_ID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS REMAINING,
         SUM(SALARY) OVER () AS TOTAL
  FROM EMPLOYEES);

Result1 row · 3 columns

ROWS_TOTAL	ROWS_WHERE_IT_HOLDS	ROWS_WHERE_IT_FAILS
8	8	0

COUNT_IF counts the rows where a predicate is true, so this is the claim tested on every row at once. It holds on all eight and fails on none. The frames are doing exactly what their clauses say.

Can a frame be empty?

It can, and it is worth seeing once. ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING is everything before this row, excluding this row.

Query

SELECT EMP_ID, FIRST_NAME, SALARY,
       SUM(SALARY) OVER (ORDER BY EMP_ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS SUM_BEFORE,
       COUNT(*)    OVER (ORDER BY EMP_ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS ROWS_BEFORE
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 5 columns

EMP_ID	FIRST_NAME	SALARY	SUM_BEFORE	ROWS_BEFORE
1	Aisha	118000.00	NULL	0
2	Marcus	96500.00	118000.00	1
3	Lena	104000.00	214500.00	2
4	Tomas	87250.00	318500.00	3
5	Priya	96500.00	405750.00	4
6	Daniel	131000.00	502250.00	5
7	Sofia	71400.00	633250.00	6
8	Grace	92000.00	704650.00	7

Nothing comes before the first row, so its frame is empty, and that is not an error. It is a frame with no rows in it, which the two functions report differently. SUM_BEFORE is NULL and ROWS_BEFORE is 0.

Same rule as always

SUM of nothing is NULL. COUNT of nothing is 0. That is exactly the rule for an ordinary aggregate over no rows, and frames do not invent a new one. If something downstream cannot cope with that NULL, COALESCE is the answer, not a different frame.

This frame shape is more useful than it looks. Everything before me is how we compare a row against its own history without letting the row itself into the comparison.

Can we run a frame backwards?

We cannot, and this is the one you will meet by accident. A frame has to run forwards. The start bound must come before the end bound.

ROWS BETWEEN CURRENT ROW AND 2 PRECEDING asks for a frame that ends behind where it starts. Let's run it and see what Snowflake says.

Query — this one fails

SELECT EMP_ID, SUM(SALARY) OVER (ORDER BY EMP_ID
                                 ROWS BETWEEN CURRENT ROW AND 2 PRECEDING) AS X
FROM EMPLOYEES;

Query failed — Snowflake returned an error

002309 (42601): SQL compilation error: error line 2 at position 46
Invalid window frame

It is a compilation error, not a wrong answer, which is the good outcome. The query never runs. If you see Invalid window frame, read it as your two bounds are the wrong way round and swap them.

Now let's add a PARTITION BY

Add PARTITION BY and the frame is confined to the partition, exactly as the rest of this level would lead us to expect. With FRAME_FROM on the page we can see the confinement rather than assume it.

Query

SELECT DEPT_ID, EMP_ID, FIRST_NAME, SALARY,
       SUM(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY EMP_ID
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUNNING_IN_DEPT,
       COUNT(*)    OVER (PARTITION BY DEPT_ID ORDER BY EMP_ID
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ROWS_IN_FRAME,
       MIN(EMP_ID) OVER (PARTITION BY DEPT_ID ORDER BY EMP_ID
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FRAME_FROM
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, EMP_ID;

Result8 rows · 7 columns · scroll →

DEPT_ID	EMP_ID	FIRST_NAME	SALARY	RUNNING_IN_DEPT	ROWS_IN_FRAME	FRAME_FROM
10	1	Aisha	118000.00	118000.00	1	1
10	2	Marcus	96500.00	214500.00	2	1
10	6	Daniel	131000.00	345500.00	3	1
20	3	Lena	104000.00	104000.00	1	3
20	4	Tomas	87250.00	191250.00	2	3
30	5	Priya	96500.00	96500.00	1	5
30	7	Sofia	71400.00	167900.00	2	5
NULL	8	Grace	92000.00	92000.00	1	8

FRAME_FROM is no longer 1 all the way down. It is the first EMP_ID of each department: 1 through department 10, then 3 when department 20 starts, then 5 for department 30, then 8 for the row whose DEPT_ID is NULL.

ROWS_IN_FRAME restarts at one in each of those four groups.

UNBOUNDED PRECEDING means the start of the partition, not the start of the table. A frame cannot reach out of its own partition, whatever bounds we give it.

Let's finish with cumulative revenue by month

Employee salaries make the frame easy to watch. Here is the shape you will actually write. We aggregate ORDERS to one row per month, then run a frame over those monthly rows.

Sample data

-- Creates the ORDERS table the cumulative-revenue query on this page uses.
-- Part of the shared sample-data script; run it once.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE ORDERS (
    ORDER_ID     INT,
    CUSTOMER_ID  INT,
    ORDER_DATE   DATE,
    SHIP_DATE    DATE,
    STATUS       VARCHAR,
    AMOUNT       NUMBER(10,2),
    QUANTITY     INT
);

INSERT INTO ORDERS VALUES
    (1001, 101, '2023-01-12', '2023-01-15', 'SHIPPED',   4200.00,  12),
    (1002, 102, '2023-01-28', '2023-02-02', 'SHIPPED',    890.50,   3),
    (1003, 101, '2023-02-14', '2023-02-16', 'SHIPPED',   6150.00,  20),
    (1004, 103, '2023-02-27', NULL,         'CANCELLED', 1500.00,   5),
    (1005, 104, '2023-03-09', '2023-03-14', 'SHIPPED',   2310.75,   8),
    (1006, 101, '2023-03-22', '2023-03-24', 'SHIPPED',   5020.00,  16),
    (1007, 105, '2023-04-05', '2023-04-11', 'SHIPPED',   7890.00,  25),
    (1008, 102, '2023-04-19', '2023-04-23', 'SHIPPED',   1120.00,   4),
    (1009, 103, '2023-05-02', '2023-05-06', 'SHIPPED',   3340.25,  11),
    (1010, 106, '2023-05-21', NULL,         'PENDING',    640.00,   2),
    (1011, 101, '2023-06-08', '2023-06-10', 'SHIPPED',   8100.00,  27),
    (1012, 104, '2023-06-25', '2023-06-30', 'SHIPPED',   1975.50,   7),
    (1013, 105, '2023-07-11', '2023-07-15', 'SHIPPED',   6420.00,  21),
    (1014, 102, '2023-07-29', '2023-08-03', 'RETURNED',   750.00,   3),
    (1015, 107, '2023-08-14', '2023-08-17', 'SHIPPED',   9250.00,  30),
    (1016, 103, '2023-08-30', '2023-09-04', 'SHIPPED',   2860.00,   9),
    (1017, 101, '2023-09-16', '2023-09-19', 'SHIPPED',   5540.00,  18),
    (1018, 106, '2023-10-03', '2023-10-08', 'SHIPPED',   1430.00,   5),
    (1019, 105, '2023-10-22', '2023-10-26', 'SHIPPED',   7015.00,  23),
    (1020, 107, '2023-11-07', '2023-11-10', 'SHIPPED',  10400.00,  34),
    (1021, 102, '2023-11-24', '2023-11-29', 'SHIPPED',    980.00,   4),
    (1022, 104, '2023-12-09', NULL,         'CANCELLED', 2250.00,   8),
    (1023, 101, '2023-12-27', '2023-12-30', 'SHIPPED',   6730.00,  22),
    (1024, 103, '2024-01-15', '2024-01-18', 'SHIPPED',   3910.00,  13),
    (1025, 107, '2024-01-31', '2024-02-05', 'SHIPPED',  11200.00,  36),
    (1026, 105, '2024-02-17', '2024-02-21', 'SHIPPED',   8340.00,  27),
    (1027, 106, '2024-03-04', '2024-03-09', 'SHIPPED',   1690.00,   6),
    (1028, 102, '2024-03-21', '2024-03-25', 'SHIPPED',   1240.00,   5),
    (1029, 101, '2024-04-06', '2024-04-09', 'SHIPPED',   7460.00,  24),
    (1030, 109, '2024-04-23', '2024-04-28', 'SHIPPED',   5180.00,  17),
    (1031, 103, '2024-05-10', '2024-05-14', 'SHIPPED',   4025.00,  14),
    (1032, 107, '2024-05-28', '2024-06-01', 'SHIPPED',  12850.00,  41),
    (1033, 105, '2024-06-13', NULL,         'PENDING',   6900.00,  22),
    (1034, 109, '2024-06-30', '2024-07-04', 'SHIPPED',   4760.00,  16),
    (1035, 101, '2024-07-18', '2024-07-21', 'SHIPPED',   8820.00,  29),
    (1036, 104, '2024-08-02', '2024-08-07', 'SHIPPED',   2540.00,   9),
    (1037, 107, '2024-08-25', '2024-08-29', 'SHIPPED',  13100.00,  43),
    (1038, 109, '2024-09-11', '2024-09-15', 'SHIPPED',   5930.00,  19),
    (1039, 102, '2024-09-29', '2024-10-03', 'RETURNED',  1080.00,   4),
    (1040, 101, '2024-10-16', '2024-10-19', 'SHIPPED',   9640.00,  31);

-- One deliberate NULL amount, inserted separately so it is easy to see why it
-- is there. It makes COUNT(*), COUNT(AMOUNT) and SUM() give different answers.
INSERT INTO ORDERS VALUES
    (1041, 106, '2024-11-05', NULL, 'PENDING', NULL, 3);

Query

WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
)
SELECT MONTH_START, TOTAL,
       SUM(TOTAL) OVER (ORDER BY MONTH_START
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMULATIVE
FROM M
ORDER BY MONTH_START;

Result23 rows · 3 columns

MONTH_START	TOTAL	CUMULATIVE
2023-01-01	5090.50	5090.50
2023-02-01	7650.00	12740.50
2023-03-01	7330.75	20071.25
2023-04-01	9010.00	29081.25
2023-05-01	3980.25	33061.50
2023-06-01	10075.50	43137.00
2023-07-01	7170.00	50307.00
2023-08-01	12110.00	62417.00
2023-09-01	5540.00	67957.00
2023-10-01	8445.00	76402.00
2023-11-01	11380.00	87782.00
2023-12-01	8980.00	96762.00
2024-01-01	15110.00	111872.00
2024-02-01	8340.00	120212.00
2024-03-01	2930.00	123142.00
2024-04-01	12640.00	135782.00
2024-05-01	16875.00	152657.00
2024-06-01	11660.00	164317.00
2024-07-01	8820.00	173137.00
2024-08-01	15640.00	188777.00
2024-09-01	7010.00	195787.00
2024-10-01	9640.00	205427.00
2024-11-01	NULL	205427.00

23 monthly rows, each carrying its own total and the cumulative total up to and including it. The frame is the same growing frame we used for the running total. The only thing that changed is what the rows are.

The last row is the interesting one. Its own TOTAL is NULL, because the one order in that month has a NULL AMOUNT.

Its CUMULATIVE is not NULL at all. It is the same figure as the month before it, because SUM skips NULLs inside a frame exactly as it does everywhere else.

And that last cumulative figure should be every order in the table. That is a claim worth checking rather than asserting.

Query — the check

SELECT COUNT(*) AS N_ORDERS, SUM(AMOUNT) AS TOTAL FROM ORDERS;

Result1 row · 2 columns

N_ORDERS	TOTAL
41	205427.00

41 orders, and a total that matches the final CUMULATIVE figure in the monthly grid.

Key points

A frame is the set of rows the function may look at for the row it is on. It moves as the function walks the partition.
Put COUNT(*), MIN() and MAX() of the ordering column over the same frame and the frame's size and both its ends become columns you can read.
UNBOUNDED PRECEDING is the first row of the partition and UNBOUNDED FOLLOWING is the last. n PRECEDING and n FOLLOWING count rows from where you are.
UNBOUNDED PRECEDING AND CURRENT ROW grows; 2 PRECEDING AND CURRENT ROW slides; 1 PRECEDING AND 1 FOLLOWING slides and looks both ways.
A sliding frame is short at the edges, and AVG() divides by the rows the frame actually holds, not by the number you asked for.
CURRENT ROW AND UNBOUNDED FOLLOWING gives a remaining total. The current row is in both that frame and the running one, which is why RUNNING + REMAINING - SALARY = TOTAL.
An empty frame is legal. SUM over it returns NULL and COUNT returns 0, the same as any aggregate over no rows.
The start bound must come before the end bound, or the query fails to compile with Invalid window frame.
A frame never reaches out of its own partition.

Next in this series

Episode 55 — ROWS vs RANGE. The same frame words, two different answers, and the default you get when you write ORDER BY inside OVER and no frame at all is probably not the one you assumed.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including the error. The EMPLOYEES and ORDERS data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. Frame behaviour follows the Snowflake window functions documentation and the window frame syntax reference.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 54 - Window Frames in Snowflake - Measure the Frame WITH the Frame - and Watch It Move

Let's create some sample data

Let's write the frame out on a running total

The five bounds

Now let's measure the frame with the frame

Now let's make the frame stop growing

Can a frame look forward as well as back?

Now let's run the frame the other way

An identity worth checking

Can a frame be empty?

Can we run a frame backwards?

Now let's add a PARTITION BY

Let's finish with cumulative revenue by month

Key points

No comments:

Post a Comment