ORACLE分析函数小结

rank, dense_rand, row_number

这三个分析函数为每个生成一个1至n的自然数，这三个函数的唯一区别在于当碰到相同数据时的排名策略

row_number

返回一个唯一的值，当碰到相同数据时，排名按照记录集中的顺序依次递增

dense_rand

返回一个唯一的值，除非碰到相同数据时，此时所有数据的排名是一样的

rank

遇到相同数据时，相同数据的排名是一样的，同时会在最后一条记录和下一条不同记录的排名之间空出排名

 

 

rows between unbounded preceding and unbounded following

 

ntil(5) over()

 

SELECT MIN(REGION_ID) KEEP(DENSE_RANK FIRST ORDER BY SUM(CUSTOMER_SALES)DESC)FIRST,

MIN(REGION_ID) KEEP(DENSE_RANK LAST ORDER BY SUM(CUSTOMER_SALES) DESC) LAST

FROM USER_ORDER

GROUP BY REGION_ID

LAG(SUM(TOT_SALES),1) OVER(ORDER BY MONTH)

 

 

统计方面

SUM(...)OVER(PARTITION BY  ORDER BY)

SUM(...)OVER(PARTITION BY ORDER BY ROWS BETWEEN ... PRECEDING AND ... FOLLOWING)

SUM(...)OVER(PARTITION BY ORDER BY ROWS BETWEEN ... PRECEDING AND ... CURRENT ROW)

SUM(...)OVER(PARTITION BY ORDER BY RANGE BETWEEN INTERVAL '...' 'DAY' PRECEDING AND INTERVAL '...'  'DAY' FOLLOWING)

排序方面

RANK()OVER(PARTITION BY ORDER BY NULLS LAST/FIRST)

DENSE_RANK()OVER(PARTITION BY ORDER BY NULLS LAST/FIRST)

ROW_NUMBER()OVER(PARTITION BY ORDER BY NULLS LAST/FIRST)

NTILE(...)OVER(PARTITION BY ORDER BY NULLS LAST/FIRST)

最大值最小值查找

MIN(...)/MAX(...) KEEP(DENSE_RANK FIRST/LAST PARTITION BY  ORDER BY)

首记录/末记录查找

FIRST_VALUE/LAST_VALUE(SUM(...)OVER(PARTITION BY ORDER BY ROWS BETWEEN ... PRECEDING AND ... FOLLOWING))

相邻记录

LAG(SUM(...),1)OVER(PARTITION BY ORDER BY)