About
With the SQL MODEL clause, you can define a multidimensional array (See Matrix) on query results and then apply rules (calculations) on the array to calculate new values.
It allows to perform spreadsheet calculation into the database.
Oracle Example - Calculating Sales Differences
SELECT product, country, sales
FROM sales_view
WHERE country IN ('Italy', 'Spain')
GROUP BY product, country
MODEL
PARTITION BY (product) DIMENSION BY (country) MEASURES (SUM(sales) AS sales)
RULES UPSERT
(sales['DIFF ITALY-SPAIN'] = sales['Italy'] - sales['Spain']);
where:
- PARTITION BY: partition the data into bucket
- DIMENSION: define the key for the measure array
- MEASURES: define measures