MySQL - ROLLUP: 데이터 집계 마스터링

안녕하세요, MySQL 열망하는 동기들! 오늘 우리는 ROLLUP 절을 사용하여 데이터 집계의 세계로 흥미로운 여정을 떠납니다. 여러분의 친절한 이웃 컴퓨터 교사로서, 저는 이 주제를 단계별로 안내해 드리겠습니다. 프로그래밍에 처음이라면 걱정하지 마세요 - 우리는 기본에서 시작하여 차근차근 올라갈 것입니다. 그러니 커피(또는 차, 당신이 좋아하는 것)를 한 잔 마시고, 같이 뛰어들어 보세요!

MySQL ROLLUP 절: 데이터 분석에서의 새로운 최고 친구

ROLLUP은 무엇인가요?

거대한 가족 모임 피크닉을 계획하고 있다고 상상해 보세요. 모든 식재료, 그数量的 그리고 가격의 목록이 있습니다. 각 식재료 카테고리의 총 비용을 빠르게 확인하고, 모든 것의 총 합계를 확인할 수 있다면 얼마나 좋을까요? 그 precisely ROLLUP이 MySQL에서 하는 일입니다 - 데이터의 다양한 수준에서 집계 행을 생성해 줍니다.

ROLLUP은 GROUP BY 절의 확장입니다. 단일 쿼리에서 여러 수준의 소계와 총 합계를 생성할 수 있게 해줍니다. 정말 멋지죠?

기본 문법

ROLLUP을 사용한 쿼리의 기본 구조는 다음과 같습니다:

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ... WITH ROLLUP;

간단한 예제로 이를 설명해 보겠습니다. picnic_items라는 테이블을 가정해 봅시다:

CREATE TABLE picnic_items (
item_name VARCHAR(50),
category VARCHAR(50),
quantity INT,
price DECIMAL(10, 2)
);

INSERT INTO picnic_items VALUES
('Sandwich', 'Food', 20, 5.99),
('Soda', 'Drink', 30, 1.99),
('Chips', 'Snack', 15, 2.50),
('Water', 'Drink', 40, 0.99),
('Cookies', 'Snack', 25, 3.50);

이제 ROLLUP을 사용하여 피크닉 비용의 요약을 얻어보겠습니다:

SELECT category, SUM(quantity * price) AS total_cost
FROM picnic_items
GROUP BY category WITH ROLLUP;

결과는 다음과 같을 수 있습니다:

+----------+------------+
| category | total_cost |
+----------+------------+
| Drink    |     159.60 |
| Food     |     119.80 |
| Snack    |     125.00 |
| NULL     |     404.40 |
+----------+------------+

이 결과에서 우리는 각 카테고리의 총 비용을 확인하고, 마지막 행(카테고리 열에 NULL이 있는 행)에서 모든 카테고리의 총 합계를 얻습니다. NULL은 ROLLUP이 "이 것은 모든 것의 합계입니다"라고 말하는 방식입니다.

ROLLUP을 여러 컬럼에서 사용하기: 더 깊이 탐구하기

이제 기본적인 내용을 이해했으므로, 한 단계 더 나아가 보겠습니다. ROLLUP은 여러 컬럼에서 작동하여 데이터의 계층 구조를 만들어 줍니다. 피크닉 아이템을 카테고리별로, 그리고 특정 아이템별로 정리하고 각 수준에서 합계를 얻는 것처럼입니다.

이제 우리는 카테고리와 아이템 이름을 모두 그룹화하는 쿼리를 수정해 보겠습니다:

SELECT category, item_name, SUM(quantity * price) AS total_cost
FROM picnic_items
GROUP BY category, item_name WITH ROLLUP;

이 쿼리는 다음과 같은 결과를 생성할 수 있습니다:

+----------+------------+------------+
| category | item_name  | total_cost |
+----------+------------+------------+
| Drink    | Soda       |      59.70 |
| Drink    | Water      |      39.60 |
| Drink    | NULL       |      99.30 |
| Food     | Sandwich   |     119.80 |
| Food     | NULL       |     119.80 |
| Snack    | Chips      |      37.50 |
| Snack    | Cookies    |      87.50 |
| Snack    | NULL       |     125.00 |
| NULL     | NULL       |     344.10 |
+----------+------------+------------+

이제 무엇이 일어나고 있는지 설명해 보겠습니다:

1. 각 카테고리 내에서 각 특정 아이템의 합계를 얻습니다.
2. 각 카테고리의 소계(아이템 이름이 NULL인 경우)를 얻습니다.
3. 마지막 행에서 총 합계(카테고리와 아이템 이름이 모두 NULL인 경우)를 얻습니다.

이 것은 데이터베이스 쿼리 결과에서 작은 스프레드시트를 가지는 것과 같습니다!

