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;

Window Function DENSE_RANK

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

The DENSE_RANK function will use the ORDER BY expression to determine a rank of a value amongst groups. If a PARTITION BY is present, this is also included in the reset of groups.

The important difference between RANK and DENSE_RANK is that RANK will use to total number of rows to nominate it’s highest rank value; whereas DENSE_RANK will only use the number of ranks calculated, and the highest rank.

Take the following for example:

SELECT sale_id, quantity, unit_cost,
   DENSE_RANK() OVER (ORDER BY quantity DESC),
   RANK() OVER (ORDER BY quantity DESC)
FROM sales
ORDER BY quantity, sale_id;

The sale_id, quantity, and unit_cost are retrieved and ranked by the quantity value. Both RANK and DENSE_RANK are used in this example to illustrate the difference in value distribution (along the number line).

As there is a change in quantity, a new rank value is assigned:

Window Function CUME_DIST

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

The CUME_DIST function calculates the cumulative distribution of a value, for the data that’s seen.

An example of calculating the cumulative distribution of each salesperson, and the amount of value that they’ve sold would look like this:

SELECT salesperson, quantity::decimal * unit_cost,
	   CUME_DIST() OVER (
         PARTITION BY salesperson
         ORDER BY (quantity::decimal * unit_cost)
       ) as distr
FROM public.sales
ORDER BY salesperson;

As sales people have sold the same amount in different sales, you can see that the cumulative distribution value makes accumulative jumps.

Taking an interesting piece of this window, we focus in on John.

The repeated value amounts don’t seem to shift the distribution value (because of its cumulativity). By the time the next amount is present, there’s a significant jump. This is a property of this set because the data is ordered by the value column.