SQLD 그룹함수

그룹 함수

 

그룹함수를 통해 소계/중계/합계/총합계를 구할 수 있다.

하나의 SQL로 테이블을 한번만 읽어서 빠르게 원하는 리포트를 작성할 수 있다. (단일 DML만으로도 원하는 작업을 할 수 있다)

ex) 매출에 대한 리포트를 작성해야 하는 경우 개발자는 그룹 함수를 이용해 쉽게 작성할 수 있다.

 

ROLLUP

 

ROLLUP은 지정된 컬럼의 소계 및 총계를 구하기 위해 사용되는 그룹함수 

그룹핑 컬럼의 수가 N일때, N+1 Level의 Subtotal이 생성된다

계층 구조이기 때문에 순서가 바뀌면 수행 결과도 바뀌게 되기 때문에 순서에 주의해야 한다.

ROLLUP(A,B) != ROLLUP(B,A)

 

SELECT
    YEAR(OrderDate) AS Year,
    MONTH(OrderDate) AS Month,
    SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ROLLUP(YEAR(OrderDate), MONTH(OrderDate));

 

 Year   | Month | TotalSales
---------|-------|------------
  2023 | 1     | 10000
  2023 | 2     | 15000
  2023 | NULL  | 25000
  2024 | 1     | 12000
  2024 | 2     | 18000
  2024 | NULL  | 30000
 NULL  | NULL  | 55000

 

 

• ROLLUP은 연도별, 월별 집계를 수행한 후, 연도별 총합 및 전체 총합을 포함하는 행을 추가한다
• NULL 값은 모든 항목을 합산한 결과를 의미한다

ROLLUP(a,b) 의 결과는 GROUPING SETS((a,b),a,NULL) 의 결과와 동일

여기서 NULL은 전체 집합 결과를 의미한다

 

 

CUBE

 

CUBE 함수는 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹함수

CUBE 함수는 내부적으로 대상 컬럼의 순서를 변경하여 또 한 번의 쿼리를 수행한다

그룹핑 컬럼의 수가 N일때 , 2의 N승 Subtotal이 생성된다.

시스템의 성능에 무리를 많이 준다.

그룹 함수에서 총계를 나타낼 때 일반적으로는 빈 괄호()를 많이 사용하고, NULL이나 작은 따옴표를 열고 닫는 '' 를 사용한다

 

SELECT
    YEAR(OrderDate) AS Year,
    MONTH(OrderDate) AS Month,
    Region,
    SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY CUBE(YEAR(OrderDate), MONTH(OrderDate), Region);

 

 Year  | Month | Region | TotalSales
---------|-------|--------|------------
  2023 | 1     | East   | 5000
  2023 | 1     | West   | 5000
  2023 | 1     | NULL   | 10000
  2023 | NULL  | East   | 10000
  2023 | NULL  | NULL   | 15000
  2023 | 2     | East   | 8000
  2023 | 2     | West   | 7000
  2023 | 2     | NULL   | 15000
  2024 | 1     | East   | 6000
  NULL | NULL  | NULL   | 55000

 

 

GROUPING SETS

특정 항목에 대한 소계를 계산

ROLLUP과 CUBE는 GROUP BY 결과에 소그룹 합계와 토탈 합계를 보여주지만, GROUPING SETS는 각 소그룹별 합계만 간단히 보여준다.

UNION ALL과 같은 결과를 얻을 수 있다.

GROUPING SETS 함수는 ROLLUP 함수와 달리 컬럼 간 순서와 무관한 결과를 얻을 수 있다. 

 

SELECT
    YEAR(OrderDate) AS Year,
    MONTH(OrderDate) AS Month,
    Region,
    SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS(
    (YEAR(OrderDate), MONTH(OrderDate)),
    (YEAR(OrderDate), Region),
    (Region)
);

 

 Year  | Month | Region | TotalSales
---------|-------|--------|------------
  2023 | 1     | NULL   | 10000
  2023 | 2     | NULL   | 15000
  2023 | NULL  | East   | 15000
  2023 | NULL  | West   | 10000
  2024 | 1     | NULL   | 12000
  2024 | 2     | NULL   | 18000
 NULL  | NULL  | East   | 30000
 NULL  | NULL  | West   | 18000

 

GROUPING 

ROLLUP, CUBE, GROUPING SETS를 지원한다.

