MySQL 데이터 집계 및 그룹화 튜토리얼 | 인터랙티브 랩

소개

이 랩에서는 데이터베이스에서 데이터를 분석하고 요약하는 데 필수적인 기술인 MySQL 의 데이터 집계 및 그룹화를 탐구합니다. 집계 함수를 사용하여 행 전체에서 계산을 수행하고, 열 값을 기반으로 데이터를 그룹화하며, 그룹화된 결과를 필터링하는 방법을 배우게 됩니다. 이러한 기술은 보고서를 생성하고, 추세를 분석하며, 데이터에서 의미 있는 통찰력을 추출하는 데 기본적으로 사용됩니다. 실습을 통해 이러한 중요한 데이터베이스 작업에 대한 실질적인 경험을 얻고 MySQL 에서 데이터를 효과적으로 분석하는 방법을 이해하게 될 것입니다.

기본 집계 함수

이 단계에서는 MySQL 의 기본적인 집계 함수를 살펴보겠습니다. 이러한 함수는 여러 행에 걸쳐 계산을 수행하고 단일 값을 반환하므로 데이터 분석에 필수적입니다.

먼저 MySQL 에 연결하고 데이터베이스를 선택해 보겠습니다.

sudo mysql -u root

연결되면:

USE sales_db;

COUNT 함수

결과 집합의 행 수를 세는 COUNT 함수부터 시작해 보겠습니다.

-- 전체 판매 수 계산
SELECT COUNT(*) as total_sales
FROM sales;

다음과 같은 출력을 볼 수 있습니다.

+--------------+
| total_sales  |
+--------------+
| 12           |
+--------------+

COUNT 는 다양한 방식으로 사용할 수 있습니다.

-- 판매된 고유 제품 수 계산
SELECT COUNT(DISTINCT product_name) as unique_products
FROM sales;

-- 카테고리별 판매 수 계산
SELECT category, COUNT(*) as sales_count
FROM sales
GROUP BY category;

설명:

COUNT(*)는 NULL 값을 포함하여 테이블의 모든 행을 계산합니다.
COUNT(DISTINCT column)은 지정된 열의 고유 값만 계산합니다.
GROUP BY 와 함께 사용하면 COUNT 는 각 그룹에 대한 합계를 개별적으로 계산합니다.
as 키워드는 결과 열에 별칭을 생성하여 출력을 더 읽기 쉽게 만듭니다.

SUM 함수

SUM 함수는 숫자 열의 총계를 계산합니다.

-- 판매된 총 수량 계산
SELECT SUM(quantity) as total_items_sold
FROM sales;

-- 총 수익 계산
SELECT
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales;

설명:

SUM 은 숫자 열에만 사용할 수 있습니다.
quantity * unit_price와 같은 계산은 집계 전에 수행됩니다.
ROUND(x, 2) 는 숫자를 소수점 2 자리로 반올림합니다.
수익 계산의 경우 정밀도를 유지하기 위해 곱셈이 합산 전에 발생합니다.

AVG 함수

AVG 함수는 평균값을 계산합니다.

-- 평균 단가 계산
SELECT
    ROUND(AVG(unit_price), 2) as avg_price
FROM sales;

-- 판매당 평균 수량 계산
SELECT
    ROUND(AVG(quantity), 1) as avg_quantity
FROM sales;

설명:

AVG 는 계산에서 NULL 값을 자동으로 무시합니다.
ROUND 의 두 번째 매개변수는 소수점 자릿수를 지정합니다.
결과는 정밀도를 위해 자동으로 DECIMAL 유형으로 변환됩니다.
AVG 는 데이터에서 일반적인 값을 찾는 데 일반적으로 사용됩니다.

MAX 및 MIN 함수

이러한 함수는 가장 높고 낮은 값을 찾습니다.

-- 제품의 가격 범위 찾기
SELECT
    MIN(unit_price) as lowest_price,
    MAX(unit_price) as highest_price
FROM sales;

-- 첫 번째 및 마지막 판매 날짜 찾기
SELECT
    MIN(sale_date) as first_sale,
    MAX(sale_date) as last_sale
FROM sales;

설명:

MIN/MAX는 숫자, 문자열 및 날짜와 함께 작동합니다.
날짜의 경우 MIN 은 가장 빠른 날짜를 찾고 MAX 는 가장 늦은 날짜를 찾습니다.
여러 집계 함수를 단일 SELECT 문에 결합할 수 있습니다.
다른 집계 함수와 마찬가지로 NULL 값을 자동으로 무시합니다.
이러한 함수는 데이터에서 값 범위와 경계를 찾는 데 유용합니다.

