oracle lag和lead函数
2010-11-01 17:45
这两个函数，是偏移量函数，其用途是：可以查出同一字段下一个值或上一个值，并作为新列存在表中。
某表
国家-------行业-------总值---------时间---------上次总值
china------农业--------50--------2010-01-25----空缺目前没值
china------农业--------40--------2010-01-15----空缺目前没值
china------农业--------30--------2010-01-21----空缺目前没值
china------农业--------20--------2010-01-05----空缺目前没值
USA--------工业--------50--------2010-01-25----空缺目前没值
USA--------工业--------40--------2010-01-15----空缺目前没值
USA--------玩具--------500-------2010-01-25----空缺目前没值
USA--------玩具--------1000------2010-01-05----空缺目前没值
需要对该表进行更新的操作，但不能用update，最好是用 insert
规则、举例：
国家是中国，行业是农业，时间是2010-1-25 的总值是50，他上次总值是 2010-01-21 号的 30
国家是中国，行业是农业，时间是2010-1-15 的总值是40，他上次总值是 2010-01-05 号的 20
例外：
国家是中国，行业是农业，时间是2010-1-05 的总值是20,当前表中数据 同行业同国家的， 没有比他日期还小的，那么给以个值
0
以上面模拟数据，更新后的结果为：
国家-------行业-------总值---------时间---------上次总值
china------农业--------50--------2010-01-25------30
china------农业--------40--------2010-01-15------20
china------农业--------30--------2010-01-21------40
china------农业--------20--------2010-01-05------0
USA--------工业--------50--------2010-01-25------40
USA--------工业--------40--------2010-01-15------0
USA--------玩具--------500-------2010-01-25------1000
USA--------玩具--------1000------2010-01-05------0
就是这样了， 求SQL
答案：select 国家
,行业
,总值
,时间
,lag(总值,1,0) over(partition by 国家,行业 order by 时间 ) 上次总值
from tb
妈的 在网上查了半天 也没见那个网站说的清楚 而且都是同一个例子 汗啊！ 还是自己运行琢磨弄明白 下面实例为证。
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
16:53更新：感谢这个文章，终于看懂了http://blog.163.com/xieke_li/blog/static/3636427620095122340687/
Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。
语法如下：
LAG (value_expression [,offset] [,default]) OVER ([query_partition_clase] order_by_clause) 上一个
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clase] order_by_clause) 下一个
其中：
value_expression：可以是一个字段或一个内建函数。
offset是正整数，默认为1，指往前或往后几点记录．因组内第一个条记录没有之前的行，最后一行没有之后的行，
default就是用于处理这样的信息，默认为空．
以下是LAG和LEAD的例子：
SQL> select year,region,profit ,lag (profit,1) over (order by year)
2 as last_year_exp from test;
YEAR REGION PROFIT LAST_YEAR_EXP
---- ------- ---------- -------------
2003 West 88
2003 West 88 88
2003 Central 101 88
2003 Central 100 101
2003 East 102 100
2004 West 77 102
2004 East 103 77
2004 West 89 103
SQL> select year,region,profit ,lead (profit,1) over (order by year)
2 as next_year_exp from test;
YEAR REGION PROFIT NEXT_YEAR_EXP
---- ------- ---------- -------------
2003 West 88 88
2003 West 88 101
2003 Central 101 100
2003 Central 100 102
2003 East 102 77
2004 West 77 103
2004 East 103 89
2004 West 89
Lead和Lag函数也可以使用分组，以下是使用region分组的例子（即在每组之内起效果）：
SQL> select year,region,profit ,
2 lag (profit,1,0) over (PARTITION BY region order by year)
3 as last_year_exp from test;
YEAR REGION PROFIT LAST_YEAR_EXP
---- ------- ---------- -------------
2003 Central 101 0
2003 Central 100 101
2003 East 102 0
2004 East 103 102
2003 West 88 0
2003 West 88 88
2004 West 77 88
2004 West 89 77