160426: 31회차

Programming 2016. 4. 26. 18:26

종료하기 전 티스토리 네이버 로그아웃 할 것

1. 툴

동일

추가시:

2. 폴더

동일

추가시:

3. 사용할 사이트

동일

추가시:

4. 공부하는 것

오늘 어제 팀 프로젝트 하던 거 3시 40분까지 고쳐서 별로 기운이 없으니 위안이 되는 럭키블루

6시 24분!

으아아 와장창

jsp_img.zip

LAG, LEAD

게시자: MyungJong Kim, 2011. 12. 14. 오후 3:21

- 하나의 쿼리에서 동시에 다른 로우의 값에 접근할 수 있는 함수

[구문 형식]

LAG ( expr, offset, default ) OVER ( PARTITION BY 절 )

LEAD ( expr, offset, default ) OVER ( PARTITION BY 절 )

- LAG : 계산 대상 데이터(로우)들을 PARTITION BY 절로 구분하여, expr에 명시된 값을 기준으로 이전 로우의 값을 반환

off : 생략 가능한데, 생략할 경우 기본값(1)으로 설정됨 (바로 이전 로우의 값을 반환)

default: offset에 지정된 로우가 존재하지 않을 경우 LAG 함수가 반환하는 기본 값. 이 역시 생략 가능

- LEAD : 이전 로우가 아닌 이후의 로우값을 반환

select hire_date

, count(*)

, LAG(count(*)) OVER (ORDER BY hire_date) lag1 -- 이전 년도 사원수

, LAG(count(*), 1, 0) OVER (ORDER BY hire_date) lag2 -- 이전 년도 사원수(상동)

, LAG(count(*), 2, 0) OVER (ORDER BY hire_date) lag3 -- 2년전 사원수

, LEAD(count(*)) OVER (ORDER BY hire_date) lead1 -- 이후 년도 사원수

, LEAD(count(*), 1, 0) OVER (ORDER BY hire_date) lead2 -- 이후 년도 사원수(상동)

, LEAD(count(*), 2, 0) OVER (ORDER BY hire_date) lead3 -- 2년후 사원수

from (select 100 id, '2011' hire_date from dual union all

select 101 id, '2010' hire_date from dual union all

select 102 id, '2011' hire_date from dual union all

select 103 id, '2010' hire_date from dual union all

select 104 id, '2009' hire_date from dual union all

select 105 id, '2009' hire_date from dual union all

select 106 id, '2009' hire_date from dual union all

select 107 id, '2008' hire_date from dual union all

select 108 id, '2007' hire_date from dual union all

select 109 id, '2006' hire_date from dual union all

select 100 id, '2006' hire_date from dual union all

select 111 id, '2006' hire_date from dual )

group by hire_date ;

---------------------------------------------------------------------

HIRE_DATE COUNT(*) LAG1 LAG2 LAG3 LEAD1 LEAD2 LEAD3

---------------------------------------------------------------------

2006 3 0 0 1 1 1

2007 1 3 3 0 1 1 3

2008 1 1 1 3 3 3 2

2009 3 1 1 1 2 2 2

2010 2 3 3 1 2 2 0

2011 2 2 2 3 0 0

---------------------------------------------------------------------

https://sites.google.com/site/smcgbu/home/gongbu-iyagi/laglead

ORACLE LEAD() OVER() & LAG() OVER() FUNCTION

ORACLE 이야기 2012.10.04 12:43

현재 행의 이전 또는 이후의 행 값을 들고와야 할 때. 아래의 펑션을 이용하면 됨.

버전별로 지원하는지 안하는지는 확인 안했음.

/* LEAD() OVER() FUNCTION
   현재 행의 다음 데이터에 접근.
   ACCESS_ROW_INDEX : 현재 행 기준 N 번째 행의 값을 들고 옴.
   1 : 현재 행의 바로 다음 행 값.
   2 : 현재 행의 2번째 행 값.
   ...
*/
-- QUERY

SELECT LEAD([COLUMN_NANE], [ACCESS_ROW_INDEX]) OVER(ORDER BY [COLUMN_NAME])
FROM [TABLE_NAME];

/* LAG() OVER() FUNCTION
현재 행의 이전 데이터에 접근.
*/

SELECT LAG([COLUMN_NANE], [ACCESS_ROW_INDEX]) OVER(ORDER BY [COLUMN_NAME])
FROM [TABLE_NAME];

Ex)

SELECT A.*, LEAD(A.COLUMN1, 1) OVER(ORDER BY A.COLUMN2) AS NEXT_VALUE

FROM TABLE A;

COLUMN1 COLUMN2

1 A

2 B

3 C

의 데이터가 존재하는 경우 위 쿼리를 실행하면,

COLUMN1 COLUMN2 NEXT_VALUE

1 A 2

2 B 3

3 C (NULL)

--ACCESS_ROW_INDEX 가 2 인 경우

COLUMN1 COLUMN2 NEXT_VALUE

1 A 3

2 B (NULL)

3 C (NULL)

LAG 는 이전 데이터를 들고오며 LEAD와 문법은 동일.

http://nephrolepis.tistory.com/entry/Oracle-lead-over-lag-over-function

오라클 분석함수 lag(), lead()

1. LAG() OVER()
가. 지정한 컬럼의 앞의 값을 가져온다.
나. 문법

Description of lag.gif follows

