But using FROM syntax, an aggregation operation can specify that its input be obtained from the output of any previously declared statement.
The results of an Analytics statement consist of a set of aggregated records that can be used like any other Oracle Commerce Guide Search records. Because of this, aggregation operations can be layered upon one another. By default, the source of records for an Analytics statement is the result of the containing search and navigation query. But using FROM syntax, an aggregation operation can specify that its input be obtained from the output of any previously declared statement.
Example 4. Example of using FROM syntax
For example, one aggregation query might compute the total number of transactions grouped by "Quarter" and "Sales Rep". Then, a subsequent aggregation can group these results by "Quarter", computing the average number of transactions per "Sales Rep". This example can be expressed in the text-based syntax as:
DEFINE RepQuarters AS SELECT COUNT(TransId) AS NumTrans GROUP BY SalesRep, Quarter ; RETURN Quarters AS SELECT AVG(NumTrans) AS AvgTransPerRep FROM RepQuarters GROUP BY Quarter
RepQuarters produces aggregates of the form { SalesRep, Quarter, NumTrans }, such as:
{ J. Smith, 09Q1, 10 }
{ J. Smith, 09Q2, 3 }
{ F. Jackson, 08Q4, 10 }
...and Quarters returns aggregates of the form { Quarter, AvgTransPerRep }, such as:
{ 083Q4, 10 }
{ 09Q1, 4.5 }
{ 09Q2, 6 }
...The same example using the programmatic API is:
// First, define "SalesRep" "Quarter" buckets with
// "TransId" counts.
Statement repQuarters = new Statement();
repQuarters.setName("RepQuarters");
repQuarters.setShown(false);
GroupByList rqGroupBys = new GroupByList();
rqGroupBys.add(new GroupBy("SalesRep"));
rqGroupBys.add(new GroupBy("Quarter"));
repQuarters.setGroupByList(rqGroupBys);
Expr e = new ExprAggregate(ExprAggregate.COUNT,
new ExprKey("TransId"));
SelectList rqSelects = new SelectList();
rqSelects.add(new Select("NumTrans",e));
repQuarters.setSelectList(rqSelects);
// Now, feed these results into "Quarter" buckets
// computing averages
Statement quarters = new Statement();
quarters.setName("Quarters");
quarters.setFromStatementName("RepQuarters");
GroupByList qGroupBys = new GroupByList();
qGroupBys.add(new GroupBy("Quarter"));
quarters.setGroupByList(qGroupBys);
e = new ExprAggregate(ExprAggregate.AVG,
new ExprKey("NumProducts"));
SelectList qSelects = new SelectList();
qSelects.add(new Select("AvgTransPerRep", e));
quarters.setSelectList(qSelects);
