PARTITION operator

The PARTITION operator creates a property that implements partition/order or simple distribution.

Syntax

PARTITION 
type [expr1, ..., exprN]
[ORDER [DESC] orderExpr1, ..., orderExprK]
[TOP topExpr] [OFFSET offsetExpr]
[BY groupExpr1, ..., groupExprM]

Where type is defined as:

SUM
PREV
LAST
UNGROUP propertyId distributionType
CUSTOM [NULL] [className] aggrFunc

And distributionType is defined as:

PROPORTION [STRICT] ROUND(digits)
LIMIT [STRICT]

Description

The PARTITION operator creates a property that, for each object collection, either computes an aggregate function over the partition window (SUM, PREV, LAST, CUSTOM) or distributes a value among the object collections of the group (UNGROUP).

The BY block describes the groups into which object collections are split. If the BY block is not specified, all object collections are considered to belong to the same group.

The ORDER block defines the order in which the aggregate function will be calculated or the distribution will take place. If this function is non-commutative, the specified order must be uniquely determined. If a new parameter (not used earlier in the PARTITION and BY options and in the upper context) is declared in the expressions defining the order, the condition of non-NULLness of all these expressions is automatically added when calculating the resulting value.

The TOP and OFFSET blocks restrict the subset of records selected inside each partition: first OFFSET skips the leading records, then the next TOP records are taken in the specified order. Either block may be specified independently.

Parameters

• type

  Type of operation. Can be one of: SUM, PREV, LAST, UNGROUP, CUSTOM.

• propertyId

  ID of the distributed property. The value of this property must be numeric, and the number of parameters must be equal to the number of groups in the BY block. Objects identifying a group are passed to this property as input.

• distributionType

  Distribution strategy. One of:

  • PROPORTION — proportional distribution: the value of propertyId is split among the object collections of the group in proportion to the main expression and rounded to digits decimal places.
  • LIMIT — limit-based distribution: the value of propertyId is assigned to the first object collection up to the limit given by the main expression; the remainder is then assigned to the next collection, and so on.

• STRICT

  The value of propertyId must be split exactly (without remainder) across the object collections of the group. If a remainder remains (which may be negative for PROPORTION), it is added to the first object collection in the ORDER for PROPORTION and to the last object collection in the ORDER for LIMIT.

• digits

  Integer literal specifying the number of decimal places used by PROPORTION rounding.

• NULL

  Specifies that the aggregate may return NULL even when all parameter values are non-NULL.

• className

  Name of the built-in class of the value returned by CUSTOM. If omitted, the result class is inferred from the first main expression (or from the first ORDER expression when the main expression list is empty).

• aggrFunc

  String literal containing the name of a user-defined or DBMS built-in aggregate function.

• expr1, ..., exprN

  Main expressions. For SUM, PREV, LAST, and UNGROUP the list contains exactly one expression: for SUM it is summed cumulatively over the partition window; for PREV and LAST it is taken from the previous-row and current-row respectively (NULL for the first row in the case of PREV); for UNGROUP it defines the proportion (with PROPORTION) or the limit (with LIMIT). For CUSTOM, the list contains the operands passed to aggrFunc; it may be empty, but then the ORDER block is mandatory.

• groupExpr1, ..., groupExprM

  List of group expressions.

• DESC

  Keyword. Specifies a reverse iteration order for object collections.

• orderExpr1, ..., orderExprK

  A list of expressions that define the order in which object collections will be iterated. To determine the order, first the value of the first expression is used; then, if equal, the value of the second is used, etc.

• TOP topExpr

  Within each partition, only the first n records in the partition order will participate in the calculation, where n is the value of the expression topExpr.

• OFFSET offsetExpr

  Within each partition, the first m records in the partition order will be skipped, where m is the value of the expression offsetExpr.

Examples

// determines the place of the team in the conference
CLASS Conference;
conference = DATA Conference (Team);
points = DATA INTEGER (Team);
gamesWon = DATA INTEGER (Team);
place 'Place' (Team team) = PARTITION SUM 1 ORDER DESC points(team), gamesWon(team) BY conference(team);

// building ordinal indexes of objects in the database in ascending order of their internal IDs (i.e., in the order of creation)
index 'Number' (Object o) = PARTITION SUM 1 IF o IS Object ORDER o;

// finds the team next in the conference standings
prevTeam (Team team) = PARTITION PREV team ORDER place(team), team BY conference(team);

// proportional distribution example
CLASS Order;
transportSum 'Freight costs' = DATA NUMERIC[10,2] (Order);

CLASS OrderDetail;
order = DATA Order (OrderDetail) NONULL DELETE;
sum = DATA NUMERIC[14,2] (OrderDetail);

transportSum 'Freight costs by line' (OrderDetail d) = PARTITION UNGROUP transportSum
                                    PROPORTION STRICT ROUND(2) sum(d)
                                    ORDER d
                                    BY order(d);

// example of distribution with limits
discountSum 'Discount' = DATA NUMERIC[10,2] (Order);
discountSum 'Discount by line' (OrderDetail d) =
    PARTITION UNGROUP discountSum
                LIMIT STRICT sum(d)
                ORDER sum(d), d
                BY order(d);
;