Legacy Static Aggregation (Deprecated)

TIBCO LiveView releases before 2.0 included a form of table-based aggregation called static aggregation. Static aggregation is deprecated in favor of the much greater feature set of author-time aggregation. TIBCO recommends migrating your LiveView applications that use static aggregation to using author-time aggregation.

Legacy static aggregation allows the use of the static aggregation functions listed on this page, and only these functions, as the initiating function in the query's projection. If you are maintaining static aggregation tables, refer to the sections of this page for documentation of each static aggregation function, as listed in the table of contents above.

Also see the Comparison of Aggregate Functions table.

The following sections describe the use of static aggregation functions in LiveView configuration files for static aggregation tables. These tables used a <field-map> and <field> elements to map a base table to a new aggregate table, as shown in the example for the Avg() function.

Avg() Static Aggregation Function

Avg(expr)

This function computes a value for each record returned by the query predicate or from the base table and returns the mean average of the specified values.

Expression expr must be the name of a column in a LiveView table, or a static StreamBase expression that resolves to or coerces to the name of a column, where that column's data type is int, long, or double. The returned average is a value of the same type.

This example shows an lvconf file that uses static aggregation to configure the average price and total quantity of a group of items. This code is contained in the lvconf file for the ItemsSales table from the Hello LiveView sample.

<data-sources>
  <data-source>
    <aggregation table-ref="ItemsSales">
      <field-map>
          <field ref="category">category</field>
           <!-- Note that the expressions for these fields are not StreamBase
                Expression Language expressions. Refer to the LiveView
                documentation on aggregation functions to know which are currently
                available for use. -->
          <field ref="quantityAvg">Avg(quantityRemaining)</field>
          <field ref="priceAvg">Avg(lastSoldPrice)</field>
          <field ref="totalSales">Count()</field>
          <field ref="totalInventory">Avg(lastSoldPrice*quantityRemaining)</field>
      </field-map>
   </aggregation>
 </data-source>
</data-sources>

Count() Static Aggregation Function

Count()

The function name is case-sensitive and contains uppercase "C"; it returns the number of rows that meet the query predicate conditions.

CountIf() Static Aggregation Function

CountIf (pred)

The function name is case-sensitive and contains uppercase "C" and "I". This function exists only for static (table-based) aggregation; it returns the number of rows that match the specified criteria in the query predicate pred.

Max() Static Aggregation Function

Max (expr)

This function computes a value for each record returned by the query predicate, or from the specified column of a base table, and returns the maximum of the specified values.

Expression expr must be the name of a column in a LiveView table, or a static StreamBase expression that resolves to or coerces to the name of a column, where that column's data type is int, long, or double. The returned value is of the same data type.

Min() Static Aggregation Function

Min (expr)

This function computes a value for each record returned by the query predicate, or from the specified column of a base table, and returns the minimum of the specified values.

Expression expr must be the name of a column in a LiveView table, or a static StreamBase expression that resolves to or coerces to the name of a column, where that column's data type is int, long, or double. The returned value is of the same data type.

Stdev() Static Aggregation Function

Stdev (expr)

This function computes a value for each record returned by the query predicate, or from the specified column of a base table, and returns the standard deviation of those values.

Expression expr must be the name of a column in a LiveView table, or a static StreamBase expression that resolves to or coerces to the name of a column, where that column's data type is int, long, or double. The returned value is of the same data type.

StdevIf() Static Aggregation Function

StdevIf (pred1 ? expr1 ; pred2 ? expr2 ; ... ; default-expr)

The function name is case-sensitive and contains uppercase "S" and "I". This function exists only for static, table-based aggregation.

Computes a value for each record from the specified column of a base table, and returns the standard deviation, subject to constraints.