다. 예제

SELECT LEVEL LVL
     , LAG(LEVEL) OVER(ORDER BY LEVEL ASC ) AS LAG_VALUE1
     , LAG(LEVEL, 2) OVER(ORDER BY LEVEL ASC ) AS LAG_VALUE2
     , LAG(LEVEL, 3, 100) OVER(ORDER BY LEVEL ASC) AS LAG_VALUE3
  FROM DUAL CONNECT BY LEVEL <= 10
;
       LVL LAG_VALUE1 LAG_VALUE2 LAG_VALUE3
---------- ---------- ---------- ----------
         1                              100
         2          1                   100
         3          2          1        100
         4          3          2          1
         5          4          3          2
         6          5          4          3
         7          6          5          4
         8          7          6          5
         9          8          7          6
        10          9          8          7

SELECT LEVEL LVL
     , LAG(LEVEL, 1, 100) OVER(ORDER BY LEVEL DESC) AS LAG_VALUE3
  FROM DUAL CONNECT BY LEVEL <= 10
;
       LVL LAG_VALUE3
---------- ----------
        10        100
         9         10
         8          9
         7          8
         6          7
         5          6
         4          5
         3          4
         2          3
         1          2

2. LEAD() OVER()

가. 지정한 컬럼의 뒤의 값을 가져온다.

나. 문법

다. 예제
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SELECT LEVEL LVL
     , LEAD(LEVEL) OVER(ORDER BY LEVEL ASC ) AS LAG_VALUE1
     , LEAD(LEVEL, 2) OVER(ORDER BY LEVEL ASC ) AS LAG_VALUE2
     , LEAD(LEVEL, 3, 100) OVER(ORDER BY LEVEL ASC) AS LAG_VALUE3
  FROM DUAL CONNECT BY LEVEL <= 10
;

       LVL LAG_VALUE1 LAG_VALUE2 LAG_VALUE3
---------- ---------- ---------- ----------
       1    2    3    4
       2    3    4    5
       3    4    5    6
       4    5    6    7
       5    6    7    8
       6    7    8    9
       7    8    9 10
       8    9 10    100
       9 10 100
    10    100

SELECT LEVEL LVL
     , LEAD(LEVEL, 1, 100) OVER(ORDER BY LEVEL DESC) AS LAG_VALUE3
  FROM DUAL CONNECT BY LEVEL <= 10
;

       LVL LAG_VALUE3
---------- ----------
    10    9
       9    8
       8    7
       7    6
       6    5
       5    4
       4    3
       3    2
       2    1
       1    100

http://www.jigi.net/4335

오라클 over() 함수

over 함수는 group by, order by 를 이용한 서브쿼리의 사용을 편리하게 하기 위해 사용하는 함수이다.

보통 over 함수는 단독으로 사용하기 보다는

count(), max(), min(), sum(), avg(), rank(), row_number() 등과 같은 분석 함수와 함께 사용된다.

-- OVER 함수 이용
SELECT
    NAME,
    NO,
    SUM(NO) over(PARTITION BY NAME ORDER BY NAME)
FROM (
SELECT 'A' AS NAME, '31' AS NO FROM dual UNION
SELECT 'B', '32' FROM dual UNION
SELECT 'B', '45' FROM dual UNION
SELECT 'D', '95' FROM dual UNION
SELECT 'D', '41' FROM dual UNION
SELECT 'D', '15' FROM dual)
 
/*
-- 결과
1   A   31  31
2   B   32  77
3   B   45  77
4   D   15  151
5   D   41  151
6   D   95  151
*/
-- GROUP BY 이용
SELECT
    NAME,
    NO,
    sum(NO)
FROM (
SELECT 'A' AS NAME, '31' AS NO FROM dual UNION
SELECT 'B', '32' FROM dual UNION
SELECT 'B', '45' FROM dual UNION
SELECT 'D', '95' FROM dual UNION
SELECT 'D', '41' FROM dual UNION
SELECT 'D', '15' FROM dual)
GROUP BY NAME, NO
ORDER BY NAME
 
/*
-- 결과
    NAME    NO  SUM(NO)
1   A   31  31
2   B   32  32
3   B   45  45
4   D   15  15
5   D   41  41
6   D   95  95
*/

위와같이 Name에 따른 No의 합을 구하려면 group by 를 이용하면 위와 같이 사용하면 안되고 서브쿼리를 이용하여 처리를 해야 한다.

따라서 코드가 복잡해지고, 유지보수에 어려움이 생기게 될 것이다.

이럴때 등장하는 것이 over() 함수 인 것이다.

* partition by 는 group by 라고 생각하면 이해하기가 좀 더 쉬울 것 같다.

http://118k.tistory.com/40

5. 수업

진도:

hw:

6. 할것

나 왜 내일 발표야~~~~~~

'Programming' 카테고리의 다른 글

160428: 33회차 (0)	2016.04.28
160427: 32회차 (1)	2016.04.27
160425: 30회차 (0)	2016.04.25
160422: 29회차 (0)	2016.04.22
160421: 28회차 (1)	2016.04.21

Posted by ＤＡＶＩＤ

PC

160426: 31회차

LAG, LEAD

ORACLE LEAD() OVER() & LAG() OVER() FUNCTION

오라클 over() 함수

'Programming' 카테고리의 다른 글

티스토리툴바