Welcome To TechBrothersIT: Snowflake SQL Tutorial 47 - PARTITION BY in Snowflake - The Same Groups as GROUP BY

Snowflake SQL Tutorial 47 - PARTITION BY in Snowflake - The Same Groups as GROUP BY - Without Losing a Single Row

Snowflake SQL · Episode 47

In this post we are going to cover PARTITION BY in Snowflake.

PARTITION BY goes inside the brackets of OVER. It splits the rows into groups, and the window function starts again in each group. Those are the same groups GROUP BY would make, with one difference that matters: every row survives.

We will start from the empty brackets of the last post and put one column inside them. Then we will check the groups against GROUP BY, find out what happens to a NULL, partition on two columns, and finish with two different groupings sitting on the same row.

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

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

On this page

Let's create some sample data
Let's start with one group, then make it smaller
Are these the same groups as GROUP BY?
Let's prove it with MINUS
What does PARTITION BY do with a NULL?
Now let's partition by two columns
Can GROUP BY do two groupings at once?
Now let's work out each person's share of their own department
Let's try it on a bigger table

Let's create some sample data

Most of this post runs against EMPLOYEES, the eight-row table this course has used since Episode 1. Copy this into a Snowflake worksheet and run it once.

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

Two columns do the work on this page. DEPT_ID, which three people share, two pairs share, and one person does not have at all. And MANAGER_ID, which groups the same eight people a different way.

That one missing DEPT_ID and that one missing MANAGER_ID are both deliberate. PARTITION BY has to decide what to do with a NULL, and a table where every value is present would let us skip the question.

Let's start with one group, then make it smaller

Episode 46 ended with empty brackets, OVER (), which means one group containing the whole result set. Let's run that again as our baseline.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY,
       AVG(SALARY) OVER () AS AVG_ALL
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 4 columns

FIRST_NAME	DEPT_ID	SALARY	AVG_ALL
Aisha	10	118000.00	99581.25000
Marcus	10	96500.00	99581.25000
Lena	20	104000.00	99581.25000
Tomas	20	87250.00	99581.25000
Priya	30	96500.00	99581.25000
Daniel	10	131000.00	99581.25000
Sofia	30	71400.00	99581.25000
Grace	NULL	92000.00	99581.25000

One number, repeated on all eight rows. That is useful, but most of the time the group we care about is smaller than the whole table. We want each person measured against their own department, not against everybody.

So let's put something inside the brackets.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY,
       AVG(SALARY) OVER (PARTITION BY DEPT_ID) AS AVG_IN_DEPT
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, EMP_ID;

Result8 rows · 4 columns

FIRST_NAME	DEPT_ID	SALARY	AVG_IN_DEPT
Aisha	10	118000.00	115166.66666
Marcus	10	96500.00	115166.66666
Daniel	10	131000.00	115166.66666
Lena	20	104000.00	95625.00000
Tomas	20	87250.00	95625.00000
Priya	30	96500.00	83950.00000
Sofia	30	71400.00	83950.00000
Grace	NULL	92000.00	92000.00000

Still eight rows. Nothing collapsed and nothing was filtered out. The only thing that changed is the last column. The average now starts again for each department, so the three people in one department share one figure and the two in another share a different one.

Now let's put both of them in one query

Rather than hold two grids in our heads, let's ask for both windows in the same SELECT. One with empty brackets, one partitioned.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY,
       AVG(SALARY) OVER ()                     AS AVG_ALL,
       AVG(SALARY) OVER (PARTITION BY DEPT_ID) AS AVG_IN_DEPT
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, EMP_ID;

Result8 rows · 5 columns

FIRST_NAME	DEPT_ID	SALARY	AVG_ALL	AVG_IN_DEPT
Aisha	10	118000.00	99581.25000	115166.66666
Marcus	10	96500.00	99581.25000	115166.66666
Daniel	10	131000.00	99581.25000	115166.66666
Lena	20	104000.00	99581.25000	95625.00000
Tomas	20	87250.00	99581.25000	95625.00000
Priya	30	96500.00	99581.25000	83950.00000
Sofia	30	71400.00	99581.25000	83950.00000
Grace	NULL	92000.00	99581.25000	92000.00000

