ROLL UP CUBE GROUPING SETS

ROLL UP

컬럼 순서에 영향을 미치며 지정된 열의 하위 합계와 총합계를 한 번에 계산할 수 있다.

 

 

ROLLUP을 사용하면 Region과 Product의 조합뿐만 아니라 각 Region과 전체 Product, 전체 Region에 대한 판매량 합계도 계산된다.

 

 

SELECT Region, Product, SUM(Amount) AS Total
FROM Sales
GROUP BY Region, Product
UNION ALL
SELECT Region, NULL AS Product, SUM(Amount) AS Total
FROM Sales
GROUP BY Region
UNION ALL
SELECT NULL AS Region, NULL AS Product, SUM(Amount) AS Total
FROM Sales;

SELECT Region, Product, SUM(Amount) AS Total
FROM Sales
GROUP BY ROLLUP(Region, Product);

SELECT Region, Product, Category, SUM(Amount) AS Total
FROM Sales
GROUP BY ROLLUP(Region, Product, Category);



SELECT Region, Product, Category, SUM(Amount) AS Total
FROM (
    SELECT Region, Product, Category, Amount
    FROM Sales
    UNION ALL
    SELECT Region, Product, NULL AS Category, Amount
    FROM Sales
    UNION ALL
    SELECT Region, NULL AS Product, NULL AS Category, Amount
    FROM Sales
    UNION ALL
    SELECT NULL AS Region, NULL AS Product, NULL AS Category, Amount
    FROM Sales
) sub
GROUP BY Region, Product, Category;



SELECT Region, Product, Category, SUM(Amount) AS Total
FROM Sales
GROUP BY Region, Product, Category
UNION ALL
SELECT Region, Product, NULL AS Category, SUM(Amount) AS Total
FROM Sales
GROUP BY Region, Product
UNION ALL
SELECT Region, NULL AS Product, NULL AS Category, SUM(Amount) AS Total
FROM Sales
GROUP BY Region
UNION ALL
SELECT NULL AS Region, NULL AS Product, NULL AS Category, SUM(Amount) AS Total
FROM Sales;

 

 

 

 

 

 

 

CUBE 

 

CUBE는 다차원 집계를 수행하여 데이터를 여러 축에서 동시에 요약하는 SQL 집계 기능이다.

인자로 주어진 컬럼의 결합 가능한 모든 조합에 대해서 집계를 수행하므로 다른 그룹 함수에 비해 시스템에 대한 부하가 크다.

SELECT REGION, PRODUCT,SUM(AMOUNT) AS TOTAL
FROM SALES
GROUP BY CUBE(REGION, PRODUCT);

 

 

• 지역과 제품에 대한 총합
• 지역에 대한 총합
• 제품에 대한 총합
• 전체 판매 데이터에 대한 총합

 

 

CUBE를 GROUPING SETS로 변환

SELECT Region, Product, Category, SUM(Amount) AS Total
FROM Sales
GROUP BY GROUPING SETS ((Region), (Product), (Category), (Region, Product), (Region, Category),
(Product, Category), (Region, Product, Category),())
ORDER BY Region, Product, Category;

 

SELECT Region, Product, Category, SUM(Amount) AS Total
FROM Sales
GROUP BY CUBE(Region, Product, Category);

 

 

 

 

 

 

 

 

 

 

GROUPING SETS

 

GROUPING SETS는 여러 그룹화를 하나의 결과 집합으로 결합하는 데 사용되는 SQL 절이다. 각 그룹화에 대한 결과를 개별적으로 반환하고 이를 결합하여 최종 결과를 생성한다. 

 

1. 지역 단위로 그룹화
2. 지역 및 제품 단위로 그룹
3. 지역, 제품 및 카테고리 단위로 그룹

SELECT Region, Product, Category, SUM(Amount) AS Total
FROM Sales
GROUP BY GROUPING SETS ((Region), (Region, Product), (Region, Product, Category));

 

UNION ALL을 적용시켜 같은 결과를 출력

SELECT Region, NULL AS Product, NULL AS Category, SUM(Amount) AS Total
FROM Sales
GROUP BY Region

UNION ALL

SELECT Region, Product, NULL AS Category, SUM(Amount) AS Total
FROM Sales
GROUP BY Region, Product

UNION ALL

SELECT Region, Product, Category, SUM(Amount) AS Total
FROM Sales
GROUP BY Region, Product, Category;