In this post we are going to cover OVER() in Snowflake. This is the first post of Level 5, and OVER() is where window functions begin.
A window function reads a group of rows and gives us one answer for every row in that group. An ordinary aggregate reads the same rows and replaces them with a single row. OVER() is the difference between those two things, and it is two empty brackets.
We will start with an aggregate we already know, add the brackets, and then build it up: comparing a row with its own group, several windows in one SELECT, what happens to NULLs, why WHERE will not have any of it, and percentage of total on a bigger table.
Every query below was run on a real Snowflake account, and every result you see is the output it gave back.
Episode 46 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
Let's create some sample data
Before we can window anything we need a table to window over. Most of this post runs against EMPLOYEES, the same eight-row table this course has used since Episode 1.
Copy this into a Snowflake worksheet and run it once.
-- 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');Eight people and a salary each. One column is left empty for most of them, COMMISSION_PCT, and we will come back to it later on.
Let's start with an ordinary aggregate
Let's begin with the aggregate we already know. One function, one column, no GROUP BY.
SELECT AVG(SALARY) AS AVG_SALARY FROM EMPLOYEES;| AVG_SALARY |
|---|
| 99581.25000000 |
That is the right answer to the question we asked. It is also everything else gone. We cannot see who sits above that figure and who sits below it, because the rows are no longer there. The aggregate used them up.
Now let's run the same function on the same column, with two empty brackets after it and nothing else changed.
SELECT FIRST_NAME, DEPT_ID, SALARY,
AVG(SALARY) OVER () AS AVG_SALARY
FROM EMPLOYEES
ORDER BY EMP_ID;| FIRST_NAME | DEPT_ID | SALARY | AVG_SALARY |
|---|---|---|---|
| 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 |
Everybody is still here, and the average is sitting on every one of them. That is the whole idea of a window function. It reads a group of rows and answers once per row, instead of reading a group of rows and replacing them with one.
Now let's count the rows of both
Those two queries gave us the same average. What changed is how many rows came back. Rather than scroll between the two grids, let's ask Snowflake to count them for us in one result.
SELECT
(SELECT COUNT(*) FROM (SELECT AVG(SALARY) FROM EMPLOYEES)) AS PLAIN_ROWS,
(SELECT COUNT(*) FROM (SELECT AVG(SALARY) OVER () FROM EMPLOYEES)) AS OVER_ROWS;| PLAIN_ROWS | OVER_ROWS |
|---|---|
| 1 | 8 |
1 against 8. Same table, same function, same column. OVER() is the switch between them.
The empty brackets are not decoration. They are the window, and empty means the whole result set is one group. Episode 47 puts PARTITION BY inside them, which makes the group smaller. Episode 48 puts ORDER BY inside them, which makes the group grow as you go down the rows. Everything in this level is a variation on what goes between those two brackets.
Now let's compare a row with its own group
Keeping the rows is useful because of what we can now do with them. We can put a row and its group's answer in the same expression and subtract one from the other.
SELECT FIRST_NAME, SALARY,
AVG(SALARY) OVER () AS COMPANY_AVG,
SALARY - AVG(SALARY) OVER () AS VS_AVG
FROM EMPLOYEES
ORDER BY VS_AVG DESC;| FIRST_NAME | SALARY | COMPANY_AVG | VS_AVG |
|---|---|---|---|
| Daniel | 131000.00 | 99581.25000 | 31418.75000 |
| Aisha | 118000.00 | 99581.25000 | 18418.75000 |
| Lena | 104000.00 | 99581.25000 | 4418.75000 |
| Marcus | 96500.00 | 99581.25000 | -3081.25000 |
| Priya | 96500.00 | 99581.25000 | -3081.25000 |
| Grace | 92000.00 | 99581.25000 | -7581.25000 |
| Tomas | 87250.00 | 99581.25000 | -12331.25000 |
| Sofia | 71400.00 | 99581.25000 | -28181.25000 |
Each row now carries the company average and its own distance from it. The sign tells us which side of the line that person is on. Sorting by that difference puts the furthest above at the top and the furthest below at the bottom. We could not write that query at all once an aggregate had thrown the rows away.
Can we do this with a subquery instead?
We can, and it is worth seeing. A scalar subquery in the SELECT list gives us the same figures.
SELECT FIRST_NAME, SALARY,
(SELECT AVG(SALARY) FROM EMPLOYEES) AS COMPANY_AVG,
SALARY - (SELECT AVG(SALARY) FROM EMPLOYEES) AS VS_AVG
FROM EMPLOYEES
ORDER BY VS_AVG DESC;| FIRST_NAME | SALARY | COMPANY_AVG | VS_AVG |
|---|---|---|---|
| Daniel | 131000.00 | 99581.25000000 | 31418.75000000 |
| Aisha | 118000.00 | 99581.25000000 | 18418.75000000 |
| Lena | 104000.00 | 99581.25000000 | 4418.75000000 |
| Marcus | 96500.00 | 99581.25000000 | -3081.25000000 |
| Priya | 96500.00 | 99581.25000000 | -3081.25000000 |
| Grace | 92000.00 | 99581.25000000 | -7581.25000000 |
| Tomas | 87250.00 | 99581.25000000 | -12331.25000000 |
| Sofia | 71400.00 | 99581.25000000 | -28181.25000000 |
Same eight people, same order, same answers. Let's not compare two grids by eye though. Let's subtract one query from the other with MINUS, from Episode 35, which gives back the rows on the left that are not on the right.
SELECT FIRST_NAME, SALARY, AVG(SALARY) OVER () AS COMPANY_AVG FROM EMPLOYEES
MINUS
SELECT FIRST_NAME, SALARY, (SELECT AVG(SALARY) FROM EMPLOYEES) FROM EMPLOYEES;| FIRST_NAME | SALARY | COMPANY_AVG |
|---|
Nothing came back, so the two queries agree on all eight rows. The reason to prefer OVER() is what we write, not what it costs. The table is named once, the intent is on the line we are reading, and a second comparison does not mean a second subquery. Nothing on this page is timed and no performance claim is made anywhere.
Look at the COMPANY_AVG column in the windowed grid and in the subquery grid. It is the same value printed to a different number of decimal places. The windowed version is computed at a fixed scale, and Snowflake casts the salary to NUMBER(25,5) to do it. The plain aggregate keeps more places than that. MINUS compares values and not text, and trailing zeros do not change a value, so these two still agree exactly. They stop agreeing only when the true answer does not finish inside that scale, which is what happens in Episode 47.
Now let's put several windows in one SELECT
We are not limited to one window function. Each one is worked out on its own, so four of them in one SELECT list is nothing unusual.
SELECT FIRST_NAME, SALARY,
COUNT(*) OVER () AS HEADCOUNT,
SUM(SALARY) OVER () AS PAYROLL,
MIN(SALARY) OVER () AS LOWEST,
MAX(SALARY) OVER () AS HIGHEST
FROM EMPLOYEES
ORDER BY EMP_ID;| FIRST_NAME | SALARY | HEADCOUNT | PAYROLL | LOWEST | HIGHEST |
|---|---|---|---|---|---|
| Aisha | 118000.00 | 8 | 796650.00 | 71400.00 | 131000.00 |
| Marcus | 96500.00 | 8 | 796650.00 | 71400.00 | 131000.00 |
| Lena | 104000.00 | 8 | 796650.00 | 71400.00 | 131000.00 |
| Tomas | 87250.00 | 8 | 796650.00 | 71400.00 | 131000.00 |
| Priya | 96500.00 | 8 | 796650.00 | 71400.00 | 131000.00 |
| Daniel | 131000.00 | 8 | 796650.00 | 71400.00 | 131000.00 |
| Sofia | 71400.00 | 8 | 796650.00 | 71400.00 | 131000.00 |
| Grace | 92000.00 | 8 | 796650.00 | 71400.00 | 131000.00 |
Every row carries the headcount, the total payroll, the lowest salary and the highest. Four aggregates, no GROUP BY, and no rows lost.
The grid also checks itself. Divide the PAYROLL column by the HEADCOUNT column and we get the average we started this post with, arrived at from a different pair of captured columns.
Let's see what NULLs do in a window
This is where people expect a surprise. There is not one, and that is worth showing precisely because the expectation is so common. COMMISSION_PCT is empty for most of this table, so let's count it and average it inside a window.
SELECT FIRST_NAME, COMMISSION_PCT,
COUNT(*) OVER () AS ROWS_TOTAL,
COUNT(COMMISSION_PCT) OVER () AS NON_NULL_COMMISSIONS,
AVG(COMMISSION_PCT) OVER () AS AVG_COMMISSION
FROM EMPLOYEES
ORDER BY EMP_ID;| FIRST_NAME | COMMISSION_PCT | ROWS_TOTAL | NON_NULL_COMMISSIONS | AVG_COMMISSION |
|---|---|---|---|---|
| Aisha | NULL | 8 | 3 | 0.07666 |
| Marcus | NULL | 8 | 3 | 0.07666 |
| Lena | 0.05 | 8 | 3 | 0.07666 |
| Tomas | NULL | 8 | 3 | 0.07666 |
| Priya | 0.10 | 8 | 3 | 0.07666 |
| Daniel | NULL | 8 | 3 | 0.07666 |
| Sofia | 0.08 | 8 | 3 | 0.07666 |
| Grace | NULL | 8 | 3 | 0.07666 |
Read the COMMISSION_PCT column down the grid. It is NULL for five of the eight people and present for three. COUNT(*) counts rows and reports eight. COUNT(COMMISSION_PCT) counts values and reports three. The average divides by three, not by eight.
Those are the ordinary aggregate rules, unchanged. Here they are again with no window function at all, so we can compare.
SELECT COUNT(*) AS ROWS_TOTAL,
COUNT(COMMISSION_PCT) AS NON_NULL_COMMISSIONS,
AVG(COMMISSION_PCT) AS AVG_COMMISSION
FROM EMPLOYEES;| ROWS_TOTAL | NON_NULL_COMMISSIONS | AVG_COMMISSION |
|---|---|---|
| 8 | 3 | 0.07666667 |
One row instead of eight, and the same three answers. OVER() changes how many rows come back. It does not change what NULLs do.
Can we filter on it in WHERE?
This is the thing almost everybody tries within an hour of meeting OVER(). We already have a column that tells us who is above the company average, so keeping only those people should be one WHERE clause away. Let's try it.
SELECT FIRST_NAME, SALARY, AVG(SALARY) OVER () AS COMPANY_AVG
FROM EMPLOYEES
WHERE SALARY > COMPANY_AVG;000990 (42601): SQL compilation error: error line 3 at position 15 aggregate function alias 'COMPANY_AVG' cannot be used in the WHERE clause
That error is about the alias, and it is a rule we have met before. WHERE cannot see names we invented in the SELECT list. So let's write the window function out in full instead of referring to its alias.
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > AVG(SALARY) OVER ();002063 (42601): SQL compilation error: Window function [AVG(CAST(EMPLOYEES.SALARY AS NUMBER(25,5))) OVER ()] appears outside of SELECT, QUALIFY, and ORDER BY clauses.
A different error, and this one states the actual rule. A window function may appear in SELECT, in QUALIFY and in ORDER BY, and nowhere else. WHERE has already finished choosing rows by the time a window function runs. That is the only order that makes sense. The window is computed over the rows that survived the filter, so it cannot also be the filter.
So let's compute it first, then filter
We put the whole query in brackets, give it a name, and filter from outside. The window function has finished by the time the outer WHERE runs, so the outer query is filtering on an ordinary column.
SELECT FIRST_NAME, SALARY, COMPANY_AVG
FROM (SELECT FIRST_NAME, SALARY, AVG(SALARY) OVER () AS COMPANY_AVG
FROM EMPLOYEES) W
WHERE SALARY > COMPANY_AVG
ORDER BY SALARY DESC;| FIRST_NAME | SALARY | COMPANY_AVG |
|---|---|---|
| Daniel | 131000.00 | 99581.25000 |
| Aisha | 118000.00 | 99581.25000 |
| Lena | 104000.00 | 99581.25000 |
Three rows out of eight. And have a look at the average beside them. It is still the average of all eight people, not of the three that survived. It was computed before the filter, which is exactly what we wanted.
QUALIFY, from Episode 23, does this in one clause instead of a wrapper. It filters on the result of a window function in the same query that computes it. The reason it exists is the rule we just read. The reason to learn the wrapper first is that QUALIFY is a Snowflake convenience, while wrapping works everywhere.
Let's try percentage of total on a bigger table
One more, on a bigger table. Percentage of total is the pattern we will write most often, and it needs both halves of a window function in one expression: the row's own value and the total across all the rows.
So we need a second table. Here is the script for it.
-- 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 is the row the percentage-of-total section has to explain.
INSERT INTO ORDERS VALUES
(1041, 106, '2024-11-05', NULL, 'PENDING', NULL, 3);That script inserts 41 rows. The last one is on its own at the end because its AMOUNT is NULL on purpose.
Now the query. The amount, the total over all the rows, and the amount divided by that total.
SELECT ORDER_ID, CUSTOMER_ID, AMOUNT,
SUM(AMOUNT) OVER () AS ALL_ORDERS_TOTAL,
AMOUNT / SUM(AMOUNT) OVER () * 100 AS PCT_OF_TOTAL
FROM ORDERS
ORDER BY AMOUNT DESC
LIMIT 10;| ORDER_ID | CUSTOMER_ID | AMOUNT | ALL_ORDERS_TOTAL | PCT_OF_TOTAL |
|---|---|---|---|---|
| 1041 | 106 | NULL | 205427.00 | NULL |
| 1037 | 107 | 13100.00 | 205427.00 | 6.37696100 |
| 1032 | 107 | 12850.00 | 205427.00 | 6.25526300 |
| 1025 | 107 | 11200.00 | 205427.00 | 5.45205800 |
| 1020 | 107 | 10400.00 | 205427.00 | 5.06262600 |
| 1040 | 101 | 9640.00 | 205427.00 | 4.69266500 |
| 1015 | 107 | 9250.00 | 205427.00 | 4.50281600 |
| 1035 | 101 | 8820.00 | 205427.00 | 4.29349600 |
| 1026 | 105 | 8340.00 | 205427.00 | 4.05983600 |
| 1011 | 101 | 8100.00 | 205427.00 | 3.94300700 |
Every row carries the same grand total and its own share of it. Ten rows are shown because the query asks for ten. The total in that column is computed over all 41.
Two things about the first row, which is the one that would otherwise make us doubt the grid. Its AMOUNT is NULL, so its percentage is NULL too, because arithmetic on an unknown value is unknown. And it is at the top of a descending sort because Snowflake puts NULLs first when we sort descending, which was Episode 7. So this grid is the first ten rows of that sort: the NULL row, then the nine largest amounts.
The total is worth checking against the table itself, with no window function anywhere in the query.
SELECT COUNT(*) AS N_ORDERS, SUM(AMOUNT) AS TOTAL FROM ORDERS;| N_ORDERS | TOTAL |
|---|---|
| 41 | 205427.00 |
The same total, over the 41 rows the setup script inserted. The window function computed the figure the plain aggregate computes. It just handed a copy to every row instead of keeping it for itself.
Key points
OVER()turns an aggregate into a window function. Same function, same column, same table — the difference is that the rows survive.- A plain
AVGon this table returns 1 row; the sameAVGwithOVER()returns 8. That row count is the whole feature. - Empty brackets mean one group containing the entire result set.
PARTITION BYandORDER BYgo inside them, and that is the rest of this level. - Because the rows survive, you can compare a row to its group in one expression — the row's value and the group's answer are both available on the same line.
- A scalar subquery gives the same answers.
OVER()wins on what you write: the table is named once and a second comparison does not need a second subquery. NULLs are untouched by any of this.COUNT(*)counts rows,COUNT(column)counts values, and an average divides by the number of values it found.- A window function may only appear in
SELECT,QUALIFYandORDER BY. Snowflake's own error message says so. WHEREcannot filter on a window function becauseWHEREruns first. Wrap the query and filter outside, or useQUALIFYfrom Episode 23.- In a wrapped query the window was computed before the filter, so the group's answer still describes every row — not only the rows that survived.
In the next post we will put PARTITION BY inside those brackets. Empty brackets mean one group, the whole table. PARTITION BY gives us the same groups GROUP BY would make, without losing a single row — including a MINUS against GROUP BY that was supposed to return nothing and did not.
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 both errors and the empty MINUS result. 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.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.