Oracle分析函式、多維函式和Model函式簡要說明，主要針對BI報表統計
以下程式碼均經過測試，可直接執行
Oracle分析函式、多維函式和Model函式簡要說明，主要針對BI報表統計，不一定很全面，但對BI應用場景做了少許說明
--建立一張銷售數量表，資料趨勢是遞增的
CREATE TABLE ComputerSales AS
SELECT
120+TRUNC(rn/12)+ROUND(DBMS_RANDOM.VALUE(1,10)) SalesNumber
FROM
(
SELECT level,ROWNUM rn
FROM DUAL
CONNECT BY ROWNUM<=120
);
--下面用於比較NULL值和非NULL值的統計，可以看出NULL值情況下的COUNT是存在問題的，所以建議資料庫系統中最好不要使用NULL值列
SELECT
COUNT(*),
COUNT(a.SalesNumber),
COUNT(DISTINCT a.SalesNumber),
SUM(a.SalesNumber),
AVG(a.SalesNumber),
MAX(a.SalesNumber),
MIN(a.SalesNumber)
FROM ComputerSales A;
DELETE FROM ComputerSales WHERE SalesNumber IS NULL;
COMMIT;
INSERT INTO ComputerSales VALUES(NULL);
COMMIT;
INSERT INTO ComputerSales VALUES(NULL);
COMMIT;
SELECT
COUNT(*),
COUNT(a.SalesNumber),
COUNT(DISTINCT a.SalesNumber),
SUM(a.SalesNumber),
AVG(a.SalesNumber),
MAX(a.SalesNumber),
MIN(a.SalesNumber)
FROM ComputerSales A;
SELECT trunc(dbms_random.value(1,101)),
DELETE FROM ComputerSales WHERE SalesNumber IS NULL;
COMMIT;
--建立增加了日期欄位的表
CREATE TABLE ComputerSalesBAK AS
SELECT SalesNumber,TRUNC(SYSDATE)+MOD(A.DateSEQ-1,10) SalesDate
FROM (SELECT SalesNumber,ROW_NUMBER() OVER(ORDER BY ROWID) DateSEQ FROM ComputerSales) A;
DROP TABLE ComputerSales;
RENAME ComputerSalesBAK TO ComputerSales;
--下面是兩種建立方式，構招Area列和日期列
CREATE TABLE ComputerSalesBAK AS
SELECT SalesNumber,TRUNC(SYSDATE)+MOD(A.DateSEQ-1,24) SalesDate,
CASE WHEN TRUNC((DateSEQ-1)/24)=1 THEN '華南地區'
WHEN TRUNC((DateSEQ-1)/24)=2 THEN '華北地區'
WHEN TRUNC((DateSEQ-1)/24)=3 THEN '東北地區'
WHEN TRUNC((DateSEQ-1)/24)=4 THEN '華東地區'
ELSE '其他地區'
END
FROM (SELECT SalesNumber,ROW_NUMBER() OVER(ORDER BY ROWID) DateSEQ FROM ComputerSales) A;
DROP TABLE ComputerSales;
RENAME ComputerSalesBAK TO ComputerSales;
--該例可構造SalesDate和Area的重複資料
CREATE TABLE ComputerSalesBAK AS
SELECT SalesNumber,
TRUNC(SYSDATE)+MOD(A.DateSEQ-1,10) SalesDate,
CASE WHEN AreaSEQ=1 THEN '華南地區'
WHEN AreaSEQ=2 THEN '華北地區'
WHEN AreaSEQ=3 THEN '東北地區'
WHEN AreaSEQ=4 THEN '華東地區'
ELSE '其他地區'
END
FROM (SELECT SalesNumber,ROW_NUMBER() OVER(ORDER BY ROWID) DateSEQ,ROUND(dbms_random.VALUE(1,5)) AreaSEQ FROM ComputerSales) A;
DROP TABLE ComputerSales;
RENAME ComputerSalesBAK TO ComputerSales;
--移動平均值，累計求和，當前視窗平均值，當前視窗求和，以及視窗函式和排序函式的作用域
SELECT
Area,SalesDate,SalesNumber,
MIN(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS min_Area_SalesDate,
MAX(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS max_Area_SalesDate,
AVG(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS avg_Area_SalesDate,
SUM(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS sum_Area_SalesDate,
COUNT(*) OVER (PARTITION BY Area ORDER BY SalesDate) AS count_Area,
MIN(SalesNumber) OVER (PARTITION BY Area) AS min_Area,
MAX(SalesNumber) OVER (PARTITION BY Area) AS max_Area,
AVG(SalesNumber) OVER (PARTITION BY Area) AS avg_Area,
SUM(SalesNumber) OVER (PARTITION BY Area) AS sum_Area,
COUNT(*) OVER (PARTITION BY Area) AS count_Area
FROM ComputerSales
--觀察Rank、Dense_Rank,Row_number,Count的區別
--Rank跳號，Dense_Rank不跳號，Row_number唯一，Count按統計數計也跳號
--如果PARTITION BY和order by 的欄位是唯一的話，則這四個函式沒什麼區別
SELECT
Area,SalesDate,SalesNumber,
RANK() OVER (PARTITION BY Area order by SalesNumber) AS Rank_Area_SalesNumber,
DENSE_RANK() OVER (PARTITION BY Area order by SalesNumber) AS DenseRank_Area_SalesNumber,
ROW_NUMBER() OVER (PARTITION BY Area order by SalesNumber) AS Rownumber_Area_SalesNumber,
COUNT(*) OVER (PARTITION BY Area order by SalesNumber) AS CountAll_Area_SalesNumber,
COUNT(SalesNumber) OVER (PARTITION BY Area order by SalesNumber) AS Count_Area_SalesNumber
FROM ComputerSales
--觀察Lag和Lead的異同，以及Lag引數之間的異同
--預設情況下Lag取前一行的值，Lead取後一行的值
--Lag、lead的第一個引數決定了取行的位置，第二個引數為取不到值時的預設值
SELECT
Area,SalesDate,SalesNumber,
LAG(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS Lag_Area_SalesNumber,
LEAD(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS Lead_Area_SalesNumber,
LAG(SalesNumber,1) OVER (PARTITION BY Area order by SalesDate) AS Lag1_Area_SalesNumber,
LAG(SalesNumber,2) OVER (PARTITION BY Area order by SalesDate) AS Lag2_Area_SalesNumber,
LEAD(SalesNumber,1) OVER (PARTITION BY Area order by SalesDate) AS Lead1_Area_SalesNumber,
LEAD(SalesNumber,2) OVER (PARTITION BY Area order by SalesDate) AS Lead2_Area_SalesNumber,
LAG(SalesNumber,1,0) OVER (PARTITION BY Area order by SalesDate) AS Lag10_Area_SalesNumber,
LAG(SalesNumber,2,1) OVER (PARTITION BY Area order by SalesDate) AS Lag21_Area_SalesNumber,
LEAD(SalesNumber,1,0) OVER (PARTITION BY Area order by SalesDate) AS Lead10_Area_SalesNumber,
LEAD(SalesNumber,2,1) OVER (PARTITION BY Area order by SalesDate) AS Lead21_Area_SalesNumber
FROM ComputerSales
--觀察First_Value和Last_Value的不同
--如果取同一個同組中最大值最小值對應的某列，使用FIRST_VALUE，按照升降序排列即可
--LAST_VALUE有些像兩次分組所求的最後一行
SELECT
Area,SalesDate,SalesNumber,
FIRST_VALUE(SalesDate) OVER (PARTITION BY Area order by SalesNumber) AS FirstValue_Area,
FIRST_VALUE(SalesDate) OVER (PARTITION BY Area order by SalesNumber DESC) AS FirstValue_Area_Desc,
LAST_VALUE(SalesDate) OVER (PARTITION BY Area order by SalesNumber) AS LastValue_Area,
LAST_VALUE(SalesDate) OVER (PARTITION BY Area order by SalesNumber DESC) AS LastValue_Area_Desc
FROM ComputerSales
--與上面不同的是，KEEP需要和DENSE_RANK FIRST |DENSE_RANK LAST配合使用，且取的是相同Area中按SalesNumber排序所獲得最大或最小的值，而上面只是取第一行或最後一行
SELECT Area,SalesDate,SalesNumber,
DENSE_RANK() OVER(PARTITION BY Area ORDER BY SalesNumber) DENSE_RANK,
MIN(SalesDate) KEEP (DENSE_RANK FIRST ORDER BY SalesNumber) OVER(PARTITION BY Area) min_first,
MIN(SalesDate) KEEP (DENSE_RANK LAST ORDER BY SalesNumber) OVER(PARTITION BY Area) min_last,
MAX(SalesDate) KEEP (DENSE_RANK FIRST ORDER BY SalesNumber) OVER(PARTITION BY Area) max_first,
MAX(SalesDate) KEEP (DENSE_RANK LAST ORDER BY SalesNumber) OVER(PARTITION BY Area) max_last
FROM ComputerSales
--CUME_DIST和PERCENT_RANK差不多，都是累計計算比例，只不過計算基準不同，CUME_DIST更符合一般的做法
--NTILE把資料平分為若干份，更適合用來計算四分位上的值
--RATIO_TO_REPORT,則是求當前值在分割槽中的比例，且不能與ORDER BY 合起來使用
--PERCENTILE_DISC和PERCENTILE_CONT，則是給定的比例引數所對應的值，一般使用PERCENTILE_DISC即可
SELECT Area,SalesDate,SalesNumber,
ROUND(CUME_DIST() OVER(PARTITION BY Area ORDER BY SalesNumber),2) cume_dist,
ROUND(PERCENT_RANK() OVER(PARTITION BY Area ORDER BY SalesNumber),2) PERCENT_RANK,
ROUND(RATIO_TO_REPORT(SalesNumber) OVER(PARTITION BY Area),2) RATIO_TO_REPORT,
NTILE(4) OVER(PARTITION BY Area ORDER BY SalesNumber) NTILE,
PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY SalesNumber) OVER(PARTITION BY Area) PERCENTILE_DISC,
PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY SalesNumber) OVER(PARTITION BY Area) PERCENTILE_CONT
FROM ComputerSales
--增加了一列叫銷售額，可以進行相關數理統計
CREATE TABLE ComputerSalesBAK AS
SELECT SalesNumber,
ROUND(SalesNumber*10+5*DBMS_RANDOM.VALUE(1,10)) SalesValue,
TRUNC(SYSDATE)+MOD(A.DateSEQ-1,24) SalesDate,
CASE WHEN TRUNC((DateSEQ-1)/24)=1 THEN '華南地區'
WHEN TRUNC((DateSEQ-1)/24)=2 THEN '華北地區'
WHEN TRUNC((DateSEQ-1)/24)=3 THEN '東北地區'
WHEN TRUNC((DateSEQ-1)/24)=4 THEN '華東地區'
ELSE '其他地區'
END Area
FROM (SELECT SalesNumber,ROW_NUMBER() OVER(ORDER BY ROWID) DateSEQ FROM ComputerSales) A;
DROP TABLE ComputerSales;
RENAME ComputerSalesBAK TO ComputerSales;
SELECT * FROM ComputerSales;
--其他統計,對數理分析有研究的同學可以嘗試一下其經濟學含義
SELECT Area,SalesDate,SalesValue,SalesNumber,
REGR_SLOPE(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "斜率",
REGR_INTERCEPT(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "截距",
REGR_R2(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "迴歸線決定係數",
REGR_AVGX(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "迴歸線自變數平均值",
REGR_AVGY(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "迴歸線應變數平均值",
VAR_POP(SalesValue) OVER(PARTITION BY Area ORDER BY SalesDate) "VAR_POP_應變數",
VAR_POP(SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "VAR_POP_自變數",
COVAR_POP(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "COVAR_POP",
REGR_SXX(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "REGR_SXX", --REGR_COUNT(expr1, expr2) * VAR_POP(expr2)
REGR_SYY(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "REGR_SXY", --REGR_COUNT(expr1, expr2) * VAR_POP(expr1)
REGR_SXY(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "REGR_SXY", --REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)
REGR_COUNT(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "REGR_COUNT"
FROM ComputerSales
--關於按日期進行環比的問題
--同比則有麻煩，因為日期天數是不固定的
--從ComputerSales隨機刪除幾行再測
SELECT AREA,SALESDATE,SALESNUMBER,
LAG(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS Lag_error, --如遇斷號，會導致資料不準
SUM(SalesNumber) OVER (PARTITION BY AREA ORDER BY SALESDATE RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING) yesterday, --昨天的值
SUM(SalesNumber) OVER (PARTITION BY AREA ORDER BY SALESDATE RANGE BETWEEN 6 PRECEDING AND 6 PRECEDING) lastweek, --上週資料
SUM(SalesNumber) OVER (PARTITION BY AREA ORDER BY SALESDATE RANGE BETWEEN 6 PRECEDING AND 0 PRECEDING) last7_accu, --前7天累計，包括當天
SUM(SalesNumber) OVER (PARTITION BY AREA ORDER BY SALESDATE RANGE BETWEEN 29 PRECEDING AND 0 PRECEDING) last30_accu--前30天累計，包括當天
FROM ComputerSales
--再度增加一個product產品列，以方便進行CUBE函式演示
CREATE TABLE ComputerSalesBAK AS
SELECT SalesNumber,
ROUND(SalesNumber*10+5*DBMS_RANDOM.VALUE(1,10)) SalesValue,
TRUNC(SYSDATE)+MOD(A.DateSEQ-1,24) SalesDate,
CASE WHEN TRUNC((DateSEQ-1)/24)=1 THEN '華南地區'
WHEN TRUNC((DateSEQ-1)/24)=2 THEN '華北地區'
WHEN TRUNC((DateSEQ-1)/24)=3 THEN '東北地區'
WHEN TRUNC((DateSEQ-1)/24)=4 THEN '華東地區'
ELSE '其他地區'
END Area,
CASE WHEN ROUND(DBMS_RANDOM.VALUE(1,3))=1 THEN '產品A'
WHEN ROUND(DBMS_RANDOM.VALUE(1,3))=2 THEN '產品B'
ELSE '產品C'
END Product
FROM (SELECT SalesNumber,ROW_NUMBER() OVER(ORDER BY ROWID) DateSEQ FROM ComputerSales) A;
DROP TABLE ComputerSales;
RENAME ComputerSalesBAK TO ComputerSales;
SELECT * FROM ComputerSales;
--傳統的group by語法
SELECT Product,Area,SalesDate,SUM(SalesNumber),SUM(SalesValue)
FROM ComputerSales
GROUP BY Product,Area,SalesDate
ORDER BY Product,Area,SalesDate
--ROLLUP (group的欄位順序）
--會自動按Group欄位分層統計，與日常報表較為相似
SELECT Product,Area,SalesDate,SUM(SalesNumber),SUM(SalesValue)
FROM ComputerSales
GROUP BY ROLLUP(Product,Area,SalesDate)
ORDER BY Product,Area,SalesDate --加不加均可，已經自動按分組欄位排序
--等價於
SELECT * FROM
(
SELECT Product,Area,SalesDate,SUM(SalesNumber) SalesNumber,SUM(SalesValue) SalesValue --最大級分組
FROM ComputerSales
GROUP BY Product,Area,SalesDate
UNION ALL
SELECT Product,Area,NULL,SUM(SalesNumber),SUM(SalesValue) --按產品、地區分組
FROM ComputerSales
GROUP BY Product,Area,NULL
UNION ALL
SELECT Product,NULL,NULL,SUM(SalesNumber),SUM(SalesValue) --按產品分組
FROM ComputerSales
GROUP BY Product,NULL,NULL
UNION ALL
SELECT NULL,NULL,NULL,SUM(SalesNumber),SUM(SalesValue) --統計總和
FROM ComputerSales
GROUP BY NULL,NULL,NULL
) ORDER BY 1,2,3 --最後再排序
--CUBE (group的欄位順序），與OLAP比較相似，求得所有維度的交匯點
--會自動按Group欄位排列組合進行統計
SELECT Product,Area,SalesDate,SUM(SalesNumber),SUM(SalesValue)
FROM ComputerSales
GROUP BY CUBE(Product,Area,SalesDate)
ORDER BY Product,Area,SalesDate --加不加均可，已經自動按分組欄位排序
--兩則的區別
--即ROLLUP 為C(3,1)即多了3層
--按照Product,Area,SalesDate；Product,Area；Product；ALL的順序進行了統計
--CUBE的統計層級則為2的N次方，即全部的有序組合
--按照Product,Area,SalesDate;Product,Area;Product,SalesDate;Product;Area,SalesDate;Area;SalesDate;ALL的順序進行了統計
--與ROLLUP的等價表示式，相當於ROLLUP的排列組合
SELECT * FROM
(
SELECT Product,Area,SalesDate,SUM(SalesNumber),SUM(SalesValue) --先按Product,Area,SalesDate求ROLLUP
FROM ComputerSales
GROUP BY ROLLUP(Product,Area,SalesDate)
UNION
SELECT Product,NULL,SalesDate,SUM(SalesNumber),SUM(SalesValue) --再按Product,SalesDate求ROLLUP
FROM ComputerSales
GROUP BY ROLLUP(Product,NULL,SalesDate)
UNION
SELECT NULL,Area,SalesDate,SUM(SalesNumber),SUM(SalesValue) --再按Area,SalesDate求ROLLUP
FROM ComputerSales
GROUP BY ROLLUP(NULL,Area,SalesDate)
UNION
SELECT NULL,NULL,SalesDate,SUM(SalesNumber),SUM(SalesValue) --最後按SalesDate求ROLLUP
FROM ComputerSales
GROUP BY ROLLUP(NULL,NULL,SalesDate)
)
ORDER BY 1,2,3
--GROUPING SETS等同於按三列單獨求統計，一般不常用
SELECT Product,Area,SalesDate,SUM(SalesNumber),SUM(SalesValue)
FROM ComputerSales
GROUP BY GROUPING SETS(Product,Area,SalesDate)
ORDER BY Product,Area,SalesDate ;--加不加均可，已經自動按分組欄位排序
--等價於
SELECT * FROM
(
SELECT Product,NULL Area,NULL SalesDate,SUM(SalesNumber),SUM(SalesValue) --按產品分組
FROM ComputerSales
GROUP BY Product,NULL,NULL
UNION ALL
SELECT NULL,Area,NULL,SUM(SalesNumber),SUM(SalesValue) --按地區分組
FROM ComputerSales
GROUP BY NULL,Area,NULL
UNION ALL
SELECT NULL,NULL,SalesDate,SUM(SalesNumber) SalesNumber,SUM(SalesValue) SalesValue --按日期分組
FROM ComputerSales
GROUP BY NULL,NULL,SalesDate
) ORDER BY 1,2,3
--GROUPING函式只接受一個引數，引數為資料表的一列。如果該列為空返回1，否則返回0。
--並且它僅能與 GROUP BY,ROLLUP,CUBE,GROUPING SETS 一起使用。
--稍微執行一下，就發現該函式只是為了做BI報表使用的，把統計行變為1，將來用作字串替代
SELECT GROUPING(Product), Product,GROUPING(Area),Area,GROUPING(SalesDate),SalesDate,SUM(SalesNumber),SUM(SalesValue)
FROM ComputerSales
GROUP BY ROLLUP(Product,Area,SalesDate)
ORDER BY Product,Area,SalesDate ;
--BI標準報表格式
SELECT
DECODE(ProductFlag,1,'產品彙總',Product),
DECODE(AreaFlag,1,'地區彙總',Area),
DECODE(SalesDateFlag,1,'日期彙總',TO_CHAR(SalesDate,'YYYY-MM-DD')),
SalesNumber,SalesValue
FROM
(
SELECT
GROUPING(Product) ProductFlag, Product,
GROUPING(Area) AreaFlag,Area,
GROUPING(SalesDate) SalesDateFlag,SalesDate,
SUM(SalesNumber) SalesNumber,SUM(SalesValue) SalesValue
FROM ComputerSales
GROUP BY ROLLUP(Product,Area,SalesDate)
ORDER BY Product,Area,SalesDate
)
--GROUPING_ID其實和GROUPING原理差不多，GROUPING引數為單值，且只返回1,1
--GROUPING_ID,則返回按2的指數進行累計得到空值區域的值
SELECT Product,Area,SalesDate,
GROUPING_ID(Product,Area,SalesDate) GROUPING421,
GROUPING_ID(Product,Area) GROUPPING21,
GROUPING_ID(Product) GROUPING1,
SUM(SalesNumber),
SUM(SalesValue)
FROM ComputerSales
GROUP BY ROLLUP(Product,Area,SalesDate)
ORDER BY Product,Area,SalesDate ;--加不加均可，已經自動按分組欄位排序
--GROUP_ID函式可以區分重複分組結果，第1 次出現為0，以後每次出現增1。
--GROUP_ID單獨答應在SELECT 中出現意義不大，常在HAVING 中使用達到過濾重複統計的目的。
SELECT Product,Area,SalesDate,GROUP_ID(),
SUM(SalesNumber),SUM(SalesValue)
FROM ComputerSales
GROUP BY CUBE(Product,Area),CUBE(Product,SalesDate)
HAVING GROUP_ID()=0
ORDER BY 1,2,3
--例如該例子中分別按Product,Area和Product,SalesDate會導致產品地區、產品時間的重複計算，導致報表的不清晰
--我們用HAVING GROUP_ID()=0把重複計算的行去掉就OK了
--一般情況下不建議報表程式過度分組，否則到最後連自己都搞糊塗了
--GROUP BY,ROLLUP,CUBE能組合使用，但SELECT中的分組欄位必須出現在GROUP BY的相關欄位
--MODEL:MODEL語句的關鍵字，必須。
--DIMENSION BY: DIMENSION維度的意思，可以理解為陣列的索引，必須。
--MEASURES:指定作為陣列的列
--RULES:對陣列進行各種操作的描述。
--暫時還沒搞明白如何應用，只是簡單實現了一個求上月、前30天、前7天，前1天的例子
SELECT AREA,PRODUCT,SALESDATE,SALESNUMBER,
AVG30DAY,AVG1MONTH, --最近30天的平均值，最近一個月的平均值
ACCU30DAY,ACCU1MONTH, --最近30天的累加值，最近一個月的累加值
SALESNUMBER1DAY,SALESNUMBER7DAY, --昨天的銷售額，一週前的銷售額
SALESNUMBER30DAY,SALESNUMBER1MONTH --30天的銷售額，上月同天的銷售額
FROM ComputerSales
MODEL DIMENSION BY (AREA,PRODUCT,SALESDATE)
MEASURES (SALESNUMBER,0 AVG30DAY,0 AVG1MONTH,0 ACCU30DAY,0 ACCU1MONTH,0 SALESNUMBER1DAY,0 SALESNUMBER7DAY,0 SALESNUMBER30DAY,0 SALESNUMBER1MONTH)
RULES UPDATE
(AVG30DAY[ANY,ANY,ANY]=AVG(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN CV(SALESDATE)-29 AND CV(SALESDATE)],
AVG1MONTH[ANY,ANY,ANY]=AVG(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN ADD_MONTHS(CV(SALESDATE),-1) AND CV(SALESDATE)],
ACCU30DAY[ANY,ANY,ANY]=SUM(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN CV(SALESDATE)-30 AND CV(SALESDATE)],
ACCU1MONTH[ANY,ANY,ANY]=SUM(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN ADD_MONTHS(CV(SALESDATE),-1) AND CV(SALESDATE)],
SALESNUMBER1DAY[ANY,ANY,ANY]=MAX(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN CV(SALESDATE)-1 AND CV(SALESDATE)-1],
SALESNUMBER7DAY[ANY,ANY,ANY]=MAX(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN CV(SALESDATE)-7 AND CV(SALESDATE)-7],
SALESNUMBER30DAY[ANY,ANY,ANY]=MAX(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN CV(SALESDATE)-30 AND CV(SALESDATE)-30],
SALESNUMBER1MONTH[ANY,ANY,ANY]=MAX(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN CV(SALESDATE)-30 AND CV(SALESDATE)-30]
)
ORDER BY 1,2,3
[ 本帖最後由 bq_wang 於 2011-6-6 19:01 編輯 ]
Oracle分析函式、多維函式和Model函式簡要說明，主要針對BI報表統計，不一定很全面，但對BI應用場景做了少許說明
--建立一張銷售數量表，資料趨勢是遞增的
CREATE TABLE ComputerSales AS
SELECT
120+TRUNC(rn/12)+ROUND(DBMS_RANDOM.VALUE(1,10)) SalesNumber
FROM
(
SELECT level,ROWNUM rn
FROM DUAL
CONNECT BY ROWNUM<=120
);
--下面用於比較NULL值和非NULL值的統計，可以看出NULL值情況下的COUNT是存在問題的，所以建議資料庫系統中最好不要使用NULL值列
SELECT
COUNT(*),
COUNT(a.SalesNumber),
COUNT(DISTINCT a.SalesNumber),
SUM(a.SalesNumber),
AVG(a.SalesNumber),
MAX(a.SalesNumber),
MIN(a.SalesNumber)
FROM ComputerSales A;
DELETE FROM ComputerSales WHERE SalesNumber IS NULL;
COMMIT;
INSERT INTO ComputerSales VALUES(NULL);
COMMIT;
INSERT INTO ComputerSales VALUES(NULL);
COMMIT;
SELECT
COUNT(*),
COUNT(a.SalesNumber),
COUNT(DISTINCT a.SalesNumber),
SUM(a.SalesNumber),
AVG(a.SalesNumber),
MAX(a.SalesNumber),
MIN(a.SalesNumber)
FROM ComputerSales A;
SELECT trunc(dbms_random.value(1,101)),
DELETE FROM ComputerSales WHERE SalesNumber IS NULL;
COMMIT;
--建立增加了日期欄位的表
CREATE TABLE ComputerSalesBAK AS
SELECT SalesNumber,TRUNC(SYSDATE)+MOD(A.DateSEQ-1,10) SalesDate
FROM (SELECT SalesNumber,ROW_NUMBER() OVER(ORDER BY ROWID) DateSEQ FROM ComputerSales) A;
DROP TABLE ComputerSales;
RENAME ComputerSalesBAK TO ComputerSales;
--下面是兩種建立方式，構招Area列和日期列
CREATE TABLE ComputerSalesBAK AS
SELECT SalesNumber,TRUNC(SYSDATE)+MOD(A.DateSEQ-1,24) SalesDate,
CASE WHEN TRUNC((DateSEQ-1)/24)=1 THEN '華南地區'
WHEN TRUNC((DateSEQ-1)/24)=2 THEN '華北地區'
WHEN TRUNC((DateSEQ-1)/24)=3 THEN '東北地區'
WHEN TRUNC((DateSEQ-1)/24)=4 THEN '華東地區'
ELSE '其他地區'
END
FROM (SELECT SalesNumber,ROW_NUMBER() OVER(ORDER BY ROWID) DateSEQ FROM ComputerSales) A;
DROP TABLE ComputerSales;
RENAME ComputerSalesBAK TO ComputerSales;
--該例可構造SalesDate和Area的重複資料
CREATE TABLE ComputerSalesBAK AS
SELECT SalesNumber,
TRUNC(SYSDATE)+MOD(A.DateSEQ-1,10) SalesDate,
CASE WHEN AreaSEQ=1 THEN '華南地區'
WHEN AreaSEQ=2 THEN '華北地區'
WHEN AreaSEQ=3 THEN '東北地區'
WHEN AreaSEQ=4 THEN '華東地區'
ELSE '其他地區'
END
FROM (SELECT SalesNumber,ROW_NUMBER() OVER(ORDER BY ROWID) DateSEQ,ROUND(dbms_random.VALUE(1,5)) AreaSEQ FROM ComputerSales) A;
DROP TABLE ComputerSales;
RENAME ComputerSalesBAK TO ComputerSales;
--移動平均值，累計求和，當前視窗平均值，當前視窗求和，以及視窗函式和排序函式的作用域
SELECT
Area,SalesDate,SalesNumber,
MIN(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS min_Area_SalesDate,
MAX(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS max_Area_SalesDate,
AVG(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS avg_Area_SalesDate,
SUM(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS sum_Area_SalesDate,
COUNT(*) OVER (PARTITION BY Area ORDER BY SalesDate) AS count_Area,
MIN(SalesNumber) OVER (PARTITION BY Area) AS min_Area,
MAX(SalesNumber) OVER (PARTITION BY Area) AS max_Area,
AVG(SalesNumber) OVER (PARTITION BY Area) AS avg_Area,
SUM(SalesNumber) OVER (PARTITION BY Area) AS sum_Area,
COUNT(*) OVER (PARTITION BY Area) AS count_Area
FROM ComputerSales
--觀察Rank、Dense_Rank,Row_number,Count的區別
--Rank跳號，Dense_Rank不跳號，Row_number唯一，Count按統計數計也跳號
--如果PARTITION BY和order by 的欄位是唯一的話，則這四個函式沒什麼區別
SELECT
Area,SalesDate,SalesNumber,
RANK() OVER (PARTITION BY Area order by SalesNumber) AS Rank_Area_SalesNumber,
DENSE_RANK() OVER (PARTITION BY Area order by SalesNumber) AS DenseRank_Area_SalesNumber,
ROW_NUMBER() OVER (PARTITION BY Area order by SalesNumber) AS Rownumber_Area_SalesNumber,
COUNT(*) OVER (PARTITION BY Area order by SalesNumber) AS CountAll_Area_SalesNumber,
COUNT(SalesNumber) OVER (PARTITION BY Area order by SalesNumber) AS Count_Area_SalesNumber
FROM ComputerSales
--觀察Lag和Lead的異同，以及Lag引數之間的異同
--預設情況下Lag取前一行的值，Lead取後一行的值
--Lag、lead的第一個引數決定了取行的位置，第二個引數為取不到值時的預設值
SELECT
Area,SalesDate,SalesNumber,
LAG(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS Lag_Area_SalesNumber,
LEAD(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS Lead_Area_SalesNumber,
LAG(SalesNumber,1) OVER (PARTITION BY Area order by SalesDate) AS Lag1_Area_SalesNumber,
LAG(SalesNumber,2) OVER (PARTITION BY Area order by SalesDate) AS Lag2_Area_SalesNumber,
LEAD(SalesNumber,1) OVER (PARTITION BY Area order by SalesDate) AS Lead1_Area_SalesNumber,
LEAD(SalesNumber,2) OVER (PARTITION BY Area order by SalesDate) AS Lead2_Area_SalesNumber,
LAG(SalesNumber,1,0) OVER (PARTITION BY Area order by SalesDate) AS Lag10_Area_SalesNumber,
LAG(SalesNumber,2,1) OVER (PARTITION BY Area order by SalesDate) AS Lag21_Area_SalesNumber,
LEAD(SalesNumber,1,0) OVER (PARTITION BY Area order by SalesDate) AS Lead10_Area_SalesNumber,
LEAD(SalesNumber,2,1) OVER (PARTITION BY Area order by SalesDate) AS Lead21_Area_SalesNumber
FROM ComputerSales
--觀察First_Value和Last_Value的不同
--如果取同一個同組中最大值最小值對應的某列，使用FIRST_VALUE，按照升降序排列即可
--LAST_VALUE有些像兩次分組所求的最後一行
SELECT
Area,SalesDate,SalesNumber,
FIRST_VALUE(SalesDate) OVER (PARTITION BY Area order by SalesNumber) AS FirstValue_Area,
FIRST_VALUE(SalesDate) OVER (PARTITION BY Area order by SalesNumber DESC) AS FirstValue_Area_Desc,
LAST_VALUE(SalesDate) OVER (PARTITION BY Area order by SalesNumber) AS LastValue_Area,
LAST_VALUE(SalesDate) OVER (PARTITION BY Area order by SalesNumber DESC) AS LastValue_Area_Desc
FROM ComputerSales
--與上面不同的是，KEEP需要和DENSE_RANK FIRST |DENSE_RANK LAST配合使用，且取的是相同Area中按SalesNumber排序所獲得最大或最小的值，而上面只是取第一行或最後一行
SELECT Area,SalesDate,SalesNumber,
DENSE_RANK() OVER(PARTITION BY Area ORDER BY SalesNumber) DENSE_RANK,
MIN(SalesDate) KEEP (DENSE_RANK FIRST ORDER BY SalesNumber) OVER(PARTITION BY Area) min_first,
MIN(SalesDate) KEEP (DENSE_RANK LAST ORDER BY SalesNumber) OVER(PARTITION BY Area) min_last,
MAX(SalesDate) KEEP (DENSE_RANK FIRST ORDER BY SalesNumber) OVER(PARTITION BY Area) max_first,
MAX(SalesDate) KEEP (DENSE_RANK LAST ORDER BY SalesNumber) OVER(PARTITION BY Area) max_last
FROM ComputerSales
--CUME_DIST和PERCENT_RANK差不多，都是累計計算比例，只不過計算基準不同，CUME_DIST更符合一般的做法
--NTILE把資料平分為若干份，更適合用來計算四分位上的值
--RATIO_TO_REPORT,則是求當前值在分割槽中的比例，且不能與ORDER BY 合起來使用
--PERCENTILE_DISC和PERCENTILE_CONT，則是給定的比例引數所對應的值，一般使用PERCENTILE_DISC即可
SELECT Area,SalesDate,SalesNumber,
ROUND(CUME_DIST() OVER(PARTITION BY Area ORDER BY SalesNumber),2) cume_dist,
ROUND(PERCENT_RANK() OVER(PARTITION BY Area ORDER BY SalesNumber),2) PERCENT_RANK,
ROUND(RATIO_TO_REPORT(SalesNumber) OVER(PARTITION BY Area),2) RATIO_TO_REPORT,
NTILE(4) OVER(PARTITION BY Area ORDER BY SalesNumber) NTILE,
PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY SalesNumber) OVER(PARTITION BY Area) PERCENTILE_DISC,
PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY SalesNumber) OVER(PARTITION BY Area) PERCENTILE_CONT
FROM ComputerSales
--增加了一列叫銷售額，可以進行相關數理統計
CREATE TABLE ComputerSalesBAK AS
SELECT SalesNumber,
ROUND(SalesNumber*10+5*DBMS_RANDOM.VALUE(1,10)) SalesValue,
TRUNC(SYSDATE)+MOD(A.DateSEQ-1,24) SalesDate,
CASE WHEN TRUNC((DateSEQ-1)/24)=1 THEN '華南地區'
WHEN TRUNC((DateSEQ-1)/24)=2 THEN '華北地區'
WHEN TRUNC((DateSEQ-1)/24)=3 THEN '東北地區'
WHEN TRUNC((DateSEQ-1)/24)=4 THEN '華東地區'
ELSE '其他地區'
END Area
FROM (SELECT SalesNumber,ROW_NUMBER() OVER(ORDER BY ROWID) DateSEQ FROM ComputerSales) A;
DROP TABLE ComputerSales;
RENAME ComputerSalesBAK TO ComputerSales;
SELECT * FROM ComputerSales;
--其他統計,對數理分析有研究的同學可以嘗試一下其經濟學含義
SELECT Area,SalesDate,SalesValue,SalesNumber,
REGR_SLOPE(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "斜率",
REGR_INTERCEPT(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "截距",
REGR_R2(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "迴歸線決定係數",
REGR_AVGX(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "迴歸線自變數平均值",
REGR_AVGY(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "迴歸線應變數平均值",
VAR_POP(SalesValue) OVER(PARTITION BY Area ORDER BY SalesDate) "VAR_POP_應變數",
VAR_POP(SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "VAR_POP_自變數",
COVAR_POP(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "COVAR_POP",
REGR_SXX(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "REGR_SXX", --REGR_COUNT(expr1, expr2) * VAR_POP(expr2)
REGR_SYY(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "REGR_SXY", --REGR_COUNT(expr1, expr2) * VAR_POP(expr1)
REGR_SXY(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "REGR_SXY", --REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)
REGR_COUNT(SalesValue,SalesNumber) OVER(PARTITION BY Area ORDER BY SalesDate) "REGR_COUNT"
FROM ComputerSales
--關於按日期進行環比的問題
--同比則有麻煩，因為日期天數是不固定的
--從ComputerSales隨機刪除幾行再測
SELECT AREA,SALESDATE,SALESNUMBER,
LAG(SalesNumber) OVER (PARTITION BY Area order by SalesDate) AS Lag_error, --如遇斷號，會導致資料不準
SUM(SalesNumber) OVER (PARTITION BY AREA ORDER BY SALESDATE RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING) yesterday, --昨天的值
SUM(SalesNumber) OVER (PARTITION BY AREA ORDER BY SALESDATE RANGE BETWEEN 6 PRECEDING AND 6 PRECEDING) lastweek, --上週資料
SUM(SalesNumber) OVER (PARTITION BY AREA ORDER BY SALESDATE RANGE BETWEEN 6 PRECEDING AND 0 PRECEDING) last7_accu, --前7天累計，包括當天
SUM(SalesNumber) OVER (PARTITION BY AREA ORDER BY SALESDATE RANGE BETWEEN 29 PRECEDING AND 0 PRECEDING) last30_accu--前30天累計，包括當天
FROM ComputerSales
--再度增加一個product產品列，以方便進行CUBE函式演示
CREATE TABLE ComputerSalesBAK AS
SELECT SalesNumber,
ROUND(SalesNumber*10+5*DBMS_RANDOM.VALUE(1,10)) SalesValue,
TRUNC(SYSDATE)+MOD(A.DateSEQ-1,24) SalesDate,
CASE WHEN TRUNC((DateSEQ-1)/24)=1 THEN '華南地區'
WHEN TRUNC((DateSEQ-1)/24)=2 THEN '華北地區'
WHEN TRUNC((DateSEQ-1)/24)=3 THEN '東北地區'
WHEN TRUNC((DateSEQ-1)/24)=4 THEN '華東地區'
ELSE '其他地區'
END Area,
CASE WHEN ROUND(DBMS_RANDOM.VALUE(1,3))=1 THEN '產品A'
WHEN ROUND(DBMS_RANDOM.VALUE(1,3))=2 THEN '產品B'
ELSE '產品C'
END Product
FROM (SELECT SalesNumber,ROW_NUMBER() OVER(ORDER BY ROWID) DateSEQ FROM ComputerSales) A;
DROP TABLE ComputerSales;
RENAME ComputerSalesBAK TO ComputerSales;
SELECT * FROM ComputerSales;
--傳統的group by語法
SELECT Product,Area,SalesDate,SUM(SalesNumber),SUM(SalesValue)
FROM ComputerSales
GROUP BY Product,Area,SalesDate
ORDER BY Product,Area,SalesDate
--ROLLUP (group的欄位順序）
--會自動按Group欄位分層統計，與日常報表較為相似
SELECT Product,Area,SalesDate,SUM(SalesNumber),SUM(SalesValue)
FROM ComputerSales
GROUP BY ROLLUP(Product,Area,SalesDate)
ORDER BY Product,Area,SalesDate --加不加均可，已經自動按分組欄位排序
--等價於
SELECT * FROM
(
SELECT Product,Area,SalesDate,SUM(SalesNumber) SalesNumber,SUM(SalesValue) SalesValue --最大級分組
FROM ComputerSales
GROUP BY Product,Area,SalesDate
UNION ALL
SELECT Product,Area,NULL,SUM(SalesNumber),SUM(SalesValue) --按產品、地區分組
FROM ComputerSales
GROUP BY Product,Area,NULL
UNION ALL
SELECT Product,NULL,NULL,SUM(SalesNumber),SUM(SalesValue) --按產品分組
FROM ComputerSales
GROUP BY Product,NULL,NULL
UNION ALL
SELECT NULL,NULL,NULL,SUM(SalesNumber),SUM(SalesValue) --統計總和
FROM ComputerSales
GROUP BY NULL,NULL,NULL
) ORDER BY 1,2,3 --最後再排序
--CUBE (group的欄位順序），與OLAP比較相似，求得所有維度的交匯點
--會自動按Group欄位排列組合進行統計
SELECT Product,Area,SalesDate,SUM(SalesNumber),SUM(SalesValue)
FROM ComputerSales
GROUP BY CUBE(Product,Area,SalesDate)
ORDER BY Product,Area,SalesDate --加不加均可，已經自動按分組欄位排序
--兩則的區別
--即ROLLUP 為C(3,1)即多了3層
--按照Product,Area,SalesDate；Product,Area；Product；ALL的順序進行了統計
--CUBE的統計層級則為2的N次方，即全部的有序組合
--按照Product,Area,SalesDate;Product,Area;Product,SalesDate;Product;Area,SalesDate;Area;SalesDate;ALL的順序進行了統計
--與ROLLUP的等價表示式，相當於ROLLUP的排列組合
SELECT * FROM
(
SELECT Product,Area,SalesDate,SUM(SalesNumber),SUM(SalesValue) --先按Product,Area,SalesDate求ROLLUP
FROM ComputerSales
GROUP BY ROLLUP(Product,Area,SalesDate)
UNION
SELECT Product,NULL,SalesDate,SUM(SalesNumber),SUM(SalesValue) --再按Product,SalesDate求ROLLUP
FROM ComputerSales
GROUP BY ROLLUP(Product,NULL,SalesDate)
UNION
SELECT NULL,Area,SalesDate,SUM(SalesNumber),SUM(SalesValue) --再按Area,SalesDate求ROLLUP
FROM ComputerSales
GROUP BY ROLLUP(NULL,Area,SalesDate)
UNION
SELECT NULL,NULL,SalesDate,SUM(SalesNumber),SUM(SalesValue) --最後按SalesDate求ROLLUP
FROM ComputerSales
GROUP BY ROLLUP(NULL,NULL,SalesDate)
)
ORDER BY 1,2,3
--GROUPING SETS等同於按三列單獨求統計，一般不常用
SELECT Product,Area,SalesDate,SUM(SalesNumber),SUM(SalesValue)
FROM ComputerSales
GROUP BY GROUPING SETS(Product,Area,SalesDate)
ORDER BY Product,Area,SalesDate ;--加不加均可，已經自動按分組欄位排序
--等價於
SELECT * FROM
(
SELECT Product,NULL Area,NULL SalesDate,SUM(SalesNumber),SUM(SalesValue) --按產品分組
FROM ComputerSales
GROUP BY Product,NULL,NULL
UNION ALL
SELECT NULL,Area,NULL,SUM(SalesNumber),SUM(SalesValue) --按地區分組
FROM ComputerSales
GROUP BY NULL,Area,NULL
UNION ALL
SELECT NULL,NULL,SalesDate,SUM(SalesNumber) SalesNumber,SUM(SalesValue) SalesValue --按日期分組
FROM ComputerSales
GROUP BY NULL,NULL,SalesDate
) ORDER BY 1,2,3
--GROUPING函式只接受一個引數，引數為資料表的一列。如果該列為空返回1，否則返回0。
--並且它僅能與 GROUP BY,ROLLUP,CUBE,GROUPING SETS 一起使用。
--稍微執行一下，就發現該函式只是為了做BI報表使用的，把統計行變為1，將來用作字串替代
SELECT GROUPING(Product), Product,GROUPING(Area),Area,GROUPING(SalesDate),SalesDate,SUM(SalesNumber),SUM(SalesValue)
FROM ComputerSales
GROUP BY ROLLUP(Product,Area,SalesDate)
ORDER BY Product,Area,SalesDate ;
--BI標準報表格式
SELECT
DECODE(ProductFlag,1,'產品彙總',Product),
DECODE(AreaFlag,1,'地區彙總',Area),
DECODE(SalesDateFlag,1,'日期彙總',TO_CHAR(SalesDate,'YYYY-MM-DD')),
SalesNumber,SalesValue
FROM
(
SELECT
GROUPING(Product) ProductFlag, Product,
GROUPING(Area) AreaFlag,Area,
GROUPING(SalesDate) SalesDateFlag,SalesDate,
SUM(SalesNumber) SalesNumber,SUM(SalesValue) SalesValue
FROM ComputerSales
GROUP BY ROLLUP(Product,Area,SalesDate)
ORDER BY Product,Area,SalesDate
)
--GROUPING_ID其實和GROUPING原理差不多，GROUPING引數為單值，且只返回1,1
--GROUPING_ID,則返回按2的指數進行累計得到空值區域的值
SELECT Product,Area,SalesDate,
GROUPING_ID(Product,Area,SalesDate) GROUPING421,
GROUPING_ID(Product,Area) GROUPPING21,
GROUPING_ID(Product) GROUPING1,
SUM(SalesNumber),
SUM(SalesValue)
FROM ComputerSales
GROUP BY ROLLUP(Product,Area,SalesDate)
ORDER BY Product,Area,SalesDate ;--加不加均可，已經自動按分組欄位排序
--GROUP_ID函式可以區分重複分組結果，第1 次出現為0，以後每次出現增1。
--GROUP_ID單獨答應在SELECT 中出現意義不大，常在HAVING 中使用達到過濾重複統計的目的。
SELECT Product,Area,SalesDate,GROUP_ID(),
SUM(SalesNumber),SUM(SalesValue)
FROM ComputerSales
GROUP BY CUBE(Product,Area),CUBE(Product,SalesDate)
HAVING GROUP_ID()=0
ORDER BY 1,2,3
--例如該例子中分別按Product,Area和Product,SalesDate會導致產品地區、產品時間的重複計算，導致報表的不清晰
--我們用HAVING GROUP_ID()=0把重複計算的行去掉就OK了
--一般情況下不建議報表程式過度分組，否則到最後連自己都搞糊塗了
--GROUP BY,ROLLUP,CUBE能組合使用，但SELECT中的分組欄位必須出現在GROUP BY的相關欄位
--MODEL:MODEL語句的關鍵字，必須。
--DIMENSION BY: DIMENSION維度的意思，可以理解為陣列的索引，必須。
--MEASURES:指定作為陣列的列
--RULES:對陣列進行各種操作的描述。
--暫時還沒搞明白如何應用，只是簡單實現了一個求上月、前30天、前7天，前1天的例子
SELECT AREA,PRODUCT,SALESDATE,SALESNUMBER,
AVG30DAY,AVG1MONTH, --最近30天的平均值，最近一個月的平均值
ACCU30DAY,ACCU1MONTH, --最近30天的累加值，最近一個月的累加值
SALESNUMBER1DAY,SALESNUMBER7DAY, --昨天的銷售額，一週前的銷售額
SALESNUMBER30DAY,SALESNUMBER1MONTH --30天的銷售額，上月同天的銷售額
FROM ComputerSales
MODEL DIMENSION BY (AREA,PRODUCT,SALESDATE)
MEASURES (SALESNUMBER,0 AVG30DAY,0 AVG1MONTH,0 ACCU30DAY,0 ACCU1MONTH,0 SALESNUMBER1DAY,0 SALESNUMBER7DAY,0 SALESNUMBER30DAY,0 SALESNUMBER1MONTH)
RULES UPDATE
(AVG30DAY[ANY,ANY,ANY]=AVG(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN CV(SALESDATE)-29 AND CV(SALESDATE)],
AVG1MONTH[ANY,ANY,ANY]=AVG(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN ADD_MONTHS(CV(SALESDATE),-1) AND CV(SALESDATE)],
ACCU30DAY[ANY,ANY,ANY]=SUM(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN CV(SALESDATE)-30 AND CV(SALESDATE)],
ACCU1MONTH[ANY,ANY,ANY]=SUM(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN ADD_MONTHS(CV(SALESDATE),-1) AND CV(SALESDATE)],
SALESNUMBER1DAY[ANY,ANY,ANY]=MAX(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN CV(SALESDATE)-1 AND CV(SALESDATE)-1],
SALESNUMBER7DAY[ANY,ANY,ANY]=MAX(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN CV(SALESDATE)-7 AND CV(SALESDATE)-7],
SALESNUMBER30DAY[ANY,ANY,ANY]=MAX(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN CV(SALESDATE)-30 AND CV(SALESDATE)-30],
SALESNUMBER1MONTH[ANY,ANY,ANY]=MAX(SALESNUMBER)[CV(),CV(),SALESDATE BETWEEN CV(SALESDATE)-30 AND CV(SALESDATE)-30]
)
ORDER BY 1,2,3
[ 本帖最後由 bq_wang 於 2011-6-6 19:01 編輯 ]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6517/viewspace-697234/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Vector容器主要函式說明函式
- 多維分析函式函式
- oracle 統計分析函式Oracle函式
- Oracle聚合函式/分析函式Oracle函式
- Less函式說明函式
- sap 函式說明函式
- Oracle分析函式七——分析函式案例Oracle函式
- 【分析函式】Oracle分析函式之LAG和LEAD函式Oracle
- 分析函式——統計函式
- PHP 風險函式說明PHP函式
- mysql count函式說明MySql函式
- Oracle分析函式與視窗函式Oracle函式
- 函式式思維和函數語言程式設計函式函數程式設計
- Java @FunctionInterface函式式介面使用說明JavaFunction函式
- Oracle 分析函式Oracle函式
- Oracle分析函式Oracle函式
- oracle 10g函式大全--分析函式Oracle 10g函式
- Oracle OCP(03)：字元函式、數字函式和日期函式Oracle字元函式
- 【函式】ORACLE函式大全函式Oracle
- ORACLE函式介紹第五篇 分析函式簡述Oracle函式
- oracle分析函式,keep and over解說Oracle函式
- 函式表示式和函式宣告簡單介紹函式
- MySQL函式大全(字串函式，數學函式，日期函式，系統級函式，聚合函式)MySql函式字串
- 【函式】Oracle函式系列（2）--數學函式及日期函式函式Oracle
- Oracle 函式大全(字串函式，數學函式，日期函式，邏輯運算函式，其他函式)Oracle函式字串
- memset函式詳細說明函式
- 【函式】Oracle EXTRACT()函式與to_char() 函式函式Oracle
- 核函式 多項式核函式 高斯核函式(常用)函式
- ORACLE函式介紹第六篇 著名函式之分析函式Oracle函式
- oracle的分析函式over 及開窗函式Oracle函式
- 字元函式、數字函式和日期函式字元函式
- oracle 分析函式(轉)Oracle函式
- oracle分析函式（一）Oracle函式
- Oracle常用分析函式Oracle函式
- Oracle分析函式{轉}Oracle函式
- Oracle分析函式-6Oracle函式
- Oracle分析函式-5Oracle函式
- Oracle分析函式-4Oracle函式