[MS-SQL] 이전행의 특정 컬럼값이나 다음행의 특정 컬럼값 가져오는 방법(LAG, LEAD)

떠돌이개발자Aug 9, 2023

특정 데이터의 기간 데이터를 가져와야할 경우가 있다.

시작일과 종료일이 있다면 수월하게 조회쿼리를 작성할 수 있지만 시작일과 종료일이 아닌 등록일이나 수정일로 기간데이터를 가져와야 할경우 어떻게 하면 좋을까?

SQL Server 2012 버전 부터 이전 행과 다음 행의 값을 가져올 수 있는 LAG, LEAD 함수를 제공해주고 있다.

그럼 시작해볼까??

쿼리 테스트를 위한 사전 작업

1. 테스트 쿼리 작성을 위해 임시 테이블을 생성 한다.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TB_GRADE_SAMPLE](
	[SEQ_ID] [bigint] IDENTITY(100000,1) NOT NULL,
	[GRADE] [varchar](30) NOT NULL,
	[CRT_TIME] [datetime2](7) NULL,
	[CRT_USER] [varchar](30) NOT NULL,
 CONSTRAINT [PK_TB_GRADE_SAMPLE] PRIMARY KEY CLUSTERED 
(
	[SEQ_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

2. 데이터 추가해준다.

INSERT INTO TB_GRADE_SAMPLE(GRADE, CRT_TIME, CRT_USER)  VALUES ('A', GETDATE(), 'master')
INSERT INTO TB_GRADE_SAMPLE(GRADE, CRT_TIME, CRT_USER)  VALUES ('B', dateadd(DAY,1,getdate()), 'master')
INSERT INTO TB_GRADE_SAMPLE(GRADE, CRT_TIME, CRT_USER)  VALUES ('C', dateadd(DAY,2,getdate()), 'master')
INSERT INTO TB_GRADE_SAMPLE(GRADE, CRT_TIME, CRT_USER)  VALUES ('D', dateadd(DAY,3,getdate()), 'master')
INSERT INTO TB_GRADE_SAMPLE(GRADE, CRT_TIME, CRT_USER)  VALUES ('E', dateadd(DAY,4,getdate()), 'master')

3. 추가한 데이터를 조회해본다.

테스트 쿼리를 작성할 준비는 끝났다.

LAG, LEAD의 기본 사용법을 알아보자.

[LAG 구문]

LAG (scalar_expression [,offset] [,default])  
    OVER ( [ partition_by_clause ] order_by_clause )

[LEAD 구문]

LEAD ( scalar_expression [ ,offset ] , [ default ] )   
    OVER ( [ partition_by_clause ] order_by_clause )

scalar_expression
지정한 오프셋에 따라 반환할 값입니다. 단일(스칼라) 값을 반환하는 모든 유형의 식입니다. scalar_expression은 분석 함수일 수 없습니다.

offset
현재 행 앞에 있는 행의 수로, 그 수만큼 앞에 있는 행에서 값을 가져옵니다. 이 인수를 지정하지 않으면 기본값은 1입니다. offset은 열, 하위 쿼리 또는 양의 정수로 계산되거나 암시적으로 bigint로 변환될 수 있는 기타 식일 수 있습니다. offset은 음수 또는 분석 함수일 수 없습니다.

default
오프셋이 파티션의 범위를 벗어날 때 반환할 값입니다. 기본값이 지정되어 있지 않으면 NULL이 반환됩니다. default는 열, 하위 쿼리 또는 기타 식일 수 있지만 분석 함수일 수는 없습니다. default는 scalar_expression과 호환되는 형식이어야 합니다.

OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause는 FROM 절이 생성한 결과 집합을 함수가 적용되는 파티션으로 나눕니다. 지정하지 않을 경우 쿼리 결과 집합의 모든 행이 단일 그룹으로 취급됩니다. order_by_clause는 함수를 적용하기 전에 데이터의 순서를 결정합니다. partition_by_clause가 지정되면 파티션의 데이터 순서가 결정됩니다. order_by_clause가 필요합니다.

이전 일자 가져오기 (LAG 함수 사용)

SELECT * 
, LAG(CRT_TIME) OVER(ORDER BY CRT_TIME) AS [이전 일자]
FROM TB_GRADE_SAMPLE

다음 일자 가져오기(LEAD 함수 사용)

SELECT * 
, LEAD(CRT_TIME) OVER(ORDER BY CRT_TIME) AS [다음 일자]
FROM TB_GRADE_SAMPLE

LEAD와 LAD 함수를 사용시 행의 위치를 생략했다. 행의위치 값을 생략하면 기본값 1이 적용되며 다음행의 값을 가져온다. 그럼 행의 위치값을 2를 적용하게 되면 다다음 행의 값을 가져올 수 있다.

LEAD 함수를 사용한 다음 예를 보면 이해가 갈 것이다. LAD역시 동일하게 사용가능하다.

SELECT * 
, LEAD(CRT_TIME) OVER(ORDER BY CRT_TIME) AS [다음 일자]
, LEAD(CRT_TIME, 2) OVER(ORDER BY CRT_TIME) AS [다다음 일자]
FROM TB_GRADE_SAMPLE

기간데이터 조회 쿼리는 다음과 같이 작성할 수 있다. 두번째 쿼리 처럼 CONCAT함수를 사용해서 문자열을 연결해도 된다.

SELECT * 
	, CONVERT(VARCHAR, CONVERT(DATE, CRT_TIME), 120) 
	+ ' ~ ' 
	+ LEAD(CONVERT(VARCHAR, CONVERT(DATE, CRT_TIME), 120), 1,'') OVER(ORDER BY CRT_TIME) AS 시작일_종료일_기간
FROM TB_GRADE_SAMPLE

--또는

SELECT * 
	, CONCAT(CONVERT(VARCHAR, CONVERT(DATE, CRT_TIME), 120) 
	, ' ~ ' 
	, LEAD(CONVERT(VARCHAR, CONVERT(DATE, CRT_TIME), 120), 1,'') OVER(ORDER BY CRT_TIME)) AS 시작일_종료일_기간
FROM TB_GRADE_SAMPLE

[쿼리 실행결과]