The AVG_ALL column is the same all the way down. The AVG_IN_DEPT column changes every time DEPT_ID changes. Same function, same rows, same query. The only difference is what we told Snowflake to compute it over.

Are these the same groups as GROUP BY?

If those groups sound like GROUP BY groups, it is because they are. Let's run the ordinary GROUP BY over the same column and see.

Query

SELECT DEPT_ID, COUNT(*) AS HEADCOUNT, AVG(SALARY) AS AVG_IN_DEPT
FROM EMPLOYEES
GROUP BY DEPT_ID
ORDER BY DEPT_ID NULLS LAST;

Result4 rows · 3 columns

DEPT_ID	HEADCOUNT	AVG_IN_DEPT
10	3	115166.66666667
20	2	95625.00000000
30	2	83950.00000000
NULL	1	92000.00000000

Four rows. One for each department, and one more for the person who has no department at all. The averages in the AVG_IN_DEPT column are the same ones the partitioned window gave us, give or take the number of decimal places. That difference turns out to matter, and it is the next thing we look at.

So the groups are identical. What differs is how many rows we get back: 4 here against 8 from the window.

	`GROUP BY DEPT_ID`	`OVER (PARTITION BY DEPT_ID)`
Groups	One per department	One per department — the same ones
Rows returned	4	8
Row-level columns	Only the grouping column and aggregates	Every column the table has
Answer lives	On the group	On every row of the group
Two different groupings in one query	Not possible	Yes, and we will do it on this page

That is the whole trade. GROUP BY is the right tool when we want the summary and nothing else. PARTITION BY is the right tool when we want the summary and the detail in one result. A person's salary beside their department's average, in one grid, with nobody dropped.

Let's prove it with MINUS

This course proves a claim like “the same groups” with MINUS rather than by saying it. We take the windowed answers, subtract the GROUP BY answers, and expect nothing back. That is what we expected here. It is not what we got.

Query

SELECT DISTINCT DEPT_ID, AVG(SALARY) OVER (PARTITION BY DEPT_ID) FROM EMPLOYEES
MINUS
SELECT DEPT_ID, AVG(SALARY) FROM EMPLOYEES GROUP BY DEPT_ID;

Result1 row · 2 columns

DEPT_ID	AVG(SALARY) OVER (PARTITION BY DEPT_ID)
10	115166.66666000

One row, for one department out of the four. It is not a bug and it is not a different average. It is the decimal places. Count them in the windowed AVG_IN_DEPT column and then in the GROUP BY one.

The windowed average is computed at a fixed scale of five decimal places. The error message in Episode 46 shows the cast Snowflake applies to the salary column to compute it.
The GROUP BY average carries eight decimal places.
For three of the four departments the division comes out exactly. Two of them have two people and one has a single person, so the extra places are zeros, the values are equal, and MINUS finds nothing to report.
For the remaining department, three salaries divided by three people do not come out exactly. Cut off at five places and at eight places, those are two genuinely different numbers, and MINUS compares numbers exactly.

The practical rule

Round both sides before you compare a windowed aggregate with a GROUP BY aggregate. This is the failure mode that produces a reconciliation report with a handful of rows on it that nobody can explain. Every figure correct, every total agreeing to the cent on screen, and a MINUS or a join on the value quietly disagreeing in the eighth decimal place.

Why that column is called that

The second column of that grid is headed with the expression itself, because the SELECT DISTINCT never gave it a name. Worth remembering from Episode 1: an unnamed expression becomes a column heading spelled out in full.

What does PARTITION BY do with a NULL?

One person in this table has no department. So does a window function drop them, group them with somebody, or give them a group of their own? Let's add a count to the window and find out, because the count tells us how big each partition is.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY,
       COUNT(*)    OVER (PARTITION BY DEPT_ID) AS PEOPLE_IN_PARTITION,
       AVG(SALARY) OVER (PARTITION BY DEPT_ID) AS AVG_IN_DEPT
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, EMP_ID;

