SQL - Model Clause

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Structured Query Language (SQL)

1 - 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.

3 - 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
Advertising

4 - Reference