태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

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

Posted at 2009.11.05 18:42 // in MSSQL/SQL Server 2005 new Feature // by 승와니

수많은 개발자와 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은 피벗된 테이블과 같은 유사항 형태의 데이터를 펼쳐주는 역할을 한다고 생각하면
될 것이다.
  1. Favicon of http://gdbt.tistory.com BlogIcon 건방진연이

    2009.11.06 13:08 신고 [수정/삭제] [답글]

    승완주임님 고생했습니다.
    근데... 예제가 조금 이상한듯 해요 -0-;;

    CREATE FUNCTION 에서 AVG(카운트) ?? 아니고 AVG(접속자수) 아닐까요 ?
    PIVOT에서도 SUM(평균접속자수) 가 아니고 SUM(평균유저수) 가 아닐까요 ?

  2. Favicon of http://gdbt.tistory.com BlogIcon 건방진연이

    2009.11.06 13:10 신고 [수정/삭제] [답글]

    APPLY랑 PIVOT을 안쓰고

    SELECT *
    , (
    SELECT AVG(접속자수) as [평균접속자수]
    FROM 접속자 as b
    WHERE b.날짜 >= A.날짜 - 7 and b.날짜 < A.날짜
    ) AS [7일평균]
    , (
    SELECT AVG(접속자수) as [평균접속자수]
    FROM 접속자 as b
    WHERE b.날짜 >= A.날짜 - 14 and b.날짜 < A.날짜
    ) AS [14일평균]
    , (
    SELECT AVG(접속자수) as [평균접속자수]
    FROM 접속자 as b
    WHERE b.날짜 >= A.날짜 - 15 and b.날짜 < A.날짜
    ) AS [30일평균]
    FROM 접속자 AS A

    이렇게 해버리면?? 어떻죠?? 안좋은가요?? 어떻게 안좋은가요??
    *^^*

  3. Favicon of http://zionsky.tistory.com BlogIcon 새벽하늘에

    2016.09.28 15:23 신고 [수정/삭제] [답글]

    ;with cteMoveInterval (cMove) as (
    select 07 union all
    select 14 union all
    select 30
    )
    select convert(char(10),a.날짜,121) as 날짜
    , 접속자수
    , b.cMove as 이동평균
    , c.평균접속자
    from 접속자 as a
    cross join
    cteMoveInterval as b
    cross apply (
    select avg(접속자수) as 평균접속자
    from 접속자 as x
    where x.날짜 < a.날짜
    and x.날짜 >= a.날짜 - b.cMove
    ) as c
    where c.평균접속자 > 0
    order by 날짜 desc
    go

댓글을 남겨주세요.