Data Science with PostgreSQL – Value Window Functions

This far in the Window Function in PostgreSQL series I have covered window function basics, and how to use aggregate window functions and ranking window function. I suggest you check the previous posts out 🙂

In this 4th post, I’ll show you how to use Value window functions – that can be used to calculate various “value” type aggregations such as Lag, LeadFirst_Value and Last_Value within each group of rows referred here as “window” or “partition”.

Let’s first create our test dataset

If you already have Postgres installed on your computer, please run below CREATE TABLE and INSERT statements to create test dataset.

--DROP TABLE Orders
CREATE TABLE IF NOT EXISTS Orders
(
	order_id INT,
	order_date DATE,
	customer_name VARCHAR(250),
	city VARCHAR(100),	
	order_amount INT
)

-- TRUNCATE TABLE Orders
INSERT INTO Orders
VALUES	(101,'2022-02-01','David Smith','Charleston',20000),
  		(102,'2022-02-02','David Jones','Savannah',30000),
  		(103,'2022-02-03','John Smith','Seattle',6000),
  		(104,'2022-02-04','Michael Smith','Charleston',16000),
  		(105,'2022-02-05','David Williams','Seattle',8000),
  		(106,'2022-02-06','Paum Smith','Charleston',26000),
  		(107,'2022-02-10','Andrew Smith','Savannah',16000),
  		(108,'2022-02-11','David Brown','Savannah',3000),
  		(109,'2022-02-20','Robert Smith','Seattle',2000),
  		(110,'2022-02-25','Peter Smith','Charleston',600);
Types of Value window functions

LAG(n) – Provides a value that is at an offset of n elements before the current row

LEAD(n) – Provides a value that is at an offset of n elements before the current row

FIRST_VALUE() – Outputs the first value in a list of elements

LAST_VALUE() – Outputs the last value in a list of elements

Using Value Window Functions
I. LAG()

The LAG() function allows to access data from the previous row in the same result set without use of any SQL joins.

SELECT order_id,
       customer_name,
       city,
       order_amount,
       order_date,
       --1 here indicates check for the previous row
       Lag(order_date, 1)
         OVER(
           ORDER BY order_date) AS "prev_order_date"
FROM   orders; 
II. LEAD()

LEAD() function allows to access data from the next row in the same result set without use of any SQL joins.

SELECT order_id,
       customer_name,
       city,
       order_amount,
       order_date,
       -- 1 here indicates check for the next row
       Lead(order_date, 1)
         OVER(
           ORDER BY order_date) AS "next_order_date"
FROM   orders; 
III. FIRST_VALUE(n) and LAST_VALUE(n)

FIRST_VALUE() and LAST_VALUE() functions help you to identify first and last record within a partition Or entire table (if PARTITION BY is not specified).

SELECT order_id,
       order_date,
       customer_name,
       city,
       order_amount,
       First_value(order_date)
         OVER(
           partition BY city
           ORDER BY city) first_order_date,
       Last_value(order_date)
         OVER(
           partition BY city
           ORDER BY city) last_order_date
FROM   orders; 
This is 4th article in four-part series on Window Functions in PostgreSQL

I hope you now have a better understanding of window functions in PostgreSQL. Windowing functions are a powerful skill for any Analyzing data. And there are clear benefits of using window functions that allows you to quickly output transformed data versus writing your own code. To read additional details about postgres window functions, please refer to the PostgreSQL Window Function Docs.

Enjoy what you’re reading?

Join my mailing list, and I’ll let you know whenever I write another post. No spam, I promise! 🧑🏻‍💻

Author: Varun Dhawan

Hello dear reader, I'm a DevOps Engineer based in MN, US (beautiful land of 10,000 lakes). I am perpetually curious and always willing to learn and engineer systems that can help solve complex problems using data. When I am not engineering or blogging, you’ll find me cooking and spending time with my family. Varun.Dhawan@gmail.com

3 thoughts on “Data Science with PostgreSQL – Value Window Functions”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: