[MSSQL] ROLLUP 을 이용한 소계/총계 - GROUPING 함수

예전부터 포스팅 하고 싶었던 내용입니다만, 소스코드를 예쁘게 꾸미는 게 좀 엄두가 나질 않아서 미루고 미루다가 포스팅 합니다. (사실 앞서 올린 글의 조회수가 높은 게 좀 무섭기도 해서 밀어내기 용도로다가...)

이 내용은, 현업에서 SQL을 다루시는 분들이시라면 간단한 내용이지만, 사실 깊이 들어가면 이해하기도 어려우며, 쿼리를 자주 접하지 못하는 분들, 또는 이제 막 쿼리를 익히기 시작한 분들, 학생들을 대상으로 작성하는 것입니다. ROLLUP, CUBE 를 심도있게 들어가면 복잡하고 머리도 아프지만, 필요한 것은 결과치, 제대로 된 결과치라는 생각이 들어서, 일단 값을 잘 뽑을 수 있는 요령에 대해 쓰고자 합니다.

다른 DB 엔진은 잘 모르고, 또 설치되어 있는 것이 없기 때문에 M$SQL 2000, 2005 기반으로 작성합니다.(타 제품, 오라클, 사이베이스 쪽은 약간 문법이 다릅니다.) 이러한 결과를 내는 쿼리를 작성할 겁니다. 일별 판매량 계, 그리고 총계. SUBSTRING 등을 사용하여 월계 이런 것 까지 할까 하다가, 그러자면 내용도 길어지고, 간단한 정도만 제시하고 나머지는 응용해 보시기 바래요.

먼저 필요한 테이블, 데이터 생성에 대한 스크립트 입니다. 그냥 대충 보고 지나가도 되고, 한번 해 볼 생각이 있다면 그냥 긁어다가 돌리면 됩니다.

1. CREATE TABLE TBL_ITEM
2. (
3.   ITEM_CODE  NVARCHAR(30) NOT NULL,
4.   ITEM_NAME  NVARCHAR(80) NOT NULL,
5.   ITEM_PRICE NUMERIC(9,0) NOT NULL DEFAULT 0,
6.   CONSTRAINT PK_TBL_ITEM PRIMARY KEY CLUSTERED
7.   (
8.     ITEM_CODE ASC
9.   )
10. )
11. GO
12.  
13.  
14. CREATE TABLE TBL_SALES
15. (
16.   SALE_DATE     NVARCHAR(8) NOT NULL,
17.   ITEM_CODE     NVARCHAR(30) NOT NULL,
18.   SALE_QUANTITY NUMERIC(9,0) NOT NULL DEFAULT 0,
19.   CONSTRAINT PK_TBL_SALES PRIMARY KEY CLUSTERED
20.   (
21.     SALE_DATE DESC,
22.     ITEM_CODE ASC
23.   )  
24. )
25. GO
26.  
27. INSERT TBL_ITEM VALUES ('12345-BK', '클래식타입축구화FG-블랙', 50000)
28. INSERT TBL_ITEM VALUES ('12345-WH', '클래식타입축구화FG-화이트', 50000)
29. INSERT TBL_ITEM VALUES ('12355-BK', '기능성축구화FG-블랙', 75000)
30. INSERT TBL_ITEM VALUES ('12355-WH', '기능성축구화FG-화이트', 75000)
31. INSERT TBL_ITEM VALUES ('12388-WH', '국가대표축구화FG-블랙', 180000)
32. GO
33.  
34. INSERT TBL_SALES VALUES ('20090203', '12345-BK', 30)
35. INSERT TBL_SALES VALUES ('20090203', '12345-WH', 35)
36. INSERT TBL_SALES VALUES ('20090203', '12355-BK', 22)
37. INSERT TBL_SALES VALUES ('20090204', '12355-WH', 27)
38. INSERT TBL_SALES VALUES ('20090204', '12388-WH', 5)
39. INSERT TBL_SALES VALUES ('20090213', '12345-BK', 22)
40. INSERT TBL_SALES VALUES ('20090213', '12345-WH', 27)
41. INSERT TBL_SALES VALUES ('20090215', '12355-BK', 15)
42. INSERT TBL_SALES VALUES ('20090215', '12355-WH', 12)
43. INSERT TBL_SALES VALUES ('20090216', '12388-WH', 2)
44. INSERT TBL_SALES VALUES ('20090220', '12345-BK', 42)
45. INSERT TBL_SALES VALUES ('20090220', '12345-WH', 37)
46. INSERT TBL_SALES VALUES ('20090221', '12388-WH', 7)
47. INSERT TBL_SALES VALUES ('20090221', '12345-BK', 20)
48. INSERT TBL_SALES VALUES ('20090221', '12355-BK', 25)
49. INSERT TBL_SALES VALUES ('20090222', '12355-WH', 33)
50. INSERT TBL_SALES VALUES ('20090223', '12345-BK', 22)
51. INSERT TBL_SALES VALUES ('20090223', '12345-WH', 33)
52. INSERT TBL_SALES VALUES ('20090223', '12388-WH', 11)
53. GO