Result8 rows · 5 columns

FIRST_NAME	DEPT_ID	SALARY	PEOPLE_IN_PARTITION	AVG_IN_DEPT
Aisha	10	118000.00	3	115166.66666
Marcus	10	96500.00	3	115166.66666
Daniel	10	131000.00	3	115166.66666
Lena	20	104000.00	2	95625.00000
Tomas	20	87250.00	2	95625.00000
Priya	30	96500.00	2	83950.00000
Sofia	30	71400.00	2	83950.00000
Grace	NULL	92000.00	1	92000.00000

Nobody is dropped. Eight rows again. And the person with no department has a partition to themselves, of size one. All the NULLs go together into one partition, and here there happens to be only one of them.

Now compare the PEOPLE_IN_PARTITION column with the HEADCOUNT column of the GROUP BY DEPT_ID query. They are the same headcounts, once per person instead of once per department. PARTITION BY treats NULL exactly as GROUP BY does, as a group and not as a reason to disappear.

Two different NULL rules, both correct

This is worth contrasting with the other place NULL turns up in this course. In a JOIN condition or a WHERE clause, NULL never matches anything, including itself. In grouping — GROUP BY, PARTITION BY, DISTINCT — all the NULLs are treated as one value. Both rules are consistent. They are just different rules, and knowing which one you are standing in is most of the skill.

Now let's partition by two columns

PARTITION BY takes a list, exactly as GROUP BY does. Two columns means the partition is the combination of the two.

Query

SELECT FIRST_NAME, DEPT_ID, MANAGER_ID, SALARY,
       COUNT(*) OVER (PARTITION BY DEPT_ID, MANAGER_ID) AS IN_SAME_DEPT_AND_MANAGER
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, MANAGER_ID NULLS LAST, EMP_ID;

Result8 rows · 5 columns

FIRST_NAME	DEPT_ID	MANAGER_ID	SALARY	IN_SAME_DEPT_AND_MANAGER
Aisha	10	6	118000.00	2
Marcus	10	6	96500.00	2
Daniel	10	NULL	131000.00	1
Tomas	20	3	87250.00	1
Lena	20	6	104000.00	1
Sofia	30	5	71400.00	1
Priya	30	6	96500.00	1
Grace	NULL	6	92000.00	1

Read the last column. Two people share a department and a manager, so each of them reports a partition of two. Everybody else is alone in their combination and reports one.

And here is the GROUP BY that says the same thing. This is where the row counts make the argument more sharply than anywhere else on this page.

Query

SELECT DEPT_ID, MANAGER_ID, COUNT(*) AS N
FROM EMPLOYEES
GROUP BY DEPT_ID, MANAGER_ID
ORDER BY DEPT_ID NULLS LAST, MANAGER_ID NULLS LAST;

Result7 rows · 3 columns

DEPT_ID	MANAGER_ID	N
10	6	2
10	NULL	1
20	3	1
20	6	1
30	5	1
30	6	1
NULL	6	1

Seven rows against eight. Seven distinct department-and-manager combinations exist among eight people, and the only way eight rows collapse into seven groups is if exactly two of them share one. The window kept both of those people and told each of them the size of the group they are in. The GROUP BY merged them into a single row. The counts in the two grids match group for group.

Can GROUP BY do two groupings at once?

It cannot, and this is the part that is not just a convenience. A query has one GROUP BY and therefore one set of groups.

Each window function carries its own window, so two of them in one SELECT can group the same row two different ways. Let's try that.

Query

SELECT FIRST_NAME, DEPT_ID, MANAGER_ID, SALARY,
       AVG(SALARY) OVER (PARTITION BY DEPT_ID)    AS AVG_IN_DEPT,
       AVG(SALARY) OVER (PARTITION BY MANAGER_ID) AS AVG_UNDER_MANAGER
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 6 columns · scroll →

