Oracle分析函数

二、窗口函数示例－全统计：

 unbounded preceding and unbouned following

 

unbounded就是告诉oracle不管当前记录是第几条，只要前面有多少条记录，都列入统计的范围。

 SUM(SUM(SALES)) OVER(ORDER BY MONTHS ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS TOTAL_SALES,   -- 全统计

 

ROUND(AVG(SUM(SALES)) OVER(ORDER BY MONTHS ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),2) AS TOTAL_AVG_SALES  -- 全统计

 三、窗口函数进阶－滚动统计(累积/均值)：

      考虑前面提到的第2个需求：列出每月的订单总额以及截至到当前月的订单总额。也就是说2月份的记录要显示当月的订单总额和1,2月份订单总额的和。3月份要显示当月的订单总额和1,2,3月份订单总额的和，依此类推。

     很明显这个需求需要在统计第N月的订单总额时，还要再统计这N个月来的订单总额之和。

    

SUM(SUM(SALES)) OVER(ORDER BY MONTHS) AS YEAR_SALES,    -- 类似滚动统计

 

SUM(SUM(SALES)) OVER(ORDER BY MONTHS ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT   ROW) AS ADD_SALES,           -- 滚动统计(累积/均值)

 四、窗口函数进阶－根据时间范围统计：

 

SUM(SUM(SALES)) OVER(ORDER BY MONTHS RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)  AS RANGE_SALES,                 -- 根据范围统计

 

 ROUND(AVG(SUM(SALES)) OVER(ORDER BY MONTHS RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING),2) AS RANGE_AVG_SALES,     -- 根据范围统计

 

五、窗口函数进阶－first_value/last_value：

       Oracle提供了2个额外的函数：first_value、last_value，用于在窗口记录集中查找第一条记录和最后一条记录。假设我们的报表需要显示当前月、上一个月、后一个月的销售情况，以及每3个月的销售平均值，这两个函数就可以派上用场了。

       首先我们来看:rows between 1 preceding and 1 following告诉Oracle在当前记录的前一条、后一条范围内查找并统计，而first_value和last_value在这3条记录中至分别找出第一条、第三条记录，这样我们就轻松地得到相邻三个月的销售记录及平均值了

 

 LAST_VALUE(MONTHS)  OVER(ORDER BY MONTHS RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) NEXT_MONTH,                 -- (Range No sum)first_value/last_value：  

 

 LAST_VALUE (SUM(SALES)) OVER(ORDER BY MONTHS RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)  AS NEXT_SALES,         -- (Range)first_value/last_value：

 

 FIRST_VALUE(SUM(SALES)) OVER(ORDER BY MONTHS ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) AS FIRST_SALES3,         -- (Rows)first_value/last_value：

 

 FIRST_VALUE(MONTHS) OVER(ORDER BY MONTHS RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) PRE_MONTH,                  -- (Range No sum)first_value/last_value：       

 

 FIRST_VALUE(SUM(SALES)) OVER(ORDER BY MONTHS RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)  AS PRE_SALES,          -- (Range)first_value/last_value：

 

六、窗口函数进阶－比较相邻记录：

        利用窗口函数来显示相邻的记录，现在假如我们想每次显示当月的销售额和上个月的销售额

        利用first_value(sum(tot_sales) over (order by month rows between 1 preceding and 0 following))就可以做到了，其实Oracle还有一个更简单的方式让我们来比较2条记录，它就是lag函数。

        leg函数类似于preceding和following子句，它能够通过和当前记录的相对位置而被应用，在比较同一个相邻的记录集内两条相邻记录的时候特别有用。 

        lag(sum(tot_sales),1)中的1表示以1月为基准。

 

   

 LAG(SUM(SALES)) OVER(ORDER BY MONTHS)  AS LAG_PRE_SALES1,                                                       -- 比较相邻记录(默认基准月)

 

 LAG(SUM(SALES),1) OVER(ORDER BY MONTHS)  AS LAG_PRE_SALES2,                                                     -- 比较相邻记录(指定基准月)

 

 FIRST_VALUE(SUM(SALES)) OVER(ORDER BY MONTHS ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) AS FIRST_SALES3,         -- 比较相邻记录 / (Rows)first_value/last_value：