이 랩에서는 집계 함수와 그룹화 절을 사용하여 SQLite 에서 데이터를 요약하고 분석하는 방법을 배우게 됩니다. 계산을 위해 COUNT와 SUM을 살펴보고, 단일 열로 데이터를 그룹화하며, HAVING을 사용하여 그룹을 필터링하고, 그룹화된 출력을 정렬합니다. 이 실습 경험을 통해 필수적인 SQLite 데이터 조작 기술을 습득할 수 있습니다.
이 단계에서는 sales.db라는 데이터베이스와 그 안에 orders 테이블을 생성합니다. 그런 다음 샘플 데이터를 테이블에 삽입합니다. 이 테이블은 데이터 그룹화 기술을 연습하기 위해 랩 전체에서 사용됩니다.
먼저, LabEx VM 에서 터미널을 엽니다. 기본 경로는 /home/labex/project입니다.
시작하려면 다음 명령을 사용하여 sales.db 데이터베이스를 생성하고 연결합니다.
sqlite3 sales.db이 명령은 SQLite 셸을 열고 sqlite>와 같은 프롬프트를 표시합니다.
다음으로, order_id, customer_id, product_name, quantity, 및 price 열을 사용하여 orders 테이블을 생성합니다. 다음 SQL 명령을 실행합니다.
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
product_name TEXT,
quantity INTEGER,
price REAL
);이 명령은 지정된 열과 데이터 유형으로 orders 테이블을 생성합니다. order_id 열은 기본 키로 설정됩니다.
이제 샘플 데이터를 orders 테이블에 삽입합니다. 다음 INSERT 문을 하나씩 실행합니다.
INSERT INTO orders (customer_id, product_name, quantity, price) VALUES
(1, 'Laptop', 1, 1200.00),
(1, 'Mouse', 2, 25.00),
(2, 'Keyboard', 1, 75.00),
(2, 'Monitor', 1, 300.00),
(3, 'Laptop', 1, 1200.00),
(3, 'Headphones', 1, 100.00),
(1, 'Keyboard', 1, 75.00);이 명령은 서로 다른 고객 주문을 나타내는 7 개의 데이터 행을 orders 테이블에 삽입합니다.
데이터가 올바르게 삽입되었는지 확인하려면 간단한 SELECT 쿼리를 실행할 수 있습니다.
SELECT * FROM orders;이 명령은 orders 테이블의 모든 행과 열을 표시합니다.
이 단계에서는 COUNT 및 SUM 집계 함수를 사용하여 orders 테이블의 데이터에 대한 계산을 수행합니다. 집계 함수를 사용하면 여러 행의 데이터를 단일 결과로 요약할 수 있습니다.
이전 단계에서 sales.db 데이터베이스에 연결되어 있어야 합니다. 그렇지 않은 경우 다음을 사용하여 다시 연결합니다.
sqlite3 sales.db먼저, COUNT 함수를 사용하여 테이블의 총 주문 수를 확인해 보겠습니다. 다음 SQL 명령을 실행합니다.
SELECT COUNT(*) FROM orders;이 쿼리는 orders 테이블의 총 행 수를 반환하며, 이는 총 주문 수를 나타냅니다.
COUNT(*) 함수는 열에 NULL 값이 포함되어 있는지 여부에 관계없이 테이블의 모든 행을 계산합니다.
다음으로, SUM 함수를 사용하여 주문된 모든 제품의 총 수량을 계산해 보겠습니다. 다음 SQL 명령을 실행합니다.
SELECT SUM(quantity) FROM orders;이 쿼리는 orders 테이블의 모든 행에 대한 quantity 열의 합계를 반환합니다.
SUM 함수는 지정된 열의 값을 더합니다.
마지막으로, 모든 주문에서 발생한 총 수익을 계산해 보겠습니다. 다음 SQL 명령을 실행합니다.
SELECT SUM(quantity * price) FROM orders;이 쿼리는 각 행에 대해 quantity 및 price 열을 곱한 다음 결과를 합산하여 총 수익을 제공합니다.
이 단계에서는 하나 이상의 열의 값을 기반으로 행을 그룹화하기 위해 GROUP BY 절을 사용하는 방법을 배웁니다. 이는 각 그룹에 대한 요약 통계를 계산하기 위해 집계 함수와 함께 자주 사용됩니다.
이전 단계에서 sales.db 데이터베이스에 연결되어 있어야 합니다. 그렇지 않은 경우 다음을 사용하여 다시 연결합니다.
sqlite3 sales.dbcustomer_id로 orders 테이블을 그룹화하고 각 고객의 주문 수를 계산해 보겠습니다. 다음 SQL 명령을 실행합니다.
SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id;이 쿼리는 각 고유 고객에 대한 customer_id와 주문 수 (order_count) 를 반환합니다. GROUP BY customer_id 절은 SQLite 에 customer_id 열의 값을 기반으로 행을 그룹화하도록 지시합니다. 그런 다음 COUNT(*) 함수는 각 그룹의 행 수를 계산합니다.
다음으로, product_name으로 orders 테이블을 그룹화하고 각 제품에 대해 주문된 총 수량을 계산해 보겠습니다. 다음 SQL 명령을 실행합니다.
SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name;이 쿼리는 각 고유 제품에 대한 product_name과 주문된 총 수량 (total_quantity) 을 반환합니다. GROUP BY product_name 절은 SQLite 에 product_name 열의 값을 기반으로 행을 그룹화하도록 지시합니다. 그런 다음 SUM(quantity) 함수는 각 그룹에 대한 quantity 열의 합계를 계산합니다.
마지막으로, customer_id로 orders 테이블을 그룹화하고 각 고객이 생성한 총 수익을 계산해 보겠습니다. 다음 SQL 명령을 실행합니다.
SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id;이 쿼리는 각 고객이 생성한 customer_id와 총 수익 (total_revenue) 을 반환합니다.
이 단계에서는 GROUP BY 절에 의해 생성된 그룹을 필터링하기 위해 HAVING 절을 사용하는 방법을 배웁니다. HAVING 절은 WHERE 절과 유사하지만 개별 행이 아닌 그룹에 대해 작동합니다.
이전 단계에서 sales.db 데이터베이스에 연결되어 있어야 합니다. 그렇지 않은 경우 다음을 사용하여 다시 연결합니다.
sqlite3 sales.dbcustomer_id로 orders 테이블을 그룹화하고 각 고객의 주문 수를 계산해 보겠습니다. 그런 다음 HAVING 절을 사용하여 주문을 1 개 이상 한 고객만 결과에 포함하도록 필터링합니다. 다음 SQL 명령을 실행합니다.
SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;이 쿼리는 주문을 1 개 이상 한 각 고객에 대한 customer_id와 주문 수 (order_count) 를 반환합니다. GROUP BY customer_id 절은 customer_id로 행을 그룹화하고, HAVING COUNT(*) > 1 절은 주문 수가 1 보다 큰 그룹만 포함하도록 그룹을 필터링합니다.
다음으로, product_name으로 orders 테이블을 그룹화하고 각 제품에 대해 주문된 총 수량을 계산해 보겠습니다. 그런 다음 HAVING 절을 사용하여 주문된 총 수량이 1 보다 큰 제품만 결과에 포함하도록 필터링합니다. 다음 SQL 명령을 실행합니다.
SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name HAVING SUM(quantity) > 1;이 쿼리는 주문된 총 수량이 1 보다 큰 각 제품에 대한 product_name과 주문된 총 수량 (total_quantity) 을 반환합니다.
마지막으로, customer_id로 orders 테이블을 그룹화하고 각 고객이 생성한 총 수익을 계산해 보겠습니다. 그런 다음 HAVING 절을 사용하여 수익이 1000 달러 이상인 고객만 결과에 포함하도록 필터링합니다. 다음 SQL 명령을 실행합니다.
SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id HAVING SUM(quantity * price) > 1000;이 쿼리는 수익이 1000 달러 이상인 각 고객이 생성한 customer_id와 총 수익 (total_revenue) 을 반환합니다.
이 단계에서는 GROUP BY 절을 포함하는 쿼리의 출력을 정렬하기 위해 ORDER BY 절을 사용하는 방법을 배웁니다. 그룹화된 출력을 정렬하면 데이터를 더 쉽게 분석하고 이해할 수 있습니다.
이전 단계에서 sales.db 데이터베이스에 연결되어 있어야 합니다. 그렇지 않은 경우 다음을 사용하여 다시 연결합니다.
sqlite3 sales.dbcustomer_id로 orders 테이블을 그룹화하고 각 고객의 주문 수를 계산해 보겠습니다. 그런 다음 ORDER BY 절을 사용하여 주문 수를 기준으로 내림차순으로 결과를 정렬합니다. 다음 SQL 명령을 실행합니다.
SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ORDER BY order_count DESC;이 쿼리는 각 고객에 대한 customer_id와 주문 수 (order_count) 를 반환하며, order_count를 기준으로 내림차순으로 정렬됩니다. GROUP BY customer_id 절은 customer_id로 행을 그룹화하고, ORDER BY order_count DESC 절은 order_count 별칭을 기준으로 내림차순으로 결과를 정렬합니다.
다음으로, product_name으로 orders 테이블을 그룹화하고 각 제품에 대해 주문된 총 수량을 계산해 보겠습니다. 그런 다음 ORDER BY 절을 사용하여 제품 이름을 기준으로 오름차순으로 결과를 정렬합니다. 다음 SQL 명령을 실행합니다.
SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name ORDER BY product_name ASC;이 쿼리는 각 제품에 대한 product_name과 주문된 총 수량 (total_quantity) 을 반환하며, product_name을 기준으로 오름차순으로 정렬됩니다.
마지막으로, customer_id로 orders 테이블을 그룹화하고 각 고객이 생성한 총 수익을 계산해 보겠습니다. 그런 다음 ORDER BY 절을 사용하여 총 수익을 기준으로 내림차순으로 결과를 정렬합니다. 다음 SQL 명령을 실행합니다.
SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id ORDER BY total_revenue DESC;이 쿼리는 각 고객에 대한 customer_id와 총 수익 (total_revenue) 을 반환하며, total_revenue를 기준으로 내림차순으로 정렬됩니다.
SQLite 셸을 종료하려면 다음을 실행합니다.
.exit이 랩에서는 COUNT 및 SUM과 같은 집계 함수를 사용하여 SQLite 에서 데이터를 요약하는 방법을 배웠습니다. orders 테이블이 있는 sales.db 데이터베이스를 생성하고 샘플 데이터를 삽입했습니다. 그런 다음 COUNT(*)를 사용하여 총 주문 수를 결정하고 SUM(quantity * price)를 사용하여 총 수익을 계산했습니다. 또한 GROUP BY 절을 사용하여 데이터를 그룹화하고, HAVING 절을 사용하여 그룹을 필터링하고, ORDER BY 절을 사용하여 출력을 정렬하는 방법을 배웠습니다. 이러한 기술은 SQLite 에서 데이터 분석을 위한 견고한 기반을 제공합니다.
