Page History

...

1. Windowing functions
   • LEAD
     • The number of rows to lead can optionally be specified. If the number of rows to lead is not specified, the lead is one row.
     • Returns null when the lead for the current row extends beyond the end of the window.
   • LAG
     • The number of rows to lag can optionally be specified. If the number of rows to lag is not specified, the lag is one row.
     • Returns null when the lag for the current row extends before the beginning of the window.
   • FIRST_VALUE
   • LAST_VALUE
2. The OVER clause
   • OVER with standard aggregates:
     • COUNT
     • SUM
     • MIN
     • MAX
     • AVG
   • OVER with a PARTITION BY statement with one or more partitioning columns of any primitive datatype.
   • OVER with PARTITION BY and ORDER BY with one or more partitioning and/or ordering columns of any datatype.

     • OVER with a window specification. Windows can be defined separately in a WINDOW clause. Window specifications support these standard options:

       No Format
       ROWS ((CURRENT ROW) | (UNBOUNDED | [num]) PRECEDING) AND (UNBOUNDED | [num]) FOLLOWING

       Note
       The OVER clause supports the following functions, but it does not support a window with them (see HIVE-4797): Ranking functions: Rank, NTile, DenseRank, CumeDist, PercentRank. Lead and Lag functions.

3. Analytics functions
   • RANK
   • ROW_NUMBER
   • DENSE_RANK
   • CUME_DIST
   • PERCENT_RANK
   • NTILE
4. Distinct Support (See support in Hive 2.1.0 and later (see HIVE-9534)

Distinct is supported for aggregation functions including SUM, COUNT and AVG, which aggregates aggregate over the distinct values within each partition. Current implementation has the limitation that no ORDER BY or window specification can be supported in the partitioning clause for performance reason. The supported syntax is as follows.

...

COUNT(DISTINCT a) OVER (PARTITION BY c)

...

Examples

This section provides examples of how to use the Hive QL windowing and analytics functions in SELECT statements. See HIVE-896 for additional examples.

...