Oracle分析函數、多維函數和Model函數的簡要說明

以下代碼均經過測試，可直接運行

Oracle分析函數、多維函數和Model函數簡要說明，主要針對BI報表統計，不一定很全面，但對BI應用情境做了少許說明

--建立一張銷售數量表，資料趨勢是遞增的

CREATE TABLE ComputerSales ASSELECT 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 ASSELECT 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 ASSELECT 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 ASSELECT 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_SalesNumberFROM 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_DescFROM 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_lastFROM 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_CONTFROM ComputerSales    --增加了一列叫銷售額，可以進行相關數理統計CREATE TABLE ComputerSalesBAK ASSELECT 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 ASSELECT 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 ALLSELECT Product,Area,NULL,SUM(SalesNumber),SUM(SalesValue) --按產品、地區分組  FROM ComputerSales GROUP BY Product,Area,NULL UNION ALLSELECT Product,NULL,NULL,SUM(SalesNumber),SUM(SalesValue) --按產品分組  FROM ComputerSales GROUP BY Product,NULL,NULL UNION ALLSELECT 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)UNIONSELECT Product,NULL,SalesDate,SUM(SalesNumber),SUM(SalesValue) --再按Product,SalesDate求ROLLUP  FROM ComputerSales GROUP BY ROLLUP(Product,NULL,SalesDate)UNIONSELECT NULL,Area,SalesDate,SUM(SalesNumber),SUM(SalesValue) --再按Area,SalesDate求ROLLUP  FROM ComputerSales GROUP BY ROLLUP(NULL,Area,SalesDate)UNIONSELECT 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 ALLSELECT NULL,Area,NULL,SUM(SalesNumber),SUM(SalesValue) --按地區分組  FROM ComputerSales GROUP BY NULL,Area,NULL UNION ALLSELECT 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        關於按年月環比統計中可能出現的問題    CREATE TABLE TEST (SALESMONTH VARCHAR(6),SALESNUMBER INT) ;INSERT INTO TEST VALUES('201002',2);INSERT INTO TEST VALUES('201004',4);INSERT INTO TEST VALUES('201007',7);INSERT INTO TEST VALUES('201008',8);INSERT INTO TEST VALUES('201010',10);    SELECT SALESMONTH,SALESNUMBER,LAG(SalesNumber) OVER(order by SalesMONTH) AS Lag10_Area_SalesNumber,--如遇斷號，會導致資料不準SUM(SalesNumber) OVER(ORDER BY TO_DATE(SalesMONTH||'01','YYYYMMDD') RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING)FROM TEST