MODEL
The MODEL
clause is a feature specific to Oracle SQL and is not supported by all DBMS. It is unique to Oracle and provides advanced, spreadsheet-like calculation capabilities within SQL queries, which is not a standard feature across other SQL databases.
Description
The MODEL
clause in Oracle SQL is a feature that allows to perform inter-row and inter-column calculations in a spreadsheet-like fashion within a SQL query. It provides a way to define a multidimensional array and apply rules to it, enabling complex data manipulations and calculations.
Key Features of the MODEL Clause
Multidimensional Arrays: Treats query results as multidimensional arrays.
Rules-Based Calculations: Allows the definition of rules for calculations that can span across rows and columns.
Cell References: Supports references to other cells in the array, similar to how spreadsheet cells can reference each other.
Basic Syntax
Here is a simplified syntax of the MODEL
clause:
PARTITION BY: Divides the data into partitions, similar to a
GROUP BY
clause.DIMENSION BY: Specifies the dimensions of the array (e.g., rows or columns). In the context of the
MODEL
clause, dimensions act like rows in a spreadsheetMEASURES: Defines the measures or the actual data values that will be manipulated. Measures act like the data cells in a spreadsheet that can be manipulated.
RULES: Contains the rules that define how calculations are performed.
Pseudo Keywords
ANY: This keyword refers to all members within a particular dimension. It's often used in conjunction with aggregate functions like SUM
or AVG
to calculate values across all elements in that dimension.
CURRENT: This keyword refers to the current row being processed within the model. It can be useful for calculations that depend on the values in the current row.
Pseudo Functions
CV() (Current Value): This function retrieves the value of a measure from the previous row within the same dimension partition. It's particularly helpful for calculations that involve comparisons or running totals.
DENSE_RANK() and RANK(): These functions assign ranking positions to members within a dimension, considering potential ties and gaps in the data. They can be used for calculations based on ranking or percentiles.
LAG() and LEAD(): These functions access values from previous or subsequent rows within the same dimension partition, allowing for calculations that depend on past or future values.
Example
Sample Data
Simple example with no calculations or modifications (rules) applied to the data.
Addressing a specific cell
Addressing a range of cells (using a loop)
Following query throws an error
Use of pseudo function cv()
Addressing a range of cells (using a list of values)
Addressing a range of cells (using a subquery)
The pseudo keyword ANY
Addressing cells relative to the current cell
Generating data
Row returning behaviour
Last updated