FIRST_NAME	DEPT_ID	MANAGER_ID	SALARY	AVG_IN_DEPT	AVG_UNDER_MANAGER
Aisha	10	6	118000.00	115166.66666	101400.00000
Marcus	10	6	96500.00	115166.66666	101400.00000
Lena	20	6	104000.00	95625.00000	101400.00000
Tomas	20	3	87250.00	95625.00000	87250.00000
Priya	30	6	96500.00	83950.00000	101400.00000
Daniel	10	NULL	131000.00	115166.66666	131000.00000
Sofia	30	5	71400.00	83950.00000	71400.00000
Grace	NULL	6	92000.00	92000.00000	101400.00000

Every row now carries the average of its department and the average of the people who report to the same manager. Those are different groupings that cut across each other. People in one department report to different managers, and one manager has people in several departments. Both answers are on the same line.

To get that from GROUP BY we would need two separate aggregate queries and then a join back to the detail rows to put the answers beside each person. This is a structural difference, not a performance claim. Nothing on this page is timed.

Here is the pattern we will actually reach for. SUM partitioned by department gives every row its department's payroll. Divide the row's own salary by that and we have each person's share of their own department.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY,
       SUM(SALARY) OVER (PARTITION BY DEPT_ID)                  AS DEPT_PAYROLL,
       SALARY / SUM(SALARY) OVER (PARTITION BY DEPT_ID) * 100   AS PCT_OF_DEPT
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, PCT_OF_DEPT DESC;

Result8 rows · 5 columns

FIRST_NAME	DEPT_ID	SALARY	DEPT_PAYROLL	PCT_OF_DEPT
Daniel	10	131000.00	345500.00	37.91606400
Aisha	10	118000.00	345500.00	34.15340100
Marcus	10	96500.00	345500.00	27.93053500
Lena	20	104000.00	191250.00	54.37908500
Tomas	20	87250.00	191250.00	45.62091500
Priya	30	96500.00	167900.00	57.47468700
Sofia	30	71400.00	167900.00	42.52531300
Grace	NULL	92000.00	92000.00	100.00000000

Each percentage is relative to that person's own department, not to the company, so the percentages add up to a hundred within each department rather than across the whole grid. The person with a department to themselves is the whole of it. That is correct and not a rounding artefact, because there is nobody else in the partition to share it with.

The payrolls are worth checking against a plain GROUP BY, which gives four rows where the window gave eight.

Query

SELECT DEPT_ID, SUM(SALARY) AS DEPT_PAYROLL
FROM EMPLOYEES GROUP BY DEPT_ID ORDER BY DEPT_ID NULLS LAST;

Result4 rows · 2 columns

DEPT_ID	DEPT_PAYROLL
10	345500.00
20	191250.00
30	167900.00
NULL	92000.00

The same four totals that the DEPT_PAYROLL column repeats down the windowed grid. The window did not compute anything different. It just declined to throw the people away afterwards.

Let's try it on a bigger table

Last one, on ORDERS, because this is the shape of the query we will write most often in practice. A detail row that also knows something about its own group.

So we need a second table. Here is the script for it.

Sample data

-- Creates the ORDERS table the last section of 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);

That script inserts 41 rows. Now let's count the orders and total the amounts per customer, without collapsing anything.

Query — LIMIT 12 so it fits on a screen

SELECT ORDER_ID, CUSTOMER_ID, AMOUNT,
       COUNT(*)    OVER (PARTITION BY CUSTOMER_ID) AS ORDERS_FOR_CUSTOMER,
       SUM(AMOUNT) OVER (PARTITION BY CUSTOMER_ID) AS CUSTOMER_TOTAL
FROM ORDERS
ORDER BY CUSTOMER_ID, ORDER_ID
LIMIT 12;

Result12 rows · 5 columns

