Oracle--model usage

I. what is the model statement

The model statement is a new feature of oracle10g,
The model clause in SQL is an important new feature that Oracle 10g database introduces to the Enterprise intelligence domain. This clause is of great help to accountants who like to come out of Oracle and then put it into spreadsheets for analysis. The purpose of the model clause in SQL is to allow the SQL statement to have the ability to create multidimensional arrays from the normal select results, and then perform the calculation of rows or arrays on this SQL spreadsheet.
The model clause defines a multi-bit array, which is partitioned (partitions), dimensions (dimensions), and metering (measures), by talking about the columns in a query mapped to three groups.
The result set of SQL is processed. The order of execution is located after the having.

Two. Model Usage Scenarios

Model typical usage scenarios.
1. Total Row Append
2. Row and Column transformation
3. Use the front and back lines of the current line
4. Cyclic execution of the Regexp_replace function

Three. Model syntax

MODEL [RETURN [UPDATED | All] Rows][reference models][partition by (<cols>)]dimension by (<cols>) MEASURES (<cols>) [IGNORE NAV ] | [KEEP NAV] [Rules[upsert | Update][automatic ORDER | Sequential ORDER]

Example Description:

CREATE TABLE A asselect ' Lottu ' as vname,1 as vals from dual; SELECT vname,vals from Amodel--partition by () can ignore dimension by (Vals) MEASURES (vname)    RULES (vname[1]= ' 0924 ');

Output Result:
0924 1

Explanation Parameters:

The keyword for the Model:model statement, which must be.
Partition by: Group BY XX
Dimension by n dimension: The meaning of the dimension, which can be understood as an index to an array, must.
Measures: Specifies the column as an array
Rules: Description of various operations on an array

Four. Model return updated rows meaning
Note: The default behavior of the rules is the presence of the update, the non-existent is appended; If you use the model return updated rows, the rows that are updated or inserted by rules are displayed without a
The new row is no longer the result of SQL

INSERT into A VALUES (' LI ', 2); SELECT vname,vals from AMODEL RETURN UPDATED rowsdimension by (Vals) MEASURES (vname) RULES (vname[2]= ' 0924 ');

Five. For example:

For example: The data is as follows

1. A forecast of 2015 of the income is the sum of the previous two years.

SELECT * from B-MODEL RETURN UPDATED ROWS PARTITION by (p_id) DIMENSION by (p_year) MEASURES (p_val) RULES (p_val[' 2015 ']= p_val[']+p_val[' 2013 ']);

2. Forecast operating income data for 2015. Assuming that 2015 years and 1001 of the company's operating income is the sum of its first two years, 1002 of the company's 2015 data than its annual income of twice times. So the model clauses are as follows

SELECT * from B-MODEL RETURN UPDATED ROWS DIMENSION by (p_id,p_year) MEASURES (p_val) RULES (p_val[1001, ']=p_val[1001 ') , ']+p_val[1001 ', ' p_val[1002 ', '        ]=2 ' p_val[1002, ' 2014 ']);

3. Returning data units in a specific range with between and and

SELECT * from B-MODEL RETURN UPDATED ROWS PARTITION by (p_id) DIMENSION by (p_year) MEASURES (p_val) RULES (p_val[' 2015 ']= SUM (p_val) [p_year between ' and ' 2014 ']);

4. Use the for in statement to return a specific range

SELECT * from B-MODEL RETURN UPDATED ROWS PARTITION by (p_id) DIMENSION by (p_year) MEASURES (p_val) RULES (p_val[' 2015 ']= SUM (p_val) [For P_year in (' 2014 ', ' 2013 ')]);
--If P_year is a numeric type, it can also be used for the increment 1 syntax, if other types,
You can also use the method in the IN clause to query the string, such as for P_year in (select year from B)

5. Access all data units using any and is any predicate to access all data units in the array. Any and position tags are combined, is any and symbol tag
For example, a forecast of 2017 business income data, which is the sum of all years;

SELECT * from B-MODEL RETURN UPDATED ROWS PARTITION by (p_id) DIMENSION by (p_year) MEASURES (p_val) RULES (p_val[' 2017 ']= SUM (P_val) [any]);--or any to p_year is any.

6. Get the current value of a dimension with CURRENTV ()
Overwrite Case 2

SELECT * from B-MODEL RETURN UPDATED ROWS DIMENSION by (p_id,p_year) MEASURES (p_val) RULES (p_val[1001, ']=p_val[curr ') Entv (), ']+p_val[currentv ' (), ' n '), p_val[1002, ']=2 * P_VAL[CURRENTV (), ' 2014 ');

7. What are the benefits of using it? Take a look at the following example
Not long ago I saw a problem in the group, and found that the p_value of P_color as ' red ' was more than ' blue ' p_product
This topic is not difficult, you use decode, or case, the blue P_value judgment negative, and then use the group function sum.
If you use model to write, you see there is no big on the feeling.

Select P_roduct,p_value (SELECT * from TB  model return updated rows partition by (P_product) dimension by (P_color) Me Asures (P_value) Rules (p_value[' red-bule ']=p_value[' red ']-p_value[' Blue ')) from  p_vlaue > 0;

Summary: The function is very powerful, the syntax is too complex,

Data--<pro Oracle sql> nineth Chapter.

Oracle--model usage