oracle model 分组,oracle model子句学习笔记例 解析

学习 东方醉仙 的 “oracle model子句学习笔记例”，并记之

总看model子句

SELECT PRODUCT, COUNTRY, YEAR, WEEKS, RECEIPTS, SALE, INVENTORY FROM SALES_FACT

WHERE /*COUNTRY = 'Australia'

AND PRODUCT = 'Xtend Memory'

AND */

YEAR = '2001'

AND WEEKS <= 5

AND ROWNUM <= 50

/*MODEL 标示字*/

MODEL

/*RETURN {ALL|UPDATED} ROWS 返回的结果集是否只包含修改或新增的记录,默认是ALL*/

RETURN UPDATED ROWS

/*PARTITION BY 按参数中的列分组，之后的运算都是分组进行的；参数可以是表达式，但是必须有别名*/

PARTITION BY(PRODUCT, COUNTRY)

/*DIMENSION BY 声明各分组中的维度字段，相当于多维键值对的建，字段值即键；参数可以是表达式，但是必须有别名*/

DIMENSION BY(YEAR, WEEKS)

/*MEASURES 声明各分组中的指标字段，即键值对中的值；参数可以是表达式，但是必须有别名*/

MEASURES(0 INVENTORY, RECEIPTS, SALE)

/*AUTOMATIC ORDER|SEQUENTIAL ORDER 指标计算的顺序，逻辑依赖顺序|书写顺序，默认是SEQUENTIAL ORDER 。*/

RULES AUTOMATIC

ORDER

/*-6.008 = 12.016 + 162.216 - 180.24*/

(INVENTORY [ YEAR, WEEKS ] = NVL(INVENTORY [ CV(YEAR), CV(WEEKS) - 1 ], 0)

/**/

+RECEIPTS [ CV(YEAR), CV(WEEKS) ]

/**/

-SALE [ CV(YEAR), CV(WEEKS) ]

/**/

)

ORDER BY PRODUCT, COUNTRY, WEEKS, RECEIPTS, SALE

excel对应部分公式见 http://download.csdn.net/detail/jgmydsai/6014673

位置标记

SELECT PRODUCT, COUNTRY, YEAR, WEEKS, RECEIPTS, SALE, INVENTORY

FROM SALES_FACT

WHERE COUNTRY = 'Australia'

AND PRODUCT = 'Xtend Memory'

AND YEAR IN ('2001', '2002')

AND WEEKS <= 5

/*MODEL 标示字*/

MODEL

/*RETURN {ALL|UPDATED} ROWS 返回的结果集是否只包含修改或新增的记录,默认是ALL*/

RETURN UPDATED ROWS

/*PARTITION BY 按参数中的列分组，之后的运算都是分组进行的；参数可以是表达式，但是必须有别名*/

PARTITION BY(PRODUCT, COUNTRY)

/*DIMENSION BY 声明各分组中的维度字段，相当于多维键值对的建，字段值即键；参数可以是表达式，但是必须有别名*/

DIMENSION BY(YEAR, WEEKS)

/*MEASURES 声明各分组中的指标字段，即键值对中的值；参数可以是表达式，但是必须有别名*/

MEASURES(0 INVENTORY, RECEIPTS, SALE)

/*AUTOMATIC ORDER|SEQUENTIAL ORDER 指标计算的顺序，逻辑依赖顺序|书写顺序，默认是SEQUENTIAL ORDER 。*/

RULES AUTOMATIC

ORDER

/**/

(INVENTORY [ YEAR, WEEKS ] = NVL(INVENTORY [ CV(YEAR), CV(WEEKS) - 1 ], 0)

/**/

+RECEIPTS [ CV(YEAR), CV(WEEKS) ]

/**/

-SALE [ CV(YEAR), CV(WEEKS) ],

/*原数据中没有2002新增一行*/

SALE [ 2002, 1 ] = 0,

/**/

RECEIPTS [ 2002, 1 ] = 0

/**/

)

符号标记

SELECT PRODUCT, COUNTRY, YEAR, WEEKS, SALE

FROM SALES_FACT

