이런 T-SQL도 있다!! - APPLY활용, PIVOT & UNPIVOT

수많은 개발자와 DBA들은 때때로 복잡하고 길고 긴 SQL 쿼리를 작성해야 하는 경우가 있다.
보통 이런경우 정작 얻고자 하는 데이터는 생각보다 단순하다.

이것은 모든 개발언어가 가지고 있는 약점 일지도 모른다. 가끔은 사람의 머리에서 나오는 비정형화된
논리연산을 개발언어를 통해 정형화 시키는게 더욱 복잡하다는 생각을 들게 한다.

이런이유에서 각각의 개발언어 들에 대에 더욱 단순하고 정형화된 개발방법론이 나오고  새로운 프레임워크
가 개발되어 개발자의 코딩이 좀더 줄어들고 복잡한 논리연산은 좀더 구체화되고 단순화 시키고 있다.
또한 이러한 것들이 하루가 다르게 진화되고 있다. 그리고 게으른 개발자는 도태될 것이다.
(생각해 보니..내가 게으른데 -_-;;; ㅈㄴ슬픈 얘기임ㅠㅠ)

자~!!! 아무튼 이번에 소개할 내용은 SQL Server 2005의 새로운 T-SQL 기능중 하나인 APPLY JOIN과
PIVOT & UNPIVOT에 대해 한번에 묶어서 썰~을 풀어보시겠다~!!

1. APLLY
어떠한 결과 집합을 만들기 위해서는 부득이 하게 복잡하게 쿼리를 만들어야 하는 경우가 있다. 보통 이런
이런 경우는 JOIN테크닉이 많이 들어가는 T-SQL을 작성하게 되는데 특히 한행을 기초로 여러개의 행들을
묶은 JOIN문을 써야 하는 경우에 쿼리가 매우 복잡하고 길어질수 밖에 없다.
SQL Server 2005에서는 이런 상황에서 발생하는 쿼리를 훨씬 더 단순하고 이해하기 쉬운 방식으로 코딩이
가능한 APPLY를 지원한다. APPLY에는 CROSS APLLY와 OUTER APPLY로 구분하는데 함수가 반환하는
행이 없을때 결과 집합에 해당 JOIN되는 부분을 포함하지 않는 것이 CROSS APLLY이고 그 반대인 경우가
OUTER APPLY이다.

자 이제 실제 쿼리를 보면서 얘기해보겠다~!!
시나리오는 이것다. 일별 평균 접속자에 대해서 이동평균을 만들어 보자.
※ 이동평균 : 해당 기준일에 n일간의 평균 데이터 값(예:5일평균, 10일평균, 20일평균)

CREATE TABLE 접속자
(
	날짜	datetime,
	접속자수	int
)
INSERT INTO 접속자 VALUES ('2009-10-01',430)
INSERT INTO 접속자 VALUES ('2009-10-02',421)
INSERT INTO 접속자 VALUES ('2009-10-03',398)
INSERT INTO 접속자 VALUES ('2009-10-04',477)
INSERT INTO 접속자 VALUES ('2009-10-05',447)
INSERT INTO 접속자 VALUES ('2009-10-06',454)
INSERT INTO 접속자 VALUES ('2009-10-07',476)
INSERT INTO 접속자 VALUES ('2009-10-08',430)
INSERT INTO 접속자 VALUES ('2009-10-09',430)
INSERT INTO 접속자 VALUES ('2009-10-10',434)
INSERT INTO 접속자 VALUES ('2009-10-11',486)
INSERT INTO 접속자 VALUES ('2009-10-12',449)
INSERT INTO 접속자 VALUES ('2009-10-13',460)
INSERT INTO 접속자 VALUES ('2009-10-14',509)
INSERT INTO 접속자 VALUES ('2009-10-15',486)
INSERT INTO 접속자 VALUES ('2009-10-16',525)
INSERT INTO 접속자 VALUES ('2009-10-17',529)
INSERT INTO 접속자 VALUES ('2009-10-18',585)
INSERT INTO 접속자 VALUES ('2009-10-19',537)
INSERT INTO 접속자 VALUES ('2009-10-20',538)
INSERT INTO 접속자 VALUES ('2009-10-21',582)
INSERT INTO 접속자 VALUES ('2009-10-22',557)
INSERT INTO 접속자 VALUES ('2009-10-23',557)
INSERT INTO 접속자 VALUES ('2009-10-24',550)
INSERT INTO 접속자 VALUES ('2009-10-25',600)
INSERT INTO 접속자 VALUES ('2009-10-26',556)
INSERT INTO 접속자 VALUES ('2009-10-27',560)
INSERT INTO 접속자 VALUES ('2009-10-28',592)
INSERT INTO 접속자 VALUES ('2009-10-29',554)
INSERT INTO 접속자 VALUES ('2009-10-30',562)
INSERT INTO 접속자 VALUES ('2009-10-31',579)
INSERT INTO 접속자 VALUES ('2009-11-01',632)

