[PostgreSQL] GROUPING SETS/ CUBE/ ROLLUP

 

 

 

 

 

그룹화 집합을 여러개 만들기 위한 함수

GROUPING SETS / CUBE / ROLLUP

 

 

 

 

• GROUPING SETS

 

group by + union all + group by + union all + group by 같이 그룹화된 각 쿼리 결과를 union을 사용해서 하나의 결과로 결합해야 할 때, 쿼리문이 길어지고 쿼리문을 각각 연산해야 하는 문제가 발생합니다. 이러한 문제를 보완하기 위해 GROUPING SETS 을 사용하면 한 번에 그룹화된 쿼리 결과 집합을 생성할 수 있습니다. 

SELECT
    c1,
    c2,
    aggregate_function(c3)
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (c1, c2),
        (c1),
        (c2),
        ()
);

 

• Example - 국가별 고객 수, 도시별 고객 수, 전체 고객 수 구하기 (PostgreSQL sample database 사용)

SELECT country, city, COUNT(c.customer_id)
FROM customer c 
JOIN address a ON c.address_id = a.address_id 
JOIN city c2 ON a.city_id = c2.city_id 
JOIN country c3 ON c2.country_id = c3.country_id 
GROUP BY GROUPING SETS (
    (country), 
    (city), 
    ()
    )

 

 

 

 

 

• CUBE

 

CUBE는 ( ) 속에 지정된 칼럼으로 조합이 가능한 모든 그룹화 집합을 간단하게 생성할 수 있습니다. 

SELECT
    c1,
    c2,
    c3,
    aggregate (c4)
FROM
    table_name
GROUP BY
    CUBE (c1, c2, c3);

 

아래  CUBE 절과 GROUPING SETS 절은 같은 결과를 반환합니다.

모든 그룹 집합을 만들 때는 GROUPING SETS 보다 CUBE를 이용하면 간단하고 보기 좋게 쿼리를 작성할 수 있습니다. 

CUBE(c1,c2,c3) 

GROUPING SETS (
    (c1,c2,c3), 
    (c1,c2),
    (c1,c3),
    (c2,c3),
    (c1),
    (c2),
    (c3), 
    ()
 )

 

• Example - 국가별 도시별 고객수, 국가별 고객수, 도시별 고객수, 전체 고객수 구하기 (PostgreSQL sample database 사용)

SELECT country, city, COUNT(c.customer_id)
FROM customer c 
JOIN address a ON c.address_id = a.address_id 
JOIN city c2 ON a.city_id = c2.city_id 
JOIN country c3 ON c2.country_id = c3.country_id 
GROUP BY CUBE (country, city)

 

 

 

 

• ROLLUP

 

그룹화된 집합을 생성하는데 CUBE가 모든 하위 집합을 생성한다면, ROLLUP은 입력된 칼럼의 계층 구조를 고려하여 하위 집합을 생성합니다. ROLLUP(c1,c2,c3)는 계층 구조가 c1 > c2 > c3 와 같다고 가정하고 하위 집합 생성하는 것입니다. 간단하게 c1, c2, c3를 Year, Month, Day 예시로 들어볼 수 있습니다. 

SELECT
    c1,
    c2,
    c3,
    aggregate(c4)
FROM
    table_name
GROUP BY
    ROLLUP (c1, c2, c3);

ROLLUP(c1,c2,c3)

(c1, c2, c3)
(c1, c2)
(c1)
()

 

• Example - 국가당 도시별 매출액, 국가별 매출액, 전체 매출액 구하기 (PostgreSQL sample database 사용)

SELECT country, city, SUM(amount)
FROM payment p
JOIN customer c ON p.customer_id = c.customer_id 
JOIN address a ON c.address_id = a.address_id 
JOIN city c2 ON a.city_id = c2.city_id 
JOIN country c3 ON c2.country_id = c3.country_id 
GROUP BY ROLLUP(country, city)

 

 

 

 

 

https://www.postgresqltutorial.com

PostgreSQL Tutorial - Learn PostgreSQL from Scratch