WHERE PRODUCT = 'Xtend Memory'

AND COUNTRY = 'Australia'

MODEL

/*只返回修改过和数据*/

RETURN UPDATED ROWS

PARTITION BY(PRODUCT, COUNTRY)

DIMENSION BY(YEAR, WEEKS)

MEASURES(SALE)

RULES /**/

(SALE [ YEAR IN ('2000', '2001'),

WEEKS IN (1, 2) ] ORDER BY YEAR,

WEEKS = SALE [ CV(YEAR),

CV(WEEKS) ] * 1.2

/**/)

ORDER BY YEAR, WEEKS;

/*等价于*/

SELECT PRODUCT, COUNTRY, YEAR, WEEKS, SALE * 1.2 AS SALE

FROM SALES_FACT

WHERE PRODUCT = 'Xtend Memory'

AND COUNTRY = 'Australia'

AND YEAR IN ('2000', '2001')

AND WEEKS IN (1, 2);

model子句中for循环

SELECT PRODUCT,COUNTRY,YEAR,WEEKS,RECEIPTS,SALE--,INVENTORY

FROM SALES_FACT

WHERE PRODUCT = 'Xtend Memory'

AND COUNTRY = 'Australia'

AND YEAR = '2001'

AND WEEKS <= 10

MODEL RETURN UPDATED ROWS

PARTITION BY (PRODUCT,COUNTRY)

DIMENSION BY (YEAR,WEEKS)

MEASURES(RECEIPTS,SALE,0 INVENTORY)

RULES AUTOMATIC ORDER

(

INVENTORY[YEAR,WEEKS] = NVL(INVENTORY[CV(YEAR),CV(WEEKS)-1],0)

+ RECEIPTS[CV(YEAR),CV(WEEKS)]

- SALE[CV(YEAR),CV(WEEKS)],

/*FOR 循环 符合条件数据改为0*/

SALE[2001,FOR WEEKS FROM 3 TO 10 INCREMENT 1] = 0,

RECEIPTS[2001,FOR WEEKS FROM 3 TO 10 INCREMENT 1] = 0

)

ORDER BY WEEKS,RECEIPTS,SALE

关于return updated rows子句

SELECT PRODUCT,COUNTRY,YEAR,WEEKS,SALE

FROM SALES_FACT

WHERE PRODUCT = 'Xtend Memory'

AND COUNTRY = 'Australia'

MODEL

/*返回所有数据包括修改及未修改的*/

RETURN ALL ROWS

PARTITION BY (PRODUCT,COUNTRY)

DIMENSION BY (YEAR,WEEKS)

MEASURES(SALE)

RULES

(

SALE[YEAR IN('2000','2001'),WEEKS IN(1,2)]

ORDER BY YEAR,WEEKS

= SALE[CV(YEAR),CV(WEEKS)]*1.2

)

ORDER BY YEAR,WEEKS

在model子句中使用聚合函数

SELECT PRODUCT,COUNTRY,YEAR,WEEKS,SALE,AVG_SALE,INVENTORY,MAX_INVTY

FROM SALES_FACT

WHERE PRODUCT = 'Xtend Memory'

AND COUNTRY = 'Australia'

AND YEAR = '2001'

AND WEEKS <= 10

MODEL RETURN UPDATED ROWS

PARTITION BY (PRODUCT,COUNTRY)

DIMENSION BY (YEAR,WEEKS)

MEASURES (RECEIPTS,SALE,0 INVENTORY,0 AVG_SALE,0 MAX_INVTY)

RULES AUTOMATIC ORDER

(

INVENTORY[YEAR,WEEKS] = NVL(INVENTORY[CV(YEAR),CV(WEEKS)-1],0)

+ RECEIPTS[CV(YEAR),CV(WEEKS)]

- SALE[CV(YEAR),CV(WEEKS)],

AVG_SALE[YEAR,ANY] = AVG(SALE)[CV(YEAR),WEEKS],

MAX_INVTY[YEAR,ANY] = MAX(INVENTORY)[CV(YEAR),WEEKS]

)

ORDER BY WEEKS,SALE,INVENTORY