Welcome To TechBrothersIT: Snowflake SQL Tutorial 55

Snowflake SQL · Episode 55

In this post we are going to cover ROWS and RANGE in Snowflake.

They are the two keywords a window frame can start with, and they take exactly the same bound words. ROWS counts rows. RANGE compares values. Most of the time they give us the same answer, which is why this one catches people out.

We will find the one row where they come apart, measure both frames to see why, and then look at the frame Snowflake gives us when we write ORDER BY inside OVER and no frame at all. That default is not the one most people assume.

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

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

On this page

Let's create some sample data
Let's run the same total with ROWS and with RANGE
What if the ORDER BY has no ties?
So which one do we get when we write no frame at all?
So which one should we use?
Now let's try a band of values
Let's finish with the same pair on dates

Let's create some sample data

Episode 54 wrote every frame with the keyword ROWS. The second keyword, RANGE, accepts the same bound words, and on most data it returns exactly the same numbers.

The difference only ever shows up where the ORDER BY has a tie in it. So the first job is to show that our table has one.

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');

Let's ask which salaries are shared, and by whom.

Query — which salaries are shared, and by whom

SELECT SALARY, COUNT(*) AS PEOPLE,
       LISTAGG(FIRST_NAME, ', ') WITHIN GROUP (ORDER BY FIRST_NAME) AS WHO
FROM EMPLOYEES GROUP BY SALARY HAVING COUNT(*) > 1;

Result1 row · 3 columns

SALARY	PEOPLE	WHO
96500.00	2	Marcus, Priya

One row back, so there is exactly one shared salary in EMPLOYEES, and two people hold it: Marcus and Priya.

Most of the grids on this page are ordered by SALARY, so it helps to know where those two sit in that order.

Query — the rows in salary order

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

Result8 rows · 3 columns

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

Marcus is row 4 and Priya is row 5. Keep that pair in mind. Every difference we are about to see comes from those two holding the same salary.

Let's run the same total with ROWS and with RANGE

Here is the same running total computed twice in one SELECT. Identical ORDER BY, identical bounds, identical function. The only difference between the two columns is the word ROWS against the word RANGE.

Query — ROWS and RANGE, same bounds

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

Result8 rows · 4 columns

FIRST_NAME	SALARY	BY_ROWS	BY_RANGE
Sofia	71400.00	71400.00	71400.00
Tomas	87250.00	158650.00	158650.00
Grace	92000.00	250650.00	250650.00
Marcus	96500.00	347150.00	443650.00
Priya	96500.00	443650.00	443650.00
Lena	104000.00	547650.00	547650.00
Aisha	118000.00	665650.00	665650.00
Daniel	131000.00	796650.00	796650.00

Read the two columns down together. They agree on Sofia, Tomas and Grace.

They part company on Marcus’s row, the first half of the tie, where BY_RANGE is the larger number. On Priya’s row they agree again, and from Lena down they never differ again.

Look at what BY_RANGE does to that pair. Marcus and Priya get the same value, and it is the value BY_ROWS only reaches on the second of them.

So where does the difference come from?

The sums are not where the difference lives. The frames are. So let's measure both frames the way Episode 54 did, with COUNT(*) over each of them in the same query.

Query — the size of each frame

