SQL의 COUNT 함수는 데이터베이스 내의 레코드 수를 효율적으로 집계하기 위한 기본 도구입니다. 이 글에서는 월별 및 연도별 데이터 집계 방법에 대해 구체적인 예와 함께 설명합니다. 데이터 집계는 비즈니스 인텔리전스 및 보고서 작성의 기초가 되는 중요한 기술입니다. SQL의 기초부터 응용까지 실제 데이터를 사용한 예제를 곁들여 알기 쉽게 설명합니다.
COUNT 함수의 기본적인 사용법
COUNT 함수는 지정한 열의 NULL이 아닌 값의 수를 반환하는 SQL의 집계 함수입니다. 기본적인 사용법으로는 테이블 내의 전체 레코드 수나 특정 조건을 만족하는 레코드 수를 가져올 수 있습니다. 아래에 기본 구문과 간단한 예를 보여드립니다.
기본 구문
SELECT COUNT(column_name)
FROM table_name
WHERE condition;사용 예
예를 들어, 고객 테이블(customers)에서 모든 고객 수를 가져오려면 다음과 같이 쿼리를 작성합니다.
SELECT COUNT(*)
FROM customers;이 쿼리는 customers 테이블 내의 전체 레코드 수를 반환합니다. 특정 조건에 따라 레코드 수를 가져오는 것도 가능합니다.
특정 조건에서의 사용 예
예를 들어, 활성 고객 수를 가져오고 싶다면 다음과 같이 합니다.
SELECT COUNT(*)
FROM customers
WHERE status = 'active';이 쿼리는 status가 ‘active’인 고객 수를 반환합니다. 이를 통해 조건에 따른 데이터 집계를 쉽게 수행할 수 있습니다.
월별 데이터 집계 방법
월별 데이터 집계는 특정 기간 내의 데이터 동향을 파악하는 데 매우 중요합니다. 여기에서는 SQL의 COUNT 함수와 GROUP BY 절을 사용하여 월별 데이터를 집계하는 방법을 설명합니다.
기본 구문
SELECT EXTRACT(YEAR FROM date_column) AS year, EXTRACT(MONTH FROM date_column) AS month, COUNT(*)
FROM table_name
GROUP BY EXTRACT(YEAR FROM date_column), EXTRACT(MONTH FROM date_column)
ORDER BY year, month;사용 예
예를 들어, 주문 테ーブル(orders)에서 월별 주문 수를 가져오려면 다음과 같이 쿼리를 작성합니다.
SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;이 쿼리는 orders 테이블의 order_date 열을 기준으로 각 연도 및 각 월의 주문 수를 집계하고, 결과를 연도와 월별로 정렬하여 표시합니다.
응용 예
또한, 월별 주문 수에 더해 특정 조건(예: 특정 고객)을 지정하여 집계하는 것도 가능합니다.
SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
WHERE customer_id = 123
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;이 쿼리는 특정 고객(customer_id가 123)의 주문 수를 월별로 집계하여 표시합니다. 조건을 추가함으로써 더 세부적인 데이터 분석이 가능합니다.
연도별 데이터 집계 방법
연도별 데이터 집계는 장기적인 데이터의 경향을 파악하는 데 유용합니다. SQL의 COUNT 함수와 GROUP BY 절을 사용하여 연도별 데이터를 집계하는 방법을 설명합니다.
기본 구문
SELECT EXTRACT(YEAR FROM date_column) AS year, COUNT(*)
FROM table_name
GROUP BY EXTRACT(YEAR FROM date_column)
ORDER BY year;사용 예
예를 들어, 주문 테이블(orders)에서 연도별 주문 수를 가져오려면 다음과 같이 쿼리를 작성합니다.
SELECT EXTRACT(YEAR FROM order_date) AS year, COUNT(*)
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY year;이 쿼리는 orders 테이블의 order_date 열을 기준으로 각 연도의 주문 수를 집계하고, 결과를 연도별로 정렬하여 표시합니다.
응용 예
특정 조건을 추가하여 연도별 데이터 집계를 더 상세하게 수행할 수 있습니다. 예를 들어, 특정 제품 카테고리에 대한 연도별 주문 수를 집계하려면 다음과 같이 합니다.
SELECT EXTRACT(YEAR FROM order_date) AS year, COUNT(*)
FROM orders
WHERE product_category = 'Electronics'
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY year;이 쿼리는 제품 카테고리가 ‘Electronics’인 주문 수를 연도별로 집계하여 표시합니다. 특정 조건을 추가함으로써 더 깊이 있는 통찰을 얻을 수 있습니다.
더욱 세부적인 집계
경우에 따라 연도와 월을 결합하여 집계하는 것이 유용할 수 있습니다. 이 경우의 쿼리는 다음과 같습니다.
SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;이 쿼리는 연도와 월 모두로 그룹화하여 각 기간의 주문 수를 집계합니다. 이를 통해 더 세밀한 데이터 분석이 가능합니다.
COUNT 함수와 GROUP BY의 조합
COUNT 함수와 GROUP BY 절의 조합은 SQL에서 데이터를 집계할 때 매우 강력한 도구입니다. 여기서는 GROUP BY 절을 사용하여 데이터를 특정 그룹별로 집계하는 방법을 설명합니다.
기본 구문
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
ORDER BY COUNT(*) DESC;사용 예
예를 들어, 고객 테이블(customers)에서 각 도시별 고객 수를 집계하려면 다음과 같이 쿼리를 작성합니다.
SELECT city, COUNT(*)
FROM customers
GROUP BY city
ORDER BY COUNT(*) DESC;이 쿼리는 customers 테이블의 city 열을 기준으로 각 도시의 고객 수를 집계하고, 고객 수가 많은 순서대로 정렬하여 표시합니다.
여러 열을 사용할 경우
여러 열을 사용하여 집계하는 것도 가능합니다. 예를 들어, 각 도시 및 각 연도별 고객 수를 집계하려면 다음과 같이 합니다.
SELECT city, EXTRACT(YEAR FROM signup_date) AS year, COUNT(*)
FROM customers
GROUP BY city, EXTRACT(YEAR FROM signup_date)
ORDER BY city, year;이 쿼리는 city 열과 signup_date 열을 기준으로 각 도시 및 각 연도의 고객 수를 집계하고, 도시 및 연도별로 정렬하여 표시합니다.
HAVING 절 사용
GROUP BY 절과 함께 HAVING 절을 사용하면 특정 조건을 만족하는 그룹만 필터링할 수 있습니다. 예를 들어, 고객 수가 50 이상인 도시만 표시하려면 다음과 같이 합니다.
SELECT city, COUNT(*)
FROM customers
GROUP BY city
HAVING COUNT(*) >= 50
ORDER BY COUNT(*) DESC;이 쿼리는 city 열로 그룹화하고, 고객 수가 50 이상인 도시만 표시합니다. HAVING 절을 사용하여 그룹별 집계 결과에 대해 조건을 설정할 수 있습니다.
COUNT 함수와 GROUP BY 절의 조합을 통해 데이터베이스 내의 정보를 효과적으로 집계하고 분석할 수 있습니다.
실제 데이터를 사용한 샘플 쿼리
실제 데이터를 사용한 샘플 쿼리를 통해 COUNT 함수와 GROUP BY 절의 실용적인 사용 방법을 이해하기 쉽도록 합니다. 여기서는 가상의 주문 데이터를 사용하여 구체적인 쿼리 예제를 제공합니다.
주문 테이블의 구조
먼저, 샘플 데이터베이스의 주문 테이블(orders)의 구조를 확인합니다. 이 테이블에는 다음과 같은 열이 있습니다:
- order_id (주문 ID)
- customer_id (고객 ID)
- order_date (주문 날짜)
- product_category (제품 카테고리)
- amount (주문 금액)
샘플 데이터
CREATE TABLE orders (
order_id INT,
customer_id INT,
order_date DATE,
product_category VARCHAR(50),
amount DECIMAL(10, 2)
);
INSERT INTO orders (order_id, customer_id, order_date, product_category, amount) VALUES
(1, 101, '2023-01-15', 'Electronics', 299.99),
(2, 102, '2023-02-20', 'Books', 19.99),
(3, 103, '2023-01-22', 'Electronics', 149.99),
(4, 104, '2023-03-15', 'Clothing', 79.99),
(5, 105, '2023-03-30', 'Books', 9.99);월별 주문 수를 집계하는 쿼리
아래의 쿼리는 주문 테이블에서 월별 주문 수를 집계합니다.
SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;쿼리 결과
| year | month | count |
|---|---|---|
| 2023 | 1 | 2 |
| 2023 | 2 | 1 |
| 2023 | 3 | 2 |
연도별 주문 금액을 집계하는 쿼리
다음으로, 연도별 총 주문 금액을 집계하는 쿼리를 보여드립니다.
SELECT EXTRACT(YEAR FROM order_date) AS year, SUM(amount) AS total_amount
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY year;쿼리 결과
| year | total_amount |
|---|---|
| 2023 | 559.95 |
제품 카테고리별 주문 수를 집계하는 쿼리
제품 카테고리별 주문 수를 집계하는 경우의 쿼리는 다음과 같습니다.
SELECT product_category, COUNT(*)
FROM orders
GROUP BY product_category
ORDER BY COUNT(*) DESC;쿼리 결과
| product_category | count |
|---|---|
| Electronics | 2 |
| Books | 2 |
| Clothing | 1 |
이러한 샘플 쿼리를 참고하면 실제 데이터베이스에 대한 집계 작업을 효과적으로 수행할 수 있습니다.
응용 예: 여러 조건을 사용한 집계
여러 조건을 결합하여 데이터를 집계하면 더 자세한 분석이 가능합니다. 여기서는 여러 조건을 지정하여 데이터를 집계하는 응용 예를 소개합니다.
제품 카테고리 및 월별 주문 수를 집계하는 쿼리
다음 쿼리는 제품 카테고리별로 월별 주문 수를 집계합니다.
SELECT product_category, EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
GROUP BY product_category, EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY product_category, year, month;쿼리 결과
| product_category | year | month | count |
|---|---|---|---|
| Books | 2023 | 2 | 1 |
| Books | 2023 | 3 | 1 |
| Clothing | 2023 | 3 | 1 |
| Electronics | 2023 | 1 | 2 |
고객별 연간 주문 금액을 집계하는 쿼리
고객별 연간 주문 금액을 집계하려면 다음과 같이 쿼리를 작성합니다.
SELECT customer_id, EXTRACT(YEAR FROM order_date) AS year, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id, EXTRACT(YEAR FROM order_date)
ORDER BY customer_id, year;쿼리 결과
| customer_id | year | total_amount |
|---|---|---|
| 101 | 2023 | 299.99 |
| 102 | 2023 | 19.99 |
| 103 | 2023 | 149.99 |
| 104 | 2023 | 79.99 |
| 105 | 2023 | 9.99 |
특정 조건으로 필터링한 집계
예를 들어, 주문 금액이 100달러 이상인 주문만을 대상으로 월별 집계를 수행하려면 다음과 같이 쿼리를 작성합니다.
SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
WHERE amount >= 100
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;쿼리 결과
| year | month | count |
|---|---|---|
| 2023 | 1 | 2 |
다양한 집계 방법의 조합
마지막으로, 다양한 집계 방법을 결합한 예를 소개합니다. 다음 쿼리는 각 도시별 고객 수와 해당 도시에서의 평균 주문 금액을 집계합니다.
SELECT city, COUNT(customer_id) AS customer_count, AVG(amount) AS average_order_amount
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY city
ORDER BY customer_count DESC;쿼리 결과
| city | customer_count | average_order_amount |
|---|---|---|
| New York | 5 | 159.95 |
| Los Angeles | 3 | 129.99 |
| Chicago | 2 | 99.99 |
이러한 응용 예를 통해 여러 조건을 결합한 데이터 집계 방법을 배울 수 있습니다. 이를 통해 보다 상세하고 다각적인 데이터 분석이 가능합니다.
연습 문제
이해를 심화하기 위해 다음 연습 문제를 해결해 보세요. 이 문제들은 SQL의 COUNT 함수와 GROUP BY 절을 사용하여 데이터를 집계하는 실무 능력을 향상시키기 위한 것입니다.
문제 1: 연도별 고객 수를 집계하기
고객 테이블(customers)에는 고객 ID(customer_id)와 가입일(signup_date)이 포함되어 있습니다. 각 연도별 신규 고객 수를 집계하는 쿼리를 작성하세요.
-- 답안 예시
SELECT EXTRACT(YEAR FROM signup_date) AS year, COUNT(*)
FROM customers
GROUP BY EXTRACT(YEAR FROM signup_date)
ORDER BY year;문제 2: 각 제품 카테고리별 총 주문 금액을 집계하기
주문 테이블(orders)에는 제품 카테고리(product_category)와 주문 금액(amount)이 포함되어 있습니다. 각 제품 카테고리별 총 주문 금액을 집계하는 쿼리를 작성하세요.
-- 답안 예시
SELECT product_category, SUM(amount) AS total_amount
FROM orders
GROUP BY product_category
ORDER BY total_amount DESC;문제 3: 특정 연도와 월별 주문 수를 집계하기
주문 테이블(orders)에서 2023년의 각 월별 주문 수를 집계하는 쿼리를 작성하세요.
-- 답안 예시
SELECT EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY month;문제 4: 고객별 연간 총 주문 금액을 집계하기
고객 테이블(customers)과 주문 테이블(orders)을 조인하여 고객별 연간 총 주문 금액을 집계하는 쿼리를 작성하세요.
-- 답안 예시
SELECT customers.customer_id, EXTRACT(YEAR FROM orders.order_date) AS year, SUM(orders.amount) AS total_amount
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id, EXTRACT(YEAR FROM orders.order_date)
ORDER BY customers.customer_id, year;문제 5: 주문 수가 50 이상인 제품 카테고리만 표시하기
주문 테이블(orders)에서 주문 수가 50 이상인 제품 카테고리만 표시하는 쿼리를 작성하세요.
-- 답안 예시
SELECT product_category, COUNT(*) AS order_count
FROM orders
GROUP BY product_category
HAVING COUNT(*) >= 50
ORDER BY order_count DESC;이러한 연습 문제를 통해 SQL의 COUNT 함수와 GROUP BY 절을 사용한 데이터 집계 기술을 실전에서 학습할 수 있습니다. 각 문제에 도전하여 자신의 쿼리가 정확한지 확인해 보세요.
정리
이 글에서는 SQL의 COUNT 함수를 사용한 월별, 연도별 데이터 집계 방법에 대해 자세히 설명했습니다. COUNT 함수와 GROUP BY 절을 결합하면 데이터베이스 내의 정보를 효율적으로 집계하고 분석할 수 있습니다. 기본적인 사용법에서부터 응용 예제, 실제 데이터를 사용한 샘플 쿼리 및 연습 문제를 통해 실무적인 기술을 습득할 수 있었습니다. 이러한 지식을 활용하여 비즈니스 인텔리전스 및 보고서 작성에 도움이 되길 바랍니다.