LEAD() window function takes a column and an integer offset as arguments and returns the value of the cell in that column that is the specified number of rows after the current row. A third argument can be added to fill cells that do not have a corresponding row.
LEAD(column1_name, offset) OVER( ORDER BY column2_name )
ORDER BYclause is required.
There is also a third, optional argument that sets the value of cells where the corresponding row does not exist. The third argument will be
LEAD()with partitions, any cells with a corresponding row in another partition will be filled with the default value. Think of it as treating each partition as a separate table.
Suppose there’s a
yearly_sales table that looks like this:
Running the following query shows the next month’s sales for every month, with the last month having
NA as its value.
SELECT month,sales,LEAD(sales, 1, "NA") OVER(ORDER BY month) AS next_month_salesFROM yearly_sales;
This will be the output:
- Anonymous contributorAnonymous contributor1 total contribution
- Anonymous contributor