sql之分析函数

一、lag 和lead函数  可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）； lag ，lead 分别是向前，向后； lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值） lag(expression<,offset><,default>)函数可以访问组内当前行之前的行， 而lead(expression<,offset><,default>)函数则正相反，可以访问组内当前行之后的行． 其中，offset是正整数，默认为1．因组内第一个条记录没有之前的行，最后一行没有之后的行， default就是用于处理这样的信息，默认为空． 注意：这2个函数必须指定 order By 字句． create table temp_a (a varchar(10),b varchar(10),c varchar(10),d varchar(10)); insert into temp_a values('1','100','102','103'); insert into temp_a values('2','200','202','203'); insert into temp_a values('3','300','302','303'); insert into temp_a values('4','400','402','403'); commit; SQL> select a,b,c,d,lag(d) over (order by d) lag,lead(d) over (order by d) lead from temp_a; A B C D LAG LEAD ---------- ---------- ---------- ---------- ---------- ---------- 1 100 102 103 203 2 200 202 203 103 303 3 300 302 303 203 403 4 400 402 403 303  SQL> select a,b,c,d,lag(d,1,0) over (order by d) lag,lead(d,1,d) over (order by d) lead from temp_a; A B C D LAG LEAD ---------- ---------- ---------- ---------- ---------- ---------- 1 100 102 103 0 203 2 200 202 203 103 303 3 300 302 303 203 403 4 400 402 403 303 403 二、group by和partition by区别 group by是对检索结果的保留行进行单纯分组，一般总爱和聚合函数一块用例如AVG（），COUNT（），max（），main（）等一块用。  partition by虽然也具有分组功能，但同时也具有其他的功能。  它属于oracle的分析用函数。  借用一个勤快人的数据说明一下：  sum()   over   (PARTITION   BY   ...)   是一个分析函数。   他执行的效果跟普通的sum   ...group   by   ...不一样，它计算组中表达式的累积和，而不是简单的和。         表a，内容如下：    B C D    02 02 1    02 03 2    02 04 3    02 05 4    02 01 5    02 06 6    02 07 7    02 03 5    02 02 12    02 01 2    02 01 23         select   b,c,sum(d)   e   from   a   group   by   b,c    得到：    B C E    02 01 30    02 02 13    02 03 7    02 04 3    02 05 4    02 06 6    02 07 7         而使用分析函数得到的结果是：    SELECT   b,   c,   SUM(d)   OVER(PARTITION   BY   b,c   ORDER   BY   d)   e   FROM   a    B C E    02 01 2    02 01 7    02 01 30    02 02 1    02 02 13    02 03 2    02 03 7    02 04 3    02 05 4    02 06 6    02 07 7    结果不一样，这样看还不是很清楚，我们把d的内容也显示出来就更清楚了：    SELECT   b,   c,   d,SUM(d)   OVER(PARTITION   BY   b,c   ORDER   BY   d)   e   FROM   a    B C D E    02 01 2 2                     d=2,sum(d)=2    02 01 5 7                     d=5,sum(d)=7    02 01 23 30                   d=23,sum(d)=30    02 02 1 1                     c值不同，重新累计    02 02 12 13    02 03 2 2    02 03 5 7    02 04 3 3    02 05 4 4    02 06 6 6    02 07 7 7