사용자 연주 컬럼 생성하기 (연주(년주) 개념 이해)

연주라는 한해의 주차를 세는 기준이 시스템별로 모두 다르다는 것을 최근에 알게되었다. 그래서 보다보니 사연이 많다.

가장 큰 문제가 연도가 넘어갈 때 인데 12.31(화), 1.1(수) 라면 연주를 주 중간이라도 자르고 새로 1주차부터 시작하느냐? 아니면 한주는 하나의 주차를 유지해야하느냐 이다.
이는 정답이 없고 필요성에 따라서 선택하면 되는 문제이다.

대략 내가 고려하는 연주의 종류는 연도와 함께 쓰일때 아래 3가지 중에 하나이다.

1. 연도가 바뀌면 무조건 1.1은 신년 1주차부터 시작해야 한다. (주의 시작요일은 일정)
2. 연도와 관계없이 한주는 7일을 보장해야 한다.
3. 1.1부터 7일 단위로 주차를 산정한다. (매년 주의 시작요일이 다르다)

위의 3가지 말은 큰 차이를 보인다.
1. MS SQL Server 의 YYYY, WW 기준
2. Oracle 의 IYYY, IW 기준
3. Oracle 의 YYYY, WW 기준

쿼리와 결과로 2번과 3번을 비교해보면 아래와 같다.

SELECT DT , TO_CHAR(TO_DATE(DT,'YYYYMMDD'),'YYYY') AS YYYY , TO_CHAR(TO_DATE(DT,'YYYYMMDD'),'WW')   AS WW , TO_CHAR(TO_DATE(DT,'YYYYMMDD'),'IYYY') AS IYYY , TO_CHAR(TO_DATE(DT,'YYYYMMDD'),'IW')   AS IW FROM CALN WHERE DT BETWEEN '20161230' AND '20170102';

IYYY와 IW는 연도가 바뀌어도 7일을 보장하기 위해서 주차를 유지한다.
이 방식은 가끔은 연도가 바뀌기도 전에 미리 연도가 바뀌어버린다.
아래는 2017~2018년도 넘어갈때 상황이다.

아직 2019년이 안됐는데도 12.31에 연도가 넘어갔다. ㅎㅎ

원칙을 살펴보니 이렇다.

12.31이 월~수이면 연도가 미리 넘어가고 

12.31이 목~일이면 연도가 1.1이 되어도 전년도가 유지된다.

가끔 현업들은 월~일 기준이 아닌 일~토, 목~수와 같은 이상한 자기만의 연주를 OLAP차원으로 요구하기도 하는데 대체로 주별보고일자 전날까지를 한주가 끝나는 날짜로 요구한다. ㅎㅎㅎ 

1번방식 (연도가 넘어가면 무조건 연도변경 후 1주차부터 세는 방식)은 심플하고 크게 문제될 것이 없으나 연도가 변경될때 최근 7주일이 2개의 연도로 쪼개져서 문제가 발생한다.

그래서 2번 방식 (7일이 보장되는 연주)으로 기준요일별로 연주를 리턴하는 스칼라 함수를 만들어보았다. 아래처럼 주시작요일을 정하면 IYYY, IW 산정로직에 준해서 연주를 생성해준다. 

SELECT DT      , DATEPART(WEEKDAY,DT)           AS DW      , dbo.UFN_YYYYWW(DT,1,'IYYYIW')  AS IYYYIW      , dbo.UFN_WEEKDAY(DT,2)           AS     DW_월기준      , dbo.UFN_YYYYWW(DT ,2,'IYYYIW')  AS IYYYIW_월기준      , dbo.UFN_WEEKDAY(DT,5)           AS     DW_목기준      , dbo.UFN_YYYYWW(DT ,5,'IYYYIW')  AS YYYYWW_목기준 FROM   DW_DT WHERE  DATEPART(dayofyear,DT) > 358 OR DATEPART(dayofyear,DT) < 7 ORDER BY DT

아래 함수 2개를 만들어서 해결한건데... 생각보다 골머리가 아팠다.
아직까진 버그가 없는듯...

ALTER FUNCTION dbo.UFN_WEEKDAY (@DT DATE, @CRITN_WEEKDAY TINYINT = 1) RETURNS TINYINT AS BEGIN       RETURN CASE WHEN @CRITN_WEEKDAY NOT BETWEEN 1 AND 7                   THEN 0                   ELSE (DATEPART(WEEKDAY,@DT)+(7-@CRITN_WEEKDAY))%7+1 END END;

