oracle lag和lead函数
oracle lag和lead函数
2010-11-01 17:45
这两个函数，是偏移量函数，其用途是：可以查出同一字段下一个值或上一个值，并作为新列存在表中。
妈的在网上查了半天也没见那个网站说的清楚而且都是同一个例子汗啊！还是自己运行琢磨弄明白下面实例为证。
1。
select * from tache_columns
TCH_ID COL_NAME_CN COL_VALUE
------------- ------------------------------------- ------
1 张三 10
2 李四 21
3 王五 23
1 aaa 14
1 bbb 20
2。
select t.tch_id,
t.col_name_cn,
t.col_value,
lead(t.col_value,1,null) over(partition by t.tch_id order by t.col_value) sal
from tache_columns t；
注：lead函数是先按tch_id分组，按col_val排序，并把当前行col_value的下一个值放到sal中，比如：
col_value=10的下一个值是14,col_value=14 的下一个值是20。
函数中的null是当没有下一个值时用null代替，当然也可以用其他值替换NULL.
3。
select t.tch_id,
t.col_name_cn,
t.col_value,
lag(t.col_value,1,null) over(partition by t.tch_id order by t.col_value) sal
from tache_columns t
lag与lead相反。
函数中的1是可以修改的。
具体可以运行查看
4。
select t.tch_id,
t.col_name_cn,
t.col_value,
lag(t.col_value) over(order by t.col_value) sal
from tache_columns t
当然也可以不要partition,lag的默认参数是1。
Oracle分析函数用法详解
Oracle分析函数Oracle分析函数实际上操作对象是查询出的数据集，也就是说不需二次查询数据库，实际上就是oracle实现了一些我们自身需要编码实现的统计功能，对于简化开发工作量有很大的帮助，特别在开发第三方报表软件时是非常有帮助的。
Oracle从8.1.6开始提供分析函数。
一、基本语法oracle分析函数的语法：function_name(arg1,arg2,...)over(<partition-clause> <order-by-clause ><windowing clause>)说明：1.partition-clause 数据记录集分组2.order-by-clause 数据记录集排序3.windowing clause 功能非常强大、比较复杂，定义分析函数在操作行的集合。
有三种开窗方式: range、row、specifying。
二、常用分析函数1. avg(distinct|all expression) 计算组内平均值，distinct 可去除组内重复数据select deptno,empno,sal,avg(sal) over (partition by deptno) avg_sal from t;DEPTNO EMPNO SAL AVG_SAL---------- ---------- ---------- ----------10 7782 2450 2916.666677839 5000 2916.666677934 1300 2916.6666720 7566 2975 21757902 3000 21757876 1100 21757369 800 21757788 3000 217530 7521 1250 1566.666677844 1500 1566.666677499 1600 1566.666677900 950 1566.666677698 2850 1566.666677654 1250 1566.666672.count(<distinct><*><expression>) 对组内数据进行计数3．rank() 和dense_rank()dense_rank()根据 order by 子句表达式的值，从查询返回的每一行，计算和其他行的相对位置，序号从 1 开始，有重复值时序号不跳号。
Oracle 分析函数的使用
Oracle 分析函数的使用Oracle 分析函数使用介绍分析函数是oracle816引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式.在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的sql语句就可以实现了,而且在执行效率方面也有相当大的提高.下面我将针对分析函数做一些具体的说明.今天我主要给大家介绍一下以下几个函数的使用方法1. 自动汇总函数rollup,cube,2. rank 函数, rank,dense_rank,row_number3. lag,lead函数4. sum,avg,的移动增加,移动平均数5. ratio_to_report报表处理函数6. first,last取基数的分析函数基础数据Code: [Copy to clipboard]06:34:23 SQL> select * from t;BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE--------------- ---------- ---------- --------------200405 5761 G 7393344.04200405 5761 J 5667089.85200405 5762 G 6315075.96200405 5762 J 6328716.15200405 5763 G 8861742.59200405 5763 J 7788036.32200405 5764 G 6028670.45200405 5764 J 6459121.49200405 5765 G 13156065.77200405 5765 J 11901671.70200406 5761 G 7614587.96200406 5761 J 5704343.05200406 5762 G 6556992.60200406 5762 J 6238068.05200406 5763 G 9130055.46200406 5763 J 7990460.25200406 5764 G 6387706.01200406 5764 J 6907481.66200406 5765 G 13562968.81200406 5765 J 12495492.50200407 5761 G 7987050.65200407 5761 J 5723215.28200407 5762 G 6833096.68200407 5762 J 6391201.44200407 5763 G 9410815.91200407 5763 J 8076677.41200407 5764 G 6456433.23200407 5764 J 6987660.53200407 5765 G 14000101.20200407 5765 J 12301780.20200408 5761 G 8085170.84200408 5761 J 6050611.37200408 5762 G 6854584.22200408 5762 J 6521884.50200408 5763 G 9468707.65200408 5763 J 8460049.43200408 5764 G 6587559.23BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE --------------- ---------- ---------- --------------200408 5764 J 7342135.86200408 5765 G 14450586.63200408 5765 J 12680052.3840 rows selected.Elapsed: 00:00:00.001. 使用rollup函数的介绍Quote:下面是直接使用普通sql语句求出各地区的汇总数据的例子06:41:36 SQL> set autot on06:43:36 SQL> select area_code,sum(local_fare) local_fare06:43:50 2 from t06:43:51 3 group by area_code06:43:57 4 union all06:44:00 5 select '合计' area_code,sum(local_fare) local_fare06:44:06 6 from t06:44:08 7 /AREA_CODE LOCAL_FARE---------- --------------5761 54225413.045762 52039619.605763 69186545.025764 53156768.465765 104548719.19合计 333157065.316 rows selected.Elapsed: 00:00:00.03Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1310 Bytes=24884)1 0 UNION-ALL2 1 SORT (GROUP BY) (Cost=5 Card=1309 Bytes=24871)3 2 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309Bytes=24871)4 1 SORT (AGGREGATE)5 4 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309Bytes=17017)Statistics----------------------------------------------------------0 recursive calls0 db block gets6 consistent gets0 physical reads0 redo size561 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)6 rows processed下面是使用分析函数rollup得出的汇总数据的例子06:44:09 SQL> select nvl(area_code,'合计') area_code,sum(local_fare) local_fare06:45:26 2 from t06:45:30 3 group by rollup(nvl(area_code,'合计'))06:45:50 4 /AREA_CODE LOCAL_FARE---------- --------------5761 54225413.045762 52039619.605763 69186545.025764 53156768.465765 104548719.19333157065.316 rows selected.Elapsed: 00:00:00.00Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1309Bytes=24871)1 0 SORT (GROUP BY ROLLUP) (Cost=5 Card=1309 Bytes=24871)2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309Bytes=24871)Statistics----------------------------------------------------------0 recursive calls0 db block gets4 consistent gets0 physical reads0 redo size557 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)6 rows processed从上面的例子我们不难看出使用rollup函数,系统的sql语句更加简单,耗用的资源更少,从6个consistent gets降到4个consistent gets,如果基表很大的话,结果就可想而知了.1. 使用cube函数的介绍Quote:为了介绍cube函数我们再来看看另外一个使用rollup的例子06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare06:53:37 2 from t06:53:38 3 group by rollup(area_code,bill_month)06:53:49 4 /---------- --------------- --------------5761 200405 13060433.895761 200406 13318931.015761 200407 13710265.935761 200408 14135782.215761 54225413.045762 200405 12643792.115762 200406 12795060.655762 200407 13224298.125762 200408 13376468.725762 52039619.605763 200405 16649778.915763 200406 17120515.715763 200407 17487493.325763 200408 17928757.085763 69186545.025764 200405 12487791.945764 200406 13295187.675764 200407 13444093.765764 200408 13929695.095764 53156768.465765 200405 25057737.475765 200406 26058461.315765 200407 26301881.405765 200408 27130639.015765 104548719.19333157065.3126 rows selected.Elapsed: 00:00:00.00系统只是根据rollup的第一个参数area_code对结果集的数据做了汇总处理,而没有对bill_month做汇总分析处理,cube函数就是为了这个而设计的.下面,让我们看看使用cube函数的结果06:58:02 SQL> select area_code,bill_month,sum(local_fare) local_fare 06:58:30 2 from t06:58:32 3 group by cube(area_code,bill_month)06:58:42 4 order by area_code,bill_month nulls last06:58:57 5 /---------- --------------- --------------5761 200405 13060.435761 200406 13318.935761 200407 13710.275761 200408 14135.785761 54225.415762 200405 12643.795762 200406 12795.065762 200407 13224.305762 200408 13376.475762 52039.625763 200405 16649.785763 200406 17120.525763 200407 17487.495763 200408 17928.765763 69186.545764 200405 12487.795764 200406 13295.195764 200407 13444.095764 200408 13929.695764 53156.775765 200405 25057.745765 200406 26058.465765 200407 26301.885765 200408 27130.645765 104548.72200405 79899.53200406 82588.15200407 84168.03200408 86501.34333157.0530 rows selected.Elapsed: 00:00:00.01可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.这就是cube函数根据bill_month做的汇总统计结果1 rollup 和cube函数的再深入Quote:从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,我们如何来区分到底是根据那个字段做的汇总呢,这时候,oracle的grouping函数就粉墨登场了.如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回01 select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code,2 decode(grouping(bill_month),1,'all month',bill_month) bill_month,3 sum(local_fare) local_fare4 from t5 group by cube(area_code,bill_month)6* order by area_code,bill_month nulls last07:07:29 SQL> /AREA_CODE BILL_MONTH LOCAL_FARE---------- --------------- --------------5761 200405 13060.435761 200406 13318.935761 200407 13710.275761 200408 14135.785761 all month 54225.415762 200405 12643.795762 200406 12795.065762 200407 13224.305762 200408 13376.475762 all month 52039.625763 200405 16649.785763 200406 17120.525763 200407 17487.495763 200408 17928.765763 all month 69186.545764 200405 12487.795764 200406 13295.195764 200407 13444.095764 200408 13929.695764 all month 53156.775765 200405 25057.745765 200406 26058.465765 200407 26301.885765 200408 27130.645765 all month 104548.72all area 200405 79899.53all area 200406 82588.15all area 200407 84168.03all area 200408 86501.34all area all month 333157.0530 rows selected.Elapsed: 00:00:00.0107:07:31 SQL>可以看到,所有的空值现在都根据grouping函数做出了很好的区分,这样利用rollup,cube和grouping函数,我们做数据统计的时候就可以轻松很多了.2. rank函数的介绍介绍完rollup和cube函数的使用,下面我们来看看rank系列函数的使用方法.问题2.我想查出这几个月份中各个地区的总话费的排名.Quote:为了将rank,dense_rank,row_number函数的差别显示出来,我们对已有的基础数据做一些修改,将5763的数据改成与5761的数据相同.1 update t t1 set local_fare = (2 select local_fare from t t23 where t1.bill_month = t2.bill_month4 and _type = _type5 and t2.area_code = '5761'6* ) where area_code = '5763'07:19:18 SQL> /8 rows updated.Elapsed: 00:00:00.01我们先使用rank函数来计算各个地区的话费排名.07:34:19 SQL> select area_code,sum(local_fare) local_fare,07:35:25 2 rank() over (order by sum(local_fare) desc) fare_rank07:35:44 3 from t07:35:45 4 group by area_codee07:35:50 507:35:52 SQL> select area_code,sum(local_fare) local_fare,07:36:02 2 rank() over (order by sum(local_fare) desc) fare_rank07:36:20 3 from t07:36:21 4 group by area_code07:36:25 5 /AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 25764 53156.77 45762 52039.62 5Elapsed: 00:00:00.01我们可以看到红色标注的地方出现了,跳位,排名3没有出现下面我们再看看dense_rank查询的结果.07:36:26 SQL> select area_code,sum(local_fare) local_fare,07:39:16 2 dense_rank() over (order by sum(local_fare) desc ) fare_rank 07:39:39 3 from t07:39:42 4 group by area_code07:39:46 5 /AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 25764 53156.77 3 这是这里出现了第三名5762 52039.62 4Elapsed: 00:00:00.00在这个例子中,出现了一个第三名,这就是rank和dense_rank的差别,rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,而dense_rank则不会,差别更大的是,row_number哪怕是两个数据完全相同,排名也会不一样,这个特性在我们想找出对应没个条件的唯一记录的时候又很大用处1 select area_code,sum(local_fare) local_fare,2 row_number() over (order by sum(local_fare) desc ) fare_rank3 from t4* group by area_code07:44:50 SQL> /AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 35764 53156.77 45762 52039.62 5在row_nubmer函数中,我们发现,哪怕sum(local_fare)完全相同,我们还是得到了不一样排名,我们可以利用这个特性剔除数据库中的重复记录.这个帖子中的几个例子是为了说明这三个函数的基本用法的. 下个帖子我们将详细介绍他们的一些用法.2. rank函数的介绍a. 取出数据库中最后入网的n个用户select user_id,tele_num,user_name,user_status,create_datefrom (select user_id,tele_num,user_name,user_status,create_date,rank() over (order by create_date desc) add_rankfrom user_info)where add_rank <= :n;b.根据object_name删除数据库中的重复记录create table t as select obj#,name from sys.obj$;再insert into t1 select * from t1 数次.delete from t1 where rowid in (select row_id from (select rowid row_id,row_number() over (partition by obj# order by rowid ) rn ) where rn <> 1);c. 取出各地区的话费收入在各个月份排名.SQL> select bill_month,area_code,sum(local_fare) local_fare,2 rank() over (partition by bill_month order by sum(local_fare) desc) area_rank3 from t4 group by bill_month,area_code5 /BILL_MONTH AREA_CODE LOCAL_FARE AREA_RANK--------------- --------------- -------------- ----------200405 5765 25057.74 1200405 5761 13060.43 2200405 5763 13060.43 2200405 5762 12643.79 4200405 5764 12487.79 5200406 5765 26058.46 1200406 5761 13318.93 2200406 5763 13318.93 2200406 5764 13295.19 4200406 5762 12795.06 5200407 5765 26301.88 1200407 5761 13710.27 2200407 5763 13710.27 2200407 5764 13444.09 4200407 5762 13224.30 5200408 5765 27130.64 1200408 5761 14135.78 2200408 5763 14135.78 2200408 5764 13929.69 4200408 5762 13376.47 520 rows selected.SQL>3. lag和lead函数介绍取出每个月的上个月和下个月的话费总额1 select area_code,bill_month, local_fare cur_local_fare,2 lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,3 lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,4 lead(local_fare,1,0) over (partition by area_code order by bill_month )next_local_fare,5 lead(local_fare,2,0) over (partition by area_code order by bill_month )post_local_fare6 from (7 select area_code,bill_month,sum(local_fare) local_fare8 from t9 group by area_code,bill_month10* )SQL> /AREA_CODE BILL_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARELAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE--------- ---------- -------------- -------------- --------------- --------------- ---------------5761 200405 13060.433 0 0 13318.93 13710 .2655761 200406 13318.93 0 13060.433 13710.265 14 135.7815761 200407 13710.265 13060.433 13318.93 14135.7815761 200408 14135.781 13318.93 13710.265 05762 200405 12643.791 0 0 12795.06 13224 .2975762 200406 12795.06 0 12643.791 13224.297 13 376.4685762 200407 13224.297 12643.791 12795.06 13376.4685762 200408 13376.468 12795.06 13224.297 05763 200405 13060.433 0 0 13318.93 13710 .2655763 200406 13318.93 0 13060.433 13710.265 14 135.7815763 200407 13710.265 13060.433 13318.93 14135.7815763 200408 14135.781 13318.93 13710.265 05764 200405 12487.791 0 0 13295.187 1344 4.0935764 200406 13295.187 0 12487.791 13444.093 1 3929.6945764 200407 13444.093 12487.791 13295.187 13929.6945764 200408 13929.694 13295.187 13444.093 05765 200405 25057.736 0 0 26058.46 26301 .8815765 200406 26058.46 0 25057.736 26301.881 27 130.6385765 200407 26301.881 25057.736 26058.46 27130.6385765 200408 27130.638 26058.46 26301.881 020 rows selected.利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据.4. sum,avg,max,min移动计算数据介绍计算出各个连续3个月的通话费用的平均数1 select area_code,bill_month, local_fare,2 sum(local_fare)3 over ( partition by area_code4 order by to_number(bill_month)5 range between 1 preceding and 1 following ) "3month_sum",6 avg(local_fare)7 over ( partition by area_code8 order by to_number(bill_month)9 range between 1 preceding and 1 following ) "3month_avg",10 max(local_fare)11 over ( partition by area_code12 order by to_number(bill_month)13 range between 1 preceding and 1 following ) "3month_max",14 min(local_fare)15 over ( partition by area_code16 order by to_number(bill_month)17 range between 1 preceding and 1 following ) "3month_min"18 from (19 select area_code,bill_month,sum(local_fare) local_fare20 from t21 group by area_code,bill_month22* )SQL> /AREA_CODE BILL_MONTH LOCAL_FARE 3month_sum 3month_avg3month_max 3month_min--------- ---------- ---------------- ---------- ---------- ---------- ----------5761 200405 13060.433 26379.363 13189.6815 13318.93 13060.433 5761 200406 13318.930 40089.628 13363.2093 13710.265 13060.433 5761 200407 13710.265 41164.976 13721.6587 14135.781 13318.93 40089.628 = 13060.433 + 13318.930 + 13710.26513363.2093 = (13060.433 + 13318.930 + 13710.265) / 313710.265 = max(13060.433 + 13318.930 + 13710.265)13060.433 = min(13060.433 + 13318.930 + 13710.265)5761 200408 14135.781 27846.046 13923.023 14135.781 13710.265 5762 200405 12643.791 25438.851 12719.4255 12795.06 12643.791 5762 200406 12795.060 38663.148 12887.716 13224.297 12643.791 5762 200407 13224.297 39395.825 13131.9417 13376.468 12795.06 5762 200408 13376.468 26600.765 13300.3825 13376.468 13224.297 5763 200405 13060.433 26379.363 13189.6815 13318.93 13060.433 5763 200406 13318.930 40089.628 13363.2093 13710.265 13060.433 5763 200407 13710.265 41164.976 13721.6587 14135.781 13318.935763 200408 14135.781 27846.046 13923.023 14135.781 13710.265 5764 200405 12487.791 25782.978 12891.489 13295.187 12487.791 5764 200406 13295.187 39227.071 13075.6903 13444.093 12487.791 5764 200407 13444.093 40668.974 13556.3247 13929.694 13295.187 5764 200408 13929.694 27373.787 13686.8935 13929.694 13444.093 5765 200405 25057.736 51116.196 25558.098 26058.46 25057.736 5765 200406 26058.460 77418.077 25806.0257 26301.881 25057.736 5765 200407 26301.881 79490.979 26496.993 27130.638 26058.46 5765 200408 27130.638 53432.519 26716.2595 27130.638 26301.88120 rows selected.5. ratio_to_report函数的介绍Quote:1 select bill_month,area_code,sum(local_fare) local_fare,2 ratio_to_report(sum(local_fare)) over3 ( partition by bill_month ) area_pct4 from t5* group by bill_month,area_codeSQL> break on bill_month skip 1SQL> compute sum of local_fare on bill_monthSQL> compute sum of area_pct on bill_monthSQL> /BILL_MONTH AREA_CODE LOCAL_FARE AREA_PCT---------- --------- ---------------- ----------200405 5761 13060.433 .1711492795762 12643.791 .1656894315763 13060.433 .1711492795764 12487.791 .1636451435765 25057.736 .328366866********** ---------------- ----------sum 76310.184 1200406 5761 13318.930 .1690507725762 12795.060 .1624015425763 13318.930 .1690507725764 13295.187 .1687494145765 26058.460 .330747499********** ---------------- ----------sum 78786.567 1200407 5761 13710.265 .1705451975762 13224.297 .1645001275763 13710.265 .1705451975764 13444.093 .1672342215765 26301.881 .327175257********** ---------------- ----------sum 80390.801 1200408 5761 14135.781 .1709111475762 13376.468 .1617305395763 14135.781 .1709111475764 13929.694 .1684194165765 27130.638 .328027751********** ---------------- ----------sum 82708.362 120 rows selected.6 first,last函数使用介绍Quote:取出每月通话费最高和最低的两个用户.1 select bill_month,area_code,sum(local_fare) local_fare,2 first_value(area_code)3 over (order by sum(local_fare) desc4 rows unbounded preceding) firstval,5 first_value(area_code)6 over (order by sum(local_fare) asc7 rows unbounded preceding) lastval8 from t9 group by bill_month,area_code10* order by bill_monthSQL> /BILL_MONTH AREA_CODE LOCAL_FARE FIRSTVAL LASTVAL ---------- --------- ---------------- --------------- ---------------200405 5764 12487.791 5765 5764200405 5762 12643.791 5765 5764200405 5761 13060.433 5765 5764200405 5765 25057.736 5765 5764200405 5763 13060.433 5765 5764200406 5762 12795.060 5765 5764200406 5763 13318.930 5765 5764200406 5764 13295.187 **** ****200406 5765 26058.460 5765 5764200406 5761 13318.930 5765 5764200407 5762 13224.297 5765 5764200407 5765 26301.881 5765 5764200407 5761 13710.265 5765 5764200407 5763 13710.265 5765 5764200407 5764 13444.093 5765 5764200408 5762 13376.468 5765 5764200408 5764 13929.694 5765 5764200408 5761 14135.781 5765 5764200408 5765 27130.638 5765 5764200408 5763 14135.781 5765 576420 rows selected.。
oracle常用的分析函数
oracle常⽤的分析函数常⽤的分析函数如下所列:row_number() over(partition by ... order by ...)rank() over(partition by ... order by ...)dense_rank() over(partition by ... order by ...)count() over(partition by ... order by ...)max() over(partition by ... order by ...)min() over(partition by ... order by ...)sum() over(partition by ... order by ...)avg() over(partition by ... order by ...)first_value() over(partition by ... order by ...)last_value() over(partition by ... order by ...)lag() over(partition by ... order by ...)lead() over(partition by ... order by ...)⼀、Oracle分析函数简介：在⽇常的⽣产环境中，我们接触得⽐较多的是OLTP系统(即Online Transaction Process)，这些系统的特点是具备实时要求，或者⾄少说对响应的时间多长有⼀定的要求；其次这些系统的业务逻辑⼀般⽐较复杂，可能需要经过多次的运算。
⽐如我们经常接触到的电⼦商城。
在这些系统之外，还有⼀种称之为OLAP的系统(即Online Aanalyse Process)，这些系统⼀般⽤于系统决策使⽤。
通常和数据仓库、数据分析、数据挖掘等概念联系在⼀起。
这些系统的特点是数据量⼤，对实时响应的要求不⾼或者根本不关注这⽅⾯的要求，以查询、统计操作为主。
oracle求同比,环比函数（LAG与LEAD）的详解
SELECT EMPLOYEENO ,YEARMONTH ,SALARY ,MIN(SALARY) KEEP(DENSE_RANK FIRST ORDER BY YEARMONTH) OVER(PARTITION BY EMPLOYEENO)
FIRST_SALARY -- 基比分析 salary/first_salary ,LAG(SALARY, 1, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_SAL
Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现， 不过使用LAG和LEAD有更高效率。
复制代码 代码如下:
CREATE TABLE salaryByMonth ( employeeNo varchar2(20), yearMonth varchar2(6), salary number ); insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY) values (1, '200805', 500); insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY) values (1, '200802', 150); insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY) values (1, '200803', 200); insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY) values (1, '200804', 300); insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY) values (1, '200708', 100); commit;
lag和lead函数的用法
lag和lead函数的⽤法今天刚开通了博客，随便学了点，⽹上写写。
当然刚开始肯定不是写的肯定不是很好的，但是总⽐不写强点，慢慢的进步么！！！！主要是lag和lead函数的⽤法，举两个例⼦来说明这两个函数是偏移量函数，可以查出⼀个字段的上⼀个值或者下⼀个值，配合over来使⽤。
如全表查询select * from tmp_cmu t 结果如下ITEM_NO PERIOD CMU---------------- ------------ ----------A01 201001 1000A01 201002 1200A01 201003 1190A01 201004 1600A01 201005 1980A01 201006 1890A01 201007 2100A01 201008 2320A01 201009 2540A01 201010 2490A01 201011 2610现在⾸先⽤lead函数，这个函数是向上偏移，现在对cmu字段进⾏偏移查询，如下sql语句select item_no,period,lead(cmu, 1) over(partition by item_no,substr(period, 1, 4) order by period) as cmupfrom tmp_cmu查询结果为：ITEM_NO PERIOD CMUP---------------- ------------ ----------A01 201001 1200A01 201002 1190A01 201003 1600A01 201004 1980A01 201005 1890A01 201006 2100A01 201007 2320A01 201008 2540A01 201009 2490A01 201010 2610A01 201011 2890可以看出cmu字段向上偏移了⼀位，lead(cmu,1)这⾥的参数是可以修改的，1代表偏移⼀位。
ORALCE函数LAG和LEAD分析函数详解
ORALCE函数LAG和LEAD分析函数详解在Oracle数据库中，LAG(和LEAD(是两个非常有用的分析函数，它们用于在查询结果集中访问前一行和后一行的数据。
LAG(函数允许我们在当前行之前的行中访问数据，而LEAD(函数允许我们在当前行之后的行中访问数据。
这两个函数可以在查询语句中作为窗口函数使用，提供了分析和比较结果集中数据的功能。
LAG(函数语法如下：LAG (expr [, offset [, default]]) OVER ( [ PARTITION BY expr ] ORDER BY expr)LEAD(函数语法如下：LEAD (expr [, offset [, default]]) OVER ( [ PARTITION BY expr ] ORDER BY expr)其中，expr是要访问的列或表达式，offset是向前或向后的偏移量（默认为1），default是可选的，表示当无法找到有效的偏移量时返回的默认值。
PARTITION BY和ORDER BY子句是可选的，用于定义分区和排序的方式。
例如，假设我们有一个名为employees的表，其中包含员工的姓名、职位和薪水信息。
我们可以使用LAG(函数来查找每个员工的上一个员工的职位：SELECT employee_name, job_title,LAG(job_title) OVER (ORDER BY employee_name) ASprevious_job_titleFROM employees;这个查询将返回一个结果集，其中包含员工姓名、职位和上一个职位的信息。
如果没有上一个职位，则LAG(函数将返回NULL。
类似地，我们可以使用LEAD(函数来查找每个员工的下一个员工的职位：SELECT employee_name, job_title,LEAD(job_title) OVER (ORDER BY employee_name) ASnext_job_titleFROM employees;这个查询将返回一个结果集，其中包含员工姓名、职位和下一个职位的信息。
oracle分组排序
分组函数oracle分析函数十分强大，我们只要掌握这些方法，更直接的说法就是知道这些分析函数的作用就能完成很多工作。
下边贴出这些函数，及简单应用。
其中我想对lag（）和lead（）函数坐下说明：lag（）本身是延后的意思也就是延后出现某列的数，而lead（）有引领、领先的意思也就是提前几行显示某列数据RANK()dense_rank()【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )【功能】聚合函数RANK 和dense_rank 主要的功能是计算一组数值中的排序值。
【参数】dense_rank与rank()用法相当，【区别】dence_rank在并列关系是，相关等级不会跳过。
rank则跳过rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。
【说明】Oracle分析函数【示例】聚合函数RANK 和dense_rank 主要的功能是计算一组数值中的排序值。
在9i版本之前，只有分析功能（analytic ），即从一个查询结果中计算每一行的排序值，是基于order_by_clause子句中的value_exprs指定字段的。
其语法为：RANK ( ) OVER ( [query_partition_clause] order_by_clause )在9i版本新增加了合计功能（aggregate），即对给定的参数值在设定的排序查询中计算出其排序值。
这些参数必须是常数或常值表达式，且必须和ORDER BY子句中的字段个数、位置、类型完全一致。
其语法为：RANK ( expr [, expr]... ) WITHIN GROUP( ORDER BYexpr [ DESC | ASC ] [NULLS { FIRST | LAST }][, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)例子1：有表Table内容如下COL1COL21 12 13 23 14 14 25 25 26 2分析功能：列出Col2分组后根据Col1排序,并生成数字列。
Oracle聚合函数分析
COVAR_POP，返回一对表达式的总体协方差。
COVAR_SAMP，返回一对表达式的样本协方差。
COUNT，对组内发生的事情进行累计。如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。
RATIO_TO_REPORT，该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比，即当前行对sum(expression)的贡献。
REGR_ (Linear Regression) Functions
功能描述：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用。
FIRST，从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
FIRST_VALUE，返回组中数据窗口的第一个值。
LAG，可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标是一个数组一样。在给定组中可参考当前行之前的 行，这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一 行），其相反的函数是LEAD
REGR_SLOPE：返回斜率，等于COVAR_POP(expr1, expr2) / VAR_POP(expr2)
REGR_INTERCEPT：返回回归线的y截距，等于
AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)
分析函数总结(oracle技术交流群)
分析函数的基础语法oracle分析函数的语法：function_name(arg1,arg2,...)over(<partition-clause> <order-by-clause ><windowing clause>)说明：1. partition-clause 数据记录集分组2. order-by-clause 数据记录集排序3. windowing clause 功能超级壮大、比较复杂，概念分析函数在操作行的集合。
有三种开窗方式: range、row、specifying。
分析函数的四大要点Analytic FunctionPARTITION BY ORDER BYROWS Vs RANGEUNBOUNDED PRECEDING FOLLOWING CURRENT ROW1.有的分析函数能够带window,有的不能带window子句,不能带window子句的比如rank,dense_rank,row_number,FIRST,LAST,lead,lag能够带windows的比如count,SUM,AVG,MIN,MAX,first_value,last_value。
FIRST,LAST里的分析函数部份order BY都是不许诺的有的分析函数，比如row_number,dense_rank,rank是必需要有order BY的。
BY 是按字段值将对应的行分组(不能带括号，带括号的是model和PARTITIONED outer JOIN利用的)，ORDER BY 是组内行的顺序，window子句决定每行对应的窗口范围BY ,ORDER BY ,window子句一起决定了当前行对应的窗口范围，当前行分析函数值确实是基于那个窗口计算的4.注意partition BY,ORDER BY,window子句的关系window子句是在partition by和order BY前提下设定当前行对应的窗口范围的,因此必需有order by才能写window子句。
用分析函数计算环比、同比oracle
⽤分析函数计算环⽐、同⽐oracle1.普及⼀下概念环⽐ = 2018年10⽉/2018年09⽉（同⼀时期内不同时间段的⽐较）同⽐ = 2018年10⽉/2017年10⽉（不同时期内相同时间段的⽐较）环⽐增长率 = （2018年10⽉-2018年09⽉）/2018年09⽉同⽐增长率 = （2018年10⽉-2017年10⽉）/2017年10⽉2.关键sql 解释(1)分析函数lead(params,m,n) 以params为⽬标向下m位取数，当取不到时默认为 n,lag(params,m,n) 以params为⽬标向上m位取数，当取不到时默认为 nover(order by...) 相当于 order by...partition by ... 根据参数最为分割，将相同的先分割到⼀块3.具体sql例⼦//按⽉统计发票的总⾦额、总税额、总票数、⽉同⽐、⽉环⽐、同⽐增长率、环⽐增长率select a.sumquantity,nvl(round(a.sumquantity /lag(a.sumquantity) over( order by a.yearmonth)*100, 2), '0') ||'%' sumquantityHB,nvl(round((gAHB)/lag(a.sumquantity) over( order by a.yearmonth)*100, 2), '0') ||'%' sumquantityHBG,//计算环⽐（按⽉）关键代码lag()over(partition by ⽉份/年份 order by 排序条件)nvl(round(a.sumquantity/lag(a.sumquantity) over(partition by to_char((to_date(a.yearmonth, 'yyyy/MM')), 'MM') order by a.yearmonth)*100, 2), '0')||'%' sumquantityTB,nvl(round((gATB)/lag(a.sumquantity) over(partition by to_char((to_date(a.yearmonth, 'yyyy/MM')), 'MM') order bya.yearmonth)*100, 2), '0') ||'%' sumquantityTBG,a.totalamount,nvl(round(a.totalamount/lag(a.totalamount) over( order by a.yearmonth)*100, 2), '0')||'%' totalamountHB,nvl(round((gBHB)/lag(a.totalamount) over( order by a.yearmonth)*100, 2), '0') || '%' totalamountHBG,nvl(round(a.totalamount/lag(a.totalamount) over(partition by to_char((to_date(a.yearmonth, 'yyyy/MM')), 'MM') order by a.yearmonth)*100, 2), '0')||'%' totalamountTB,nvl(round((gBTB)/lag(a.totalamount) over(partition by to_char((to_date(a.yearmonth, 'yyyy/MM')), 'MM') order bya.yearmonth)*100, 2), '0')||'%' totalamountTB,a.taxamount ,nvl(round(a.taxamount/lag(a.taxamount) over( order by a.yearmonth)*100, 2), '0')||'%' taxamountHB,nvl(round((gCHB)/lag(a.taxamount) over( order by a.yearmonth)*100, 2), '0')||'%' taxamountHBG,nvl(round(a.taxamount/lag(a.taxamount) over(partition by to_char((to_date(a.yearmonth, 'yyyy/MM')), 'MM') order by a.yearmonth)*100, 2), '0')||'%' taxamountTB,nvl(round((gCTB)/lag(a.taxamount) over(partition by to_char((to_date(a.yearmonth,'yyyy/MM')),'MM') orderbya.yearmonth)*100,2),'0')||'%' taxamountTBGfrom( select yearMonth, sumQuantity, lag(sumQuantity) over( order by yearmonth) lagAHB ,lag(sumQuantity) over(partition by to_char((to_date( yearmonth, 'yyyy/MM')), 'MM') order by yearmonth) lagATB,totalAmount, lag(totalAmount) over(order by yearmonth) lagBHB,lag(totalAmount) over(partition by to_char((to_date( yearmonth, 'yyyy/MM')), 'MM') order by yearmonth) lagBTB,taxAmount , lag(taxAmount) over(partition by to_char((to_date( yearmonth, 'yyyy/MM')), 'MM') order by yearmonth) lagCTB,lag(taxAmount) over( order by yearmonth) lagCHB //计算环⽐（按⽉）关键代码lag()over(partition by ⽉份/年份 order by 排序条件)from( select to_char(billing_date, 'yyyy/MM') yearMonth, count(1) sumQuantity , nvl(sum(total_amount) , 0.00) totalAmount, nvl(sum(total_tax) , 0.00) taxAmountfrom input_original_accountwhere 1=1 group by to_char(billing_date, 'yyyy/MM'))//先按⽉份对要统计的数据进项分组) awhere 1=1 and a.yearMonth='2018/09'。