Spark SQL - LEAD Window Function

access_time 10 days ago visibility7 comment 0

About LEAD function

Spark LEAD function provides access to a row at a given offset that follows the current row in a window. This analytic function can be used in a SELECT statement to compare values in the current row with values in a following row. This function is like Spark SQL - LAG Window Function.

Function signature

lead(input[, offset[, default]])	OVER ([PARYITION BY ..] ORDER BY ...)
  • input: column name to get values from.
  • offset: the default value of parameter `offset` is 1.
  • default: the default value of `default` is null.

If the value of `input` at the `offset`th row is null, null is returned. If there is no such offset row (e.g., when the offset is 1, the last row of the window does not have any subsequent row), `default` is returned.

Example

The following sample SQL uses LEAD function to find the subsequent transaction record's amount based on DATE for each account. No default value is specified. 

SELECT TXN.*, LEAD(AMT,1) OVER (PARTITION BY ACCT ORDER BY TXN_DT) AS AMT_SUB FROM VALUES 
(101,10.01, DATE'2021-01-01'), (101,102.01, DATE'2021-01-01'), (102,93., DATE'2021-01-01'), (103,913.1, DATE'2021-01-02'), (102,913.1, DATE'2021-01-02'), (101,900.56, DATE'2021-01-03') AS TXN(ACCT,AMT, TXN_DT);

Result:

ACCT    AMT     TXN_DT  AMT_SUB
101     10.01   2021-01-01      102.01
101     102.01  2021-01-01      900.56
101     900.56  2021-01-03      NULL
103     913.10  2021-01-02      NULL
102     93.00   2021-01-01      913.10
102     913.10  2021-01-02      NULL
infoPARTITION BY is not mandatory; if it is not specified, all the records will be moved to one single partition which can cause performance issues. 

Example table

The virtual table/data frame is cited from SQL - Construct Table using Literals.

copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn or Twitter.

Want to publish your article on Kontext?

Learn more

More from Kontext

visibility 11
thumb_up 0
access_time 8 days ago

Similar as  Convert String to Date using Spark SQL , you can convert string of timestamp to Spark SQL timestamp data type. Function  to_timestamp(timestamp_str[, fmt]) p arses the `timestamp_str` expression with the `fmt` expression to a timestamp data type in Spark.  Example ...

visibility 9
thumb_up 0
access_time 7 days ago

JSON string values can be extracted using built-in Spark functions like get_json_object or json_tuple.  Values can be extracted using get_json_object function. The function has two parameters: json_txt and path. The first is the JSON text itself, for example a string column in your Spark ...

visibility 9
thumb_up 0
access_time 8 days ago

Function unix_timestamp() returns the UNIX timestamp of current time. You can also specify a input timestamp value.  Example: spark-sql> select unix_timestamp(); unix_timestamp(current_timestamp(), yyyy-MM-dd HH:mm:ss) 1610174099 spark-sql> select unix_timestamp(current_timestamp ...