Oracle中LAG()和LEAD()以及OVER(PARTITION BY)

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

LAG(EXPRESSION,<OFFSET>,<DEFAULT>)
SQL> select year,region,profit ,lag (profit,1) over (order by year)  as 51xit_exp from test;
--输出结果
YEAR REGION      PROFIT   51xit_exp
---- ------- ---------- -------------
West            88
West            88            88
Central        101            88
Central        100           101
East           102           100
West            77           102
East           103            77
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
---- ------- ---------- -------------
West            88            88
West            88           101
Central        101           100
Central        100           102
East           102            77
West            77           103
East           103            89
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
---- ------- ---------- -------------
Central        101             0
Central        100           101
East           102             0
East           103           102
West            88             0
West            88            88
West            77            88
West            89            77

例子
如图所示的表：

请问 ：如何查找相邻两次入住旅馆名称不同的人；也就是说 一个人的证件号码是123的话 那么这个人的信息按照入住时间排序后 相邻两条数据的旅馆名称不能一样 。

--查找同一个人下次入住不在同一旅馆的数据
SELECT 证件号码,姓名,入住时间 FROM (SELECT f.证件号码,f.姓名 ,f.入住时间, f.旅馆名称 AS f旅馆,
LEAD(旅馆名称,1) OVER(PARTITION BY 证件号码 ORDER BY 入住时间) AS n旅馆
FROM ldy_temp_2 f)
WHERE f旅馆<>n旅馆;

ROW_NUMBER() over(PARTITION BY … ORDER BY … ASC) AS line
按照分组和排序给每组标序号，每一组的row_number都是相同的。