model字句用来行间计算,可以理解为分析函数的拓展。后面展示一个不用测试数据的例子:
推算中国与美国后续几年GDP
首先看下代码:
SELECT YEAR 年份,
ROUND(GDP, 2) GDP总量,
UP * 100 || '%' GDP增速,
COUNTRY 国家
FROM (select 2015 YEAR, 16197.96 GDP, 0.03 UP, '美国' COUNTRY
from dual
SELECT 2015 YEAR, 10385.66 GDP, 0.06 UP, '中国' COUNTRY
FROM DUAL) --dual 列出2015年中国与美国GDP总量及当年较去年增速
MODEL RETURN UPDATED ROWS--声明model字句
PARTITION BY (COUNTRY)--按COUNTRY字段分区
DIMENSION BY (YEAR)--已YEAR为维度
MEASURES(GDP,UP)–需要计算的字段,与DIMENTION结合为 GDP=FUNCTION(YEAR),UP=FUNCTION(YEAR)
RULES(
UP[FOR YEAR FROM 2016 TO 2040 INCREMENT 1] ORDER BY YEAR= UP[CV(YEAR)-1]- 0.001,--计算公式:今年GDP总量=去年GDP总量*(1+今年的增速)
GDP[FOR YEAR FROM 2016 TO 2040 INCREMENT 1] ORDER BY YEAR= GDP[CV(YEAR)-1]*(1+UP[CV(YEAR)]))--计算公式:今年GDP总量=去年GDP总量*(1+今年的增速)
ORDER BY YEAR,GDP DESC
上述例子说明及SQL步骤
1、网上找到2015年中美两国GDP总量及增速,将其作为子查询的方式提供两条数据,另外说明:两个国家的增速每年都下降0.1%!
2、MODEL RETURN UPDATED ROWS:声明model字句,并且返回更新过的数据行ps:此SQL的查询结果将不包含2015年数据!!!
3、PARTITION BY (COUNTRY):按COUNTRY字段分区,类似于分析函数中的分区
4、DIMENSION BY (YEAR)-:已YEAR为维度
5、MEASURES(GDP,UP):需要计算的字段,与DIMENTION结合为 GDP=FUNCTION(YEAR),UP=FUNCTION(YEAR),即当YEAR为XXX值时,GDP与UP为XXX值
6、RULES:写出第5点中的FUNCTION,例如:UP[FOR YEAR FROM 2016 TO 2040 INCREMENT 1] ORDER BY YEAR= UP[CV(YEAR)-1]- 0.001
--计算公式:今年GDP总量=去年GDP总量*(1+今年的增速)
--通过FOR声明强制构造出YEAR由2016年到2040年数据,YEAR间隔为1
--ORDER BY声明排序,默认情况下model行间运算为序列排序,不会按YEAR进行排序后进行行间运算,或者可使用rules automatic order让优化器自动排序 (需考虑场景)
--RULES中队UP及GDP字段的处理先后顺序需要将UP放在前面,原因是:计算公式:今年GDP总量=去年GDP总量*(1+今年的增速),得到今年增速才能计算总量
--CV(YEAR)表示当前行的YEAR,CV=current value
输出结果:GDP总量单位-万亿
从结果中可以看到中国将在2031年在GDP总量上超越美国!!!
每个年份都有两个国家的数据,这是配合PARTITION子句才说明的!!!当把两个国家的数据合成一行,那么PARTITION子句将不再需要,如以下SQL:
SELECT YEAR 年份,
ROUND(US_GDP, 2) 美国GDP总量,
US_UP * 100 || '%' 美国GDP增速,
ROUND(CHN_GDP, 2) 中国GDP总量,
CHN_UP * 100 || '%' 中国GDP增速
FROM (select 2015 YEAR,
16197.96 US_GDP,
0.03 US_UP,
10385.66 CHN_GDP,
0.06 CHN_UP
from dual)--dual 列出2015年中国与美国GDP总量及当年较去年增速
MODEL RETURN UPDATED ROWS
DIMENSION BY (YEAR)
MEASURES(US_GDP,US_UP,CHN_GDP,CHN_UP)
RULES(
US_UP[FOR YEAR FROM 2016 TO 2040 INCREMENT 1] ORDER BY YEAR= US_UP[CV(YEAR)-1]- 0.001,--今年增速较去年下降0.1%
CHN_UP[FOR YEAR FROM 2016 TO 2040 INCREMENT 1] ORDER BY YEAR= CHN_UP[CV(YEAR)-1]-0.001,--今年增速较去年下降0.1%
US_GDP[FOR YEAR FROM 2016 TO 2040 INCREMENT 1] ORDER BY YEAR= US_GDP[CV(YEAR)-1]*(1+US_UP[CV(YEAR)]),--计算公式:今年GDP总量=去年GDP总量*(1+今年的增速)
CHN_GDP[FOR YEAR FROM 2016 TO 2040 INCREMENT 1] ORDER BY YEAR= CHN_GDP[CV(YEAR)-1]*(1+CHN_UP[CV(YEAR)]))--计算公式:今年GDP总量=去年GDP总量*(1+今年的增速)
输出结果:
感兴趣的同学可以看下面两个链接,对model子句都有详细的说明
http://blog.csdn.net/huangyunzeng2008/article/details/5664172
http://blog.csdn.net/wanghai__/article/details/4783021