ORDER_ID	CUSTOMER_ID	AMOUNT	ORDERS_FOR_CUSTOMER	CUSTOMER_TOTAL
1001	101	4200.00	9	61660.00
1003	101	6150.00	9	61660.00
1006	101	5020.00	9	61660.00
1011	101	8100.00	9	61660.00
1017	101	5540.00	9	61660.00
1023	101	6730.00	9	61660.00
1029	101	7460.00	9	61660.00
1035	101	8820.00	9	61660.00
1040	101	9640.00	9	61660.00
1002	102	890.50	6	6060.50
1008	102	1120.00	6	6060.50
1014	102	750.00	6	6060.50

Twelve rows shown out of the 41, because the query asks for twelve. Every order carries how many orders its customer has placed and what they have spent in total. Every one of a customer's rows carries the same pair of figures, which is what makes this readable.

Now the check, with no window function anywhere in it.

Query

SELECT CUSTOMER_ID, COUNT(*) AS N_ORDERS, SUM(AMOUNT) AS TOTAL
FROM ORDERS GROUP BY CUSTOMER_ID ORDER BY CUSTOMER_ID;

Result8 rows · 3 columns

CUSTOMER_ID	N_ORDERS	TOTAL
101	9	61660.00
102	6	6060.50
103	5	15635.25
104	4	9076.25
105	5	36565.00
106	4	3760.00
107	5	56800.00
109	3	15870.00

Eight rows, one per customer who has placed an order, with the same counts and totals the windowed grid repeats.

One last thing about both of those grids. A customer with no orders at all cannot appear in either of them. A window function adds columns to the rows it is given, and ORDERS never mentions those customers. Finding them is an anti-join, which was Episode 25.

Key points

PARTITION BY goes inside the brackets of OVER and splits the rows into groups. The window function restarts in each group.
Those are the same groups GROUP BY would make, and the aggregate values match. The difference is the row count: GROUP BY returned 4 rows here, the window returned 8.
NULL is a partition. All the NULLs group together and nothing is dropped — the same rule GROUP BY follows, and the opposite of how NULL behaves in a join or a WHERE clause.
PARTITION BY a, b partitions on the combination. Eight rows fell into seven combinations here, which is only possible because two rows share one.
Two window functions in one SELECT can use two different partitions on the same row. GROUP BY cannot — a query has one set of groups.
Share-of-group is a row's value divided by a SUM partitioned by that group, and it is relative to the row's own partition, not to the table.
A windowed aggregate and a GROUP BY aggregate can be computed at different scales. Where the division does not terminate, MINUS or an equality test will say they differ.
Round both sides before comparing a windowed average with a GROUP BY average. The grids agree on screen and the comparison does not.
A window function never adds or removes rows. If a group has no rows in the source table, no window function will invent one — that is still an outer join or an anti-join problem.

Next in this series

In the next post we will put ORDER BY inside OVER. There is still no notion of order inside those brackets, so every row in a partition sees the same answer. We add three words and each row sees only the rows up to itself, which is how a total becomes a running total.

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 MINUS that returned a row it was not expected to. The EMPLOYEES and ORDERS data is invented demo data created by the setup scripts above. No performance or benchmark claims are made anywhere in this series. Clause behaviour follows the Snowflake window function documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 47 - PARTITION BY in Snowflake - The Same Groups as GROUP BY - Without Losing a Single Row

Let's create some sample data

Let's start with one group, then make it smaller

Now let's put both of them in one query

Are these the same groups as GROUP BY?

Let's prove it with MINUS

What does PARTITION BY do with a NULL?

Now let's partition by two columns

Can GROUP BY do two groupings at once?

Let's try it on a bigger table

Key points

No comments:

Post a Comment

Label

Snowflake SQL Tutorial 47 - PARTITION BY in Snowflake - The Same Groups as GROUP BY - Without Losing a Single Row

Let's create some sample data

Let's start with one group, then make it smaller

Now let's put both of them in one query

Are these the same groups as GROUP BY?

Let's prove it with MINUS

What does PARTITION BY do with a NULL?

Now let's partition by two columns

Can GROUP BY do two groupings at once?

Now let's work out each person's share of their own department

Let's try it on a bigger table

Key points

No comments:

Post a Comment