From Subqueries
You can use subqueries in
RAQL in the From clause
only. The following example has three levels of subqueries based on the stocks dataset introduced in
Use an
In-Memory Store
to Store and Load Datasets for
MashZone NextGen
Analytics in Getting Started.
Each level within the query builds on the previous level:
1. The innermost subquery groups stocks data by year and quarter and calculates the average volume for each group.
2. This average volume is used in the inner middle subquery to retrieve the previous quarter’s average volume for each row.
3. These two average volumes are then used in the outer middle subquery to calculate the percentage of change using a simple math expression.
4. The final outer query then filters the results to include only those rows where the percentage of change is greater than 15%.
Also of interest in the inner middle subquery is the use of the lag() analytical function to find and add the previous quarter’s average volume to each row. In the outer middel subquery, the nvl() plain function in the equation for the percentage of change handles the first row where the previous quarter value is null.
Use of Aliases and Subqueries
The example shown above also illustrates the use of subqueries to allow the use of aliases in calculations in the Select clause or in conditions in the Where clause. The inner middle subquery defines prev_qtr as an alias. This alias is then used in the subquery one level up in the calculation of the pct_change column.
Similarly, the use of pct_change in the Where clause can only occur in the final outer query because pct_change has been defined in the previous subquery. If the query attempted to use this in Where in the intermediate query, it would fail with an error.