요로코롬 넣고, 한번 SELECT 날려보겠습니다. 품목 마스터 쪽에 단가를 넣어두었기 때문에, 하루 판매 수량을 곱하면 해당 판매 가격을 계산할 수 있겠지요, 그래서 JOIN 을 걸어 금액(AMOUNT) 까지 뽑습니다.

1. SELECT TBL_SALES.SALE_DATE AS SALE_DATE,
2.        TBL_SALES.ITEM_CODE AS ITEM_CODE,
3.        TBL_ITEM.ITEM_NAME AS ITEM_NAME,
4.        TBL_SALES.SALE_QUANTITY AS SALE_QT,
5.        TBL_ITEM.ITEM_PRICE AS ITEM_PRICE,
6.        ISNULL(TBL_SALES.SALE_QUANTITY, 0) *
7.        ISNULL(TBL_ITEM.ITEM_PRICE, 0) AS ITEM_AMOUNT
8.   FROM TBL_SALES INNER JOIN TBL_ITEM ON TBL_ITEM.ITEM_CODE = TBL_SALES.ITEM_CODE
9.  ORDER BY SALE_DATE ASC, ITEM_CODE ASC
10. GO

다음과 같이 결과가 나옵니다.

그냥 넣은 대로 나왔지요? 그럼, 우리는 일별 소계, 총계를 내려고 하는데, 일별 소계란 무엇일까요? 생각해 보면, 일별 소계=품목의 합, 총계=일별 소계의 합 입니다. 그런 생각을 기반으로 그냥 일단 일자와 품목 코드로 GROUP BY 하고 WITH ROLLUP 절을 붙여줍니다. 이 때에, 주의할 점은 소계/합계를 내고자 하는 대상은 수량과 금액입니다. 단가는 각 품목당 개별 단가이기 때문에, 품명과 마찬가지로 그냥 MAX 함수로 뽑고, 소계/합계를 낼 대상인 수량과 금액에만 SUM 함수를 적용합니다.

1. SELECT TBL_SALES.SALE_DATE AS SALE_DATE,
2.        TBL_SALES.ITEM_CODE AS ITEM_CODE,
3.        MAX(TBL_ITEM.ITEM_NAME) AS ITEM_NAME,
4.        SUM(TBL_SALES.SALE_QUANTITY) AS SALE_QT,
5.        MAX(TBL_ITEM.ITEM_PRICE) AS ITEM_PRICE,
6.        SUM(ISNULL(TBL_SALES.SALE_QUANTITY, 0) *
7.            ISNULL(TBL_ITEM.ITEM_PRICE, 0)) AS ITEM_AMOUNT
8.   FROM TBL_SALES INNER JOIN TBL_ITEM ON TBL_ITEM.ITEM_CODE = TBL_SALES.ITEM_CODE
9.  GROUP BY TBL_SALES.SALE_DATE, TBL_SALES.ITEM_CODE WITH ROLLUP
10.  ORDER BY SALE_DATE ASC, ITEM_CODE ASC
11. GO

이런 좀 이상한 결과가 나옵니다. 그런데, 붉은색으로 표시한 부분을 보면, 어엉? 총계 입니다. 그리고, 중간중간에 값이 NULL인 ITEM_NAME Column이 끼어있는 Row 를 보면, 아... 일자별 소계군요. 여기에서 우리가 알 수 있는 건, 그냥 대충 GROUP BY 묶고, WITH ROLLUP 만 써 줘도 순서는 맞지 않지만, 뭔가 결과치를 알려준다는 겁니다.

