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