Multiple analytics sub-queries can be specified within the context of a single Oracle Commerce Navigation query, each corresponding to a different analytical view, or to a sub-total at a different granularity level.
Statements can be nested within one another to compute layered Analytics.
Additionally, using a special cross-table referencing facility provided by the SELECT expression syntax, expressions can make use of values from other computed statements. This is often useful for when coarser subtotals are required for computing analytics within a finer-grained bucket.
For example, if computing the percent contribution for each sales representative last year, the overall year total is needed to compute the value for each individual rep. Queries such as this can be composed using inter-statement references.
Example 5. Examples of inter-statement references
As an example, suppose we want to compute the percentage of sales per "ProductType" per "Region". One aggregation computes totals grouped by "Region", and a subsequent aggregation computes totals grouped by "Region" and "ProductType". This second aggregation would use expressions that referred to the results from the "Region" aggregation. That is, it would allow each "Region" and "ProductType" pair to compute the percentage of the full "Region" subtotal represented by the "ProductType" in this "Region".
The first statement computes the total product sales for each region:
DEFINE RegionTotals AS SELECT SUM(Amount) AS Total GROUP BY Region
Then, a statement uses the "RegionTotals" results defined above to determine the percentage for each region, making use of the inter-statement reference syntax (square brackets for addressing and dot operator for field selection).
RETURN ProductPcts AS SELECT 100 * SUM(Amount) / RegionTotals[Region].Total AS PctTotal GROUP BY Region, ProductType
The bracket operator specifies that we are referencing the result of RegionTotals statement whose group-by value is equal to the local ProductPcts bucket's value for the Region dimension. The dot operator indicates that we are referencing the Total field in the specified RegionTotals bucket.
The above example makes use of referencing values in a separate statement, but the reference operator can also be used to reference values within the current statement. This is useful for computing trends that change over time, such as year-on-year sales change, which could be expressed as:
RETURN YearOnYearChange AS SELECT SUM(Amount) AS TotalSales, SUM(Amount) - YearOnYearChange[Year-1].TotalSales AS Change GROUP BY Year
This same example expressed using the programmatic API is:
Statement stmnt = new Statement();
stmnt.setName("YearOnYearChange");
GroupByList groupBys = new GroupByList();
groupBys.add(new GroupBy("Year"));
stmnt.setGroupByList(groupBys);
SelectList selects = new SelectList();
Expr totalSales = new ExprAggregate(ExprAggregate.SUM,
new ExprKey("Amount"));
selects.add(new Select("TotalSales",totalSales));
LookupList lookup = new LookupList();
lookup.add(new ExprBinary(ExprBinary.MINUS,
new ExprKey("Year"), new ExprConstant("1")));
Expr change = new ExprBinary(ExprBinary.MINUS, totalSales,
new ExprLookup("YearOnYearChange", "TotalSales", lookup));
selects.add(new Select("Change",change));
stmnt.setSelectList(selects);