여기에서 등장하는 것이 GROUPING 함수 입니다. 이걸 잘 써서 정렬을 제대로 해 주면, ROLLUP을 이용한 소계, 총계 등등은 간단히 해결할 수가 있습니다. GROUPING 함수는 GROUP BY 절에 있는 컬럼에 대해 사용할 수가 있습니다. 말 그대로, 얘가 지금 그룹핑 되어 있는지 여부를 0, 1 값으로 리턴합니다.
참고(예전 글) : 2007/11/07 - [Coders] - MSSQL 2000 VS 2005 : Group by, Max
일단 한번 SELECT 절에 넣고 뽑아 보겠습니다. 정렬(ORDER BY)절을 잘 봐 주세요. 뽑아낸 DATE_GROUPING, CODE_GROUPING 을 우선적으로 정렬했습니다. 순서는 DATE_GROUPING, SALE_DATE ASC, CODE_GROUPING, ITEM_CODE ASC 이렇게 말이죠.

1. SELECT TBL_SALES.SALE_DATE AS SALE_DATE,
2.        TBL_SALES.ITEM_CODE AS ITEM_CODE,
3.        MAX(TBL_ITEM.ITEM_NAME) AS ITEM_NAME,
4.        SUM(TBL_SALES.SALE_QUANTITY) AS SALE_QT,
5.        MAX(TBL_ITEM.ITEM_PRICE) AS ITEM_PRICE,
6.        SUM(ISNULL(TBL_SALES.SALE_QUANTITY, 0) *
7.                ISNULL(TBL_ITEM.ITEM_PRICE, 0)) AS ITEM_AMOUNT,
8.        GROUPING(TBL_SALES.SALE_DATE) AS DATE_GROUPING,
9.        GROUPING(TBL_SALES.ITEM_CODE) AS CODE_GROUPING
10.   FROM TBL_SALES INNER JOIN TBL_ITEM ON TBL_ITEM.ITEM_CODE = TBL_SALES.ITEM_CODE
11.  GROUP BY TBL_SALES.SALE_DATE, TBL_SALES.ITEM_CODE WITH ROLLUP
12.  ORDER BY DATE_GROUPING, SALE_DATE ASC, CODE_GROUPING, ITEM_CODE ASC
13. GO

결과는 이렇습니다. 뭔가 0, 1 이 DATE_GROUPING 컬럼과 CODE_GROUPING 컬럼에 나타나는데, 두 값이 모두 0, 0 이 아닌 곳은 적어도 어느 하나는 그룹지어졌다. 즉 이 말은 얘들은 소계 또는 총계 아무튼 뭔가 합계를 나타내고 있다고 알려주는 것이죠. 제가 계속 캡처하고, 코드 따고 이게 좀 귀찮아서, 중간 과정을 생략했는데요, 튀어나온 값으로부터, GROUPING 으로 정렬을 해 버렸습니다. GROUP지어지면 1, 아니면 0 이기 때문에, 기본 정렬 순서대로 0, 1 이 순서대로 정렬된 것이죠.(명확히 하기 위해선, DATE_GROUPING ASC, CODE_GROUPING ASC 이런 식으로 써 줘야 맞겠죠.)

아항... 이제, 결과 테이블에서 보기 싫은 GROUPING 컬럼을 제거하고, 각각 NULL 로 튀어나오는 부분과, 무분별한 MAX 함수로 튀어나온 불분명한 값을 제거해 보도록 합니다. 이렇게...

1. SELECT CASE WHEN GROUPING(TBL_SALES.SALE_DATE) = 1
2.             THEN '총계' ELSE TBL_SALES.SALE_DATE END AS SALE_DATE,
3.        CASE WHEN GROUPING(TBL_SALES.SALE_DATE) = 1
4.             THEN NULL
5.             WHEN GROUPING(TBL_SALES.ITEM_CODE) = 1
6.             THEN '소계' ELSE TBL_SALES.ITEM_CODE END AS ITEM_CODE,
7.        CASE WHEN GROUPING(TBL_SALES.ITEM_CODE) = 1 THEN NULL
8.             ELSE MAX(TBL_ITEM.ITEM_NAME) END AS ITEM_NAME,
9.        SUM(TBL_SALES.SALE_QUANTITY) AS SALE_QT,
   