SELECT FIRST_NAME, SALARY,
       COUNT(*) OVER (ORDER BY SALARY
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS ROWS_FRAME_SIZE,
       COUNT(*) OVER (ORDER BY SALARY
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RANGE_FRAME_SIZE
FROM EMPLOYEES
ORDER BY SALARY, FIRST_NAME;

Result8 rows · 4 columns

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

Row by row the two sizes match, until Marcus’s row. There ROWS_FRAME_SIZE is 4 and RANGE_FRAME_SIZE is 5. That single pair of cells explains every other difference on this page.

ROWS counts rows. CURRENT ROW means stop at this row, and Marcus is the fourth row, so his frame holds four.
RANGE counts values. CURRENT ROW means stop at this value, and Priya has the same value, so Priya is inside Marcus’s frame even though she comes after him.

Rows that share the current row’s ordering value are called its peers, and that is the whole rule in one sentence. RANGE pulls in every peer, in both directions.

Which is also why both halves of a tie always get the same answer from RANGE. Their frames are the same frame.

Let's count it instead of eyeballing it

Eyeballing eight rows is fine. Eyeballing a production table is not, so let's ask Snowflake how many rows the two columns disagree on.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(BY_ROWS = BY_RANGE)  AS ROWS_WHERE_THEY_AGREE,
       COUNT_IF(BY_ROWS <> BY_RANGE) AS ROWS_WHERE_THEY_DIFFER
FROM (
  SELECT SUM(SALARY) OVER (ORDER BY SALARY
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS BY_ROWS,
         SUM(SALARY) OVER (ORDER BY SALARY
                           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BY_RANGE
  FROM EMPLOYEES);

Result1 row · 3 columns

ROWS_TOTAL	ROWS_WHERE_THEY_AGREE	ROWS_WHERE_THEY_DIFFER
8	7	1

Eight rows, seven agreements, one disagreement. The whole of this post is that one row. On a table where the ordering column has thousands of repeats it would be most of them.

What if the ORDER BY has no ties?

Then the two keywords cannot differ at all. If the ORDER BY has no duplicate values, no row has any peers, and there is nothing for RANGE to pull in.

Let's run the same pair of running totals ordered by EMP_ID instead of SALARY. The salaries are unchanged and the tie is still in the table, but EMP_ID is unique, so the tie is not in the ORDER BY any more.

Query

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

Result8 rows · 5 columns

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

Query — counted

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(BY_ROWS <> BY_RANGE) AS ROWS_WHERE_THEY_DIFFER
FROM (
  SELECT SUM(SALARY) OVER (ORDER BY EMP_ID ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BY_ROWS,
         SUM(SALARY) OVER (ORDER BY EMP_ID RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BY_RANGE
  FROM EMPLOYEES);

Result1 row · 2 columns

ROWS_TOTAL	ROWS_WHERE_THEY_DIFFER
8	0

Identical on every row, and no disagreements at all. RANGE only ever bites when your ORDER BY contains duplicates. The tie has to be in the ordering expression, not merely somewhere in the table.

The question to ask about your own query

This is also the practical way to decide whether you need to care. Can the ordering expression repeat? If it is a surrogate key or a unique timestamp, the two keywords are interchangeable and you can write whichever you like. If it is a salary, a date, a score or an amount, they are not, and the choice is a real one.

So which one do we get when we write no frame at all?

This is the part that catches people out, because it is silent. When we write ORDER BY inside OVER and no frame clause, we still get a frame. Snowflake supplies one. Which one?

Let's put three columns in one query, all with the same ordering. The bare default, then the same thing with RANGE spelled out, then with ROWS spelled out.

Query

SELECT FIRST_NAME, SALARY,
       SUM(SALARY) OVER (ORDER BY SALARY)                                   AS DEFAULT_FRAME,
       SUM(SALARY) OVER (ORDER BY SALARY RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS EXPLICIT_RANGE,
       SUM(SALARY) OVER (ORDER BY SALARY ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS EXPLICIT_ROWS
FROM EMPLOYEES
ORDER BY SALARY, FIRST_NAME;

Result8 rows · 5 columns

FIRST_NAME	SALARY	DEFAULT_FRAME	EXPLICIT_RANGE	EXPLICIT_ROWS
Sofia	71400.00	71400.00	71400.00	71400.00
Tomas	87250.00	158650.00	158650.00	158650.00
Grace	92000.00	250650.00	250650.00	250650.00
Marcus	96500.00	443650.00	443650.00	347150.00
Priya	96500.00	443650.00	443650.00	443650.00
Lena	104000.00	547650.00	547650.00	547650.00
Aisha	118000.00	665650.00	665650.00	665650.00
Daniel	131000.00	796650.00	796650.00	796650.00

DEFAULT_FRAME and EXPLICIT_RANGE are the same column. EXPLICIT_ROWS is the odd one out, and it is the odd one out on exactly the row we would expect by now, Marcus’s.

Reading two columns and declaring them identical is eyeballing again, so let's do it as a set operation. MINUS returns the rows of the first query that are not in the second.

Query

SELECT FIRST_NAME, SUM(SALARY) OVER (ORDER BY SALARY) FROM EMPLOYEES
MINUS
SELECT FIRST_NAME, SUM(SALARY) OVER (ORDER BY SALARY
                                     RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM EMPLOYEES;

Result0 rows · 2 columns

FIRST_NAME	SUM(SALARY) OVER (ORDER BY SALARY)

Nothing comes back. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

The silent one

A running total written as SUM(SALARY) OVER (ORDER BY SALARY) is a RANGE running total. If your ordering column has duplicates and you expected row-by-row accumulation, you are already getting something else, and it will look right on every row that happens not to be tied. If you want ROWS, write ROWS.

So which one should we use?

Neither keyword is the right one. They answer different questions, and the way to choose is to work out which question we are actually asking.

A question about values

What is the total payroll at or below this salary? That is a question about a salary, not about a row, so it wants RANGE.

Query — RANGE

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

Result8 rows · 3 columns

FIRST_NAME	SALARY	PAYROLL_AT_OR_BELOW
Sofia	71400.00	71400.00
Tomas	87250.00	158650.00
Grace	92000.00	250650.00
Marcus	96500.00	443650.00
Priya	96500.00	443650.00
Lena	104000.00	547650.00
Aisha	118000.00	665650.00
Daniel	131000.00	796650.00

Marcus and Priya get the same number, and they have to. They earn the same, so the payroll at or below their salary is one figure, not two. A smaller number for Marcus would be a wrong answer to this question.

A question about positions

What is the sum of this row and the two rows before it? That is a question about position in a sequence, so it wants ROWS. It also needs a tiebreak in the ORDER BY so that “before” is well defined at all.

Query — ROWS, with EMP_ID as the tiebreak

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

Result8 rows · 3 columns

FIRST_NAME	SALARY	LAST_THREE_ROWS
Sofia	71400.00	71400.00
Tomas	87250.00	158650.00
Grace	92000.00	250650.00
Marcus	96500.00	275750.00
Priya	96500.00	285000.00
Lena	104000.00	297000.00
Aisha	118000.00	318500.00
Daniel	131000.00	353000.00

Here Marcus and Priya get different numbers, and that is equally correct, because they are different rows with different rows behind them.

	`ROWS`	`RANGE`
The bounds count	rows	values
`CURRENT ROW` means	stop at this row	stop at this value, peers included
Tied rows get	different frames	the same frame
Frame size on a tie	as asked	as asked, plus the peers
Answers	“the last n rows”	“at or below this value”
Accepts a numeric offset	no, counts only	yes, a band of values
Is the default when you write only `ORDER BY`	no	yes

So the test is one question. Is your question about values, or about positions? Payroll at or below a salary is values. The last three rows is positions.

Now let's try a band of values

So far RANGE has only been a different reading of the same bounds. It can also do something ROWS has no way of expressing. It can take a number as the offset, so the frame becomes a band of values rather than a count of rows.

RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING is everybody whose salary is within 10,000 of mine, in either direction. The frame is measured in currency, not in rows.

Query

SELECT FIRST_NAME, SALARY,
       COUNT(*)    OVER (ORDER BY SALARY RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING) AS PEERS_WITHIN_10K,
       MIN(SALARY) OVER (ORDER BY SALARY RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING) AS BAND_FROM,
       MAX(SALARY) OVER (ORDER BY SALARY RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING) AS BAND_TO
FROM EMPLOYEES
ORDER BY SALARY, FIRST_NAME;

Result8 rows · 5 columns

FIRST_NAME	SALARY	PEERS_WITHIN_10K	BAND_FROM	BAND_TO
Sofia	71400.00	1	71400.00	71400.00
Tomas	87250.00	4	87250.00	96500.00
Grace	92000.00	4	87250.00	96500.00
Marcus	96500.00	5	87250.00	104000.00
Priya	96500.00	5	87250.00	104000.00
Lena	104000.00	3	96500.00	104000.00
Aisha	118000.00	1	118000.00	118000.00
Daniel	131000.00	1	131000.00	131000.00

PEERS_WITHIN_10K is the size of each band, and BAND_FROM and BAND_TO are its two ends. That is what makes the grid readable.

We can see that Marcus and Priya share the widest band, that Tomas and Grace share an identical band without sharing a salary, and that three people are alone in theirs: the lowest paid, the second highest and the highest. Nobody else sits within 10,000 of them.

We cannot write that with ROWS. ROWS counts positions, and a position knows nothing about how far apart two salaries are. 2 PRECEDING gives us the two rows above us whether their salaries are next to ours or nowhere near them.

What a numeric offset requires

A numeric offset needs something it can subtract from, so the ORDER BY inside OVER has to be a single expression of a type that supports it. A number, or a date or timestamp with an INTERVAL offset. Episode 56 uses the interval form for a rolling window measured in days.

Let's finish with the same pair on dates

One last pair, on the ORDERS table. We take daily totals with a cumulative sum over them, computed both ways. The query carries its own LIMIT 12, so the grid is the first stretch of the series rather than all of it.

Sample data

-- Creates the ORDERS table the daily-totals 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 D AS (
  SELECT ORDER_DATE, SUM(AMOUNT) AS DAY_TOTAL FROM ORDERS GROUP BY ORDER_DATE
)
SELECT ORDER_DATE, DAY_TOTAL,
       SUM(DAY_TOTAL) OVER (ORDER BY ORDER_DATE ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BY_ROWS,
       SUM(DAY_TOTAL) OVER (ORDER BY ORDER_DATE RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BY_RANGE
FROM D
ORDER BY ORDER_DATE
LIMIT 12;

Result12 rows · 4 columns

ORDER_DATE	DAY_TOTAL	BY_ROWS	BY_RANGE
2023-01-12	4200.00	4200.00	4200.00
2023-01-28	890.50	5090.50	5090.50
2023-02-14	6150.00	11240.50	11240.50
2023-02-27	1500.00	12740.50	12740.50
2023-03-09	2310.75	15051.25	15051.25
2023-03-22	5020.00	20071.25	20071.25
2023-04-05	7890.00	27961.25	27961.25
2023-04-19	1120.00	29081.25	29081.25
2023-05-02	3340.25	32421.50	32421.50
2023-05-21	640.00	33061.50	33061.50
2023-06-08	8100.00	41161.50	41161.50
2023-06-25	1975.50	43137.00	43137.00

Identical, twelve rows out of twelve. On its own that proves nothing at all. A dozen agreeing rows is exactly what we would see on tied data too, as long as the tie happened to be further down.

So let's not leave it hanging. We ask whether this ordering column has any duplicates in it.

Query — does any order date repeat?

SELECT ORDER_DATE, COUNT(*) AS N
FROM ORDERS GROUP BY ORDER_DATE HAVING COUNT(*) > 1 ORDER BY ORDER_DATE;

Result0 rows · 2 columns

ORDER_DATE	N

No rows, so no ORDER_DATE appears twice anywhere in ORDERS. That is the reason the two columns match, and it is a fact about this data rather than a property of RANGE.

The agreement holds for the whole series, not just the twelve rows shown, because there is no tie anywhere for RANGE to pull in.

What changes the day the data does

Put two orders on the same date, which real order tables do constantly, and those two columns come apart on exactly the rows that share a date. Nothing about the query changed. The data changed. A frame you have only ever tested on unique ordering values has not been tested.

Key points

ROWS counts rows. RANGE counts values. The bound words are identical.
Under RANGE, CURRENT ROW means this value, so every row tying with the current row, its peers, is inside the frame, in both directions.
Both halves of a tie therefore get the same RANGE answer, because they get the same frame.
They can only differ where the ORDER BY contains duplicates. Order by something unique and the two are interchangeable.
The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, not ROWS. Writing only ORDER BY inside OVER gives you RANGE.
Ask whether your question is about values (“at or below this salary”) or about positions (“the last three rows”). Both answers are correct; only one of them answers your question.
RANGE accepts a numeric or interval offset, which makes the frame a band of values. ROWS cannot express that at all.
A numeric offset needs a single ordering expression that can be subtracted from: a number, or a date or timestamp with an INTERVAL.
Columns that agree on your data are not a property of the keywords. Check whether the ordering column can repeat before you conclude anything.

Next in this series

Episode 56 — moving averages and rolling windows. Frames doing real work on a time series, the three things that go wrong on real data, and RANGE with an INTERVAL offset for a rolling window measured in days rather than in rows.

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. 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 55 - ROWS vs RANGE in Snowflake

Let's create some sample data

Let's run the same total with ROWS and with RANGE

So where does the difference come from?

Let's count it instead of eyeballing it

What if the ORDER BY has no ties?

So which one do we get when we write no frame at all?

So which one should we use?

A question about values

A question about positions

Now let's try a band of values

Let's finish with the same pair on dates

Key points

No comments:

Post a Comment