이 랩에서는 SQLite 윈도우 분석 (window analytics) 을 탐구하며, 행 순위 지정 및 누적 합계 계산에 중점을 둡니다. 윈도우 함수를 사용하여 현재 행과 관련된 행 집합에서 계산을 수행하는 방법을 배우게 됩니다.
구체적으로, 판매 금액을 기준으로 각 행에 고유한 순위를 할당하기 위해 ROW_NUMBER() 함수를 사용합니다. 또한 누적 합계를 계산하고, 보다 진보된 분석을 위해 데이터를 분할 (partition) 하는 방법도 배우게 됩니다. 이 랩은 SQLite 에서 윈도우 함수에 대한 실질적인 소개를 제공합니다.
첫 번째 단계에서는 sales.db라는 SQLite 데이터베이스와 판매 데이터를 저장할 sales라는 테이블을 생성합니다. 이 테이블에는 제품 ID, 제품 이름 및 판매 금액 열이 포함됩니다.
LabEx VM 에서 터미널을 엽니다. 기본 경로는 /home/labex/project입니다.
시작하려면 다음 명령을 실행하여 sales.db 데이터베이스를 생성하고 SQLite 명령줄 도구를 엽니다.
sqlite3 sales.db이 명령은 데이터베이스 파일을 생성하고 SQL 명령을 실행할 수 있는 SQLite 셸을 엽니다. 다음과 같은 프롬프트가 표시됩니다.
SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>다음으로, product_id, product_name, 및 sales_amount 열을 사용하여 sales 테이블을 생성합니다. sqlite> 프롬프트에서 다음 SQL 명령을 입력하고 Enter 키를 누릅니다.
CREATE TABLE sales (
product_id INTEGER,
product_name TEXT,
sales_amount INTEGER
);이 명령은 다음과 같은 sales 테이블을 설정합니다.
product_id는 각 제품의 고유 식별자를 나타내는 정수입니다.product_name은 제품 이름을 저장하는 텍스트 필드입니다.sales_amount는 제품의 판매 금액을 나타내는 정수입니다.명령이 성공적으로 실행되면 아무런 출력도 표시되지 않습니다.
sales 테이블을 생성했으므로, 이제 몇 가지 샘플 데이터를 추가해 보겠습니다. 서로 다른 제품과 해당 판매 금액을 나타내는 6 개의 레코드를 삽입합니다.
sqlite> 프롬프트에서 다음 명령을 하나씩 실행하여 다음 레코드를 sales 테이블에 삽입합니다.
INSERT INTO sales (product_id, product_name, sales_amount) VALUES
(1, 'Laptop', 1200),
(2, 'Keyboard', 75),
(3, 'Mouse', 25),
(4, 'Monitor', 300),
(5, 'Headphones', 100),
(6, 'Webcam', 50);이 명령은 sales 테이블에 6 개의 행을 추가합니다. 각 행은 제품 ID, 이름 및 판매 금액을 나타냅니다.
INSERT INTO sales (product_id, product_name, sales_amount)는 sales 테이블의 product_id, product_name, 및 sales_amount 열에 데이터를 삽입함을 지정합니다.VALUES (1, 'Laptop', 1200)은 각 레코드에 삽입할 값을 제공합니다.데이터가 올바르게 추가되었는지 확인하려면 이 명령을 실행하여 테이블의 모든 레코드를 확인합니다.
SELECT * FROM sales;예상 출력:
1|Laptop|1200
2|Keyboard|75
3|Mouse|25
4|Monitor|300
5|Headphones|100
6|Webcam|50이 출력은 각 레코드의 product_id, product_name, 및 sales_amount를 보여줍니다. SELECT * 명령은 지정된 테이블에서 모든 열을 검색합니다.
이 단계에서는 ROW_NUMBER() 윈도우 함수를 사용하여 sales_amount를 기준으로 각 행에 고유한 순위를 할당하는 방법을 배웁니다. 이는 최고 판매 제품을 식별하는 데 유용합니다.
ROW_NUMBER() 함수는 결과 집합의 파티션 내 각 행에 고유한 정수를 할당합니다. 순위는 ORDER BY 절에 지정된 순서에 따라 결정됩니다.
sqlite> 프롬프트에서 다음 쿼리를 실행합니다.
SELECT
product_name,
sales_amount,
ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM
sales;이 쿼리는 각 제품의 sales_amount를 기준으로 내림차순으로 순위를 계산합니다.
ROW_NUMBER() OVER (ORDER BY sales_amount DESC)는 sales_amount를 기준으로 각 행에 순위를 할당하며, 가장 높은 판매 금액이 1 위를 받습니다.ORDER BY sales_amount DESC는 순위가 sales_amount를 기준으로 내림차순으로 지정되어야 함을 지정합니다.예상 출력:
Laptop|1200|1
Monitor|300|2
Headphones|100|3
Keyboard|75|4
Webcam|50|5
Mouse|25|6보시다시피, sales_rank 열에는 이제 각 제품의 sales_amount를 기준으로 한 순위가 포함되어 있으며, 가장 높은 판매 금액 (Laptop) 이 1 위를 받았습니다.
이 단계에서는 윈도우 함수를 사용하여 누적 합계 (cumulative sum) 를 계산하는 방법을 배웁니다. 누적 합계는 일정 기간 또는 일련의 행에 걸쳐 값의 합계를 추적하는 데 유용합니다.
누적 합계를 계산하려면 OVER() 절과 ORDER BY 절이 있는 SUM() 함수를 사용하여 합계를 계산할 순서를 지정합니다.
먼저, sale_date 열을 sales 테이블에 추가하고 몇 가지 샘플 날짜로 채웁니다. sqlite> 프롬프트에서 다음 명령을 실행합니다.
ALTER TABLE sales ADD COLUMN sale_date DATE;
UPDATE sales SET sale_date = '2023-01-01' WHERE product_name = 'Laptop';
UPDATE sales SET sale_date = '2023-01-05' WHERE product_name = 'Keyboard';
UPDATE sales SET sale_date = '2023-01-10' WHERE product_name = 'Mouse';
UPDATE sales SET sale_date = '2023-01-15' WHERE product_name = 'Monitor';
UPDATE sales SET sale_date = '2023-01-20' WHERE product_name = 'Headphones';
UPDATE sales SET sale_date = '2023-01-25' WHERE product_name = 'Webcam';이 명령은 sales 테이블에 sale_date 열을 추가하고 각 제품에 대한 샘플 날짜로 테이블을 업데이트합니다.
이제 sale_date를 기준으로 정렬된 sales_amount의 누적 합계를 계산해 보겠습니다. 다음 쿼리를 실행합니다.
SELECT
sale_date,
product_name,
sales_amount,
SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM
sales;이 쿼리는 sale_date를 기준으로 정렬된 sales_amount의 누적 합계를 계산합니다.
SUM(sales_amount) OVER (ORDER BY sale_date)는 각 sale_date까지의 sales_amount의 누적 합계를 계산합니다.ORDER BY sale_date는 누적 합계가 sale_date를 기준으로 오름차순으로 계산되어야 함을 지정합니다.예상 출력:
2023-01-01|Laptop|1200|1200
2023-01-05|Keyboard|75|1275
2023-01-10|Mouse|25|1300
2023-01-15|Monitor|300|1600
2023-01-20|Headphones|100|1700
2023-01-25|Webcam|50|1750running_total 열은 각 sale_date까지의 sales_amount의 누적 합계를 보여줍니다. 예를 들어, '2023-01-15'의 누적 합계는 1600 이며, 이는 '2023-01-01', '2023-01-05', '2023-01-10', 및 '2023-01-15'의 판매액 합계입니다.
이 단계에서는 윈도우 함수 내에서 PARTITION BY 절을 사용하여 데이터를 분할하는 방법을 배웁니다. 분할을 통해 데이터를 논리적 그룹으로 나누고 각 그룹 내에서 독립적으로 계산을 수행할 수 있습니다.
sales 테이블에 product_category 열을 추가해 보겠습니다. sqlite> 프롬프트에서 다음 명령을 실행합니다.
ALTER TABLE sales ADD COLUMN product_category TEXT;
UPDATE sales SET product_category = 'Electronics' WHERE product_name IN ('Laptop', 'Monitor', 'Headphones', 'Webcam');
UPDATE sales SET product_category = 'Accessories' WHERE product_name IN ('Keyboard', 'Mouse');이 명령은 sales 테이블에 product_category 열을 추가하고 각 제품에 대한 범주로 테이블을 업데이트합니다.
이제 PARTITION BY를 사용하여 각 product_category 내에서 sales_amount의 누적 합계를 계산해 보겠습니다. 다음 쿼리를 실행합니다.
SELECT
product_category,
sale_date,
product_name,
sales_amount,
SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) AS running_total_by_category
FROM
sales;이 쿼리는 sale_date를 기준으로 정렬된 각 product_category 내에서 sales_amount의 누적 합계를 계산합니다.
PARTITION BY product_category는 product_category를 기준으로 데이터를 파티션으로 나눕니다.SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date)는 sale_date를 기준으로 정렬된 각 product_category 내에서 sales_amount의 누적 합계를 계산합니다.예상 출력:
Accessories|2023-01-05|Keyboard|75|75
Accessories|2023-01-10|Mouse|25|100
Electronics|2023-01-01|Laptop|1200|1200
Electronics|2023-01-15|Monitor|300|1500
Electronics|2023-01-20|Headphones|100|1600
Electronics|2023-01-25|Webcam|50|1650running_total_by_category 열은 sale_date를 기준으로 정렬된 각 product_category 내의 sales_amount의 누적 합계를 보여줍니다. 누적 합계가 각 범주에 대해 다시 시작되는 것을 확인하십시오.
이 랩에서는 SQLite 에서 윈도우 함수를 사용하여 고급 데이터 분석을 수행하는 방법을 배웠습니다. 먼저 sales 테이블을 생성하고 샘플 데이터를 삽입하는 것으로 시작했습니다. 그런 다음 ROW_NUMBER() 함수를 사용하여 판매액을 기준으로 제품의 순위를 매겼습니다. 또한 OVER() 절과 함께 SUM() 함수를 사용하여 누적 합계를 계산하는 방법과 PARTITION BY 절을 사용하여 데이터를 분할하여 논리적 그룹 내에서 계산을 수행하는 방법을 배웠습니다. 이러한 기술은 SQLite 에서 더 복잡한 데이터 분석 작업을 위한 기반을 제공합니다.
