SQL - Model Clause

Data System Architecture

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

Reference







Share this page:
Follow us:
Task Runner