Contents
- Avg() Static Aggregation Function
- Count() Static Aggregation Function
- CountIf() Static Aggregation Function
- Max() Static Aggregation Function
- Min() Static Aggregation Function
- Stdev() Static Aggregation Function
- StdevIf() Static Aggregation Function
- Stdevp() Static Aggregation Function
- StdevpIf() Static Aggregation Function
- Sum() Static Aggregation Function
- SumIf() Static Aggregation Function
TIBCO LiveView releases before 2.0 included a form of table-based aggregation called static aggregation. Starting with release 2.0.0, static aggregation is supported but 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(
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()
The function name is case-sensitive and contains uppercase "C"; it returns the number of rows that meet the query predicate
conditions.
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 (
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 (
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 (
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 (
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 (
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 (
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 (
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 (
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 = ), but uses a
semicolon instead of a colon.
condition ? result-if-true : result-if-false