GROUP BY 를 사용한 데이터 그룹화

이 단계에서는 GROUP BY 절을 사용하여 데이터를 그룹화하는 방법을 배우겠습니다. 그룹화를 통해 특정 열 값을 기반으로 데이터의 하위 집합에 대한 집계 계산을 수행할 수 있습니다.

기본 그룹화

간단한 그룹화 작업부터 시작해 보겠습니다.

-- 카테고리별 판매 수 및 총 수량
SELECT
    category,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
ORDER BY total_quantity DESC;

이 쿼리는 각 카테고리에서 얼마나 많은 판매가 이루어졌고 총 몇 개의 품목이 판매되었는지 보여줍니다. 다음과 같은 출력을 볼 수 있습니다.

+-------------+-------------+----------------+
| category    | sales_count | total_quantity |
+-------------+-------------+----------------+
| Furniture   |           5 |             22 |
| Electronics |           5 |             21 |
| Appliances  |           2 |             10 |
+-------------+-------------+----------------+

설명:

GROUP BY 는 동일한 카테고리의 행을 단일 행으로 통합합니다.
ORDER BY total_quantity DESC 는 결과를 가장 높은 수량에서 가장 낮은 수량으로 정렬합니다.
각 집계 함수 (COUNT, SUM) 는 각 그룹 내에서 독립적으로 작동합니다.
SELECT 의 비집계 열은 GROUP BY 절에 나타나야 합니다.

여러 열 그룹화

더 자세한 통찰력을 얻기 위해 여러 열을 기준으로 그룹화할 수 있습니다.

-- 카테고리 및 지역별 판매 분석
SELECT
    category,
    region,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category, region
ORDER BY category, total_revenue DESC;

설명:

여러 열을 기준으로 그룹화하면 각 고유한 조합에 대한 하위 그룹이 생성됩니다.
GROUP BY 의 열 순서는 데이터가 그룹화되는 방식에 영향을 미칩니다.
결과는 먼저 카테고리별로 정렬된 다음 각 카테고리 내에서 총 수익별로 정렬됩니다.
이 접근 방식은 각 카테고리 내에서 실적이 가장 좋은 지역을 식별하는 데 도움이 됩니다.

날짜 기반 그룹화

MySQL 은 시간 기반 그룹화에 유용한 날짜의 일부를 추출하는 함수를 제공합니다.

-- 일별 판매 요약
SELECT
    sale_date,
    COUNT(*) as transactions,
    SUM(quantity) as items_sold,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

설명:

동일한 날짜에 발생한 모든 트랜잭션을 그룹화합니다.
ORDER BY sale_date 는 결과를 연대순으로 정렬합니다.
하루에 트랜잭션 수를 계산하고 일별 합계를 계산합니다.
일별 판매 패턴 및 추세를 식별하는 데 유용합니다.
DATE 함수를 사용하여 월 또는 연도별로 그룹화하도록 수정할 수 있습니다.

HAVING 을 사용한 그룹화된 데이터 필터링

이 단계에서는 그룹화 후 결과를 필터링할 수 있는 HAVING 절에 대해 배우겠습니다. WHERE 가 그룹화 전에 개별 행을 필터링하는 반면, HAVING 은 그룹 자체를 필터링합니다.

기본 HAVING 사용법

총 판매된 품목이 15 개 이상인 카테고리를 찾아보겠습니다.

SELECT
    category,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
HAVING total_quantity > 15;

이 쿼리는 총 수량이 15 개 이상인 카테고리만 표시합니다.

+-------------+----------------+
| category    | total_quantity |
+-------------+----------------+
| Electronics |             21 |
| Furniture   |             22 |
+-------------+----------------+

설명:

HAVING 은 GROUP BY 가 적용된 후 그룹을 필터링합니다.
HAVING 에서 집계 함수 결과를 참조할 수 있습니다.
필터 조건은 별칭 'total_quantity'를 사용합니다.
품목이 15 개 이하인 카테고리는 결과에서 제외됩니다.

WHERE 및 HAVING 결합

WHERE 와 HAVING 을 함께 사용할 수 있습니다. WHERE 는 그룹화 전에 행을 필터링하고, HAVING 은 그룹화 후에 필터링합니다.

-- 북부 지역의 대량 판매 카테고리 찾기
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity
FROM sales
WHERE region = 'North'
GROUP BY category
HAVING total_quantity > 5;