10.        CASE WHEN GROUPING(TBL_SALES.ITEM_CODE) = 1 THEN NULL
11.             ELSE MAX(TBL_ITEM.ITEM_PRICE) END AS ITEM_PRICE,
12.        SUM(ISNULL(TBL_SALES.SALE_QUANTITY, 0) *
13.            ISNULL(TBL_ITEM.ITEM_PRICE, 0)) AS ITEM_AMOUNT
14. 
    
15.   FROM TBL_SALES INNER JOIN TBL_ITEM ON TBL_ITEM.ITEM_CODE = TBL_SALES.ITEM_CODE
16.  GROUP BY TBL_SALES.SALE_DATE, TBL_SALES.ITEM_CODE WITH ROLLUP
17.  ORDER BY GROUPING(TBL_SALES.SALE_DATE), SALE_DATE ASC,
18.           GROUPING(TBL_SALES.ITEM_CODE), ITEM_CODE ASC
19. GO

이렇게 하면 맨 처음 첨부 이미지와 같은 결과치가 도출됩니다. 이를 컬럼명까지 좀 더 꾸며주는 쿼리를 작성한다라면, 다음과 같이 하고,

1. SELECT CASE WHEN GROUPING(TBL_SALES.SALE_DATE) = 1
2.             THEN '총계' ELSE TBL_SALES.SALE_DATE END AS [판매년월일],
3.        CASE WHEN GROUPING(TBL_SALES.SALE_DATE) = 1
4.             THEN NULL
5.             WHEN GROUPING(TBL_SALES.ITEM_CODE) = 1
6.             THEN '소계' ELSE TBL_SALES.ITEM_CODE END AS [품목코드],
7.        CASE WHEN GROUPING(TBL_SALES.ITEM_CODE) = 1 THEN NULL
8.             ELSE MAX(TBL_ITEM.ITEM_NAME) END AS [품목명],
9.        SUM(TBL_SALES.SALE_QUANTITY) AS [판매수량],
10.        CASE WHEN GROUPING(TBL_SALES.ITEM_CODE) = 1 THEN NULL
11.             ELSE MAX(TBL_ITEM.ITEM_PRICE) END AS [가격],
12.        SUM(ISNULL(TBL_SALES.SALE_QUANTITY, 0) *
13.            ISNULL(TBL_ITEM.ITEM_PRICE, 0)) AS [판매금액]
14.   FROM TBL_SALES INNER JOIN TBL_ITEM ON TBL_ITEM.ITEM_CODE = TBL_SALES.ITEM_CODE
15.  GROUP BY TBL_SALES.SALE_DATE, TBL_SALES.ITEM_CODE WITH ROLLUP
16.  ORDER BY GROUPING(TBL_SALES.SALE_DATE), [판매년월일] ASC,
17.           GROUPING(TBL_SALES.ITEM_CODE), [품목코드] ASC
18. GO

조회하면 이런 결과가 나오겠지요.

쉽죠? 또 자주하다보면 감이 오게 되어서 이런저런 응용이 가능해집니다. 평균도 내고 뭐도 하고 뭐도 하고... 그리고, 참고로, 제가 예제에서는 GROUPING 부분을 SELECT 절에서 제외시켰지만, 사실상 현업에서는 저 값을 뽑아서, 클라이언트 그리드에 바인딩시에 색상도 좀 집어넣고 하는데 사용하기도 합니다. 그리고, 이 예제에서는 정렬에만 사용했지만, 실제로는 여러 컬럼을 가지고 GROUP 을 지을 경우에는 보고 싶지 않은 소계도 나올 때가 있는데, 이 때에도 역시 이 GROUPING 함수를 가지고 HAVING 조건절 등을 사용해서 제거하기도 합니다.

아무튼 핵심은 GROUPING 함수에 대한 이야기였습니다. WITH ROLLUP 을 이용한 소계/총계를 낼 때에는 GROUPING 함수를 잘 사용해서 정렬을 잘 해 보자... 뭐 그런 거.