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

--移动平均值，累计求和，当前窗口平均值，当前窗口求和，以及窗口函数和排序函数的作用域
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,