Snowflake SQL Tutorial 19 - LISTAGG in Snowflake

Snowflake SQL · Episode 19

In this post we are going to cover LISTAGG in Snowflake.

LISTAGG is an aggregate, the same as COUNT and SUM. The difference is what it hands back. The other aggregates collapse a group of rows into a number. LISTAGG collapses it into a string, with a delimiter of our choosing between the values.

We will start with the simplest call and then work through the things about it that catch people out: the order of the items inside the string, the delimiter we are allowed to leave out, what happens to the NULLs, and what comes back when every value in a group is NULL.

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

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

Let's create some sample data

LISTAGG turns rows into a string, so it matters which rows. Almost everything in this post runs against the EMPLOYEES table, and the script below creates it.

Copy this into a Snowflake worksheet and run it once. Every post in this series uses the same table, so if you have run it already you are ready to go.

Sample data
-- Sample data for this post.
-- Run this once. Every post in this series uses the same EMPLOYEES table.
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 things in that script are worth noticing before we run the first query, because both of them become traps later. The first is the order the names go in. Aisha, Marcus, Lena, Tomas. That is not alphabetical, and whether LISTAGG follows it is the first thing we deal with.

The second is COMMISSION_PCT, which is NULL for most of these people. Only Lena, Priya and Sofia have a value in it. That column is the whole of the second half of this post.

One other table

One query uses the ORDERS table instead. That is the other table from the same shared sample-data script, and the post on SUM, AVG, MIN and MAX lists it in full.

Let's start with the simplest LISTAGG

The whole idea fits in one query. We write LISTAGG, then the column we want, then a delimiter to put between the values.

Query
SELECT LISTAGG(FIRST_NAME, ', ') AS EVERYONE FROM EMPLOYEES;
Result1 row · 1 column
EVERYONE
Aisha, Marcus, Lena, Tomas, Priya, Daniel, Sofia, Grace

One row and one column came back, and in that single cell are eight names. That is every employee in the table, in a readable list rather than a count. That is the entire pitch for the function.

Everything else in this post is the things that will catch us out, and the first one is already visible above.

The order is not what it looks like

Read those names again. Aisha, Marcus, Lena, Tomas. That is not alphabetical. It is the order the rows were inserted in, which on this run is also EMP_ID order. That is a coincidence, not a rule. The documentation is blunt about it.

“If you do not specify WITHIN GROUP (ORDER BY), the order of elements within each list is unpredictable.”

Watch out

Unpredictable means unpredictable. Do not write code that depends on the order you got today. Do not assume it is the order you will get tomorrow on the same data. If the order of the items inside the string matters to anybody, and in a report it always does, you have to ask for it.

Now let's ask for an order with WITHIN GROUP

Here is the same query with WITHIN GROUP (ORDER BY FIRST_NAME) added. Nothing else changed.

Query
SELECT LISTAGG(FIRST_NAME, ', ') WITHIN GROUP (ORDER BY FIRST_NAME) AS ALPHABETICAL FROM EMPLOYEES;
Result1 row · 1 column
ALPHABETICAL
Aisha, Daniel, Grace, Lena, Marcus, Priya, Sofia, Tomas

Aisha, Daniel, Grace, Lena. Alphabetical, because that is what we asked for. Same names, same cell, same everything else.

Read it as one phrase

WITHIN GROUP reads oddly at first, so it is worth being precise about what it does. The ORDER BY inside it does not order our result rows. It orders the items inside the string. Ordering the rows of the result is still the query's own ORDER BY clause, as usual. The two are independent and a query can carry both.

Write WITHIN GROUP (ORDER BY ...) essentially always. It costs us ten characters and it is the difference between a list and a list we can trust.

Now let's get one list per group

This is probably the shape we came here for. We add a GROUP BY and we get one string per group instead of one string for the whole table.

Query
SELECT DEPT_ID, LISTAGG(FIRST_NAME, ', ') WITHIN GROUP (ORDER BY FIRST_NAME) AS TEAM
FROM EMPLOYEES GROUP BY DEPT_ID ORDER BY DEPT_ID;
Result4 rows · 2 columns
DEPT_IDTEAM
10Aisha, Daniel, Marcus
20Lena, Tomas
30Priya, Sofia
NULLGrace

One row per department, with the team as a readable list. Department 10 is Aisha, Daniel and Marcus.

Now notice what did not change. Four rows, four departments, and that includes the NULL department with Grace on her own. Those are exactly the same four groups that COUNT and GROUP BY produced earlier in this level. Nothing about the grouping changed. Only the aggregate changed.

That is the useful way to hold LISTAGG in our heads. It is not a different kind of query, it is a different answer to the same question.

Can we use DISTINCT inside it?

We can. DISTINCT goes inside LISTAGG exactly the way it goes inside COUNT.

