Getting LEAD and LAG using Windowing Functions - order by

#1

Let us understand LEAD and LAG functions to get column values from following or prior rows.

Here is the example where we can get prior or following records based on ORDER BY Clause.

USE training_retail;

SELECT * FROM daily_revenue
ORDER BY order_date DESC
LIMIT 10;

SELECT t.*,
  lead(order_date) OVER (ORDER BY order_date DESC) AS prior_date,
  lead(order_revenue) OVER (ORDER BY order_date DESC) AS prior_revenue
FROM daily_revenue t
LIMIT 10;

We can also pass number of rows as well as default values for nulls as arguments.

USE training_retail;

SELECT t.*,
  lead(order_date, 7) OVER (ORDER BY order_date DESC) AS prior_date,
  lead(order_revenue, 7) OVER (ORDER BY order_date DESC) AS prior_revenue
FROM daily_revenue t
LIMIT 10;

SELECT t.*,
  lead(order_date, 7) OVER (ORDER BY order_date DESC) AS prior_date,
  lead(order_revenue, 7, 0) OVER (ORDER BY order_date DESC) AS prior_revenue
FROM daily_revenue t
LIMIT 10;

Practice hive on state of the art Big Data cluster - https://labs.itversity.com
You can sign up for our courses on Udemy using $10 coupons - Udemy Coupons - Big Data Courses


0 Likes