GROUPING
GROUPING
ROLLUP과 CUBE는 GROUP BY의 수식어(modifier)예요. 둘 다 소계(subtotal)를 계산해요. ROLLUP은 예를 들어 (day, month, year)처럼 정렬된 컬럼 목록을 받아 집계의 각 수준에서 소계를 계산한 뒤 합계(grand total)를 계산해요. CUBE는 지정된 컬럼의 모든 가능한 조합에 대해 소계를 계산해요. GROUPING은 ROLLUP이나 CUBE가 반환한 행 중 어떤 것이 슈퍼집계(superaggregate)이고 어떤 것이 수정되지 않은 GROUP BY가 반환할 행인지 식별해요.
출처: 문서
본문
GROUPING 함수는 여러 컬럼을 인자로 받아 비트마스크(bitmask)를 반환해요.
1—GROUP BY의ROLLUP또는CUBE수식어가 반환한 행이 소계임을 나타내요.0—ROLLUP또는CUBE가 반환한 행이 소계가 아님을 나타내요.
GROUPING SETS
기본적으로 CUBE 수식어는 CUBE에 전달된 컬럼의 모든 가능한 조합에 대해 소계를 계산해요. GROUPING SETS는 계산할 특정 조합을 지정할 수 있게 해줘요.
계층형 데이터 분석은 ROLLUP, CUBE, GROUPING SETS 수식어를 쓰기에 좋은 사례예요. 아래 샘플은 두 데이터센터에 설치된 Linux 배포판과 해당 배포판의 버전에 대한 데이터를 담은 테이블이에요. 데이터를 배포판, 버전, 위치별로 살펴보는 것이 유용할 수 있어요.
샘플 데이터 로드 (Load sample data)
CREATE TABLE servers ( datacenter VARCHAR(255),
distro VARCHAR(255) NOT NULL,
version VARCHAR(50) NOT NULL,
quantity INT
)
ORDER BY (datacenter, distro, version)
INSERT INTO servers(datacenter, distro, version, quantity)
VALUES ('Schenectady', 'Arch','2022.08.05',50),
('Westport', 'Arch','2022.08.05',40),
('Schenectady','Arch','2021.09.01',30),
('Westport', 'Arch','2021.09.01',20),
('Schenectady','Arch','2020.05.01',10),
('Westport', 'Arch','2020.05.01',5),
('Schenectady','RHEL','9',60),
('Westport','RHEL','9',70),
('Westport','RHEL','7',80),
('Schenectady','RHEL','7',80)
SELECT
*
FROM
servers;
┌─datacenter──┬─distro─┬─version────┬─quantity─┐
│ Schenectady │ Arch │ 2020.05.01 │ 10 │
│ Schenectady │ Arch │ 2021.09.01 │ 30 │
│ Schenectady │ Arch │ 2022.08.05 │ 50 │
│ Schenectady │ RHEL │ 7 │ 80 │
│ Schenectady │ RHEL │ 9 │ 60 │
│ Westport │ Arch │ 2020.05.01 │ 5 │
│ Westport │ Arch │ 2021.09.01 │ 20 │
│ Westport │ Arch │ 2022.08.05 │ 40 │
│ Westport │ RHEL │ 7 │ 80 │
│ Westport │ RHEL │ 9 │ 70 │
└─────────────┴────────┴────────────┴──────────┘
10 rows in set. Elapsed: 0.409 sec.
간단한 쿼리 (Simple queries)
배포판별로 각 데이터센터의 서버 수를 구해 보세요.
SELECT
datacenter,
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
datacenter,
distro;
┌─datacenter──┬─distro─┬─qty─┐
│ Schenectady │ RHEL │ 140 │
│ Westport │ Arch │ 65 │
│ Schenectady │ Arch │ 90 │
│ Westport │ RHEL │ 150 │
└─────────────┴────────┴─────┘
4 rows in set. Elapsed: 0.212 sec.
SELECT
datacenter,
SUM (quantity) qty
FROM
servers
GROUP BY
datacenter;
┌─datacenter──┬─qty─┐
│ Westport │ 215 │
│ Schenectady │ 230 │
└─────────────┴─────┘
2 rows in set. Elapsed: 0.277 sec.
SELECT
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
distro;
┌─distro─┬─qty─┐
│ Arch │ 155 │
│ RHEL │ 290 │
└────────┴─────┘
2 rows in set. Elapsed: 0.352 sec.
SELECT
SUM(quantity) qty
FROM
servers;
┌─qty─┐
│ 445 │
└─────┘
1 row in set. Elapsed: 0.244 sec.
GROUPING SETS로 여러 GROUP BY 문 비교하기 (Comparing multiple GROUP BY statements with GROUPING SETS)
CUBE, ROLLUP, GROUPING SETS 없이 데이터를 나누는 방법:
SELECT
datacenter,
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
datacenter,
distro
UNION ALL
SELECT
datacenter,
null,
SUM (quantity) qty
FROM
servers
GROUP BY
datacenter
UNION ALL
SELECT
null,
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
distro
UNION ALL
SELECT
null,
null,
SUM(quantity) qty
FROM
servers;
┌─datacenter─┬─distro─┬─qty─┐
│ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 445 │
└────────────┴────────┴─────┘
┌─datacenter──┬─distro─┬─qty─┐
│ Westport │ ᴺᵁᴸᴸ │ 215 │
│ Schenectady │ ᴺᵁᴸᴸ │ 230 │
└─────────────┴────────┴─────┘
┌─datacenter──┬─distro─┬─qty─┐
│ Schenectady │ RHEL │ 140 │
│ Westport │ Arch │ 65 │
│ Schenectady │ Arch │ 90 │
│ Westport │ RHEL │ 150 │
└─────────────┴────────┴─────┘
┌─datacenter─┬─distro─┬─qty─┐
│ ᴺᵁᴸᴸ │ Arch │ 155 │
│ ᴺᵁᴸᴸ │ RHEL │ 290 │
└────────────┴────────┴─────┘
9 rows in set. Elapsed: 0.527 sec.
GROUPING SETS를 사용해 동일한 정보를 얻는 방법:
SELECT
datacenter,
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
GROUPING SETS(
(datacenter,distro),
(datacenter),
(distro),
()
)
┌─datacenter──┬─distro─┬─qty─┐
│ Schenectady │ RHEL │ 140 │
│ Westport │ Arch │ 65 │
│ Schenectady │ Arch │ 90 │
│ Westport │ RHEL │ 150 │
└─────────────┴────────┴─────┘
┌─datacenter──┬─distro─┬─qty─┐
│ Westport │ │ 215 │
│ Schenectady │ │ 230 │
└─────────────┴────────┴─────┘
┌─datacenter─┬─distro─┬─qty─┐
│ │ │ 445 │
└────────────┴────────┴─────┘
┌─datacenter─┬─distro─┬─qty─┐
│ │ Arch │ 155 │
│ │ RHEL │ 290 │
└────────────┴────────┴─────┘
9 rows in set. Elapsed: 0.427 sec.
CUBE를 GROUPING SETS와 비교하기 (Comparing CUBE with GROUPING SETS)
다음 쿼리의 CUBE인 CUBE(datacenter,distro,version)는 의미가 없을 수 있는 계층을 제공해요. 두 배포판(Arch와 RHEL은 같은 릴리스 주기나 버전 명명 기준을 쓰지 않기 때문)에 걸쳐 조회하는 것은 의미가 없어요. 다음 예제의 GROUPING SETS는 distro와 version을 같은 집합으로 묶기 때문에 더 적절해요.
SELECT
datacenter,
distro,
version,
SUM(quantity)
FROM
servers
GROUP BY
CUBE(datacenter,distro,version)
ORDER BY
datacenter,
distro;
┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐
│ │ │ 7 │ 160 │
│ │ │ 2020.05.01 │ 15 │
│ │ │ 2021.09.01 │ 50 │
│ │ │ 2022.08.05 │ 90 │
│ │ │ 9 │ 130 │
│ │ │ │ 445 │
│ │ Arch │ 2021.09.01 │ 50 │
│ │ Arch │ 2022.08.05 │ 90 │
│ │ Arch │ 2020.05.01 │ 15 │
│ │ Arch │ │ 155 │
│ │ RHEL │ 9 │ 130 │
│ │ RHEL │ 7 │ 160 │
│ │ RHEL │ │ 290 │
│ Schenectady │ │ 9 │ 60 │
│ Schenectady │ │ 2021.09.01 │ 30 │
│ Schenectady │ │ 7 │ 80 │
│ Schenectady │ │ 2022.08.05 │ 50 │
│ Schenectady │ │ 2020.05.01 │ 10 │
│ Schenectady │ │ │ 230 │
│ Schenectady │ Arch │ 2022.08.05 │ 50 │
│ Schenectady │ Arch │ 2021.09.01 │ 30 │
│ Schenectady │ Arch │ 2020.05.01 │ 10 │
│ Schenectady │ Arch │ │ 90 │
│ Schenectady │ RHEL │ 7 │ 80 │
│ Schenectady │ RHEL │ 9 │ 60 │
│ Schenectady │ RHEL │ │ 140 │
│ Westport │ │ 9 │ 70 │
│ Westport │ │ 2020.05.01 │ 5 │
│ Westport │ │ 2022.08.05 │ 40 │
│ Westport │ │ 7 │ 80 │
│ Westport │ │ 2021.09.01 │ 20 │
│ Westport │ │ │ 215 │
│ Westport │ Arch │ 2020.05.01 │ 5 │
│ Westport │ Arch │ 2021.09.01 │ 20 │
│ Westport │ Arch │ 2022.08.05 │ 40 │
│ Westport │ Arch │ │ 65 │
│ Westport │ RHEL │ 9 │ 70 │
│ Westport │ RHEL │ 7 │ 80 │
│ Westport │ RHEL │ │ 150 │
└─────────────┴────────┴────────────┴───────────────┘
39 rows in set. Elapsed: 0.355 sec.
위 예제에서 version은 distro와 연결되지 않으면 의미가 없을 수 있어요. 커널 버전을 추적한다면 두 distro 어느 쪽과도 연결될 수 있으므로 의미가 있을 수도 있어요. 다음 예제처럼 GROUPING SETS를 쓰는 편이 더 나은 선택일 수 있어요.
SELECT
datacenter,
distro,
version,
SUM(quantity)
FROM servers
GROUP BY
GROUPING SETS (
(datacenter, distro, version),
(datacenter, distro))
┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐
│ Westport │ RHEL │ 9 │ 70 │
│ Schenectady │ Arch │ 2022.08.05 │ 50 │
│ Schenectady │ Arch │ 2021.09.01 │ 30 │
│ Schenectady │ RHEL │ 7 │ 80 │
│ Westport │ Arch │ 2020.05.01 │ 5 │
│ Westport │ RHEL │ 7 │ 80 │
│ Westport │ Arch │ 2021.09.01 │ 20 │
│ Westport │ Arch │ 2022.08.05 │ 40 │
│ Schenectady │ RHEL │ 9 │ 60 │
│ Schenectady │ Arch │ 2020.05.01 │ 10 │
└─────────────┴────────┴────────────┴───────────────┘
┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐
│ Schenectady │ RHEL │ │ 140 │
│ Westport │ Arch │ │ 65 │
│ Schenectady │ Arch │ │ 90 │
│ Westport │ RHEL │ │ 150 │
└─────────────┴────────┴─────────┴───────────────┘
14 rows in set. Elapsed: 1.036 sec.