Query
SELECT LISTAGG(DISTINCT STATUS, ' | ') WITHIN GROUP (ORDER BY STATUS) AS STATUSES FROM ORDERS;
Result1 row · 1 column
STATUSES
CANCELLED | PENDING | RETURNED | SHIPPED

Four status values, in one cell, separated by a pipe.

ORDERS holds 41 rows. That count comes from the COUNT episode earlier in this level, not from anything on this page. So without DISTINCT this cell would have been 41 values long with every status repeated. With it, we get the four that exist.

What values actually appear in this column? is a question we ask constantly when somebody hands us an unfamiliar table, and this is the one-line version of it.

Now let's leave the delimiter out

The delimiter is optional. If we leave it out, LISTAGG still runs. It does not complain, and it does not pick a sensible default for us. Here it is over EMP_ID.

Query
SELECT LISTAGG(EMP_ID) WITHIN GROUP (ORDER BY EMP_ID) AS IDS FROM EMPLOYEES;
Result1 row · 1 column
IDS
12345678

Separate employee IDs, glued into what reads as a single number. If that arrived in a report or in a downstream join key, it would take a while to work out what had happened.

It is not a bug, and it is documented. “If no delimiter is specified, an empty string is used as the delimiter.” An empty string is a perfectly valid separator. It is just not a visible one.

Watch out

Pass a delimiter unless you genuinely want the values glued together. This is the easiest mistake on the page to make by accident, because the query that causes it is shorter than the query that does not.

Let's see what happens to the NULLs

COMMISSION_PCT is NULL for most of these employees. Let's aggregate it and see what LISTAGG does about that, one list per department.

Query
SELECT DEPT_ID, LISTAGG(COMMISSION_PCT, ',') WITHIN GROUP (ORDER BY EMP_ID) AS COMMISSIONS
FROM EMPLOYEES GROUP BY DEPT_ID ORDER BY DEPT_ID;
Result4 rows · 2 columns
DEPT_IDCOMMISSIONS
10
200.05
300.10,0.08
NULL

Department 20 is the row to look at. Two people are in it, Lena and Tomas, and only Lena has a commission. The output is 0.05.

The thing worth noticing is what is not there. There is no comma. There is no empty slot where Tomas would have been. LISTAGG did not leave a gap. It left the row out entirely. That missing comma is the whole lesson.

Documented again. “If some but not all input expressions evaluate to NULL, the output contains all non-NULL values and excludes the NULL values.”

Now compare department 30 on the row below. Both Priya and Sofia have a commission there, and we get 0.10,0.08 with the comma present. The delimiter only ever appears between values that are there.

One last thing: an all-NULL group gives back an empty string

Look at department 10 in the grid above. Three people, none of them with a commission, and the cell is blank. The NULL department is blank for the same reason, because Grace has no commission either.

So what is that blank, actually? A NULL, or an empty string?

It is an empty string. “If all input expressions evaluate to NULL, the output is an empty string.”

This is the one place LISTAGG behaves differently from every aggregate we already know, and the difference is small enough to cost us an afternoon.

Aggregate over a group where every value is NULLWhat comes back
SUM(col)NULL
AVG(col)NULL
MIN(col) / MAX(col)NULL
LISTAGG(col, ',')'' — an empty string
The trap this creates

A test for IS NULL will not find those rows. WHERE COMMISSIONS IS NULL returns nothing, and the blank cells sit there looking exactly like missing data. To spot the empty ones, compare against an empty string with LISTAGG(COMMISSION_PCT, ',') <> '', or check the length.

Key points

  • LISTAGG is the aggregate that collapses a group of rows into a string instead of a number. Rows in, one sentence out.
  • Without WITHIN GROUP (ORDER BY ...) the order of the items inside the string is, in Snowflake's own word, unpredictable. Write it essentially always.
  • The ORDER BY inside WITHIN GROUP orders the items in the string, not the rows of the result. They are separate jobs and a query can do both.
  • With GROUP BY we get one string per group. The same groups COUNT gave us, with a different answer in the second column.
  • DISTINCT works inside LISTAGG, which makes it a one-line way to see what values a column actually holds.
  • The delimiter is optional and defaults to an empty string, so LISTAGG(EMP_ID) glues the values together with nothing between them.
  • NULLs are skipped, not blanked. No stray delimiter is left where the missing value was.
  • A group whose values are all NULL returns an empty string, not NULL. That is unlike SUM, AVG, MIN and MAX, and IS NULL will not find those rows.
Next in this series

In the next post we will look at ARRAY_AGG and OBJECT_AGG. Same idea with one important difference. Instead of a string we get an array or an object back, and both of those are things we can keep working with.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the script above. The EMPLOYEES and ORDERS data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. The four sentences quoted in italics are from the Snowflake LISTAGG documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.