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

  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