Oracle分析函数、多维函数和Model函数简要说明，主要针对BI报表统计

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 (PARTITI