현재 레코드 행에서 이전 레코드 행이나 다음 레코드 행의 값을 구하거나, 그룹으로 지정한 여러 행의 레코드 필드 값을 한 레코드 행에 보이는 명령을 실습한다.
16.1 LEAD()
LEAD() 함수는 다음 레코드의 필드 값을 구하는 기능으로, ‘그룹필드명’으로 그룹을 만들고 ‘정렬필드명’으로 정렬한 ‘필드명’의 다음 레코드 값을 구한다. 물론, 다음 레코드가 없으면 구하는 필드 값은 NULL 이다.
•
LEAD(필드명) OVER (PARTITION BY 그룹필드명 ORDER BY 정렬필드명)
예를들어 “LEAD(STATION_NM) OVER (PARTITION BY TRAIN_NO ORDER BY RUN_ORDR)” 이라고 하면 TRAIN_NO그룹을 만들고 RUN_ORDR값으로 정렬한 상태에서 다음 레코드 행의 STATION_NM 값을 구한다.
예제1. 열차시간표 테이블(TB_TRAIN_TM)에서 열차번호 그룹(TRAIN_NO)을 만들고 운행순서(RUN_ORDR)로 정렬해 현재 레코드의 역명(STATION_NM)과 다음 레코드의 역명을 구한다.
SELECT TRAIN_NO,
RUN_ORDR,
STATION_NM, -- 현재 레코드의 역명
LEAD(STATION_NM) OVER (PARTITION BY TRAIN_NO ORDER BY RUN_ORDR) AS "다음 역명"
FROM TB_TRAIN_TM;
SQL
복사
16.2 LAG()
LAG()는 이전 레코드의 필드 값을 구하는 기능으로, ‘그룹필드명’으로 그룹을 만들고 ‘정렬필드명’으로 정렬한 ‘필드명’의 이전 레코드 값을 구한다. 이전 레코드가 없으면 구하는 필드 값은 NULL이다.
•
LAG(필드명) OVER (PARTITION BY 그룹필드명 ORDER BY 정렬필드명)
예제1. 열차시간표 테이블에서 열차번호 그룹을 만들고 운행순서로 정렬해 현재 레코드의 역명과 이전 레코드의 역명을 구한다.
SELECT TRAIN_NO,
RUN_ORDR,
STATION_NM,
LAG(STATION_NM) OVER (PARTITION BY TRAIN_NO ORDER BY RUN_ORDR) AS "이전 역명"
FROM TB_TRAIN_TM;
SQL
복사
16.3 XMLAGG(XMLELEMENT(x,구분자,묶음필드명)
XMLAgg, XMLElement 함수는 그룹에 속한 여러 행의 레코드 필드 값을 한 행에 보여주는 기능으로 GROUP BY 구문에서 지정한 필드로 그룹을 만들고 ‘정렬필드명’의 순서로 ‘묶음필드명’을 ‘구분자’ 문자로 구분한 문자열로 만든다.
•
XMLAgg(XMLElement(x, 구분자, 묶음필드명) ORDER BY 정렬필드명). Extract(’//text()’)
예를들어, XMLAgg(XMLElement(x, ‘,’ , STATION_NM) ORDER BY RUN_ORDR). Extract(’//text()’) GROUP BY TRAIN_NO 가 의미하는 것은 TRAIN_NO 그룹에 속한 STATION_NM을 RUN_ORDR 순서로 ‘,’ 구분자를 연결해서 한 문자열로 만드는 것을 말한다.
예제1. 열차시간표 테이블에서 열차번호 그룹(TRAIN_NO)을 만들고 운행순서로 정렬(RUN_ORDR)한 역명(STATION_NM)을 ‘,’ 문자로 구분해 한 행에 구한다. (XMLAgg,XMLElement)
SELECT TRAIN_NO,
SUBSTR(XMLAGG(XMLELEMENT(X,',',STATION_NM) ORDER BY RUN_ORDR).EXTRACT('//text()'),2) AS 역목록
FROM TB_TRAIN_TM
GROUP BY TRAIN_NO;
SQL
복사
‘//text()’는 반드시 소문자로 써야 한다.
예제2. 열차시간표 테이블에서 열차번호 그룹(TRAIN_NO)을 만들고 운행순서로 정렬한(RUN_ORDR) 역명(STATION_NM)과 도착시각(ARV_TM)을 ‘역명(도착시각)’ 형식으로 ‘,’ 문자로 구분해 한 행에 구한다.
SELECT TRAIN_NO,
SUBSTR(XMLAGG(XMLELEMENT(X,',',STATION_NM || '(' || ARV_TM || ')') ORDER BY RUN_ORDR).EXTRACT('//text()'),2)
AS 역정보목록
FROM TB_TRAIN_TM
GROUP BY TRAIN_NO;
SQL
복사