이 랩에서는 데이터베이스에서 데이터를 분석하고 요약하는 데 필수적인 기술인 MySQL 의 데이터 집계 및 그룹화를 탐구합니다. 집계 함수를 사용하여 행 전체에서 계산을 수행하고, 열 값을 기반으로 데이터를 그룹화하며, 그룹화된 결과를 필터링하는 방법을 배우게 됩니다. 이러한 기술은 보고서를 생성하고, 추세를 분석하며, 데이터에서 의미 있는 통찰력을 추출하는 데 기본적으로 사용됩니다. 실습을 통해 이러한 중요한 데이터베이스 작업에 대한 실질적인 경험을 얻고 MySQL 에서 데이터를 효과적으로 분석하는 방법을 이해하게 될 것입니다.
이 단계에서는 MySQL 의 기본적인 집계 함수를 살펴보겠습니다. 이러한 함수는 여러 행에 걸쳐 계산을 수행하고 단일 값을 반환하므로 데이터 분석에 필수적입니다.
먼저 MySQL 에 연결하고 데이터베이스를 선택해 보겠습니다.
sudo mysql -u root연결되면:
USE sales_db;결과 집합의 행 수를 세는 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)은 지정된 열의 고유 값만 계산합니다.as 키워드는 결과 열에 별칭을 생성하여 출력을 더 읽기 쉽게 만듭니다.SUM 함수는 숫자 열의 총계를 계산합니다.
-- 판매된 총 수량 계산
SELECT SUM(quantity) as total_items_sold
FROM sales;
-- 총 수익 계산
SELECT
ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales;설명:
quantity * unit_price와 같은 계산은 집계 전에 수행됩니다.AVG 함수는 평균값을 계산합니다.
-- 평균 단가 계산
SELECT
ROUND(AVG(unit_price), 2) as avg_price
FROM sales;
-- 판매당 평균 수량 계산
SELECT
ROUND(AVG(quantity), 1) as avg_quantity
FROM sales;설명:
이러한 함수는 가장 높고 낮은 값을 찾습니다.
-- 제품의 가격 범위 찾기
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;설명:
이 단계에서는 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 |
+-------------+-------------+----------------+설명:
더 자세한 통찰력을 얻기 위해 여러 열을 기준으로 그룹화할 수 있습니다.
-- 카테고리 및 지역별 판매 분석
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;설명:
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;설명:
이 단계에서는 그룹화 후 결과를 필터링할 수 있는 HAVING 절에 대해 배우겠습니다. WHERE 가 그룹화 전에 개별 행을 필터링하는 반면, 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 |
+-------------+----------------+설명:
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;설명:
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;설명:
이 마지막 단계에서는 지금까지 배운 모든 것을 결합하여 더 고급 집계 기술을 살펴보겠습니다. 이러한 도구의 강력함을 함께 보여주는 포괄적인 판매 보고서를 만들 것입니다.
포괄적인 판매 분석을 만들어 보겠습니다.
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;설명:
지역별 판매 실적을 분석해 보겠습니다.
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;설명:
일별 판매 추세 보고서를 만들어 보겠습니다.
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 에서 데이터 집계 및 그룹화의 필수적인 측면을 다루었습니다.
이러한 기술은 MySQL 에서 데이터 분석 및 보고의 기본입니다. 데이터를 효과적으로 집계하고 그룹화하는 방법을 이해하면 데이터베이스에서 의미 있는 통찰력을 추출하고 가치 있는 비즈니스 보고서를 만들 수 있습니다.