설명:

WHERE 는 그룹화 전에 개별 행 (region = 'North') 을 필터링합니다.
그런 다음 데이터는 카테고리별로 그룹화됩니다.
마지막으로 HAVING 은 그룹화된 결과 (total_quantity > 5) 를 필터링합니다.
순서가 중요합니다: FROM → WHERE → GROUP BY → HAVING → SELECT

복잡한 HAVING 조건

HAVING 에서 여러 조건을 사용할 수 있습니다.

-- 높은 판매량과 수익을 가진 카테고리 찾기
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
HAVING total_quantity > 10 AND total_revenue > 1000
ORDER BY total_revenue DESC;

설명:

AND/OR을 사용하여 여러 조건을 결합할 수 있습니다.
모든 집계 계산은 HAVING 조건에 사용할 수 있습니다.
HAVING 에서 산술 연산을 사용할 수 있습니다.
ORDER BY 는 HAVING 필터가 평가된 후에 적용됩니다.
이러한 유형의 쿼리는 실적이 가장 좋은 카테고리를 식별하는 데 유용합니다.

고급 집계 기술

이 마지막 단계에서는 지금까지 배운 모든 것을 결합하여 더 고급 집계 기술을 살펴보겠습니다. 이러한 도구의 강력함을 함께 보여주는 포괄적인 판매 보고서를 만들 것입니다.

판매 실적 대시보드

포괄적인 판매 분석을 만들어 보겠습니다.

SELECT
    category,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as total_transactions,
    SUM(quantity) as total_quantity,
    ROUND(AVG(quantity), 1) as avg_quantity_per_sale,
    ROUND(MIN(unit_price), 2) as min_price,
    ROUND(MAX(unit_price), 2) as max_price,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

설명:

완전한 카테고리 실적 개요를 생성합니다.
DISTINCT 를 사용하여 각 카테고리 내의 고유 제품 수를 계산합니다.
포괄적인 분석을 위해 여러 집계 함수를 결합합니다.
MIN 및 MAX 를 사용하여 가격 범위를 표시합니다.
평균 수량 및 총 수익을 계산합니다.
결과는 최고 실적을 강조하기 위해 수익별로 정렬됩니다.

지역별 실적 분석

지역별 판매 실적을 분석해 보겠습니다.

SELECT
    region,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue,
    ROUND(SUM(quantity * unit_price) / SUM(quantity), 2) as avg_price_per_unit
FROM sales
GROUP BY region
HAVING total_revenue > 1000
ORDER BY total_revenue DESC;

설명:

판매 데이터를 지역별로 그룹화합니다.
DISTINCT 수를 사용하여 제품 다양성을 표시합니다.
총 수익 및 수량 지표를 계산합니다.
avg_price_per_unit 은 수익을 수량으로 나누어 계산합니다.
HAVING 은 수익이 낮은 지역을 필터링합니다.
가장 강력하고 가장 약한 실적을 보이는 지역을 식별하는 데 도움이 됩니다.

일별 추세 분석

일별 판매 추세 보고서를 만들어 보겠습니다.

SELECT
    sale_date,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as transactions,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue,
    ROUND(AVG(quantity * unit_price), 2) as avg_transaction_value
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

설명:

일별 판매 실적 지표를 추적합니다.
하루에 제품 및 카테고리 다양성을 표시합니다.
일별 거래 및 수량을 계산합니다.
일별 수익 및 평균 거래 가치를 계산합니다.
연대순 정렬은 추세를 식별하는 데 도움이 됩니다.
패턴 및 계절적 효과를 파악하는 데 유용합니다.
재고 계획 및 인력 배치 결정에 도움이 될 수 있습니다.

요약

이 랩에서는 MySQL 에서 데이터 집계 및 그룹화의 필수적인 측면을 다루었습니다.

데이터를 요약하기 위한 기본 집계 함수 (COUNT, SUM, AVG, MAX, MIN) 사용
패턴 및 추세를 분석하기 위한 GROUP BY 를 사용한 데이터 그룹화
HAVING 절을 사용하여 그룹화된 결과 필터링
포괄적인 데이터 분석을 만들기 위해 여러 기술 결합

이러한 기술은 MySQL 에서 데이터 분석 및 보고의 기본입니다. 데이터를 효과적으로 집계하고 그룹화하는 방법을 이해하면 데이터베이스에서 의미 있는 통찰력을 추출하고 가치 있는 비즈니스 보고서를 만들 수 있습니다.