그럼 위의 데이터를 한개의 기준일을 파라미터로 받아 일평균 데이터를 추출해낼수 있는 FUNCTION을
만들어 보겠다.

CREATE FUNCTION FN_CurrentAvgUser
(	@date datetime )
RETURNS TABLE
AS
RETURN
SELECT '7일평균' as 이동평균, AVG(접속자수) as 평균접속자
FROM 접속자
WHERE 날짜 < @date
AND 날짜 >= @date-7
UNION ALL
SELECT '14일평균' as 이동평균, AVG(접속자수) as 평균접속자
FROM 접속자
WHERE 날짜 < @date
AND 날짜 >= @date-14
UNION ALL
SELECT '30일평균' as 이동평균, AVG(접속자수) as 평균접속자
FROM 접속자
WHERE 날짜 < @date
AND 날짜 >= @date-30

이제 CROSS APLLY를 이용하여 각 일별 이동평균 데이터를 뽑아내 보자

SELECT A.*,B.*
FROM 접속자 A CROSS APPLY FN_CurrentAvgUser(A.날짜) B
ORDER BY 날짜 DESC

결과.. CROSS APPLY

날짜	접속자수	이동평균	평균접속자
2009-11-01 00:00:00	632	7일평균	571
2009-11-01 00:00:00	632	14일평균	564
2009-11-01 00:00:00	632	30일평균	510
2009-10-31 00:00:00	579	7일평균	567
2009-10-31 00:00:00	579	14일평균	561
2009-10-31 00:00:00	579	30일평균	505
2009-10-30 00:00:00	562	7일평균	567
2009-10-30 00:00:00	562	14일평균	558
2009-10-30 00:00:00	562	30일평균	503
2009-10-29 00:00:00	554	7일평균	567
2009-10-29 00:00:00	554	14일평균	553
2009-10-29 00:00:00	554	30일평균	501
2009-10-28 00:00:00	592	7일평균	566
2009-10-28 00:00:00	592	14일평균	547
2009-10-28 00:00:00	592	30일평균	498
2009-10-27 00:00:00	560	7일평균	562
2009-10-27 00:00:00	560	14일평균	540
2009-10-27 00:00:00	560	30일평균	496

어떤가? 위의 데이터를 일반적인 JOIN문을 이용한다면 정말 복잡한 쿼리를 만들어야 할것이다.
하지만 함수를 만들고 CROSS APPLY한방으로 매우 단순하게 코딩된 쿼리를 작성할수 있지 않는가!

2. PIVOT & UNPIVOT
위의 결과 집합을 보면 이동평균에 대한 데이터 값을 크로스 탭으로 만들어 보다 쉽게 보고 싶은
충동을 느낀다. 그럼 저 이동평균의 값을 어떻게 피버팅을 할 것인가? 기존 SQL Server 2000버젼에서는
피버팅을 위해 CASE WHEN..절을 이용하여 아주 비효율적인 쿼리를 만들어 크로스 탭을 만들었다.
역시 SQL Server 2005에서는 이러한 복잡한 피벗팅 쿼리를 단번에 해결해 줄수 있는데 이것이 바로
PIVOT & UNPIVOT함수 이다.

