Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-24869

Implement Respect/Ignore Nulls in lag/lead

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Open
    • Major
    • Resolution: Unresolved
    • None
    • None
    • UDF
    • None

    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 ...
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              kkasa Krisztian Kasa
              Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated: