Oracle分析函数的应用（原创）

Oracle分析函数的应用

统一认证系统有个新需求：查询系统登录活跃用户。活跃用户是指：在一个登录的最小时间间隔在一个时间范围（比如一天）内的用户。因为在统一认证系统中登录日志表只有登录时间，没有上次登录时间字段，应此必须和上条数据做比较。

可以有以下处理办法：

1：用程序实现，写大量的java代码，如果对于数据量较小的应用可以使用，但是统一认证的登录日志表有千万级的数据，现在要遍历所有数据很效率很低，绝对对不行。（排除）

2：用PL/SQL实现：从当前行得到数据，并"找到"之前以及之后行中的数据。这样可以达到目的，查询的开发与运行会带来很大开销，效率也低。（排除）

3：选用ORACLE分析函数：只需做一次全表遍历，一天SQL语句就能搞定。使用分析函数，简单易行且有效（选择） 

分析函数，最早是从ORACLE8.1.6开始出现的，它的设计目的是为了解决诸如"累计计算"，"找出分组内百分比"，"前-N条查询"，"移动平均数计算""等问题。其实大部分的问题都可以用PL/SQL解决，但是它的性能并不能达到你所期望的效果。分析函数是SQL言语的一种扩充，它并不是仅仅试代码变得更简单而已，它的速度比纯粹的SQL或者PL/SQL更快。现在这些扩展已经被纳入了美国国家标准化组织SQL委员会的SQL规范说明书中。 
Analytic-Function(,,...)
OVER (
 
 
 
)    ORACLE提供了以下分析函数：avg, corr, covar_pop, covar_samp, count, cume_dist, dense_rank, first, first_value, lag, last, last_value, lead, max, min, ntile, percent_rank, percentile_cont, percentile_disc, rank, ratio_to_report, stddev, stddev_pop, stddev_samp, sum, var_pop, var_samp, variance. 在此不一一解释，可以参照《ORACLE9I SQL Reference》，有详细说明。
以下是解决这个问题的SQL语句：
SELECT logonid,username,logontime,
lag(logontime,1,null)  over (partition by username order by logonid ) lastlogin ,
logontime-lag(logontime,1,null)  over (partition by username order by logonid ) days
from logon_log order by username,logonid
1>    lag和lead分析函数：lag(在此列显示的属性-这里是登录时间,上下几条数据,如果上下没有数据显示的字符)
2>    over (partition by username order by logonid ) 按username分组，分组后按照登录ID排序，如果没有order by，分组后的数据是无序的，不符合要求。
3>    与logontime做差可以得到登录时间差。得到了需要的数据。