26강 SQL이 뭐지? PostgreSQL GROUPING SETS

26강 SQL이 뭐지? PostgreSQL GROUPING SETS   

PostgreSQL

SQL이 뭐지? 26강 시작합니다.

 

이번 강의에서는

▶ PostgreSQL GROUPING SETS

에 대해서 알아보겠습니다.

 

쿼리에 여러 그룹 집합을 생성하는 PostgreSQL GROUPING SETS 절에 대해서 학습합니다.

 

GROUP BY를 이용해 여러 개의 그룹핑을 만들고 UNION ALL로 합치던 과정을

GROUPING SETS 절을 사용하여 해결할 수 있습니다.

---

Setup a sample table   

실습을 위해 sales 테이블을 만듭니다.

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
    brand VARCHAR NOT NULL,
    segment VARCHAR NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (brand, segment)
);

INSERT INTO sales (brand, segment, quantity)
VALUES
    ('ABC', 'Premium', 100),
    ('ABC', 'Basic', 200),
    ('XYZ', 'Premium', 100),
    ('XYZ', 'Basic', 300);
COMMIT;

 

조회하면,

sales 테이블에는 브랜드 및 세그먼트별로 판매된 제품 수가 저장됩니다.

 

Introduction to PostgreSQL GROUPING SETS   

그룹화 집합은 GROUP BY 절을 사용하여 그룹화하는 열 집합입니다.

그룹화 집합은 괄호 안에 있는 쉼표로 구분된 열 목록으로 표시됩니다.

(column1, column2, ...)   

 

예를 들어, 다음 쿼리는 GROUP BY 절을 사용하여 브랜드 및 세그먼트별로 판매된 제품 수를 반환합니다.

즉, (brand, segement)로 표시되는 브랜드 및 세그먼트의 그룹화 집합을 정의합니다.

SELECT
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand,
    segment;   

 

다음 쿼리는 브랜드에서 판매한 제품 수를 찾습니다. 그룹화 세트(brand)를 정의합니다.

SELECT
    brand,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand;   

 

다음 쿼리는 세그먼트별로 판매된 제품 수를 찾습니다. 그룹화 세트(segment)를 정의합니다.

SELECT
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    segment;   

 

다음 쿼리는 모든 브랜드 및 세그먼트에 대해 판매된 제품 수를 찾습니다. 이 명령은 ()로 표시되는 빈 그룹화 집합을 정의합니다.

SELECT SUM (quantity) FROM sales;   

 

단일 쿼리를 사용하여 모든 그룹화 집합을 가져온다고 가정합니다. 이를 위해 UNION ALL을 사용하여 위의 모든 쿼리를 결합할 수 있습니다.

UNION ALL은 모든 결과 집합에 호환되는 데이터 유형이 있는 열 수가 동일해야 하므로 아래와 같이 각 결과 집합의 선택 목록에 NULL을 추가하여 쿼리를 조정해야 합니다.

SELECT
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand,
    segment

UNION ALL

SELECT
    brand,
    NULL,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand

UNION ALL

SELECT
    NULL,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    segment

UNION ALL

SELECT
    NULL,
    NULL,
    SUM (quantity)
FROM
    sales;   

이 쿼리는 모든 그룹 집합에 대한 집계가 포함된 단일 결과 집합을 생성했습니다.

위의 쿼리가 예상대로 작동하더라도 두 가지 주요 문제가 있습니다.

● 첫째로, 그것은 꽤 길다.
● 둘째,  성능 문제가 있다. PostgreSQL은 각 쿼리에 대해 판매 테이블을 별도로 검색해야 합니다.

   더 효율적으로 만들기 위해, Postgre.SQL은 GROUP BY 절의 하위 절인 GROUP SETS 절을 제공합니다.

GROUPING SETS를 사용하면 동일한 조회에서 여러 그룹화 집합을 정의할 수 있습니다.

GROUPING SETS의 일반적인 구문은 다음과 같습니다.

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

이 구문에서는 4개의 그룹 집합(c1, c2), (c1), (c2) 및 ()이 있습니다.

위의 예제에 이 구문을 적용하려면 다음과 같이 UNION ALL 절 대신 GROUPING SETS 절을 사용할 수 있습니다.

SELECT
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    GROUPING SETS (
        (brand, segment),
        (brand),
        (segment),
        ()
    );   

이 쿼리는 훨씬 더 짧고 읽기 쉽습니다.

게다가, PostgreSQL은 판매 테이블을 검색하는 횟수를 최적화하고 여러 번 검색하지 않습니다.

 

Grouping function

GROUPING() 함수는 열 이름 또는 식을 사용할 수 있는 인수를 사용합니다.

 

GROUPING( column_name | expression)   

 

열 이름 또는 식은 GROUP BY 절에 지정된 것과 일치해야 합니다.

GROUPING() 함수는 인수가 현재 그룹화 집합의 멤버이면 비트 0을 반환하고, 그렇지 않으면 1을 반환합니다.

다음 예를 참조하십시오.

SELECT
     GROUPING(brand) grouping_brand,
     GROUPING(segment) grouping_segment,
     brand,
     segment,
     SUM (quantity)
FROM
    sales
GROUP BY
    GROUPING SETS (
     (brand),
     (segment),
     ()
     )
ORDER BY
    brand,
    segment;   

grouping_brand의 값이 0일 때 sum 열은 브랜드의 소계를 보여줍니다.
grouping_segment의 값이 0이면, sum 열에 세그먼트의 부분 합계가 표시됩니다.

5번 열은 전체 합계를 보여줍니다.

 

GROUPING() 함수를 사용하여,

브랜드별 소계, 세그먼트별 소계, 합계를 한 번에 쿼리 할 수 있습니다.

---

HAVING 절의 GROUPING() 함수를 사용하여 다음과 같은 각 브랜드의 소계를 찾을 수 있습니다.

SELECT
     GROUPING(brand) grouping_brand,
     GROUPING(segment) grouping_segment,
     brand,
     segment,
     SUM (quantity)
FROM
    sales
GROUP BY
     GROUPING SETS (
     (brand),
     (segment),
     ()
     )
HAVING GROUPING(brand) = 0
ORDER BY
     brand,
     segment;   

---

이번 시간에는 PostgreSQL GROUPING SETS를 사용하여 여러 그룹화 집합을 생성하는 방법을 공부했습니다.

 

감사합니다.

---

본 강의는 PostgreSQL Tutorial을 참조합니다. 

PostgreSQL Tutorial - Learn PostgreSQL from Scratch

실습용 테이블에 대해서는 3강을 참조하세요.

3강 SQL이 뭐지? 도구들을 설치해보자. ( PostgreSQL, HeidiSQL )

---

Do it! SQL을 찾아 주셔서 감사합니다. 여러분의 댓글이 큰 힘이 됩니다.