Package net.pricefx.formulaengine.scripting.queryapi

Interface WindowFunction

public interface WindowFunction
Window function building methods. A window function is a very powerful mean to compute aggregate-like functions over some portion of the row selected by a query. This api provides standard SQL window functions equivalent.

  • Nested Class Summary

    Nested Classes
    Modifier and Type
    Interface
    Description
    static interface 
    WindowFunction.Frame
     

  • Method Summary

    Modifier and Type
    Method
    Description
    Expression
    avg(Expression expression)
    Builds a window function computing the average of the given expression over the frame.
    Expression
    countAll()
    Builds a window function computing the number of rows into the frame.
    Expression
    countNonNull(Expression expression)
    Builds a window function computing the number of non-null expression value over the frame.
    Expression
    cumeDist()
    Builds a window function computing the cumulative distribution, that is (number of partition rows preceding or peers with current row) / (total partition rows).
    Expression
    denseRank()
    Builds a window function computing the rank of the current row, without gaps; this function effectively counts peer groups.
    WindowFunction
    filter(Expression filter)
    Defines a filter to be applied only on the window function.
    Expression
    firstValue(Expression expression)
    Builds a window function computing the value evaluated at the row that is the first row of the window frame.
    Expression
    lag(Expression expression, Expression offset, Expression defaultValue)
    Builds a window function computing value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead returns default (which must be of a type compatible with value).
    Expression
    lastValue(Expression expression)
    Builds a window function computing the value evaluated at the row that is the last row of the window frame.
    Expression
    lead(Expression expression, Expression offset, Expression defaultValue)
    Builds a window function computing value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead returns default (which must be of a type compatible with value).
    Expression
    max(Expression expression)
    Builds a window function computing the maximum of the given expression over the frame.
    Expression
    min(Expression expression)
    Builds a window function computing the minimum of the given expression over the frame.
    Expression
    nthValue(Expression expression, Expression offset)
    Builds a window function computing the value evaluated at the row that is the n'th row of the window frame (counting from 1); returns NULL if there is no such row.
    Expression
    ntile(Expression expression)
    Builds a window function computing an integer ranging from 1 to the argument value, dividing the partition as equally as possible.
    WindowFunction
    partitionBy(List<Expression> partitions)
    Defines the group of rows to be processed separately.
    Expression
    percentRank()
    Builds a window function computing the relative rank of the current row, that is (rank - 1) / (total partition rows - 1).
    Expression
    rank()
    Builds a window function computing the rank of the current row, with gaps; that is, the row_number of the first row in its peer group.
    Expression
    rowNumber()
    Builds a window function computing the number of the current row within its partition, counting from 1
    WindowFunction
    sortBy(List<Orders.Order> orders)
    Defines the ordering of the window.
    Expression
    sum(Expression expression)
    Builds a window function computing the sum of the given expression over the frame.

  • Method Details

    • partitionBy

      WindowFunction partitionBy(List<Expression> partitions)
      Defines the group of rows to be processed separately.

      Like PipelineStage.aggregateBy(Function, Function), each group is defined by an existing unique combination of values of the given list of expression.

      Here is a window function that computes the average cost of each row's category: 

      
 def qapi = api.queryApi()
 def table = qapi.tables().dataSource("costs")
 qapi.source(
     table,
     [
         table.sku,
         table.category,
         table.cost,
         qapi.exprs().window()
             .allRows()
             .partitionBy([table.category])
             .avg(table.cost)
             .as("avgCategoryCost")
     ]
 ).stream { it.toList() }
      Parameters:
      partitions - the list of expressions defining the partition
      Returns:
      the window function building methods
      Since:
      15.0 - Southside

    • filter

      WindowFunction filter(Expression filter)
      Defines a filter to be applied only on the window function.

      Filter can only be used on aggregation functions, i.e. sum(Expression), avg(Expression), countAll(), countNonNull(Expression), min(Expression) and max(Expression).

      For example,here is a window function that computes the number of rows that have a cost lower to 5 in the given category: 

      
 def qapi = api.queryApi()
 def table = qapi.tables().dataSource("costs")
 qapi.source(
     table,
     [
         table.sku,
         table.category,
         table.cost,
         qapi.exprs().window()
             .allRows()
             .partitionBy([table.category])
             .filter(table.cost.lessThan(5))
             .countAll()
             .as("nbLowCost")
     ]
 ).stream { it.toList() }
      Parameters:
      filter - the filter to be applied
      Returns:
      the window function building methods
      Since:
      15.0 - Southside

    • sortBy

      WindowFunction sortBy(List<Orders.Order> orders)
      Defines the ordering of the window.

      Like PipelineStage.sortBy(Function), the ordering is defined as a list of Orders.Order that can be created thanks to QueryApi.orders().

      For example here is a query returning the skus having the 3 lowest cost by category: 

      
 def qapi = api.queryApi()
 def table = qapi.tables().dataSource("costs")
 qapi.source(
     table,
     [
         table.sku,
         table.category
         table.cost,
         qapi.exprs().window()
             .allRows()
             .partitionBy([table.category])
             .sortBy([qapi.orders().ascNullsLast(table.cost)])
             .rank()
             .as("costRank")
     ]
 ).filter(t -> t.costRank.lessOrEqual(3))
 .stream { it.toList() }
      Parameters:
      orders - list of orders
      Returns:
      the window function building methods
      Since:
      15.0 - Southside

    • rank

      Expression rank()
      Builds a window function computing the rank of the current row, with gaps; that is, the row_number of the first row in its peer group.
      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • rowNumber

      Expression rowNumber()
      Builds a window function computing the number of the current row within its partition, counting from 1
      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • denseRank

      Expression denseRank()
      Builds a window function computing the rank of the current row, without gaps; this function effectively counts peer groups.
      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • percentRank

      Expression percentRank()
      Builds a window function computing the relative rank of the current row, that is (rank - 1) / (total partition rows - 1).

      The value thus ranges from 0 to 1 inclusive.

      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • cumeDist

      Expression cumeDist()
      Builds a window function computing the cumulative distribution, that is (number of partition rows preceding or peers with current row) / (total partition rows).

      The value thus ranges from 1/N to 1.

      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • ntile

      Expression ntile(Expression expression)
      Builds a window function computing an integer ranging from 1 to the argument value, dividing the partition as equally as possible.
      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • lag

      Expression lag(Expression expression, Expression offset, Expression defaultValue)
      Builds a window function computing value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead returns default (which must be of a type compatible with value).

      Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to NULL.

      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • lead

      Expression lead(Expression expression, Expression offset, Expression defaultValue)
      Builds a window function computing value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead returns default (which must be of a type compatible with value).

      Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to NULL.

      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • firstValue

      Expression firstValue(Expression expression)
      Builds a window function computing the value evaluated at the row that is the first row of the window frame.
      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • lastValue

      Expression lastValue(Expression expression)
      Builds a window function computing the value evaluated at the row that is the last row of the window frame.
      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • nthValue

      Expression nthValue(Expression expression, Expression offset)
      Builds a window function computing the value evaluated at the row that is the n'th row of the window frame (counting from 1); returns NULL if there is no such row.
      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • sum

      Expression sum(Expression expression)
      Builds a window function computing the sum of the given expression over the frame.
      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • avg

      Expression avg(Expression expression)
      Builds a window function computing the average of the given expression over the frame.
      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • min

      Expression min(Expression expression)
      Builds a window function computing the minimum of the given expression over the frame.
      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • max

      Expression max(Expression expression)
      Builds a window function computing the maximum of the given expression over the frame.
      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • countNonNull

      Expression countNonNull(Expression expression)
      Builds a window function computing the number of non-null expression value over the frame.
      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside

    • countAll

      Expression countAll()
      Builds a window function computing the number of rows into the frame.
      Returns:
      the expression representing this window function
      Since:
      15.0 - Southside