1 : 집계가 계산된 결과

0 : 집계가 계산되지 않은 결과

 

SELECT
    YEAR(OrderDate) AS Year,
    MONTH(OrderDate) AS Month,
    GROUPING(YEAR(OrderDate)) AS YearGroup,
    GROUPING(MONTH(OrderDate)) AS MonthGroup,
    SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ROLLUP(YEAR(OrderDate), MONTH(OrderDate));

 

 Year  | Month | YearGroup | MonthGroup | TotalSales
---------|-------|-----------|------------|------------
  2023 | 1     | 0         | 0          | 10000
  2023 | 2     | 0         | 0          | 15000
  2023 | NULL  | 0         | 1          | 25000
  2024 | 1     | 0         | 0          | 12000
  2024 | 2     | 0         | 0          | 18000
  2024 | NULL  | 0         | 1          | 30000
 NULL  | NULL  | 1         | 1          | 55000

 

0) GROUP BY

 

[ SQL ]

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY 상품ID, 월;

 

 

가장 기본적이고 단순한 GROUP BY 절만 사용한 결과

상품ID와 월에 대해 매출액의 합계를 구했다. 

 

 

1) ROLLUP

ex) ROLLUP(상품ID, 월) 
- 상품ID별 월별 집계
- 상품ID별 집계 (모든 월 포함)
- 전체 집계 

--> 총 2개의 그룹핑 컬럼 = 2 + 1 = 3개의 Subtotal 생성

[ SQL ] 

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY ROLLUP(상품ID, 월);

 

 

상품ID와 월 컬럼에 대한 값이 NULL이 아닌 경우 : 상품ID별 월별 집계

월에 대한 컬럼 값이 NULL : 상품 ID별 집계 (모든 월 포함)

모든 그룹핑 컬럼 NULL : 전체 집계

맨 처음 명시한 컬럼에 대해서만 소그룹 합계를 구한것을 볼 수 있습니다. (순서 주의 !!!)

 

 

2) CUBE

x) CUBE(상품ID, 월)
- 상품ID별 월별 집계
- 상품ID별 집계 (모든 월 포함)
- 월별 집계 (모든 상품ID 포함)
- 전체 집계 

-->  총 2개의 그룹핑 컬럼 = 2의 2승 = 4개의 Subtotal 생성

[ SQL ]

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY CUBE(상품ID, 월);

 

 

상품ID와 월 컬럼에 모두 NULL이 아닌 경우 : 상품ID별 월별 집계

월에 대한 컬럼 값이 NULL : 상품별 집계 (모든 월 포함)

상품ID에 대한 컬럼 값이 NULL : 월별 집계 (모든 상품 포함)

모든 그룹핑 컬럼 NULL : 전체 집계

 

 

3) GROUPING SETS

 

GROUPING SETS(A,B)는 GROUP BY A와 GROUP BY B를 반환한다. GROUP BY A,B를 추가적으로 구하려면 GROUPING SETS(A,B,(A,B)) 로 표현해야 한다. 

 

ex) GROUPING SETS(상품ID, 월)
- 상품별 합계
- 월별 합계

--> 특정 항목에 대한 Subtotal 생성

 

[ SQL ]

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY GROUPING SETS(상품ID, 월);

 

 

상품별 합계 : 상품에 대한 소계를 생성

월별 합계  : 월에 대한 소계를 생성

 

 

4) GROUPING

GROUPING은 직접적으로 그룹별 집계를 구하는 함수가 아닌 위의 집계 함수들을 서포트하는 역할을 한다. 집계가 계산된 결과에 대해서는 1 , 그렇지 않은 경우에는 0의 값을 갖는다. 

GROUPING 함수는 SELECT 절과 HAVING 절에서만 사용이 가능하다. 

 

GROUPING 함수는 ROLLUP, CUBE, GROUPING SETS의 그룹 함수가 없어도 사용은 가능하지만, 그룹 함수를 사용하지 않으면 소계 처리가 되지 않기 때문에 0을 반환한다. 

 

[ SQL ]

SELECT 
    CASE GROUPING(상품ID) WHEN 1 THEN '모든 상품ID' ELSE 상품ID END AS 상품ID,
    CASE GROUPING(월) WHEN 1 THEN '모든 월' ELSE 월 END AS 월, 
    SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY ROLLUP(상품ID, 월);