频道栏目
首页 > 数据库 > Oracle > 正文
Oracle分析函数、多维函数和Model函数简要说明,主要针对BI报表统计
2011-06-07 08:53:26           
收藏   我要投稿

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

--观察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,0) 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,0) 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 Co

点击复制链接 与好友分享!回本站首页
上一篇:通过sqlpuls远程连接oracle10
下一篇:ORACLE常见问题一千问[101至200]
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站