Window Functions

A window function combines aggregate, ranking, or analytic functions with an over-clause for windowed calculations.

Syntax

window_function ::= aggregate_function over_clause | ranking_function over_clause | analytic_function over_clause

Window functions include:

• aggregate_function: For supported aggregate functions, see Aggregate Functions

  Example: AVG(SALES.AMOUNT_SOLD) OVER (PARTITION BY SALES.CUST_ID)

• ranking_function: Performs ranking operations within a partitioned result set.
  Syntax: ranking_function::= ROW_NUMBER'(' ')'| RANK'(' ')'| DENSE_RANK'(' ')' | PERCENT_RANK'(' ')'

  • ROW_NUMBER(): Assigns a unique number to each row in the result set.
  • RANK(): Assigns a rank to each row, with gaps for ties.
  • DENSE_RANK(): Assigns a rank to each row, without gaps for ties.
  • PERCENT_RANK(): Calculates the relative rank of a row as a percentage

  Example: ROW_NUMBER() OVER (PARTITION BY SALES.CUST_ID ORDER BY SALES.AMOUNT_SOLD DESC NULLS LAST)

• analytic_function: Retrieves values from the previous or next rows in a result set that's based on a specified ordering.

  Syntax: analytic_function::= {LAG | LEAD} '('value_returned_expression[, offset[, default]]')'

  offset::= value_returned_expression

  default::= value_returned_expression

  • LAG(): Returns the value of a previous row based on the offset.
  • LEAD(): Returns the value of a subsequent row based on the offset.
  • offset: Value should be an integer when present. The number of rows back from the current row from which to obtain a value. If not specified, the default is 1.
  • default: Default value that's used for a null value.

  Example: LAG(SALES.AMOUNT_SOLD, 1, 0) OVER (PARTITION BY SALES.CUST_ID ORDER BY SALES.AMOUNT_SOLD DESC)

• over_clause: Specifies the partitioning, ordering, and frame for window functions.
  Syntax:

  over_clause::= OVER '(' [ partition_by_clause] [order_by_clause]  [ frame_clause ]')'
  partition_by_clause::= PARTITION BY value_returned_expression_list
  order_by_clause ::= ORDER BY value_returned_expression [ASC|DESC] [NULLS FIRST|LAST)] 
                               (, value_returned_expression [ASC|DESC] [NULLS(FIRST|LAST)])…
  
  frame_clause::= (ROWS | RANGE) (frame_start | frame_between)
  frame_between::= BETWEEN frame_start AND frame_end
  frame-start::= UNBOUNDED PRECEDING | unsigned_integer PRECEDING  | CURRENT ROW
  frame_end::= UNBOUNDED FOLLOWING | unsigned_integer FOLLOWING  | CURRENT ROW
  

  • partition_by_clause: Groups rows using PARTITION BY expression.
  • order_by_clause: Orders rows using ORDER BY expression.
    • Optionally, specifies whether to sort the rows in ascending or descending order.

      Syntax: [ ASC | DESC ]

    • Optionally, specifies whether NULL values are returned before or after non-NULL values.

      Syntax: NULLS [ FIRST | LAST ]

  • frame_clause: Defines the window frame.
  • frame-start and frame_end:
    • UNBOUNDED PRECEDING: Starts from the first row.
    • unsigned_integer PRECEDING: Starts n rows before the current row.
    • CURRENT ROW: Includes only the current row.
    • UNBOUNDED FOLLOWING: Extends to the last row.
    • unsigned_integer FOLLOWING: Ends n rows after the current row.
  Example:

  SUM(SALES.AMOUNT_SOLD) OVER (PARTITION BY SALES.CUST_ID ORDER BY SALES.TIME_ID ASC ROWS UNBOUNDED PRECEDING AND CURRENT ROW)