18.4 Iterative Models
Iteration is a powerful tool in mathematical modeling. Some applications involving approximate calculations involve iterative computation. Usually developers resort to procedural languages to implement iteration. The MODEL clause provides a way to write iterate code using SQL.
At times, you may need to evaluate the rules of a model repeatedly, until some sort of condition is met. MODEL's ITERATE subclause provides the required functionality to iterate rules. The syntax of the ITERATE subclause, which is a part of the RULES clause, is:
RULES [UPSERT | UPDATE] [SEQUENTIAL ORDER | AUTOMATIC ORDER] ITERATE (n) [UNTIL (condition)]
The syntax elements are:
- n
-
A positive number specifying the number of iterations.
- condition
-
An early-termination condition.
The early-termination condition is optional. If specified, the condition is evaluated at the end of every iteration. If you specify an early-termination condition, iteration ends when that condition is satisfied, regardless of whatever value you specify for n.
The following example illustrates iteration of rules:
SELECT r, y, m, s
FROM sales_history
MODEL
UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES ITERATE (4)
(
s[2001,3] = s[2001,3] / 2
)
ORDER BY y, r, m;
R Y M S
----- ---------- ---------- ----------
5 2001 3 35405.1875
6 2001 3 39814.4375
7 2001 3 40999.5625In this query, the cell s[2001,3] is computed by dividing s[2001,3] by 2 four times. You can stop the iteration before all four iterations are executed, by using the UNTIL option, as illustrated in the following example:
SELECT r, y, m, s
FROM sales_history
MODEL
UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES ITERATE (4) UNTIL (s[2001,3] < 100000)
(
s[2001,3] = s[2001,3] / 2
)
ORDER BY y, r, m;
R Y M S
----- ---------- ---------- ----------
5 2001 3 70810.375
6 2001 3 79628.875
7 2001 3 81999.125In this latest example, after every iteration, the condition in the UNTIL clause is evaluated. If the condition is false, the next iteration starts; if the condition is true, the iterative evaluation stops. In this case, once the value of the cell s[2001,3] drops below 100,000, the iteration stops. Compare the two preceding examples, and you will find that the second query didn't perform all four iterations.
18.4.1 Knowing how many iterations have occurred
Sometimes it's useful to know how many iterations have occurred. Oracle provides a useful function (also referred to as a system variable) called ITERATION_NUMBER to keep a count of the number of iterations through a set of rules. The function returns the current iteration number. It starts with 0 for the first iteration, and increments by 1 for every subsequent iteration. So, after a query has completed four iterations, ITERATION_NUMBER will return 3 (iterations 0, 1, 2, and 3). The following example illustrates how you can get the iteration number from a query:
SELECT r, y, m, s, i
FROM sales_history
MODEL
UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s, 0 i)
RULES ITERATE (4) UNTIL (s[2001,3] < 100000)
(
s[2001,3] = s[2001,3] / 2,
i[2001,3] = ITERATION_NUMBER
)
ORDER BY y, r, m;
R Y M S I
----- ---------- ---------- ---------- ----------
5 2001 3 70810.375 2
6 2001 3 79628.875 2
7 2001 3 81999.125 2Returning the iteration number takes a bit of innovative coding. In the preceding example, a new measure called i has been introduced to capture the iteration number. This measure is updated by the output of the function ITERATION_NUMBER at every iteration. Since i doesn't correspond to any column of the table, we initialized the measure i to the constant 0; we did that in the MEASURES clause. We could have used any constant value to initialize i, and the output would still be the same. This is because the initial value of the measure i is updated to 0 during the first iteration, and then incremented by 1 each iteration thereafter.
18.4.1.1 Referencing values from the previous iteration
Another useful feature when using iterations is the PREVIOUS function. The PREVIOUS function takes a single cell reference as input, and returns the value of the cell as it existed after the previous iteration, just before the current iteration began. The following example illustrates how you can use the PREVIOUS function to get the value of a cell as it existed after the previous iteration:
SELECT r, y, m, s, i
FROM sales_history
MODEL
UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s, 'a' i)
RULES ITERATE (4)
UNTIL ( (PREVIOUS(s[2001,3]) - s[2001,3]) < 100000 )
(
s[2001,3] = s[2001,3] / 2,
i[2001,3] = ITERATION_NUMBER
)
ORDER BY y, r, m;
R Y M S I
----- ---------- ---------- ---------- -
5 2001 3 70810.375 2
6 2001 3 79628.875 2
7 2001 3 81999.125 2In this example, the previous value of the cell is compared to the current value after every iteration, and if that difference is less than 100,000, then the iteration stops. The PREVIOUS function provides a very useful means of constructing a termination condition. For problems requiring approximation solutions, you can use this approach to iterate till a point when the difference between the previous value and the current value is less than a threshold. You can then assume you have arrived at a reasonably approximate solution. For example, you could calculate pi to a resolution of 1/1000.
Iteration works only with the sequential ordering of rules. If you attempt to use ITERATE along with AUTOMATIC ORDER, you will get an error, as shown in the following example:
SELECT r, y, m, s
FROM sales_history
MODEL
UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES AUTOMATIC ORDER ITERATE (4)
(
s[2001,3] = 20000,
s[2002,3] = s[2001,3] / 2
)
ORDER BY y, r, m;
RULES AUTOMATIC ORDER ITERATE (4)
*
ERROR at line 8:
ORA-32607: invalid ITERATE value in SPREADSHEET clause
