Learn the SQL Model clause
Also see these notes on the
SQL model clause.
Jim Czuprynski has published an excellent
overview of the Oracle SQL “model” clause.
Czuprynski gives an excellent example of the
SQL model clause syntax with explanations:
SELECT state, prod, total_sales
FROM sh.sales_midwest_only
WHERE prod BETWEEN 125 AND 130
GROUP BY state, prod
MODEL
PARTITION BY (state)
DIMENSION BY (prod)
MEASURES (SUM(sold) AS total_sales)
IGNORE NAV
UNIQUE DIMENSION
RULES UPSERT
(
total_sales[99910] =
total_sales[prod=125] + total_sales[prod=126],
total_sales[99920] =
total_sales[prod=127]
+ total_sales[prod=128]
+ total_sales[prod=129]
+ total_sales[prod=130],
total_sales[99999] =
total_sales[99920]
- total_sales[99910]
)
ORDER BY state, prod;
“When this SQL
statement is executed, Oracle
retrieves the
selected result set, places the result set
into memory and then
allows the MODEL clause to divvy up all returned
rows into a
multi-dimensional array.
For example, a
three-dimensional array can be visualized as a cube that has been
cut into several horizontal slices, vertical slices, and layers.
Continuing this cubist analogy (apologies to Pablo Picasso!), the
MODEL clause also lets me apply
rules that perform
inter-row calculations
on the data that is stored at each intersection of these slices and
layers.”
Czuprynski concludes that the SQL model clause
can greatly simplify complex calculations using Oracle SQL:
“Oracle 10g's new inter-row calculation capabilities
significantly expand the already-powerful suite of analytical
functions so that any developer or Oracle DBA can perform
complex, advanced data modeling and reporting in multiple
dimensions, all without the use of third-party software to
perform these analyses.
Oracle 10g's extensions to Structured
Query Language have insured it is among the most robust,
flexible, and fully featured of any of the relational premier
database management systems.”
|