oracle中LAG()和LEAD()等分析统计函数的用法

LAG()和LEAD()统计函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现,不过使用LAG和 LEAD有更高的效率。以下整理的LAG()和LEAD()例子:

LAG(EXPRESSION,<OFFSET>,<DEFAULT>)

OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值
SQL> select year,region,profit ,lag (profit,1) over (order by year)  as 51xit_exp from test;
YEAR REGION      PROFIT 51xit_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

LEAD(EXPRESION,<OFFSET>,<DEFAULT>)
SQL> select year,region,profit ,lead (profit,1) over (order by year)  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

Lag函数为Lag(exp,N,defval),defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。
Lead和Lag函数也可以使用分组,以下是使用region分组的例子:

SQL> select year,region,profit , lag (profit,1,0) over (PARTITION BY region order by year)    as 51xit_exp from test;
YEAR REGION      PROFIT 51xit_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

一SQL问题解答:
问题:
CREATE   TABLE  ldy_temp_2
(
  分局    VARCHAR(255),
派出所    VARCHAR(255) ,
证件类型    VARCHAR(255) ,
证件号码    VARCHAR(255) ,
姓名    VARCHAR(255) ,
性别    VARCHAR(255) ,
行政区划    VARCHAR(255) ,
旅馆名称    VARCHAR(255) ,
旅馆地址    VARCHAR(255) ,
房间号    VARCHAR(255) ,
入住时间    VARCHAR(255) ,
col012    VARCHAR(255)
);

INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES('1234','ZHANGTAO','A','20100506');
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES('1234','ZHANGTAO','A','20100507');
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES('1234','ZHANGTAO','B','20100508');



INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES('1234','ZHANGTAO','A','20100509');
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES('1235','ZZZZ','A','20100506');
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES('1235','ZZZZ','B','20100507');
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES('1235','ZZZZ','A','20100508');
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES('1235','ZZZZ','B','20100509');

建表语句和测试数据已经给出  请问  如何查找相邻两次入住旅馆名称不同的人;也就是说 一个人的证件号码是123的话 那么这个人的信息按照入住时间排序后  相邻两条数据的旅馆名称不能一样 。

解答:
with temp_a as
(select
        t.证件号码,
        t.旅馆名称,
        t.入住时间,
        lag(t.旅馆名称) over (partition by t.证件号码 order by t.入住时间) as lagname
from ldy_temp_2 t)
select 证件号码,姓名,旅馆名称,入住时间
from ldy_temp_2 a
where a.证件号码 not in (select b.证件号码 from temp_a b where  b.旅馆名称=b.lagname)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值