--存放每个区域2000年和2001年全年的销售情况
SELECT * FROM sales_history
ORDER BY region_id,YEAR,MONTH;
INSERT INTO sales_history VALUES(5,2000,1,100);


--1.model查询的基本元素
--如果rules没有其他选项，则rules关键字是可以省略的
SELECT r, y, m, s
FROM sales_history
WHERE month <= 3
--MODEL子句开始
MODEL
--返回更新的行还是所有行
RETURN UPDATED ROWS
--分区生成多维数组
PARTITION BY (region_id r)
--指定多维数组的维度
DIMENSION BY (year y, month m)
--指定需要做计算的列
MEASURES (sales s)
--指定需要做计算的列的计算方式
RULES  (s[2004, FOR m in (1,2,3)] = (s[2000,CV( )] + s[2001,CV( )]) / 2)
ORDER BY y, r, m;


--2.单元格引用
--2.1符号单?格引用
--1.顺序和dimension by一致，使用比较运算符
--2.缺点是不能造不存在的单元格


符号引用与位置引用的区别：
1.符号引用可以引用多个单元格，而位置引用只能引用单个单元格，符号应用更强大
2.ANY/IS ANY都是符号引用，一样的
3.符号引用可能导致结果不确定，需要在规则里加order BY,但是位置引用的结果一定是确定的
4.符号单元格引用，相当于ANY的，也可以直接写dimension by里的列，不加条件，最好写ANY
SELECT r, y, m, s
FROM sales_history
WHERE month <= 10
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
--y=2004因为不存在，所有没有结果，改为2001可以
--s[y between 2000 and 2001,m=3]
--s[y in (2000,2001),m=3]
--s[y<=2001,m=3]
RULES (s[y=2001,m=3] = 200000)
ORDER BY y, r, m;


--2.2位置单元格引用
--可以引用不存在的单元格
SELECT r,y,m,s
FROM sales_history
WHERE MONTH<=10
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (YEAR y,MONTH m)
MEASURES (sales s)
RULES(s[2004,3]=20000)
ORDER BY y,r,m;


--2.3组合单元格：符号和位置


SELECT r, y, m, s
FROM sales_history
WHERE month <= 10
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2004,3] = (s[2000,3]+s[y=2001,m=3])/2)
ORDER BY y, r, m;


--3.NULL度量和单元格缺失
--默认KEEP NAV
SELECT r, y, m, s
FROM sales_history
WHERE month = 12
MODEL
--KEEP NAV
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2004,12] = (s[2000,12]+s[2001,12])/2)
ORDER BY y, r, m;


--使用IGNORE NAV，给予默认值


--4.UNIQUE DIMENSION/UNIQUE SINGLE REFERENCE
ORA-32638: Non unique addressing in MODEL dimensions 
Cause: The address space defined for the MODEL (partition by and dimension by expressions) do not uniquely identify each cell.


Action: Rewrite the MODEL clause. Using UNIQUE SINGLE REFERENCE option might help.


SELECT * FROM sales_history
ORDER BY region_id,YEAR,MONTH;
INSERT INTO sales_history VALUES(5,2000,12,10000);


--根据分区键值region_id和dimension year和month得到5号区域的2000年12月数据重复
--因此，输入单元格不唯一报错ORA-32638，UNIQUE DIMENSION会做全部输入的检查
--如果改为month=11,那么不会出错，12的已经不在输入源中
SELECT r, y, m, s
FROM sales_history
WHERE month =12
MODEL
IGNORE NAV
UNIQUE dimension
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2004,11] = (s[2000,11]+s[2001,11])/2)
ORDER BY y, r, m;




--UNIQUE SINGLE REFERENCE不会报错，只对rules右边进行检查，因为s[y,11]没有重复的，虽然12有重复的，但是不检查
--提高效率
SELECT r, y, m, s
FROM sales_history
WHERE month =12
MODEL
IGNORE NAV
UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2004,11] = (s[2000,11]+s[2001,11])/2)
ORDER BY y, r, m;


--在rules中的2000,12有重复，还是会报错
SELECT r, y, m, s
FROM sales_history
WHERE month =12
MODEL
IGNORE NAV
UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2004,11] = (s[2000,12]+s[2001,11])/2)
ORDER BY y, r, m;


--month=11,不会报错，虽然2000,12有重复，但是month=11决定整个输入源没有这行单元格
SELECT r, y, m, s
FROM sales_history
WHERE month =11
MODEL
IGNORE NAV
UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2004,11] = (s[2000,12]+s[2001,11])/2)
ORDER BY y, r, m;


