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, Lead, First_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! 🧑🏻💻
data-nerd.blog 
Thank you for the detailed explanation.
Thanks Kasturi!
Glad you found the explanation useful. 👍