以下代码均经过测试，可直接运行
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