--5.return all /updated rows
SELECT r, y, m, s
FROM sales_history
WHERE month = 3
MODEL
RETURN ALL ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES  (s[2004,3] = (s[y=2000,3] + s[2001,3]) / 2)
ORDER BY y, r, m;


SELECT r, y, m, s
FROM sales_history
WHERE month = 3
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES  (s[2004,3] = (s[y=2000,3] + s[2001,3]) / 2)
ORDER BY y, r, m;


--存在的行也会返回更新后的结果
SELECT r, y, m, s
FROM sales_history
WHERE month = 3
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES  (s[2000,3] = (s[y=2000,3] + s[2001,3]) / 2)
ORDER BY y, r, m;


SELECT r, y, m, s
FROM sales_history
WHERE month = 3
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES  (s[2000,3] = (s[y=2000,3] + s[2001),3]) / 2,s[2000,1]=s[2001,3])
ORDER BY y, r, m;



---------------------------------rules------------------------------
--.CV函数，写在外面必须带参数，对应维度列名
SELECT r, y, m, s
FROM sales_history
WHERE month <= 3
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
--这里的CV()自适应左边的维度m的值3，CV函数的计算结果=3
RULES (s[2004, 3] = (s[2006,CV()] + s[2001,CV()]) / 2)
ORDER BY y, r, m;


--ANY关键字
SELECT r, y, m, s
FROM sales_history
WHERE month <= 3
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
--左边ANY,得到所有month<=3的年，右边CV自适应
RULES (s[ANY, 3] = (s[CV(),1] + s[CV(),2]) / 2)
ORDER BY y, r, m;


--符号单元格引用中直接写dimension里的列相当于any
SELECT r, y, m, s
FROM sales_history
WHERE month <= 3
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[y, 3] = (s[CV(),1] + s[CV(),2]) / 2)
ORDER BY y, r, m;


SELECT r, y, m, s
FROM sales_history
WHERE month <= 3
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
--第1个CV适应ANY,第2个CV适应3
RULES (s[ANY, 3] = (s[CV(),CV()] + s[CV(),CV()]) / 2)
ORDER BY y, r, m;


--for loops
--如果使用多条规则，可以逗号分隔，因为下面的例子是对不存在的行进行预测，用符号单元格引用是没有结果的。
SELECT r, y, m, s
FROM sales_history
WHERE month <= 10
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
--RULES (s[2004,2] = (s[2000,3]+s[2001,3])/2,s[2003,2] = (s[2000,3]+s[2001,3])/2)
RULES (s[y IN (2003,2004),2] = (s[2000,3]+s[2001,3])/2)
ORDER BY y, r, m;


--for loops就可以解?这个问题
SELECT r, y, m, s
FROM sales_history
WHERE month <= 10
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[FOR y IN (2003,2004),2] = (s[2000,3]+s[2001,3])/2)
ORDER BY y, r, m;


--子查询
SELECT r, y, m, s
FROM sales_history
WHERE month <= 10
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[FOR (y,m) IN (SELECT 2003,1 FROM dual 
                               UNION ALL SELECT 2004,2 FROM dual)] 
          = (s[2000,3]+s[2001,3])/2)
ORDER BY y, r, m;


SELECT r, y, m, s
FROM sales_history
WHERE month <= 10
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[FOR y 2003,2004),2] = (s[2000,3]+s[2001,3])/2)
ORDER BY y, r, m;


--模式匹配
SELECT r, y, m, s
FROM sales_history
WHERE month <= 10
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[FOR y FROM 2003 TO 2004 INCREMENT  2,2] = (s[2000,3]+s[2001,3])/2)
ORDER BY y, r, m;


--类型不一直，会报越界错误
SELECT r, y, m, s
FROM sales_history
WHERE month <= 10
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[FOR y LIKE '200%' FROM 3 TO 4 INCREMENT  1,2] = (s[2000,3]+s[2001,3])/2)
ORDER BY y, r, m;


SELECT r, y, m, s
FROM sales_history
WHERE month <= 10
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[FOR (y,m) IN (SELECT 2003+LEVEL,1 FROM dual 
                              CONNECT BY LEVEL<10002)] 
          = (s[2000,3]+s[2001,3])/2)
ORDER BY y, r, m;


SELECT r, y, m, s
FROM sales_history
WHERE month <= 10
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[FOR (y,m) IN (SELECT 2003+LEVEL,1 FROM dual 
                              CONNECT BY LEVEL<10001)] 
          = (s[2000,3]+s[2001,3])/2,
          s[1999,1]=s[2000,3])
