Window Function LISTAGG

This article is an extension of the window function, to follow up on each of the window functions.

The LISTAGG function will aggregate values that are collected within the window defined by the partition expression.

In this example, we’ll list each sale in the database; adding a column at the end which will list all of the salespeople who made a sale on that particular day.

SELECT sale_date,
  LISTAGG(salesperson, ', ') OVER (PARTITION BY sale_date)
FROM public.sales
ORDER BY sale_date, sale_id;

This emits a dataset as follows:

Window Function LEAD

This article is an extension of the window function, to follow up on each of the window functions.

The LEAD function returns values in an offset row. The offset is given by the second argument to the function itself. The offset pushes the index below (or after) the selected row.

To demonstrate the effect of this function, we’ll take the sales_id value, and then we’ll also take a led sales_id. By adjusting the offset value, you’ll see how the leaded value ascends up the rows.

SELECT sale_id,
  LEAD(sale_id, 1) OVER (ORDER BY sale_id)
FROM public.sales
ORDER BY sale_date, sale_id
LIMIT 5;

Adjusting the index so that it’s now 2; the lead row is now offset by 2.

Window Function LAG

This article is an extension of the window function, to follow up on each of the window functions.

The LAG function returns values in an offset row. The offset is given by the second argument to the function itself. The offset pushes the index above (or before) the selected row.

To demonstrate the effect of this function, we’ll take the sales_id value, and then we’ll also take a lagged sales_id. By adjusting the offset value, you’ll see how the lagged value slips down the rows.

SELECT sale_id,
  LAG(sale_id, 1) OVER (ORDER BY sale_id)
FROM public.sales
ORDER BY sale_date, sale_id
LIMIT 5;

Note how the first row here doesn’t have a value, as there isn’t a row above row 1.

Adjusting the index so that it’s now 2, youll see that both rows 1 and 2 now don’t have a value.

Windowing functions in Redshift

Introduction

Window functions allow database developers to perform analysis over partitions of information, very quickly. Prior to window functions, developers would need to create sub-queries (or common table expressions) that would allow their windows to be created. Primary queries would then work over the sub-queries to propgate a result. This now goes away, using the OVER syntax.

In today’s article we’ll walk through the basic structure of how to use window functions.

Structure

As documented by AWS,

the standard window function syntax is:

function (expression) OVER (
[ PARTITION BY expr_list ]
[ ORDER BY order_list [ frame_clause ] ] )

where function is one of the functions described in this section and expr_list is:

expression | column_name [, expr_list ]

and order_list is:

expression | column_name [ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[, order_list ]

and frame_clause is:

ROWS
{ UNBOUNDED PRECEDING | unsigned_value PRECEDING | CURRENT ROW } |

{BETWEEN
{ UNBOUNDED PRECEDING | unsigned_value { PRECEDING | FOLLOWING } |
CURRENT ROW}
AND
{ UNBOUNDED FOLLOWING | unsigned_value { PRECEDING | FOLLOWING } |
CURRENT ROW }}

The function gets applied to an expression of columns that you’re extracting from your query. The amount of information that the function is applied to is controlled by PARTITION BY. How the information is ordered within the window is then controlled by the ORDER BY.

This is all text-booky kind of stuff, so let’s apply it to some actual data in the database.

Sample Data

For today’s article, lets consider a really simple sales breakdown. No relationships to other table; everything is locallised and contained in here (no that it needs to be - it just makes the example simpler).

CREATE TABLE public.sales (
  sale_id			INT PRIMARY KEY,
  sale_date			DATE NOT NULL,
  salesperson		VARCHAR(128) NOT NULL,
  quantity			INTEGER,
  unit_cost			DECIMAL,
  product_code		VARCHAR(32) NOT NULL
);

I’ve filled the table with some information, spanning two months.

Now that we have some information to work with, let’s look at a simple example.

Simplicity

In this first example, we’ll demonstrate the monthly averages for all sales people; and compare each instance of a sale to it.

Traditionally, we could solve this issue by employing a CTE to perform our aggregation, and join to this calculated set from our main query:

WITH avg_month AS (
  SELECT date_part('month', sale_date) as month,
  		 date_part('year', sale_date) as year,
  		 avg(quantity * unit_cost) as average
  FROM public.sales
  GROUP BY date_part('month', sale_date), date_part('year', sale_date)
)
SELECT salesperson,
	   sale_date,
       quantity * unit_cost,
       avg_month.average AS month_avg
FROM public.sales
INNER JOIN avg_month ON avg_month.month = date_part('month', sale_date)
AND						avg_month.year = date_part('year', sale_date)
ORDER BY salesperson, sale_date DESC;

The output of which starts to look like this:

We can simplify the query above greatly with the use of window functions.

SELECT salesperson, sale_date, (quantity * unit_cost),
	   avg(quantity * unit_cost) OVER (
         PARTITION BY date_part('month', sale_date), date_part('year', sale_date)
       ) as month_avg
FROM public.sales
ORDER BY salesperson, sale_date DESC;

This offers the same result, with a much cleaner query experience.

To look into this data a little further, and perform the same operation over the salesperson’s averages, we simply swap out the PARTITION BY expression:

SELECT salesperson, sale_date, (quantity * unit_cost),
	   avg(quantity * unit_cost) OVER (
         PARTITION BY salesperson
       ) as month_avg
FROM public.sales
ORDER BY salesperson, sale_date DESC;

This now tells us how each of the saleperson’s sales performed against their average.

Functions

With a basic understanding of the windowing function framework and how it can be applied to your queries, any of the supported functions can be use. The actual documentation of these functions can be found in the AWS documentation for Redshift, so I won’t reproduce that material here.

The list of functions are:

• AVG
• COUNT
• CUME_DIST
• DENSE_RANK
• FIRST_VALUE
• LAST_VALUE
• LAG
• LEAD
• LISTAGG
• MAX
• MEDIAN
• MIN
• NTH_VALUE
• NTILE
• PERCENT_RANK
• PERCENTILE_CONT
• PERCENTILE_DISC
• RANK
• RATIO_TO_REPORT
• ROW_NUMBER
• STDDEV_SAMP
• STDDEV_POP
• SUM
• VAR_SAMP
• VAR_POP

In future articles I’ll cover these functions and how they can be used to perform analysis over your datasets.

Window Function FIRST_VALUE and LAST_VALUE

This article is an extension of the window function, to follow up on each of the window functions.

The FIRST_VALUE and LAST_VALUE functions will allow you to retrieve the first and last values for a given window (respectively).

Taking the sales example for another run, we can compare every single sale for the day to see how it stacked up against the first sale of the day with the following query:

SELECT sale_date, salesperson, quantity::decimal * unit_cost,
	   FIRST_VALUE(quantity::decimal * unit_cost) OVER (
         PARTITION BY sale_date
         ORDER BY sale_id
         ROWS UNBOUNDED PRECEDING
       ) as sale_number
FROM public.sales
ORDER BY sale_date;

The result of which is as you’d expect:

This demonstrates how the first value can be drawn out of a set, per window.

Inversely, you simply use the LAST_VALUE function to get the last value; if you wanted to see how each sale stacked up against the last sale of the day:

SELECT sale_date, salesperson, quantity::decimal * unit_cost,
	   LAST_VALUE(quantity::decimal * unit_cost) OVER (
         PARTITION BY sale_date
         ORDER BY sale_id
         ROWS UNBOUNDED PRECEDING
       ) as sale_number
FROM public.sales
ORDER BY sale_date;