The constraints are defined as predicates pred1, pred2, and so forth, which must each be a StreamBase expression that resolves to a value of type bool. Expressions expr1, expr2, and so forth must each be a StreamBase expression that resolves to, or coerces to, a value of type double, int, long, or timestamp. If the expressions resolve to numeric types, the expressions and the returned value can be of different types, and StreamBase data type coercion rules apply. In this case, each expression must be of a type that can be promoted to the type of the returned value. Otherwise, all expressions must be of the same type, and the returned value is of that type. The values to compute are determined by evaluation of one or more predicates. For each record from the base table, the predicates are evaluated in left-to-right order. If a predicate evaluates to true, then the computed value for that record is the value of the corresponding expression, and no further predicates are evaluated. If no predicate evaluates to true, then the computed value for that record is the value of the default expression.

Stdevp() Static Aggregation Function

Stdevp (expr)

This function computes a value for each record returned by the query predicate, or from the specified column of a base table, and returns the population standard deviation of those values.

Expression expr must be the name of a column in a LiveView table, or a static StreamBase expression that resolves to or coerces to the name of a column, where that column's data type is int, long, or double. The returned value is of the same data type.

StdevpIf() Static Aggregation Function

StdevpIf (pred1 ? expr1 ; pred2 ? expr2 ; ... ; default-expr)

The function name is case-sensitive and contains uppercase "S" and "I". This function exists only for static (table-based) aggregation.

Computes a value for each record from the specified column of a base table, and returns the population standard deviation, subject to constraints.

The constraints are defined as predicates pred1, pred2, and so forth, which must each be a StreamBase expression that resolves to a value of type bool. Expressions expr1, expr2, and so forth must each be a StreamBase expression that resolves to, or coerces to, a value of type double, int, long, or timestamp. If the expressions resolve to numeric types, the expressions and the returned value can be of different types, and StreamBase data type coercion rules apply. In this case, each expression must be of a type that can be promoted to the type of the returned value. Otherwise, all expressions must be of the same type, and the returned value is of that type. The values to compute are determined by evaluation of one or more predicates. For each record from the base table, the predicates are evaluated in left-to-right order. If a predicate evaluates to true, then the computed value for that record is the value of the corresponding expression, and no further predicates are evaluated. If no predicate evaluates to true, then the computed value for that record is the value of the default expression.

Sum() Static Aggregation Function

Sum (expr )

This function computes a value for each record from the specified column of a base table, and returns the sum of those values.

Expression expr must be the name of a column in a LiveView table, or a static StreamBase expression that resolves to or coerces to the name of a column, where that column's data type is int, long, or double. The returned value is of the same data type.

SumIf() Static Aggregation Function

SumIf (pred1 ? expr1 ; pred2 ? expr2 ; ... ; default-expr)

The function name is case-sensitive and contains uppercase "S" and "I". This function exists only for static (table-based) aggregation.

Computes a value for each record from the specified column of a base table, and returns the sum of those values, subject to constraints.

The constraints are defined as predicates pred1, pred2, and so forth, which must each be a StreamBase expression that resolves to a value of type bool. Expressions expr1, expr2, and so forth must each be a StreamBase expression that resolves to, or coerces to, a value of type double, int, long, or timestamp. If the expressions resolve to numeric types, the expressions and the returned value can be of different types, and StreamBase data type coercion rules apply. In this case, each expression must be of a type that can be promoted to the type of the returned value. Otherwise, all expressions must be of the same type, and the returned value is of that type. The values to compute are determined by evaluation of one or more predicates. For each record from the base table, the predicates are evaluated in left-to-right order. If a predicate evaluates to true, then the computed value for that record is the value of the corresponding expression, and no further predicates are evaluated. If no predicate evaluates to true, then the computed value for that record is the value of the default expression.

Any field references in expressions must match table column names.

If the expressions are timestamps, see Simple Functions: Timestamp Overview for how to add timestamps.

The general form of SumIf, as shown above, is equivalent to the following statement:

if (pred1)
    value = expr1;
else if (pred2)
    value = expr2;
...
else
    value = default-expr;

where value is the computed value for a given record from the base table. SumIf returns the sum of these values.

The simplest form of SumIf has a single predicate:

SumIf (pred ? expr ; default-expr)

This form is analogous to the ternary if-then-else syntax of C and Java (value = condition ? result-if-true : result-if-false), but uses a semicolon instead of a colon.