分享分析函数的总结，希望对大家有所帮助有所启发

精选转载

葬心1 2014-06-13 14:25:24

文章标签 分享分析函数的总结希望对大家有所帮助有所启发 文章分类 数据库

最近把分析函数的一些总结写完了，内容发表在我itpub的博文中

内容包括:
排序:rank ，dense_rank ，row_number
统计:范围统计，滚动统计等
一些其他的常用分析函数: firstlast_value，keep ，ntile 等内容

目录连接如下
http://blog.itpub.net/28929558/cid-168772-abstract-1/

下面为一些分析函数的摘要，具体内容见在上面链接的博文中，欢迎来踩~
==================================================
统计方面:http://blog.itpub.net/28929558/viewspace-1180990/

Sum() Over ([Partition by ] [Order by ]) --统计全表

--统计前n行到后m行
Sum() Over ([Partition by ] [Order by ]
   Rows Between n Preceding And m Following)

--统计前n行到当前行
Sum() Over ([Partition by ] [Order by ]
   Rows Between n Preceding And Current Row)

--统计开始行到当前行
Sum() Over ([Partition by ] [Order by ]
   Rows Between unbounded Preceding And Current Row)

--统计开始行到当前行之后的第n行
Sum() Over ([Partition by ] [Order by ]
   Rows Between unbounded Preceding n Following)

相邻记录之间比较 lead/lag：
Lag(Sum(), 1) Over([Patition by ] [Order by ])
===================================================
有关排列或排名:

http://blog.itpub.net/28929558/viewspace-1180283/
--非连续排名
Rank() Over ([Partition by ] [Order by ] [Nulls First/Last])

--连续排名
Dense_rank() Over ([Patition by ] [Order by ] [Nulls First/Last])

--排序行号
Row_number() Over ([Partitionby ] [Order by ] [Nulls First/Last])

==========================================================
最大值/最小值查找：
http://blog.itpub.net/28929558/viewspace-1181227/
Min()/Max() Keep (Dense_rank First/Last [Partition by ] [Order by ])

==========================================================
首记录/末记录查找：
http://blog.itpub.net/28929558/viewspace-1181376/
First_value / Last_value(Sum() Over ([Patition by ] [Order by ]
   Rows Between  Preceding And  Following  ))

==========================================================
NTILE函数
Ntile() Over ([Partition by ] [Order by ])
http://blog.itpub.net/28929558/viewspace-1181432/