[SPARK-30789] Support (IGNORE | RESPECT) NULLS for LEAD/LAG/NTH_VALUE/FIRST_VALUE/LAST_VALUE

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.2.0
• Fix Version/s: 3.2.1
• Component/s: SQL
• Assignee: Apache Spark
• Reporter: Jiaan Geng
• Created: 11/Feb/20 11:13
• Updated: 10/Jan/22 23:28
• Resolved: 30/Dec/20 13:14

Description

All of LEAD/LAG/NTH_VALUE/FIRST_VALUE/LAST_VALUE should support IGNORE NULLS | RESPECT NULLS. For example:

LEAD (value_expr [, offset ])
[ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering )

 

LAG (value_expr [, offset ])
[ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering )

 

NTH_VALUE (expr, offset)
[ IGNORE NULLS | RESPECT NULLS ]
OVER
( [ PARTITION BY window_partition ]
[ ORDER BY window_ordering 
 frame_clause ] )

 

Oracle:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NTH_VALUE.html#GUID-F8A0E88C-67E5-4AA6-9515-95D03A7F9EA0

Redshift
https://docs.aws.amazon.com/redshift/latest/dg/r_WF_NTH.html

Presto
https://prestodb.io/docs/current/functions/window.html

DB2
https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_1513.htm

Teradata
https://docs.teradata.com/r/756LNiPSFdY~4JcCCcR5Cw/GjCT6l7trjkIEjt~7Dhx4w

Snowflake
https://docs.snowflake.com/en/sql-reference/functions/lead.html
https://docs.snowflake.com/en/sql-reference/functions/lag.html

 

Issue Links

• links to: [Github] Pull Request #30943 (beliefer)
• links to: [Github] Pull Request #35079 (beliefer)
• links to: [Github] Pull Request #35079 (beliefer)