분석함수 LAG 와 LEAD 함수 : 이전값, 다음값 [출처] [오라클] 분석함수 LAG 와 LEAD 함수 : 이전값, 다음값 손쉽게 가져오기|작성자 자바킹

http://javaking75.blog.me/220073169905

LAG와 LEAD 함수는 하나의 쿼리에서 동시에 다른 로우의 값에 접근할수있는 함수이다. 

 

구문형식

LAG ( expr, offset, default ) OVER ( ORDER BY 절 )  LEAD ( expr, offset, default ) OVER ( ORDER BY 절 )​

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

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

 

영어사전에서 다음 각함수의 뜻을 찾아보면 , 

 

LAG : 뒤에 처지다, 뒤떨어지다 

LEAD : 이끈다. 앞장서다 

 

LAG 함수는 계산 대상 데이터(로우)들을 ORDER BY 절로 정렬하여 PARTITION BY 절로 구분하여, 

expr에 명시된 값을 기준으로 이전 로우의 값을 반환한다.

offset은 생략 가능한데, 생략할 경우 디폴트 값인 1이 적용된다. 즉 생략하게 되면 이전 바로 이전의 로우값이 반환하게 된다.

default 역시 생략 가능한데, 이 값의 역할은 offset에 지정된 로우가 존재하지 않을 경우 LAG 함수가 반환하는 디폴트 값을 말한다.

 

LEAD 함수는 LAG 함수와 파라미터 및 용법이 같고, 다만 이전 로우가 아닌 이후의 로우의 값을 반환하는 함수.

 

LAG 함수 : 이전 로우의 값 반환​

LEAD 함수 : 이후 로우의 값 반환

 

 

[SQL문]

--EMPLOYEES 테이블 구조 ​ --EMPLOYEES 테이블에서 입사년도 별로 사원수를 추출​ SELECT TO_CHAR(HIRE_DATE, 'YYYY') 입사년도, COUNT(*) 사원수   FROM EMPLOYEES GROUP BY TO_CHAR(HIRE_DATE, 'YYYY') ORDER BY TO_CHAR(HIRE_DATE, 'YYYY'); -- 해마다 전년도에 입사한 사원을 동시에 추출 -- 인라인 뷰와 셀프조인 그리고 외부조인을 사용 SELECT A.IPSA 입사년도, A.SAWON 사원수, B.PRE_SAWON 이전연도사원수   FROM (SELECT TO_CHAR(HIRE_DATE, 'YYYY') IPSA, COUNT(*) SAWON           FROM EMPLOYEES          GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')) A, --연도별 사원수        (SELECT TO_CHAR(HIRE_DATE, 'YYYY') PRE_IPSA, COUNT(*) PRE_SAWON           FROM EMPLOYEES          GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')) B --이전연도를 구하기 위한 사원수 WHERE A.IPSA = B.PRE_IPSA(+) +1 ORDER BY A.IPSA; ​ -- => 이 쿼리는 약간 문제를 가지고 있다. 1987,1989년 그리고 1993년의 경우 직전연도에 입사한 사원이 없으므로 이전연도사원수의 값이 NULL이 된다. 1993년의 경우 직전연도인 1992년도 값이 없더라도 바로 이전인 1991년 입사사원수를 출력해야하지 않을까? ​ -- 위의 복잡하고 결과상의 문제가 있는 쿼리를 LAG함수를 사용하여 간단하게 처리 SELECT TO_CHAR(HIRE_DATE, 'YYYY') 입사년도, COUNT(*) 사원수,        LAG(COUNT(*)) OVER ( ORDER BY TO_CHAR(HIRE_DATE, 'YYYY')) 이전연도사원수   FROM EMPLOYEES  GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')  ORDER BY TO_CHAR(HIRE_DATE, 'YYYY'); ​   -- 현재 연도, 이전연도, 이후연도에 입사한 사원수를 동시에 추출하는 쿼리 -- LAG와 LEAD 함수의 세번째 파라미터인 DEFAULT 값을 0으로 명시하여 , 이 두 함수의 -- 반환값이 NULL인 것에 대해 0을 반환 SELECT TO_CHAR(HIRE_DATE, 'YYYY') 입사년도, COUNT(*) 사원수,         LAG(COUNT(*),1,0) OVER (ORDER BY TO_CHAR(HIRE_DATE, 'YYYY')) 이전연도사원수,              LEAD(COUNT(*),1,0) OVER (ORDER BY TO_CHAR(HIRE_DATE, 'YYYY')) 이후연도사원수   FROM EMPLOYEES  GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')  ORDER BY TO_CHAR(HIRE_DATE, 'YYYY');​ ​ ​

  

 

활용 

게시판에에서 상세보기시 다음글, 이전글을 LAG, LEAD함수를 이용해서 좀더 쉽게 추출 

 

[SQL문]   ※ 이런식으로 사용할 수 있다는 것을 보여주기 위한 예제이므로 성능 등 고려하지 않음.

-- 활용​ SELECT      SEQ     ,TITLE     ,CONTENT     ,LAG(SEQ,1,0) OVER (ORDER BY SEQ) AS PRE_SEQ     ,LAG(TITLE,1,'이전글없음') OVER (ORDER BY SEQ) AS PRE_TITLE     ,LEAD(SEQ,1,0) OVER (ORDER BY SEQ) AS NEXT_SEQ     ,LEAD(TITLE,1,'이후글없음') OVER (ORDER BY SEQ) AS NEXT_TITLE FROM NOTICES ORDER BY SEQ; -- LAG, LEAD함수 사용시 디폴트값을 지정하지 않으면 반환값이 없을경우 NULL -- LAG, LEAD함수의 세번째 인자(default)를 사용하면 반환되는 이전,이후값이 없더라도 기본값을 지정할수있다. -- 특정 글번호에 대해서만 값 가져오기 SELECT *  FROM (     SELECT          SEQ         ,TITLE         ,CONTENT         ,LAG(SEQ,1,0) OVER (ORDER BY SEQ) AS PRE_SEQ         ,LAG(TITLE,1,'이전글없음') OVER (ORDER BY SEQ) AS PRE_TITLE         ,LEAD(SEQ,1,0) OVER (ORDER BY SEQ) AS NEXT_SEQ         ,LEAD(TITLE,1,'이후글없음') OVER (ORDER BY SEQ) AS NEXT_TITLE     FROM NOTICES     ) WHERE SEQ = '1';

[출처] [오라클] 분석함수 LAG 와 LEAD 함수 : 이전값, 다음값 손쉽게 가져오기|작성자 자바킹