oracle model子句學習筆記例 解析

NO IMAGE

學習 東方醉仙 的 “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