[SQLD] 그룹 함수 ( ROLLUP, CUBE, GROUPING SETS )

2과목 헷갈림 2..

rollbup, cube, grouping sets

--테스트데이터생성
CREATE TABLE sales (
    sale_id       INT PRIMARY KEY,
    sale_date     DATE,
    product_id    VARCHAR(10),
    sales_amount  DECIMAL(10, 2)
);

INSERT INTO sales (sale_id, sale_date, product_id, sales_amount) VALUES (5, '2023-01-15', 'B', 1000.00);
INSERT INTO sales (sale_id, sale_date, product_id, sales_amount) VALUES (6, '2023-02-20', 'C', 1500.00);
INSERT INTO sales (sale_id, sale_date, product_id, sales_amount) VALUES (7, '2023-03-10', 'A', 800.00);
INSERT INTO sales (sale_id, sale_date, product_id, sales_amount) VALUES (8, '2023-04-05', 'C', 1200.00);

ALTER TABLE sales ADD (gubun varchar2(5));

UPDATE sales
  SET gubun =  CASE WHEN product_id = 'A' AND  mod(sale_id,2) = 1 THEN 'ONE' 
             WHEN product_id = 'B' AND  mod(sale_id,2) = 1 THEN 'TWO'  
             WHEN product_id = 'C' AND  mod(sale_id,2) = 0 THEN 'THREE' ELSE 'FOUR' END; 
           
--컬럼 1일때 (group by , cube, rollup 동일함)
SELECT product_id, sum(sales_amount)
  FROM sales
  GROUP BY cube(product_id);
 
SELECT product_id, sum(sales_amount)
  FROM sales
  GROUP BY rollup(product_id);
  
SELECT product_id, sum(sales_amount)
  FROM sales
  GROUP BY GROUPING sets(product_id);
  
 
--컬럼2개
SELECT to_char(sale_date,'MM'), product_id, sum(sales_amount)
  FROM sales
  GROUP BY cube(to_char(sale_date,'MM'), product_id);-- group by  + 모든 컬럼에 대해 각각 소계와 총계
 

SELECT to_char(sale_date,'MM'),  product_id, sum(sales_amount)
  FROM sales
  GROUP BY rollup(to_char(sale_date,'MM'), product_id); --group by  + 첫번째 컬럼기준으로 소계와 총계
  
 
SELECT to_char(sale_date,'MM'), product_id, sum(sales_amount)
  FROM sales
  GROUP BY GROUPING sets(to_char(sale_date,'MM'), product_id); --컬럼1에 대한 전체 집계, 컬럼2에 대한 전체집계, (컬럼1,컬럼2) 묶어 사용시 한컬럼으로보고집계 (group by 결과 포함 X)
   
  
  
  
--컬럼3개
SELECT gubun, to_char(sale_date,'MM'), product_id, sum(sales_amount)
  FROM sales
  GROUP BY cube(gubun,to_char(sale_date,'MM'), product_id)
  ORDER BY 1,2,3;
  -- group by  + 모든 컬럼에 대해 각각 소계와 총계
 

SELECT gubun,to_char(sale_date,'MM'),  product_id, sum(sales_amount)
  FROM sales
  GROUP BY rollup(gubun,to_char(sale_date,'MM'), product_id); --group by  + 첫번째 컬럼기준으로 소계와 총계
  
 
SELECT gubun,to_char(sale_date,'MM'), product_id, sum(sales_amount)
  FROM sales
  GROUP BY GROUPING sets(gubun,to_char(sale_date,'MM'), product_id);

 

 

--컬럼3개일때 결과

--cube

 

--rollup

 

--grouping sets

출처 : https://velog.io/@damool/rollup-cube-grouping-sets-를-알자보자