以下代码均经过测试,可直接运行
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 (PARTITI
Oracle分析函数、多维函数和Model函数简要说明,主要针对BI报表统计
最新推荐文章于 2021-04-14 23:51:23 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)