Oracle分析函数、多维函数简单应用
十年前写的，关于Oracle分析函数、多维函数简单应用，主要针对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,) 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,) 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 PRECEDING) last7_accu, --前7天累计，包括当天
SUM(SalesNumber) OVER (PARTITION BY AREA ORDER BY SALESDATE RANGE BETWEEN 29 PRECEDING AND 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，否则返回。
--并且它仅能与 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 次出现为，以后每次出现增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()=
ORDER BY 1,2,3
--例如该例子中分别按Product,Area和Product,SalesDate会导致产品地区、产品时间的重复计算，导致报表的不清晰
--我们用HAVING GROUP_ID()=把重复计算的行去掉就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, AVG30DAY, AVG1MONTH, ACCU30DAY, ACCU1MONTH, SALESNUMBER1DAY, SALESNUMBER7DAY, SALESNUMBER30DAY, 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
遇到一个问题，假如BI报表中的月份是字符串，而碰巧断月了，如何准确求得上个月的数据，理应为空
如果是天的话可以想办法规避掉，如果是字符串月没想好怎么处理
newkid给了算法
SELECT SALESMONTH,SALESNUMBER,
MAX(SalesNumber) OVER(order by TO_DATE(SalesMONTH,'YYYYMM') RANGE BETWEEN 31 PRECEDING AND 1 PRECEDING )
FROM TEST;
但我觉得结果很正确，但是不保险，而且有点迷糊
是把当前的月份转换成当月的天，并且向前推31天到前1天
假如当前月是2月，向前推31天应该到去年12月份了，求的 MAX(SalesNumber) 未必有效
可实际结果是正确的，奇怪
相关文章