This topic describes SELECT AS operations, contains lists of Analytics functions (aggregation, numeric and time/date), and describes the COALESCE expression.
Having created a set of aggregates using a GROUP BY operation, it is typical for Analytics queries to compute one or more derived analytics in each resulting bucket. This is accomplished using SELECT AS operations and Analytics expressions.
Each aggregation operation can declare an arbitrary set of named expressions, sometimes referred to as derived properties, using SELECT AS syntax. These expressions represent aggregate analytic functions that are computed for each aggregated Oracle Commerce Guide Search record in the statement result. Expressions can make use of a broad array of operators, which are described in the following section.
Analytics supports the following aggregation functions:
|
Function |
Description |
|---|---|
|
AVG |
Computes the arithmetic mean value for a field. |
|
COUNT |
Counts the number of records with valid non-null values in a field for each GROUP BY result. |
|
COUNTDISTINCT |
Counts the number of unique, valid non-null values in a field for each GROUP BY result. |
|
MAX |
Finds the maximum value for a field. |
|
MIN |
Finds the minimum value for a field. |
|
MEDIAN |
Finds the median value for a field. |
|
STDDEV |
Computes the standard deviation for a field. |
|
ARB |
Selects an arbitrary but consistent value from the set of values in a field. |
|
SUM |
Computes the sum of field values. |
|
VARIANCE |
Computes the variance (that is, the square of the standard deviation) for a field. |
Analytics supports the following numeric functions:
Time/date functions such as EXTRACT and TRUNC are discussed in the section about temporal properties. These operators may be composed to construct arbitrary, complex derived expressions. As a simple example using the text-based syntax, we could compute the total number of deals executed by each sales representative as follows:
RETURN DealsPerRep AS SELECT COUNT(TransId) AS NumDeals GROUP BY SalesRep
Continuing the example from the "Aggregation/GROUP BY" topic, if we wanted each result aggregate to be assigned the ratio of the average "Amount" value to the maximum "Amount" value as the property "AvgOverMax", we would use the following Java API syntax:
ExprBinary e = new ExprBinary(ExprBinary.DIVIDE,
new ExprAggregate(ExprAggregate.AVG,
new ExprKey("Amount")),
new ExprAggregate(ExprAggregate.MAX,
new ExprKey("Amount")));
SelectList s = new SelectList();
s.add(new Select("AvgOverMax",e));
stmnt.setSelectList(s);The same example in the text-based syntax is:
SELECT AVG(Amount) / MAX(Amount) AS AvgOverMax
The COALESCE expression allows for user-specified null-handling. You can use COALESCE to evaluate records for multiple values and return the first non-null value encountered, in the order specified. The following requirements apply:
Arguments that you specify to COALESCE should all be of the same type
The types of arguments that COALESCE takes are: integer, integer64, double, and string.
COALESCE supports alphanumeric values, but for typing reasons it does not support mixing numeric values and alphanumeric ones.
You cannot specify dimensions as arguments to COALESCE. However, if you have a dimension mapped to a property, you can then specify this property to COALESCE and this will result in a valid query.
The COALESCE expression can only be used in a SELECT clause, and not in other clauses (such as WHERE)
In the following example, all records without a specified price are treated as zero in the computation:
AVG(COALESCE(price, 0))
COALESCE can also be used on its own, for example:
SELECT COALESCE(price, 0) WHERE …