ORDER BY y, r, m;


--IS ANY,和ANY一样用于左边，都会阻止单元格插入
SELECT r, y, m, s
FROM sales_history
WHERE month = 3
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[y IS ANY, 3] = (s[CV(),3] + s[CV(),3]) / 2)
ORDER BY y, r, m;


--IS PRESENT 判断如果当前单元格在model执行之前源数据存在，不管是否值为NULL，则为true,否则在model执行之前不存在的为false
SELECT r, y, m, s
FROM sales_history
WHERE month = 3
MODEL
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2001, 3] =CASE WHEN s[2004,3] IS PRESENT
                                      THEN s[2004,3]
                             ELSE 0 END )
ORDER BY y, r, m;


--虽然s[2001,12]的值为null,但是还是为真，返回100
SELECT r, y, m, s
FROM sales_history
WHERE month = 12
MODEL
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2000, m=12] =CASE WHEN s[2001，12] IS PRESENT
                                      THEN 100
                             ELSE 0 END )
ORDER BY y, r, m;



SELECT r, y, m, s
FROM sales_history
WHERE month = 3
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2001, 3] =CASE WHEN s[2001,3] IS PRESENT
                                      THEN s[2001,3]
                             ELSE 0 END )
ORDER BY y, r, m;


--PRESENTV 函数仅允许使用在规则右边
--PRESENTV(cell,expr1,expr2)在model执行之前如果单元格cell存在，不管是否是NULL，则单元格的值为expr1,否则expr2
--功能类似于CASE+IS PRESENT
SELECT r, y, m, s
FROM sales_history
WHERE month = 3
MODEL
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2001, 3] =PRESENTV( s[2004,3], s[2004,3],0))
ORDER BY y, r, m;



--PRESENTNNV，参数和使用规则只能在规则右边一样
--NN的含义是NOT NULL，比PRESENTV多了个条件NOT NULL，如果在model执行前单元格存在且非NULL，则返回expr1,否则expr2
--1.存在且非NULL
SELECT r, y, m, s
FROM sales_history
WHERE month = 3
MODEL
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2001, 3] =PRESENTV( s[2001,3], s[2000,3],0))
ORDER BY y, r, m;


--2.s[2000,12]存在但是为NULL，取10
SELECT * FROM  sales_history WHERE MONTH=12;


SELECT r, y, m, s
FROM sales_history
WHERE month = 12
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2001, 12] =PRESENTNNV( s[2000,12], s[2000,12],10))
ORDER BY y, r, m;


--只要求存在即可，s[2000,12]是NULL,返回NULL
SELECT r, y, m, s
FROM sales_history
WHERE month = 12
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2001, 12] =PRESENTV( s[2000,12], s[2000,12],10))
ORDER BY y, r, m;


--规则右边多单元格引用

SELECT r, y, m, s
FROM sales_history
MODEL
UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2004, 3] = AVG(s)[y BETWEEN 1995 AND 2003,3])
ORDER BY y, r, m;



--sequential order
--顺序计算，后面依赖于前面的
SELECT r, y, m, s
FROM sales_history
WHERE month = 3
MODEL
  RETURN UPDATED ROWS
  PARTITION BY (region_id r)
  DIMENSION BY (year y, month m)
  MEASURES (sales s)
  RULES SEQUENTIAL ORDER
  (
    s[2002,3] = (s[2000,3] + s[2001,3])/2,
    s[2003,3] = s[2002,3] * 1.1
  )
ORDER BY y, r, m;


--s[y=2011,3]的前面已经计算出来，覆盖
SELECT r, y, m, s
FROM sales_history
WHERE month = 3
MODEL
  RETURN UPDATED ROWS
  PARTITION BY (region_id r)
  DIMENSION BY (year y, month m)
  MEASURES (sales s)
  RULES SEQUENTIAL ORDER
  (
    s[2011,3] = (s[2000,3] + s[2001,3])/2,
    s[y=2011,3] = 10
  )
ORDER BY y, r, m;


--s[y=2012,3]没有，而且是符号单元格引用，不计算，只显示第1个规则的
SELECT r, y, m, s
FROM sales_history
WHERE month = 3
MODEL
  RETURN UPDATED ROWS
  PARTITION BY (region_id r)
  DIMENSION BY (year y, month m)
  MEASURES (sales s)
  RULES   SEQUENTIAL ORDER
  (
    s[2011,3] = (s[2000,3] + s[2001,3])/2,
    s[y=2012,3] = 10
  )
ORDER BY y, r, m;


--automatic order