DATEPART와 spt_values를 이용한 날짜 계층구조 만들기

대시보드를 만들다가 차트에 날짜 기준을 잡을 일이 생겼다.

PowerBI같은 곳에선 계층 구조로 전체 날짜에 대해서 계층 구조로 분기, 반기, 주차등 여러 정보들을 제공해 주는데 대시보드에서 비슷한 기능을 만들기 위해서 쿼리를 이용해서 전체 날짜에 대한 계층 구조를 만들 필요성이 생겼다.

날짜 다듬는거야 크게 어려운 것이 아니니 한번 만들어 봤는데 DATEPART와 spt_values를 처음으로 이용하게 되어 정리해보고자 한다.

---

spt_values

SQL Server의 master DB에는 spt_values라는 테이블이 존재한다.

이 테이블에는 특정 종류의 작업에 사용되는 많은 행이 포함되어 있다.

예를 들어 Type이 'P'인 것으로 검색해보면 0~2047의 숫자가 검색되는 것을 확인할 수 있다.

따라서 이 테이블은 특별한 의미 없는 작업에 사용되는 경우가 많은데 예를 들어 반복이나 재귀를 하는 작업 등이 그러하다.

자세한 작업은 아래의 블로그를 참고하기 바란다.

https://aspdotnet.tistory.com/2320

MSSQL 의 spt_values 활용으로 특정 시작일과 종료일 사이 추출

나는 이 테이블을 이용하여 재귀를 하도록 하였는데 입력을 받을때 시작일과 종료일을 받도록 하여 해당 사이의 일자를 전부 나타내도록 하였다.

DECLARE @pStDate DATETIME = '2023-01-01'
DECLARE @pEndDate DATETIME = '2024-12-31'

DECLARE @DayCount INT = DATEDIFF(DAY, @pStDate, @pEndDate);

WITH DateList AS (
    SELECT TOP (@DayCount + 1)
        DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, @pStDate) AS GeneratedDate
    FROM master.dbo.spt_values
)

위의 쿼리를 실행시키면 2023-01-01 ~ 2024-12-31일까지의 일자가 전부 나온다.

좀 더 디테일하게 살펴보면 우선 With절을 사용해서 spt_values 테이블로 재귀를 하게 하는데 SELECT 절에는 Row_Number를 이용하여 행 번호를 붙인 후 DATEADD를 사용하여 시작일에 행번호를 더하게 하였다.

다만 이렇게 하면 시작번호가 1번이 되는데 우리는 시작일부터 계산이 되므로 -1을 해주어 0일을 더하도록 하면 시작일부터 종료일까지의 모든 날짜를 얻을 수 있는 것이다.

또한, 딱히 다른 정렬 순서는 필요 없으므로 SELECT NULL을 이용하여 순서 없이 행번호만 출력되게 하였다.

마지막으로 TOP을 이용하여 시작일과 종료일 사이의 기간을 구하고 그 기간만큼 행을 가져오면 우리가 원하는 전체 일자를 얻을 수 있다.

 

참고로 TOP을 사용하지 않으면 종료일까지가 아닌 spt_values의 행 수만큼의 날짜를 얻을 수 있는데 해당 테이블의 전체 행수가 2540개 이므로 날짜로 계산하면 7년이 좀 안되는 데이터까지는 무난하게 얻을 수 있다고 보면 된다. (반대로 말하면 최대로 구할 수 있는 기간이 7년이 안된다)

---

DATEPART

그럼 이제 구해진 날짜를 가공하는 일만 남았다.

원래라면 YEAR,MONTH,DAY와 같은 함수는 너무 간단하니 굳이 설명하지 않고 넘어가겠다.

문제는 분기, 주차와 같이 계산을 통해 얻어야 하는 데이터 들인데 다행히 SQL Server에는 DATEPART라는 유용한 함수를 지원한다!

 

사용 방법은 굉장히 심플하다.

예를 들어, 오늘의 날짜가 주를 기준으로 몇일인가를 본다고 하자.

그러면 DATEPART(원하는 날짜 형식, 날짜) 를 통하여 결과를 얻을 수 있다.

SELECT DATEPART(WEEKDAY, GETDATE()) AS [weekday]

분기, 주차도 동일하게 앞부분에 원하는 날짜 형식만 입력을 한다면 쉽게 구할 수 있게 된다.

SELECT DATEPART(QUARTER, '2023-01-01') AS 분기,
DATEPART(WEEK, '2023-01-01') AS 주차

 

---

둘을 조합해 보자!

그럼 두 기능을 조합하여 원하는 기능을 만들어 보자.

나의 경우는 처음 설계시 프로시저로 만들어서 유저가 원하는 날짜 구간을 던지면 데이터를 반환하는 형태로 구현을 하려고 했는데 팀장님께서 그냥 쓰기 편하게 테이블로 박아달라고 요청하셔서 23년부터 24년까지 2년의 날짜로만 계산을 하여 테이블로 담기로 하였다.

 

코드는 아래와 같다.

DECLARE @pStDate DATETIME = '2023-01-01'
DECLARE @pEndDate DATETIME = '2024-12-31'

DECLARE @Temp TABLE(
GeneratedDate DATETIME,
Dates DATE,
Years INT,
Quaters INT,
Months INT,
Days INT,
Weeks INT,
Half INT
)

DECLARE @DayCount INT = DATEDIFF(DAY, @pStDate, @pEndDate);

WITH DateList AS (
    SELECT TOP (@DayCount + 1)
        DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, @pStDate) AS GeneratedDate
    FROM master.dbo.spt_values
)

INSERT INTO @Temp
SELECT *, CASE WHEN Main.Quarters > 2 THEN 2 ELSE 1 END Half
FROM (
SELECT GeneratedDate, Convert(DATE,GeneratedDate) Dates, YEAR(GeneratedDate) years, 
	DATEPART(QUARTER,GeneratedDate) Quarters, MONTH(GeneratedDate) Months
	, DAY(GeneratedDate) Days, DATEPART(WEEK,GeneratedDate) Weeks
FROM DateList
) Main;



SELECT * INTO [원하는 DB 테이블]
FROM @Temp

 

앞서 설명한 대로 WITH절을 통하여 spt_values 테이블로 전체 날짜 목록을 만들고, 만들어진 DateList를 통하여 필요한 날짜 형식으로 전환 하였다.

한가지 아쉬운건 반기가 필요했는데 반기를 계산하는 형태가 없어서 그냥 Case 문을 통해서 2분기 이상은 1, 아니면 2로 박아버렸다. 

테이블 변수는 사실 굳이 쓸 필요없이 WITH 마지막에서 호출 시 바로 INSERT를 해도 되긴 되는데 원래 프로시저로 만들고 이것저것 가공을 더 하려고 만들었다가 필요가 없어져서 그냥 마지막에 테이블 변수를 이용하여 INSERT 하도록 하였다. (그니까 실제로는 @Temp에 한번 담고 @Temp를 실제 테이블에 담는 형태이다. 쓸데없는 코드라 고쳐야 하는데 어차피 데이터 량도 많은것도 아니고 그냥 테스트 차원에서 짠거라 그냥 넣어버렸다)

 

어려운 것은 아니지만 종종 기준 정보가 필요한 경우 만들어 쓰거나 날짜를 가공시에 유용하게 사용할 수 있을 것 같다.

---

참고자료

https://aspdotnet.tistory.com/2320

MSSQL 의 spt_values 활용으로 특정 시작일과 종료일 사이 추출