클라이언트 프로그램을 사용한 ROLLUP: 모든 것을 통합하기

이제 ROLLUP 기술을 더 현실적인 시나리오에 적용해 보겠습니다. 작은 상점의 인벤토리 관리 시스템을 개발 중이라고 가정해 봅시다. 각 부서별, 제품별 매출을 보여주고 전체 합계를 제공하는 보고서를 생성하고 싶습니다.

먼저 테이블을 생성하고 샘플 데이터를 입력해 보겠습니다:

CREATE TABLE sales (
department VARCHAR(50),
product VARCHAR(50),
quarter INT,
revenue DECIMAL(10, 2)
);

INSERT INTO sales VALUES
('Electronics', 'Laptop', 1, 1200.00),
('Electronics', 'Smartphone', 1, 800.00),
('Electronics', 'Laptop', 2, 1500.00),
('Clothing', 'T-Shirt', 1, 200.00),
('Clothing', 'Jeans', 2, 500.00),
('Grocery', 'Bread', 1, 50.00),
('Grocery', 'Milk', 1, 30.00),
('Grocery', 'Eggs', 2, 40.00);

이제 종합 보고서를 생성하기 위해 ROLLUP을 사용해 보겠습니다:

SELECT
IFNULL(department, 'Total') AS department,
IFNULL(product, 'Subtotal') AS product,
IFNULL(quarter, 'All Quarters') AS quarter,
SUM(revenue) AS total_revenue
FROM
sales
GROUP BY
department, product, quarter WITH ROLLUP;

이 쿼리는 다음과 같은 상세한 보고서를 생성합니다. IFNULL 함수는 NULL 값을 더 의미 있는 레이블로 변경합니다.

이 쿼리는 다음과 같은 작업을 수행합니다:

1. 데이터를 부서별, 제품별, 분기별로 그룹화합니다.
2. 각 조합의 매출 합계를 계산합니다.
3. ROLLUP을 사용하여 각 수준에서 소계를 생성합니다.
4. NULL 값을 더 읽기 쉽게 변경합니다.

결과는 다음과 같을 수 있습니다:

+-------------+------------+--------------+---------------+
| department  | product    | quarter      | total_revenue |
+-------------+------------+--------------+---------------+
| Clothing    | Jeans      | 2            |        500.00 |
| Clothing    | Jeans      | All Quarters |        500.00 |
| Clothing    | T-Shirt    | 1            |        200.00 |
| Clothing    | T-Shirt    | All Quarters |        200.00 |
| Clothing    | Subtotal   | All Quarters |        700.00 |
| Electronics | Laptop     | 1            |       1200.00 |
| Electronics | Laptop     | 2            |       1500.00 |
| Electronics | Laptop     | All Quarters |       2700.00 |
| Electronics | Smartphone | 1            |        800.00 |
| Electronics | Smartphone | All Quarters |        800.00 |
| Electronics | Subtotal   | All Quarters |       3500.00 |
| Grocery     | Bread      | 1            |         50.00 |
| Grocery     | Bread      | All Quarters |         50.00 |
| Grocery     | Eggs       | 2            |         40.00 |
| Grocery     | Eggs       | All Quarters |         40.00 |
| Grocery     | Milk       | 1            |         30.00 |
| Grocery     | Milk       | All Quarters |         30.00 |
| Grocery     | Subtotal   | All Quarters |        120.00 |
| Total       | Subtotal   | All Quarters |       4320.00 |
+-------------+------------+--------------+---------------+

이 결과는 우리에게 매출 데이터에 대한 종합적인 시각을 제공합니다. 각 제품, 각 부서, 그리고 전체 합계의 소계를 얻습니다.

유용한 ROLLUP 메서드

ROLLUP을 사용할 때 유용한 몇 가지 메서드를 요약한 표를 아래에 제시합니다:

메서드	설명
WITH ROLLUP	GROUP BY와 함께 사용하여 소계와 총 합계를 생성
IFNULL()	NULL 값을 지정된 값으로 변경
GROUPING()	ROLLUP에 의해 생성된 NULL 값에 대해 1을 반환, 그렇지 않으면 0을 반환
COALESCE()	목록에서 첫 번째 비NULL 값을 반환

기억하시기 바랍니다, 연습이 완벽을 이루는 열쇠입니다! 자신만의 테이블을 만들고 ROLLUP 쿼리를 실험해 보세요. ROLLUP은 데이터에 대한 귀중한 통찰을 제공하는 강력한 도구입니다.

그리고 여러분의 MySQL 기술이 ROLLUP 절로 한 단계 더 발전했습니다. 데이터 탐구를 계속하고, 계속 코딩하고, 가장 중요한 것은 데이터와의 즐거움을 유지하세요!

Credits: Image by storyset