그럼 저 위의 데이터를 PIVOT함수를 이용하여 크로스 탭으로 만들어 보자!

SELECT *
FROM 
(
SELECT A.*,B.*
FROM 접속자 A CROSS APPLY FN_CurrentAvgUser(A.날짜) B
) X
PIVOT 
(
SUM(평균접속자)
FOR 이동평균 IN ([7일평균],[14일평균],[30일평균])
) AS X_PIVOT
ORDER BY 날짜 DESC

결과..PIVOT

날짜	접속자수	7일평균	14일평균	30일평균
2009-11-01 00:00:00	632	571	564	510
2009-10-31 00:00:00	579	567	561	505
2009-10-30 00:00:00	562	567	558	503
2009-10-29 00:00:00	554	567	553	501
2009-10-28 00:00:00	592	566	547	498
2009-10-27 00:00:00	560	562	540	496

이처럼 훨씬 더 보기쉬운 데이터로 만들수 있다.
그럼 다시 이것을 UNPIVOT 해보자. 먼저 위의 피벗된 데이터를 기준으로 테이블을 생성한다.

SELECT *
INTO 피벗_이동평균
FROM 
(
SELECT A.*,B.*
FROM 접속자 A CROSS APPLY FN_CurrentAvgUser(A.날짜) B
) X
PIVOT 
(
SUM(접속자수)
FOR 평균선 IN ([7일평균],[14일평균],[30일평균])
) AS X_PIVOT
ORDER BY 날짜 DESC

위의 피벗 데이터를 다시 UNPIVOT을 해보겠다.

SELECT 날짜, 접속자수, 이동평균, 평균접속자
FROM 피벗_이동평균
UNPIVOT (평균접속자 
	FOR 이동평균 IN ([7일평균],[14일평균],[30일평균])
	) as X_UNPIVOT

결과..UNPIVOT

날짜	접속자수	이동평균	평균접속자
2009-11-01 00:00:00	632	7일평균	571
2009-11-01 00:00:00	632	14일평균	564
2009-11-01 00:00:00	632	30일평균	510
2009-10-31 00:00:00	579	7일평균	567
2009-10-31 00:00:00	579	14일평균	561
2009-10-31 00:00:00	579	30일평균	505
2009-10-30 00:00:00	562	7일평균	567
2009-10-30 00:00:00	562	14일평균	558
2009-10-30 00:00:00	562	30일평균	503
2009-10-29 00:00:00	554	7일평균	567
2009-10-29 00:00:00	554	14일평균	553
2009-10-29 00:00:00	554	30일평균	501
2009-10-28 00:00:00	592	7일평균	566
2009-10-28 00:00:00	592	14일평균	547
2009-10-28 00:00:00	592	30일평균	498
2009-10-27 00:00:00	560	7일평균	562
2009-10-27 00:00:00	560	14일평균	540
2009-10-27 00:00:00	560	30일평균	496

결과는 처음 PIVOT하지 않았을 때와 동일한 결과가 나온다는것을 알 수 있다.
하지만 꼭 UNPIVOT이 PIVOT의 정반대의 결과를 얻는데만 사용하는 것은 아니며 항상 동일한 결과 값이
나오는 것 또한 아니다. PIVOT은 행의 집합에 따른 결과 값을 나타내기 때문에 NULL값이 존재할수 있지만
이것을 UNPIVOT시 NULL값은 출력에 나타나지 않습니다.
그렇기 때문에 UNPIVOT은 피벗된 테이블과 같은 유사항 형태의 데이터를 펼쳐주는 역할을 한다고 생각하면
될 것이다.