[HIVE-24869] Implement Respect/Ignore Nulls in lag/lead

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: UDF
• Assignee: Unassigned
• Reporter: Krisztian Kasa
• Created: 11/Mar/21 05:18
• Updated: 03/Sep/21 07:37

Description

<lead or lag function> ::=
  <lead or lag> <left paren> <lead or lag extent>
  [ <comma> <offset> [ <comma> <default expression> ] ] <right paren>
  [ <null treatment> ]

<lead or lag> ::=
  LEAD | LAG

<lead or lag extent> ::=
  <value expression>

<offset> ::=
  <exact numeric literal>

<default expression> ::=
  <value expression>

<null treatment> ::=
  RESPECT NULLS | IGNORE NULLS

Example: get the a column value from the previous and the next row or return 0 if there is no previous/next row corresponding to the current row. Respect/Ignore nulls control whether null values should be preserved or eliminated.

SELECT 
  a,
  LAG(a, 1, 0) OVER (ORDER BY a) IGNORE NULLS,
  LEAD(a, 1, 0) OVER (ORDER BY a) RESPECT NULLS
FROM ...

Issue Links

• is related to: HIVE-24945 PTF: Support vectorization for lead/lag functions
• supercedes: HIVE-18145 PTF: Support IGNORE NULLS in LEAD/LAG functions