Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-30374 Feature Parity between PostgreSQL and Spark (ANSI/SQL)
  3. SPARK-30789

Support (IGNORE | RESPECT) NULLS for LEAD/LAG/NTH_VALUE/FIRST_VALUE/LAST_VALUE

    XMLWordPrintableJSON

Details

    • Sub-task
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 3.2.0
    • 3.2.1
    • SQL
    • None

    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

       

      Attachments

        Activity

          People

            apachespark Apache Spark
            beliefer jiaan.geng
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: