分析関数の基礎 - LAGとLEAD
September 09, 2004
分析関数の中でも、LAGとLEADは特殊な関数である。
LAGは、現在の行よりも前の行の何れかの項目の値を参照する。LEADは、現在の行よりも後の行の何れかの項目を値を参照する。これらは、通常であれば副問い合わせを行わなければ実現できない。
LAGの例
LAGは、選択した行の中で、現在の行よりも以前の行のデータを取り出す。言い換えれば、データを遅延(lag)して表示する。
各事業所(DEPT)での、各従業員の雇用日の間隔を計算してみる。PREV_HIREDATEは、その従業員より以前に雇用された従業員の直近の雇用日となる。GAPが間隔日数である。
ora817@robios.org>select dept.loc, emp.ename, emp.job, emp.hiredate,
2 lag(emp.hiredate, 1, null) over
3 (partition by dept.loc order by emp.hiredate) as prev_hiredate,
4 emp.hiredate -
5 lag(emp.hiredate, 1, null) over
6 (partition by dept.loc order by emp.hiredate) as gap
7 from scott.emp, scott.dept
8 where emp.deptno = dept.deptno;
LOC ENAME JOB HIREDATE PREV_HIR GAP
------------- ---------- --------- -------- -------- ----------
CHICAGO ALLEN SALESMAN 81-02-20
CHICAGO WARD SALESMAN 81-02-22 81-02-20 2
CHICAGO BLAKE MANAGER 81-05-01 81-02-22 68
CHICAGO TURNER SALESMAN 81-09-08 81-05-01 130
CHICAGO MARTIN SALESMAN 81-09-28 81-09-08 20
CHICAGO JAMES CLERK 81-12-03 81-09-28 66
DALLAS SMITH CLERK 80-12-17
DALLAS JONES MANAGER 81-04-02 80-12-17 106
DALLAS FORD ANALYST 81-12-03 81-04-02 245
DALLAS SCOTT ANALYST 82-12-09 81-12-03 371
DALLAS ADAMS CLERK 83-01-12 82-12-09 34
NEW YORK CLARK MANAGER 81-06-09
NEW YORK KING PRESIDENT 81-11-17 81-06-09 161
NEW YORK MILLER CLERK 82-01-23 81-11-17 67
14行が選択されました。
経過: 00:00:16.01
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 WINDOW (SORT)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0094164' (UNIQUE)
統計
----------------------------------------------------------
0 recursive calls
4 db block gets
17 consistent gets
4 physical reads
0 redo size
1108 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
それぞれの事業所で初めの行のPREV_HIREDATEがブランクなのは、それ以前の行がないからである。この場合は、LAGの3番目の引数(今回はNULL)を表示する。
LEADの例
LEADは、選択した行の中で、現在の行よりも以降の行のデータを取り出す。言い換えれば、データを先取り(lead)して表示する。
従業員SMITHからKING社長まで、直属との上司との給与の差を計算する。MGR_SALが直属の上司の給与、GAPが自分の給与との差である。(FORDは上司のJONESよりも給与が良い！)
ora817@robios.org>select job, ename, hiredate, sal,
2 lead(sal, 1, null) over (order by rownum) as mgr_sal,
3 lead(sal, 1, null) over (order by rownum) - sal as gap
4 from (
5 select rownum, job, ename, hiredate, sal
6 from scott.emp
7 connect by prior mgr = empno
8 start with empno = 7369);
JOB ENAME HIREDATE SAL MGR_SAL GAP
--------- ---------- -------- ---------- ---------- ----------
CLERK SMITH 80-12-17 800 3000 2200
ANALYST FORD 81-12-03 3000 2975 -25
MANAGER JONES 81-04-02 2975 5000 2025
PRESIDENT KING 81-11-17 5000
経過: 00:00:10.02
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 WINDOW (SORT)
2 1 COUNT
3 2 VIEW
4 3 COUNT
5 4 CONNECT BY
6 5 INDEX (UNIQUE SCAN) OF 'SYS_C0094163' (UNIQUE)
7 5 TABLE ACCESS (BY USER ROWID) OF 'EMP'
8 5 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
9 8 INDEX (UNIQUE SCAN) OF 'SYS_C0094163' (UNIQUE)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
2 physical reads
0 redo size
543 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
KING社長のMGR_SALがブランクなのは、それ以降の行がないからである。この場合は、LEADの3番目の引数(今回はNULL)を表示する。
LAG/LEADの文法
LAG/LEAD ( 項目または式, <Offset>, <Default>) over (
<Partition-Clause>
Order-By-Clause
)
項目または式は、値を取得したい項目、またはそれを組み合わせた式となる。
Offsetでは、LAGの場合は現在行から何行前から、LEADの場合は現在行から何行後からデータを取得するかを指定する。省略した場合は1となる。
Defaultは、境界を越えてしまいデータの取得が行えない場合に、表示する値である。省略した場合はNULLとなる。
LAG/LEADでは、Order-By-Clauseが必須となる。また、Windowing-Clauseは使えない。Partition-Clauseは任意で設定する。
http://www.robios.org/x/mt/mt-tb.cgi/30
Microsoft and Peter Jackson postpone the making of a film based on the Halo video game after backers pull out...
Posted by: Brent Duncan at December 14, 2006 02:25 AMVeteran game show host Bob Barker is stepping down from hosting The Price is Right after 35 years...
Posted by: Rudy Guffey at December 13, 2006 05:30 PMTV host Oprah Winfrey gives audience members $1,000 (�526) each to donate to a charitable cause...
Posted by: Josef Ennis at December 13, 2006 07:45 AMThe first stage of a �150m investment in regional museums is praised for boosting visitor numbers...
Posted by: Alan Adler at December 6, 2006 07:28 PMPioneering screenwriter Nigel Kneale, best known for the Quatermass TV serials and films, dies aged 84...
Posted by: Branden Combs at December 6, 2006 01:20 PMpregnant women gay cum dog bestiality free incest movies halo movie shemale sex bikini model close up vagina pictures fat guy pornstar houston brunette babe swedish babe mature bitches female masturbation video massive cocks fucking women blonde lesbians stocking pictures gay cum amateur and free and video anime girls fucking hardcore galleries boys naked teens sex a tight pussy carmen electra pics anal toys beach fuck bikini swimsuit nudist family interracial gang bang nude webcam porn videos pissing galleries robbs celebrities ass worship breast torture upskirt bbs big butt latinas huge black cocks hardcore blowjobs 18 hot girls paris hilton sex video shaved teens dirty blondes shit pants free adult movie clips bondage movies ass pics free sex video free nude galleries of redheads cheerleader teen bang boat cartoon rape rape sex nude asian free voyeur
Posted by: asian xxx at December 4, 2006 11:56 AMpregnant women gay cum dog bestiality free incest movies halo movie shemale sex bikini model close up vagina pictures fat guy pornstar houston brunette babe swedish babe mature bitches female masturbation video massive cocks fucking women blonde lesbians stocking pictures gay cum amateur and free and video anime girls fucking hardcore galleries boys naked teens sex a tight pussy carmen electra pics anal toys beach fuck bikini swimsuit nudist family interracial gang bang nude webcam porn videos pissing galleries robbs celebrities ass worship breast torture upskirt bbs big butt latinas huge black cocks hardcore blowjobs 18 hot girls paris hilton sex video shaved teens dirty blondes shit pants free adult movie clips bondage movies ass pics free sex video free nude galleries of redheads cheerleader teen bang boat cartoon rape rape sex nude asian free voyeur
Posted by: asian xxx at December 4, 2006 11:56 AMcartoons adult live free web cams mature pics rape galleries homemade porn animal sex closeup of vagina halle berry nude beastiality forums voyeur cam fat albert cartoon incest milla jovovich nude cheerleader sex famous transsexuals latina anal dog dick in pussy baywatch babes black porn lesbian love big breast lovers cute redhead kelly ripa bikini sublime adult directory shit eaters teen fuck pissing video black anal pink porn stars big thick butt black women asian boobs abercrombie models double fisting anal self blowjobs outdoor bondage amateur women tenchi hentai nylon webbing interracial gallery girls ass girl suck a dick first masturbation cum facial gay anime naked brunette best lesbian movie female celebrities asian gangbang lesbian girls licking naked blondes hardcore lesbians adult free video clip bikini blondes shaved penis teens naked asian sex
Posted by: free online sex games at December 4, 2006 06:09 AMcartoons adult live free web cams mature pics rape galleries homemade porn animal sex closeup of vagina halle berry nude beastiality forums voyeur cam fat albert cartoon incest milla jovovich nude cheerleader sex famous transsexuals latina anal dog dick in pussy baywatch babes black porn lesbian love big breast lovers cute redhead kelly ripa bikini sublime adult directory shit eaters teen fuck pissing video black anal pink porn stars big thick butt black women asian boobs abercrombie models double fisting anal self blowjobs outdoor bondage amateur women tenchi hentai nylon webbing interracial gallery girls ass girl suck a dick first masturbation cum facial gay anime naked brunette best lesbian movie female celebrities asian gangbang lesbian girls licking naked blondes hardcore lesbians adult free video clip bikini blondes shaved penis teens naked asian sex
Posted by: free online sex games at December 4, 2006 06:08 AMFools rush in where angels fear to tread... Dionise
Posted by: Dionise at November 22, 2006 06:03 PMMicrosoft and Peter Jackson postpone the making of a film based on the Halo video game after backers pull out...
Posted by: Craig Eng at November 17, 2006 10:28 PMPioneering screenwriter Nigel Kneale, best known for the Quatermass TV serials and films, dies aged 84...
Posted by: Antony Mchugh at November 17, 2006 05:11 PMAlec Baldwin asks for his voice to be removed from an "unfair" documentary about Arnold Schwarzenegger...
Posted by: Emiliano Curtin at November 13, 2006 11:03 AMAlec Baldwin asks for his voice to be removed from an "unfair" documentary about Arnold Schwarzenegger...
Posted by: Emiliano Curtin at November 13, 2006 11:02 AMHello all.
Posted by: carisoprodol at November 5, 2006 06:24 PMVery good content.
apuooxoo
Posted by: Jakob Oglesby at November 2, 2006 07:02 AMbus gang bang gang bang movies
Posted by: Aoirkmoesa at October 31, 2006 08:28 PMFinancing that provides Cash Advance Loans or Cash Advances are available across the country. Cash Advance http://www.idealcashadvance.com
Posted by: Cash Loan Advance at October 13, 2006 04:18 AMKeep up the great work on your blog. Best wishes WaltDe
Posted by: WaltDe at September 1, 2006 09:35 AMhttp://sites.redclouds.com/leezasplace/messages/18979.html complimentwhosewondered
Posted by: might at June 2, 2006 03:00 PMVery soon the Rabbit say to itself, I shall fall right THROUGH the earth! How funny it'll seem to dry me at all.' In that case, said in aout.
Posted by: instant credit card approval at May 1, 2006 12:44 AMbuy tramadol online at http://www.tramadol-today.com
Posted by: tramadol at March 1, 2006 05:45 AMhttp://amateur.anzwers.net/xxxgirls/cartoon/incest-video.html complimentwhosewondered
Posted by: kicked at February 25, 2006 09:17 PMhttp://www.paramom.com/wwwboard/messages/9611.html sightstickysubmit
Posted by: drying at February 10, 2006 07:34 AMhttp://www.femmesdenudees.com/lesbienne/hosenda/gayporn/storys/jackoff.html garyhoodlumnow
Posted by: simultaneously at January 19, 2006 06:44 AM