ALTER FUNCTION dbo.UFN_YYYYWW (@DT DATE, @CRITN_WEEKDAY TINYINT = 1, @FORMAT VARCHAR(6) = 'IYYYIW') RETURNS INT AS BEGIN     DECLARE @WEEKDAY_LY_0101 TINYINT = dbo.UFN_WEEKDAY(CONVERT(DATE,CONVERT(VARCHAR(4),DATEADD(YEAR,-1,@DT),112)+'0101'),@CRITN_WEEKDAY)           , @WEEKDAY_CY_0101 TINYINT = dbo.UFN_WEEKDAY(CONVERT(DATE,CONVERT(VARCHAR(4),@DT                 ,112)+'0101'),@CRITN_WEEKDAY)           , @WEEKDAY_NY_0101 TINYINT = dbo.UFN_WEEKDAY(CONVERT(DATE,CONVERT(VARCHAR(4),DATEADD(YEAR,1 ,@DT),112)+'0101'),@CRITN_WEEKDAY)           , @DT_LY_0101 DATE = CONVERT(DATE,CONVERT(VARCHAR(4),DATEADD(YEAR,-1,@DT),112)+'0101')           , @DT_CY_0101 DATE = CONVERT(DATE,CONVERT(VARCHAR(4),@DT                 ,112)+'0101')           , @DT_NY_0101 DATE = CONVERT(DATE,CONVERT(VARCHAR(4),DATEADD(YEAR,1, @DT),112)+'0101')     DECLARE @BIT_LY    SMALLINT = CASE WHEN @WEEKDAY_LY_0101 BETWEEN 5 AND 7 THEN 1 ELSE 0 END           , @BIT_CY    SMALLINT = CASE WHEN @WEEKDAY_CY_0101 BETWEEN 5 AND 7 THEN 1 ELSE 0 END           , @BIT_NY    SMALLINT = CASE WHEN @WEEKDAY_NY_0101 BETWEEN 5 AND 7 THEN 1 ELSE 0 END     DECLARE @DY_LY SMALLINT = DATEDIFF(DAY,@DT_LY_0101,@DT)+1           , @DY_CY SMALLINT = DATEPART(dayofyear,@DT)     DECLARE @YYYY         SMALLINT = DATEPART(YEAR,@DT)           , @YYYY_LY      SMALLINT = DATEPART(YEAR,@DT)-1           , @YYYY_NY      SMALLINT = DATEPART(YEAR,@DT)+1           , @WW           SMALLINT = ((@DY_CY + dbo.UFN_WEEKDAY(@DT_CY_0101,@CRITN_WEEKDAY) + 5)/7)           , @WW_LY        SMALLINT = ((@DY_LY + dbo.UFN_WEEKDAY(@DT_LY_0101,@CRITN_WEEKDAY) + 5)/7) - @BIT_LY           , @WW_CY        SMALLINT = ((@DY_CY + dbo.UFN_WEEKDAY(@DT_CY_0101,@CRITN_WEEKDAY) + 5)/7) - @BIT_CY     DECLARE @TYPE TINYINT = CASE WHEN DATEDIFF(DAY,@DT,@DT_NY_0101) < @WEEKDAY_NY_0101 AND @WEEKDAY_NY_0101 > 1                                       THEN CASE WHEN @BIT_NY = 1 THEN 1                          -- 연말+전년귀속                                                             ELSE 2 END                      -- 연말+금년귀속                                  WHEN DATEDIFF(DAY,@DT_CY_0101,@DT) <= (7-@WEEKDAY_CY_0101) AND @WEEKDAY_CY_0101 > 1                                  THEN CASE WHEN @BIT_CY = 1 THEN 3                          -- 연초+전년귀속                                                             ELSE 4 END                      -- 연초+금년귀속                                                             ELSE 5 END                      -- 일반     RETURN CASE WHEN @FORMAT = 'IYYYIW' THEN CASE @TYPE WHEN 1 THEN @YYYY   *100+@WW_CY                                                         WHEN 2 THEN @YYYY_NY*100+1                                                         WHEN 3 THEN @YYYY_LY*100+@WW_LY                                                         WHEN 4 THEN @YYYY   *100+@WW                                                         WHEN 5 THEN @YYYY   *100+@WW_CY                                              END                  WHEN @FORMAT = 'IYYY'   THEN CASE @TYPE WHEN 1 THEN @YYYY                                                           WHEN 2 THEN @YYYY_NY                                                         WHEN 3 THEN @YYYY_LY                                                         WHEN 4 THEN @YYYY                                                           WHEN 5 THEN @YYYY                                                END                  WHEN @FORMAT = 'IW'     THEN CASE @TYPE WHEN 1 THEN @WW_CY                                                         WHEN 2 THEN 1                                                         WHEN 3 THEN @WW_LY                                                         WHEN 4 THEN @WW                                                         WHEN 5 THEN @WW_CY                                              END                 ELSE 0 END END;