oracle中LAG()和LEAD()等分析統計函數的使用方法(統計月增長率)

標籤：style   color   io   使用   ar   資料   div   art   sp   

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
---- ------- ---------- -------------
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)

oracle中LAG()和LEAD()等分析統計函數的使用方法(統計月增長率)