데이터 사이언스 100번의 노크(구조화 데이터 처리편)- SQL Part 5 (Q81 to Q100)

데이터 사이언스
2023.03.31 
설명:

이 코드는 SQL 언어로 작성되었으며, 다음과 같은 단계를 수행합니다.

product_2라는 테이블이 이미 데이터베이스에 존재하는 경우, 해당 테이블을 삭제한다. 이는 아무것도 없는 상태에서 시작하도록 하기 위해 수행됩니다.

CREATE TABLE 문을 사용하여 product_2라는 새로운 테이블이 생성된다.

새 테이블의 데이터는 SELECT 문을 사용하여 product 테이블에서 가져온다.

SELECT 문에서는 product_cd, category_major_cd, category_medium_cd, category_small_cd라는 열이 product 테이블에서 선택된다.

SELECT 문에서 COALESCE 함수를 사용하여 두 개의 새로운 열이 생성되며, COALESCE는 수식 목록에서 첫 번째 NULL이 아닌 값을 반환한다. 이 경우 unit_price가 NULL인 경우 전체 상품의 평균 단가(unit_avg)가 대신 사용된다. 마찬가지로 unit_cost가 NULL인 경우 전체 상품의 평균 단가(cost_avg)가 대신 사용된다.

SELECT 문의 FROM 구문은 상품 테이블의 모든 상품의 평균 단가와 단가를 계산하는 하위 쿼리와 CROSS JOIN을 수행하며, ROUND 함수는 평균값을 가장 가까운 정수로 반올림하는 데 사용된다.

SELECT 문 결과는 CREATE TABLE 문에 의해 암묵적으로 실행되는 INSERT INTO 문으로 product_2 테이블에 입력하는 데 사용된다.

이 코드에서는 product_2라는 새로운 테이블이 생성되어 product 테이블과 동일한 열을 포함하지만, unit_price와 unit_cost 열의 null 값은 모든 상품에 대해 계산된 평균 단가와 단가로 각각 대체됩니다.
 
설명:

이 코드는 SQL 언어로 작성되었으며, 다음 단계를 수행한다.

SELECT 문을 실행하여 product_2 테이블을 조회한다.

SUM 함수를 CASE 식과 함께 사용하여 unit_price 열과 unit_cost 열의 NULL 값의 개수를 별도로 계산한다.

각 CASE 식에서 WHEN unit_price IS NULL 또는 WHEN unit_cost IS NULL은 해당 컬럼의 값이 NULL인지 여부를 확인한다.

컬럼에 NULL 값이 있으면 CASE 표현식은 1로 평가되고, 그렇지 않으면 0으로 평가된다.

그런 다음 SUM 함수가 각 행의 1과 0을 합하여 unit_price와 unit_cost 열의 NULL 값의 총 개수를 각각 계산한다.

AS 키워드는 결과 집합의 열을 의미 있는 이름, 즉 unit_price와 unit_cost로 만들기 위해 사용된다.

마지막으로 LIMIT 구문은 출력을 처음 10줄로 제한하는 데 사용된다.

요약하면, 이 코드는 product_2 테이블의 unit_price 및 unit_cost 열의 NULL 값의 수를 표시하는 결과 집합을 반환한다. 이 출력은 이러한 열에서 데이터가 누락된 행이 몇 개 있는지 보여 주며, 처리해야 할 데이터 품질 문제를 식별하는 데 유용합니다.
 
설명:

이 코드는 SQL 언어로 작성되었으며, 다음 단계를 수행한다.

product_3이라는 테이블이 이미 데이터베이스에 존재하는 경우 삭제한다. 이는 아무것도 없는 상태에서 시작하도록 하기 위한 것이다.

CREATE TABLE 문을 사용하여 product_3이라는 새 테이블을 생성한다.

새 테이블의 데이터는 SELECT 문을 사용하여 product 테이블에서 가져온다.

SELECT 문에서는 product_cd, category_major_cd, category_medium_cd, category_small_cd라는 열이 product 테이블에서 선택된다.

SELECT 문에서 COALESCE 함수를 사용하여 두 개의 새로운 열이 생성되며, COALESCE는 수식 목록에서 첫 번째 NULL이 아닌 값을 반환한다. 이 경우 unit_price가 NULL인 경우 전체 상품의 단가 중간값(unit_med)이 대신 사용된다. 마찬가지로 unit_cost가 NULL인 경우, 전체 상품의 단가 중간값(cost_med)이 대신 사용된다.

SELECT 문의 FROM 구문은 상품 테이블의 전체 상품 단가의 중앙값과 단가를 계산하는 하위 쿼리와의 CROSS JOIN을 수행한다. 중앙값 산출에는 PERCENTILE_CONT 함수가 사용되며, WITHIN GROUP 구문은 행의 순서를 지정하여 함수가 중앙값 산출에 사용할 값을 인식할 수 있도록 한다.

ROUND 함수는 중앙값을 가장 가까운 정수로 반올림하는 데 사용된다.

SELECT 구문의 결과는 CREATE TABLE 구문에 의해 암묵적으로 실행되는 INSERT INTO 구문을 통해 product_3 테이블에 입력하는 데 사용된다.

이 코드에서는 product_3이라는 새로운 테이블이 생성되어 product 테이블과 동일한 열을 포함하지만, unit_price와 unit_cost 열의 null 값은 모든 제품에서 계산된 각각의 단가와 단가의 중간값으로 대체된다. 중간값은 행의 순서를 지정하는 WITHIN GROUP 구문을 가진 PERCENTILE_CONT 함수를 사용하여 계산된다.
 
설명:

이 코드는 SQL 언어로 작성되었으며, 다음과 같은 단계를 수행한다.

SELECT 문을 실행하여 product_3 테이블을 조회한다.

SUM 함수를 CASE 식과 함께 사용하여 unit_price 열과 unit_cost 열의 NULL 값의 개수를 별도로 계산한다.

각 CASE 식에서 WHEN unit_price IS NULL 또는 WHEN unit_cost IS NULL은 각 열의 값이 NULL인지 여부를 확인한다.

컬럼에 NULL 값이 있으면 CASE 표현식은 1로 평가되고, 그렇지 않으면 0으로 평가된다.

그런 다음 SUM 함수가 각 행의 1과 0을 합산하여 unit_price와 unit_cost 열의 NULL 값의 총 수를 각각 계산한다.

AS 키워드는 결과 집합의 열을 의미 있는 이름, 즉 unit_price와 unit_cost로 만들기 위해 사용된다.

마지막으로 LIMIT 구문은 출력을 처음 10줄로 제한하는 데 사용된다.

요약하면, 이 코드는 product_3 테이블의 unit_price 및 unit_cost 열의 NULL 값의 수를 표시하는 결과 집합을 반환한다. 이 출력은 해당 열에서 데이터가 누락된 행이 몇 개 있는지 보여주기 때문에 처리해야 할 데이터 품질 문제를 식별하는 데 유용합니다. product_3 테이블은 이전 단계에서 생성되었으며, unit_price 및 unit_cost 열의 null 값은 전체 제품에서 계산된 각 단위당 가격과 단위당 평균값으로 대체됩니다. 단가와 단가의 중간값으로 대체되었습니다.
 
설명:

이 코드는 SQL 언어로 작성되었으며, 다음과 같은 단계를 수행한다.

DROP TABLE 문을 실행하여 product_4 테이블이 이미 존재하는 경우 삭제한다.

CREATE TABLE 문을 실행하여 product_4 테이블을 생성한다.

category_median이라는 이름의 공통 테이블 표현식(CTE)이 정의된다. 이 CTE는 ORDER BY 절이 있는 SELECT 문을 사용하여 상품 테이블의 행을 각 카테고리 내의 unit_price와 unit_cost 열로 정렬하고, PERCENTILE_CONT 함수를 사용하여 각 카테고리의 unit_price와 unit_cost의 중간값을 계산한다. GROUP BY 구문은 category_small_cd 열을 기준으로 데이터를 그룹화하고 있다.

메인 SELECT 문은 product 테이블에서 데이터를 가져와 category_small_cd 열에서 category_median CTE와 결합하고, COALESCE 함수를 사용하여 unit_price와 unit_cost 열의 NULL 값을 해당 카테고리의 중앙값으로 대체한다. 해당 카테고리의 중앙값으로 대체합니다.

마지막으로 CREATE TABLE AS 구문을 사용하여 결과 데이터를 product_4 테이블에 삽입한다.

요약하면, 이 코드에서는 product 테이블과 CTE category_median을 결합하여 new table product_4를 생성하고, PERCENTILE_CONT 함수를 사용하여 product 테이블의 각 카테고리에 대해 unit_price와 unit_cost의 중간값이 계산됩니다. 결과 테이블 product_4에는 unit_price와 unit_cost 열의 NULL 값을 해당 카테고리의 중앙값으로 대체한 상품 테이블의 데이터가 포함되어 있습니다.

 

 
설명:

이 코드도 SQL로 작성되었으며, 다음 단계를 수행한다.

SELECT 문을 실행하여 product_4 테이블의 unit_price 열과 unit_cost 열의 null 값의 합계를 가져온다.

SUM 함수의 CASE 문은 unit_price 열과 unit_cost 열의 각 값이 null인지 여부를 확인한다. 이렇게 해서 SUM 함수는 각 열의 NULL 값의 총 개수를 계산한다.

AS 키워드는 열의 이름을 각각 unit_price와 unit_cost로 변경하는 데 사용된다.

LIMIT 구문은 쿼리가 반환하는 행 수를 10으로 제한하는 데 사용된다.

요약하면, 이 코드는 product_4 테이블의 unit_price와 unit_cost 열의 null 값의 총 개수를 계산합니다. 이를 통해 테이블에서 누락된 데이터를 식별하고 추가 데이터 정리 또는 인퓨테이션이 필요한지 여부를 판단하는 데 도움이 됩니다.
 
설명:

이 코드도 SQL로 작성되어 있으며, 다음과 같은 단계를 수행합니다.

SELECT 문이 실행되어 영수증 테이블과 고객 테이블에서 데이터를 가져옵니다.

두 개의 하위 쿼리가 생성되어 2019년 및 모든 연도의 각 고객에 대한 총 매출액을 계산합니다.

SELECT 문은 LEFT JOIN 연산자를 사용하여 고객 테이블과 두 개의 하위 쿼리를 결합한다. 이렇게 하면 영수증 테이블에 매출 데이터가 없는 경우에도 고객 테이블의 모든 고객이 출력에 포함된다.

COALESCE 함수는 영수증 테이블에 고객에 대한 매출 데이터가 없는 경우 NULL 값을 0으로 대체해준다.

CASE 문은 각 고객의 매출 비율을 계산합니다. 전체 연도 매출 금액의 합계가 0인지 확인하고, 0을 반환합니다. 그렇지 않은 경우 2019년 매출액을 전체 연도 매출액 합계로 나눈다.

결과는 sales_rate라는 새 테이블에 삽입된다.

요약하면, 이 코드는 영수증 테이블의 매출 데이터를 기반으로 각 고객의 매출 비율을 계산한다. 판매율은 전체 연도 판매 금액의 총액에 대한 2019 년도 판매 금액의 비율입니다. 결과는 sales_rate라는 새 테이블에 저장됩니다.
 
설명:

이 코드도 SQL로 작성되어 있으며, 다음과 같은 단계를 수행한다.

SELECT 문을 실행하여 sales_rate 테이블에서 sales_rate 값이 0보다 큰 모든 행을 가져옵니다.

LIMIT 절은 출력을 처음 10개의 행으로 제한하고 있습니다.

결과가 출력으로 반환됩니다.

요약하면, 이 코드는 sales_rate 테이블에서 sales_rate가 0보다 큰 모든 행, 즉 고객이 2019년에 적어도 한 번 이상 판매를 한 것을 의미하는 모든 행을 선택한다.

 

설명:

이 코드는 SQL로 작성되었으며, 다음 단계를 수행합니다.

WITH 절을 사용하여 geocode 테이블을 사용하여 각 우편번호의 평균 경도와 위도를 계산하는 하위 쿼리를 정의한다.

SELECT 문은 postal_cd 열을 사용하여 customer 테이블과 WITH 절에서 정의한 하위 쿼리를 결합한다.

결합 작업의 결과는 CREATE TABLE 문을 사용하여 customer_1이라는 새 테이블에 삽입된다. 같은 이름의 테이블이 이미 존재하는 경우 먼저 DROP TABLE IF EXISTS 문을 사용하여 삭제된다.

요약하면, 이 코드에서는 postal_cd 열을 사용하여 customer 테이블과 geocode 테이블을 결합하고, 각 우편번호의 평균 경도와 위도를 customer 테이블에 추가하여 customer_1이라는 새로운 테이블을 생성합니다. 완성된 테이블에는 customer 테이블의 모든 열과 평균 경도와 위도를 위한 두 개의 열이 추가되어 있습니다.
 
설명:

이 코드는 SQL로 작성되었으며, customer_1 테이블에 대해 간단한 SELECT 문을 실행한다.

SELECT 문은 와일드카드 문자 *를 사용하여 customer_1 테이블의 모든 열과 행을 가져온다.

LIMIT 절은 쿼리가 반환하는 행 수를 처음 10개의 행으로 제한한다.

이 테이블에는 customer 테이블의 모든 열과 각 고객의 우편번호의 평균 경도와 위도 두 개의 열이 추가되어 있습니다.
 

 

설명:

이 SQL 코드는 customer_1과 store 두 테이블에서 데이터를 가져와 각 고객의 주소와 해당 매장의 주소 사이의 거리를 계산한다.

SELECT 문은 다음과 같은 컬럼을 가져옵니다.

customer_1 테이블에서 customer_id를 가져온다.
customer_1 테이블에서 customer_id를 가져와 customer_address로 이름을 바꾼다.
store 테이블의 address(store_address로 이름 변경).

고객과 매장 사이의 거리 계산 값으로 Haversine의 공식을 사용하여 ACOS, SIN, COS 함수를 사용하여 두 주소의 위도와 경도의 정현과 코사인의 아크 코사인을 각각 계산한다. 그리고 그 결과에 지구 반경(6371km)을 곱하여 킬로미터 단위의 거리를 구합니다.

JOIN 문은 application_store_cd 열과 store_cd 열의 일치를 기반으로 customer_1 테이블과 store 테이블의 행을 결합하고, LIMIT 구문은 반환되는 행의 수를 10으로 제한한다.

 

 
설명:

이 SQL 코드는 "customer"와 "recipate" 두 테이블의 데이터를 결합하여 "customer_u"라는 새로운 테이블을 생성합니다. 먼저 "sales_amount"라는 공통 테이블 표현식(CTE)을 정의하고, "customer" 테이블과 "recipate" 테이블을 왼쪽 결합을 사용하여 결합하여 각 고객의 총 매출액을 계산합니다. 이 CTE는 COALESCE 함수를 사용하여 매출이 없는 고객에게는 0을 반환한다.

그런 다음 "sales_ranking"이라는 이름의 또 다른 CTE가 정의되어 customer_name과 postal_cd의 각 그룹 내에서 총 판매 금액을 기준으로 각 고객의 판매 순위를 계산하고 ROW_NUMBER() 함수를 사용하여 sum_amount를 기준으로 내림차순으로 각 고객에게 순위를 할당하고 있습니다.

마지막으로 SELECT 문은 'customer' 테이블과 'sales_ranking' CTE를 결합하여 순위가 1인 행만 필터링한다. 이를 통해 customer_name과 postal_cd 그룹별로 상위 고객만 효과적으로 선택된다. 완성된 데이터는 "customer_u"라는 새로운 테이블에 삽입된다.
 
설명:

이 SQL 코드는 두 개의 서로 다른 테이블의 고객 수를 조회하고 그 차이를 계산하는 코드입니다.

코드의 첫 번째 부분은 "customer" 테이블에서 총 고객 수를 선택하고 "customer_cnt"라는 별칭을 부여하는 하위 쿼리입니다.

코드의 두 번째 부분은 "customer_u" 테이블에서 총 고객 수를 선택하고 "customer_u_cnt"라는 이름으로 별칭을 지정하는 또 다른 하위 쿼리입니다.

마지막으로 외부 쿼리에서는 두 개의 하위 쿼리에서 'customer_cnt'와 'customer_u_cnt'를 선택하고 둘의 차이를 'diff'로 계산한다. 이는 "customer_cnt"에서 "customer_u_cnt"를 빼면 된다.

결과는 "customer_cnt", "customer_u_cnt", "diff"의 세 개의 열로 구성된 한 줄로, "customer_cnt" 열은 "customer" 테이블의 총 고객 수를, "customer_u_cnt" 열은 "customer_u" 테이블의 총 고객 수를, "diff" 열은 "customer_u" 테이블의 총 고객 수를 나타냅니다. 테이블의 총 고객 수를, 'diff' 열은 이 두 카운트의 차이를 표시한다.
 
설명 :

이 SQL 코드는 기존 customer 테이블과 customer_u 테이블을 결합하여 customer_n이라는 새로운 테이블을 생성한다.

JOIN 조건은 customer_name과 postal_cd 컬럼에 동일한 값을 가진 customer_u 테이블 간의 행을 일치시킨다. 일치하는 행에 대해 결과 테이블 customer_n의 integration_id 열은 customer_u 테이블의 customer_id로 설정된다.

따라서 customer_n 테이블에는 customer 테이블의 모든 행과 customer_u 테이블의 customer_id 값을 포함하는 integration_id 컬럼이 추가되고, customer_u 테이블의 customer_name과 postal_cd 값이 일치하지 않는 행은 integration_id 컬럼에 NULL 값을 가지게 된다.

 

설명:

이 SQL 코드는 "customer_n" 테이블의 "customer_id" 컬럼과 "integration_id" 컬럼이 같지 않은 행 수를 조회한다.

customer_n 테이블은 이전 코드 블록에서 "customer" 테이블과 "customer_u" 테이블을 고객의 이름과 우편번호를 결합 조건으로 결합하여 생성되었다. "customer" 테이블에 새로운 "integration_id" 컬럼을 추가하고 "customer_u" 테이블에서 해당 "customer_id" 값을 포함함으로써 두 테이블을 효과적으로 통합했다.

현재 쿼리는 "customer_n" 테이블의 "customer_id" 값이 "integration_id" 값과 같지 않은 행 수를 계산한다. 이는 'customer' 테이블에 'customer_u' 테이블에 해당하는 레코드가 없거나 'customer_u' 테이블의 'customer_id' 값이 다른 레코드가 있다는 것을 의미한다. 이는 데이터 통합 문제를 식별하고 불일치를 추가로 조사하는 데 사용할 수 있다.
 
설명:

이 코드는 SQL을 사용하여 고객 판매 데이터를 기반으로 한 머신러닝 모델의 학습 데이터 세트와 테스트 데이터 세트를 생성하고 있다. 각 행이 수행하는 작업은 다음과 같습니다.

sql SELECT SETSEED(0.1); %%sql SELECT SETSEED(0.1);
쿼리에서 사용할 난수 생성기의 씨앗을 설정합니다. 이는 쿼리를 실행할 때마다 동일한 결과를 얻을 수 있도록 하기 위함이다.

CREATE TEMP TABLE IF NOT EXISTS sales_customer AS ( SELECT customer_id, ROW_NUMBER() OVER(ORDER BY RANDOM()) AS row FROM customer JOIN receipt USING( customer_id) GROUP BY customer_id HAVING SUM(AMOUNT) > 0 );

이것은 각 고객의 customer_id와 무작위로 할당된 행 번호를 포함하는 sales_customer라는 임시 테이블을 생성하고, JOIN을 사용하여 customer와 receipate라는 두 테이블에서 데이터를 선택한다. 데이터를 그룹화하여 구매한 고객(SUM(AMOUNT) > 0)만을 포함하도록 결과를 필터링하고 있습니다.

DROP TABLE IF EXISTS customer_train; CREATE TABLE customer_train AS SELECT customer.* FROM sales_customer JOIN customer USING(customer_id) WHERE sales_customer.row <= (SELECT COUNT(1) FROM sales_customer) * 0.8 ;

이렇게 하면 sales_customer 테이블의 고객 중 80%를 포함하는 customer_train이라는 테이블이 생성됩니다. 이는 sales_customer 테이블의 행 수가 sales_customer 테이블의 총 행 수의 80% 이하인 고객에 대해 고객 테이블에서 모든 열을 선택한다.

DROP TABLE IF EXISTS customer_test; CREATE TABLE customer_test AS SELECT customer.* FROM sales_customer JOIN customer USING(customer_id) EXCEPT SELECT * FROM customer_train ;

이것은 sales_customer 테이블에서 나머지 20%의 고객을 포함하는 customer_test라는 테이블을 생성한다. 이는 customer_id가 sales_customer 테이블에 있고 customer_train 테이블에 없는 고객의 customer 테이블에서 모든 컬럼을 선택한다. 이렇게 하면 테스트 세트에 트레이닝 세트에 없는 고객이 테스트 세트에 포함된다.
 
설명:

이 코드는 앞의 SQL 코드에서 생성한 테이블을 기반으로 트레이닝 세트와 테스트 세트의 데이터 비율을 계산하고 있습니다. 각 행이 무엇을 하는지 그 내역을 소개합니다.

sql SELECT train_cnt * 1.0 / all_cnt as Percentage of training data, test_cnt * 1.0 / all_cnt as Test Data Percentage

이것은 훈련 세트와 테스트 세트의 데이터 비율을 선택하는 메인 쿼리로, train_cnt, test_cnt, all_cnt 변수를 사용하며, 이는 아래의 서브 쿼리에서 계산된다.

FROM (SELECT COUNT(1) AS all_cnt FROM sales_customer) all_data CROSS JOIN (SELECT COUNT(1) AS train_cnt FROM customer_train) train_data CROSS JOIN ( SELECT COUNT(1) AS test_cnt FROM customer_test) test_data ;

이 서브쿼리는 sales_customer 테이블의 총 행 수(all_cnt), customer_train 테이블의 행 수(train_cnt), customer_test 테이블의 행 수(test_cnt)를 계산한다.

이러한 하위 쿼리를 하나의 테이블로 통합하려면 CROSS JOIN 연산자를 사용한다. 각 하위 쿼리는 하나의 행만 반환하므로 크로스 조인의 결과는 3개의 열(all_cnt, train_cnt, test_cnt)을 가진 하나의 행이 된다.

그런 다음 메인 쿼리는 train_cnt와 test_cnt를 각각 all_cnt로 나누어 훈련 세트와 테스트 세트의 데이터 비율을 계산한다. 결과를 부동 소수점 숫자로 변환하기 위해 * 1.0 식을 사용하여 부동 소수점 정확도로 나눗셈이 이루어지도록 보장한다.
 
설명:

이 코드에서는 매출 데이터 집계표(ts_amount)에 윈도우 함수를 적용하여 3개의 새로운 테이블(series_data_1, series_data_2, series_data_3)을 생성하고 있습니다. 아래는 각 행이 하는 일의 내역입니다.

%%sql
CREATE TEMP TABLE IF NOT EXISTS ts_amount AS
(
SELECT
SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6) AS sales_ym,
SUM(amount) AS sum_amount,
ROW_NUMBER() OVER(ORDER BY SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6)) AS rn
FROM
receipt
GROUP BY
sales_name
);;

이 코드에서는 월별로 매출 데이터를 정리하고(SUBSTR 함수를 사용하여 sales_ymd 열에서 연도와 월을 추출), 매출 총액을 계산하고(SUM(amount)), 각 행에 행 번호를 부여(ROW_NUMBER() OVER(...)) AS rn) 임시 테이블(ts_amount)을 생성하고 있다. 행 번호는 이후 쿼리에서 여러 기간의 데이터를 결합하는 데 사용된다.

DROP TABLE IF EXISTS series_data_1 ; DROP TABLE IF EXISTS series_data_1 ;
CREATE TABLE series_data_1 AS (
WITH lag_amount AS
(
SELECT
sales_ym입니다.
sum_amount가 됩니다.
LAG(rn, 0) OVER (ORDER BY rn) AS rn
FROM
ts_amount
)
SELECT
sales_ym입니다.
sum_amount가 됩니다.
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM 
lag_amount
WHERE
rn BETWEEN 1 AND 18).

이 코드는 현재 기간의 매출 데이터와 이전 기간의 매출 데이터를 (window 함수와 LAG 함수를 사용하여) 결합하여 새로운 테이블(series_data_1)을 생성하고, 각 행이 트레이닝 세트(test_flg = 0)에 속하는지 테스트 세트(test_flg = 1)에 속하는지 에 따라 바이너리 플래그(test_flg)를 할당한다. 이는 행 번호(rn)를 임계값(12)과 비교하여 데이터를 두 부분으로 나눈다: 처음 12개의 행은 훈련에, 나머지 6개의 행은 테스트에 사용된다. 이렇게 만들어진 표는 총 18개의 행으로 구성된다. 이 코드에서는 이 과정을 기간을 바꿔서 두 번 더 반복하고 있습니다.

DROP TABLE IF EXISTS series_data_2 ;.
CREATE TABLE series_data_2 AS (
WITH lag_amount AS (
SELECT
sales_ym입니다.
sum_amount로 한다.
LAG(rn, 6) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ym입니다.
sum_amount로 한다.
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18);

DROP TABLE IF EXISTS series_data_3 ;
CREATE TABLE series_data_3 AS (
WITH lag_amount AS (
SELECT
sales_ym입니다.
sum_amount로 한다.
LAG(rn, 12) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ym입니다.
sum_amount로 한다.
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18).

series_data_2는 6개월 전 매출 데이터를, series_data_3은 1년 전 매출 데이터를 참조 기간으로 한다.
 
설명:

이 코드는 테이블 series_data_1에서 모든 열과 행을 선택하는 SQL 쿼리이다. series_data_1 테이블은 앞의 SQL 코드 블록에서 CREATE TABLE 문을 사용하여 생성되었습니다.

series_data_1 생성에서 쿼리는 각 월별 금액의 합계를 계산하는 임시 테이블 ts_amount를 생성하고, 월을 기준으로 행 번호를 할당하고 있다. 행 번호와 LAG() 창 함수를 사용하여 쿼리는 새로운 테이블 series_data_1을 생성하고 각 월의 매출 금액과 해당 행이 훈련 데이터와 테스트 데이터 중 어느 쪽에 포함되는지 표시하는 플래그를 저장한다.

마지막으로 이 코드 블록은 쿼리가 series_data_1에서 모든 열과 행을 선택하여 이전 SQL 문장의 결과를 표시할 수 있도록 한다.
 
설명:

이 코드는 고객 데이터 다운샘플링을 수행하는 SQL 쿼리입니다. 다운샘플링은 데이터 집합에서 일부 데이터 포인트를 무작위로 제거하여 클래스의 균형을 맞추거나 데이터 집합의 크기를 줄이는 과정입니다.

쿼리에서는 첫 번째 문에서 무작위 시드 값을 설정하고 있다. 다음 문에서는 고객 데이터와 각 고객의 금액 합계가 포함된 down_sampling이라는 임시 테이블을 생성하고 있습니다. 첫 번째 CTE의 LEFT JOIN 구문은 고객 테이블과 영수증 테이블을 결합하여 각 고객의 금액 합계를 계산합니다.

두 번째 CTE에서는 CASE 문을 사용하여 두 개의 추가 열이 생성되고 있습니다. 이 열은 고객이 구매를 했는지 여부를 나타내는 플래그이다. 그런 다음 세 번째 CTE에서 ROW_NUMBER() 창 함수를 사용하여 is_buy_flag 열을 기반으로 각 고객 레코드에 행 번호를 할당하고 있다.

마지막으로 쿼리의 마지막 문에서 임시 테이블 down_sampling이 선택되는데, 행 번호가 구매 고객 수와 비구매 고객 수 이하인 행만 선택된다. 이렇게 하면 결과 테이블에서 각 클래스의 행 수가 균형 있게 배치됩니다.

 

설명:

이 코드는 down_sampling 임시 테이블의 레코드를 is_buy_flag 열로 그룹화하여 각 그룹 내 레코드 수를 계산하는 SQL 쿼리를 실행한다.

down_sampling 테이블은 앞의 코드 셀에서 customer 테이블과 receive 테이블의 amount 열을 customer_id로 집계하는 하위 쿼리 사이에 왼쪽 결합을 수행하여 생성되었다. 결과 테이블에는 고객 정보 열과 각 고객이 구매한 금액의 합계가 있습니다.

pre_table_2 하위 쿼리는 sum_amount 컬럼을 기반으로 두 개의 새로운 컬럼을 생성하는데, is_buy_flag 컬럼은 1회 이상 구매를 한 고객에게는 1이, 구매를 하지 않은 고객에게는 0이 설정되고, is_not_buy_flag 컬럼은 는 구매를 한 번도 하지 않은 고객에게는 1, 1회 이상 구매를 한 고객에게는 0이 설정된다.

pre_table_3 하위 쿼리는 is_buy_flag 컬럼과 임의의 순서에 따라 pre_table_2 테이블의 각 행에 대해 행 번호를 생성한다. 또한, 두 개의 별도 하위 쿼리에 의한 교차 조인을 사용하여 is_buy_flag가 1일 때와 0일 때 pre_table_2 테이블의 총 행 수를 계산한다.

마지막으로 메인 SELECT 문은 is_buy_flag가 1인 행과 0인 행의 총 개수보다 적은 행 번호를 가진 pre_table_3의 모든 행을 선택한다. 이렇게 하면 is_buy_flag의 각 값에 대해 동일한 수의 레코드가 존재하게 된다.

따라서 SELECT 문 출력은 down_sampling 테이블의 is_buy_flag가 0인 레코드와 1인 레코드의 개수를 나타낸다.
 
설명:

이 SQL 코드는 customer라는 기존 테이블에서 두 개의 새로운 테이블을 생성한다.

코드의 첫 번째 부분에서는 customer_std라는 테이블이 존재하는 경우 이를 삭제하고 customer_std라는 새로운 테이블을 생성합니다. 이 새 테이블에는 customer 테이블과 동일한 열이 있는데, customer_id, customer_name, gender_cd, birth_day, age, postal_cd, application_store_cd, application_date, status_cd로 구성되어 있다. 이 새로운 테이블을 생성하는 목적은 제공된 코드에서 명확하지 않다. 데이터를 표준화하거나 전처리하기 위해 생성된 것일 수도 있다.

코드의 두 번째 부분은 gender_std라는 테이블이 존재한다면 이를 삭제하고 gender_std라는 새로운 테이블을 생성한다. 새로운 테이블에는 gender_cd와 gender라는 두 개의 컬럼이 있는데, gender_cd 컬럼은 고객 테이블에서 찾은 모든 성별 코드의 명확한 목록이고, gender 컬럼은 해당 성별에 대한 설명이다. 이 테이블은 성별 코드를 각각의 성별 설명에 매핑하는 데 사용될 수 있다.
 
설명:

이 코드에서는 앞서 생성한 customer_std 테이블에서 처음 3개 행을 선택하는데, customer_std 테이블은 customer 테이블의 복사본으로 컬럼의 하위 집합을 가지고 있습니다. 이는 데이터가 새 테이블로 올바르게 가져왔는지 확인하기 위한 기본적인 점검입니다.
 
설명:

이 코드는 이전 코드 블록에서 생성된 gender_std 테이블에서 처음 3개의 행을 선택하는 SQL 쿼리를 실행한다.

LIMIT 절은 쿼리에서 반환되는 행 수를 3행으로 제한하는 데 사용되며, gender_std 테이블은 SELECT DISTINCT 문을 사용하여 생성되었기 때문에 gender_cd의 고유한 값과 고객 테이블에서 해당 성별의 값만 포함하게 됩니다. 있습니다. 따라서 이 쿼리는 gender_std 테이블에서 처음 세 개의 고유한 행을 반환한다.

이 쿼리의 출력은 gender_std 테이블의 처음 세 행의 gender_cd와 gender 값을 표시합니다.
 
설명:

이 코드는 "product"와 "category" 두 테이블을 결합하여 "product_full"이라는 새로운 테이블을 생성한다.

product 테이블에는 상품 코드, 카테고리 코드, 단가, 단가 등 각 상품에 대한 정보가 저장되어 있다.

category 테이블에는 각 카테고리 레벨의 카테고리 코드와 카테고리 이름이 저장된다.

JOIN 구문은 category_small_cd 열을 기준으로 두 테이블을 연결하고, USING 키워드를 사용하여 두 테이블에서 category_small_cd의 값이 동일한 행을 일치시키는 쿼리를 수행한다.

새로운 테이블 'product_full'에는 'product' 테이블의 모든 컬럼과 'category' 테이블의 카테고리 코드를 검색하여 얻은 카테고리 이름 컬럼이 추가된다.

 

설명:

이 코드는 product와 category라는 두 테이블에서 데이터를 선택하여 product_full이라는 새로운 테이블을 생성한다.

JOIN 구문은 공통 열인 category_small_cd를 기준으로 두 테이블을 결합하는 데 사용된다. 이는 결과 테이블에 category_small_cd 값이 두 테이블에서 동일한 행만 있음을 의미합니다.

두 테이블에서 선택한 컬럼은 product_cd, category_major_cd, category_major_name, category_medium_cd, category_medium_name, category_small_cd, category_small_name, unit_price, unit_cost입니다.

결과 테이블 product_full에는 결합 조건을 만족하는 모든 행에 대해 이러한 컬럼이 포함될 것이다.

LIMIT 절은 출력에 표시되는 행의 수를 3개로 제한하는 데 사용됩니다.

 

설명:

이 코드는 product_full 테이블의 내용을 "/tmp/data" 디렉토리에 있는 "S_product_full_UTF-8_header.csv"라는 이름의 CSV 파일로 내보내는 코드입니다. 명령으로, 파일이나 테이블 간에 데이터를 복사하는 데 사용됩니다. 이 경우 TO 키워드로 데이터를 복사할 파일을 지정하고, CSV 키워드는 파일 형식을 쉼표로 구분된 값으로 지정하며, HEADER 키워드는 파일의 첫 번째 행에 열 헤더를 포함할 것을 지정한다. 마지막으로 ENCODING 키워드는 출력 파일의 문자 인코딩을 UTF-8로 지정하는 데 사용된다.
 
설명:

이 코드는 PostgreSQL의 COPY 명령을 사용하여 product_full 테이블의 내용을 S_product_full_SJIS_header.csv라는 이름의 CSV 파일로 내보내는 코드입니다. 에 열 이름을 포함한 헤더 행을 포함하도록 지정하고, ENCODING 'SJIS' 옵션은 출력 파일이 Shift-JIS 문자 인코딩을 사용하여 인코딩되도록 지정합니다.

Shift-JIS는 일본에서 일반적으로 사용되는 문자 코드이며, CSV 파일에 사용할 수 있는 몇 가지 문자 코드 중 하나이며, Shift-JIS 인코딩의 CSV 파일로 데이터를 내보내면 이 특정 인코딩이 필요한 다른 응용 프로그램이나 데이터베이스에 파일을 쉽게 가져올 수 있습니다.
 
설명:

이 코드는 product_full 테이블의 내용을 출력 파일에 헤더 행을 포함하지 않고 /tmp/data/ 디렉토리에 있는 S_product_full_UTF-8_no.csv라는 CSV 파일로 내보내고 있다.

Postgres에서는 COPY 명령을 사용하여 /파일에서 /파일로 데이터를 복사합니다. WITH CSV 옵션은 파일이 쉼표로 구분된 값(CSV) 형식임을 지정하고, ENCODING 옵션은 파일의 문자 인코딩을 지정하고, UTF-8은 다양한 언어의 문자를 지원하는 UTF-8을 지정한다. 의 문자를 광범위하게 지원하는 UTF-8 인코딩을 사용하는 파일임을 나타낸다.

HEADER 옵션이 지정되지 않았기 때문에 출력 파일에는 헤더 라인이 포함되지 않는다. 따라서 출력 파일에는 product_full 테이블의 데이터 행만 CSV 형식으로 포함된다.
 
설명:

이 SQL 코드는 product_full 테이블이 존재하는 경우 이를 삭제하고 같은 이름의 새 테이블을 생성한다. 새 테이블에는 product_cd, category_major_cd, category_major_name, category_medium_cd, category_medium_name, category_small_cd, category_small_name, category_small_name, category_small_name, it_price, it_cost의 small_name, it_price, it_cost의 8개의 열이 있다. 처음 7개의 열은 길이가 다른 VARCHAR 타입이고, 마지막 2개의 열은 INTEGER 타입이다.

컬럼의 데이터 타입과 길이를 지정함으로써 코드는 product_full 테이블의 스키마를 정의하는 것이다. 이렇게 하면 각 컬럼이 특정 데이터 유형과 길이를 가지게 되어 이 테이블에 대한 쿼리 성능이 향상되고, 잘못된 유형과 길이의 데이터가 삽입되었을 때 발생할 수 있는 오류를 방지할 수 있다.
 
설명:

이 코드는 '/tmp/data/S_product_full_UTF-8_header.csv'에 있는 CSV 파일에서 데이터베이스에 이미 생성된 'product_full'이라는 새로운 테이블로 데이터를 가져온다.

PostgreSQL의 COPY 명령은 파일에서 테이블로 또는 그 반대로 데이터를 복사할 수 있는데, FROM 키워드는 CSV 파일의 경로를 지정하고, WITH 키워드는 COPY 명령의 옵션을 지정하는 데 사용된다. 이 경우 CSV는 파일이 쉼표로 구분된 값(Comma-Separated Value) 형식임을, HEADER는 파일의 첫 번째 행이 열 이름을 포함하고 있음을, ENCODING 'UTF-8'은 파일에서 사용되는 문자 인코딩을 지정하는 것을 각각 지정한다.

데이터베이스에 이미 'product_full' 테이블이 생성되어 있으므로, COPY 명령은 CSV 파일의 데이터를 기존 테이블에 삽입한다.
 
설명:

이 SQL 코드는 product_full 테이블에서 처음 세 줄의 데이터를 가져오는 간단한 SELECT 문이다. 데이터는 이전에 CSV 파일에서 COPY 명령을 사용하여 테이블에 로드되었습니다. 이 쿼리는 LIMIT 절을 사용하여 반환되는 행 수를 3행으로 제한하고 있다.

SELECT 문은 product_full 테이블에서 모든 열을 가져옵니다. 이 테이블에는 코드, 카테고리, 가격 등 상품에 대한 정보가 포함되어 있다. 데이터는 product_cd, category_major_cd, category_major_name, category_medium_cd, category_medium_name, category_small_cd, category_small_cd, category_small_name, it_price name, it_price, it_cost 등의 열로 정리하고 있다.
 
설명:

이 코드는 product_full이라는 이름의 테이블이 존재하는 경우 이를 삭제하고 동일한 이름과 스키마를 가진 새로운 테이블을 생성한다. 새 테이블에는 9개의 컬럼이 있습니다.

product_cd 컬럼은 VARCHAR(10) 타입이다.
category_major_cd 컬럼은 VARCHAR(2) 타입이다.
category_major_name 컬럼은 VARCHAR(20) 타입이다.
category_medium_cd 칼럼은 VARCHAR(4) 타입이다.
category_medium_name 칼럼은 VARCHAR(20) 타입이다.
category_small_cd 칼럼은 VARCHAR(6) 타입이다.
category_small_name 칼럼은 VARCHAR(20) 타입이다.
unit_price 컬럼은 INTEGER 타입이다.
unit_cost 컬럼은 INTEGER 타입이다.

VARCHAR 데이터 타입은 지정된 최대 길이의 가변 길이 문자열을 저장하는 데 사용되며, INTEGER 데이터 타입은 정수를 저장하는 데 사용된다.

이 코드는 지정된 스키마로 빈 테이블을 생성하고 데이터를 테이블에 추가하지 않는다.

 

설명:

이 코드는 PostgreSQL의 COPY 명령을 사용하여 'S_product_full_UTF-8_no.csv'라는 CSV 파일에서 'product_full'이라는 테이블로 데이터를 가져온다.

WITH CSV 옵션은 데이터가 CSV 형식임을 지정하고, ENCODING 옵션은 파일의 문자 인코딩(이 경우 UTF-8)을 지정합니다.

파일에는 헤더 행이 없으므로 'product_full' 테이블을 생성하기 전에 SQL 문에서 열 이름과 유형을 명시적으로 지정해야 한다.

데이터가 테이블로 임포트되면 SELECT 문으로 'product_full' 테이블의 첫 세 행을 가져와서 데이터가 제대로 임포트되었는지 확인한다.
 
설명:

이 코드는 SQL의 SELECT 문을 사용하여 "product_full" 테이블에서 처음 3개의 행을 선택하며, SELECT 문은 "product_full" 테이블에서 모든 열(product_cd, category_major_cd, category_major_name, category_medium_cd, category_small_cd, category_small_name, unit_price, unit_cost)을 추출하여 LIMIT 키워드로 출력을 출력한다. major_name, category_medium_cd, category_medium_name, category_small_cd, category_small_name, unit_price, unit_cost)를 모두 가져오고 LIMIT 키워드로 출력을 처음 세 줄로 제한합니다. 결과는 "product_full" 테이블의 첫 세 행을 표시합니다.
 
설명: 

제품 product_full 테이블의 내용을 쉼표 대신 탭(˶~~~~)을 구분 기호로 사용하여 CSV 형식으로 내보내기 합니다. 내보낸 파일은 /tmp/data 디렉토리에 저장되며, S_product_full_UTF-8_header.tsv라는 이름으로 저장되며, HEADER 옵션이 포함되어 있기 때문에 파일의 첫 번째 줄에 컬럼 헤더가 포함되어 있습니다.

다음은 코드의 개별 컴포넌트 분석입니다.

COPY product_full: product_full 테이블의 데이터를 파일로 복사할 것을 지정합니다.

TO '/tmp/data/S_product_full_UTF-8_header.tsv': 내보낼 파일의 경로와 이름을 지정한다.

WITH CSV: 내보낼 파일이 CSV 형식임을 지정합니다.
HEADER: 파일의 첫 번째 줄에 컬럼 헤더를 포함하도록 지정합니다.
DELIMITER E' \t': 탭 문자를 구분 기호로 사용하도록 지정한다.
ENCODING 'UTF-8': 파일을 내보낼 때 사용할 문자 인코딩을 지정합니다.

 

설명:

이 SQL 코드 블록은 먼저 product_full이라는 테이블이 존재할 경우 이를 삭제하고 동일한 이름과 스키마를 가진 새로운 테이블을 생성한다.

product_full 테이블의 스키마에는 다음과 같은 데이터 타입을 가진 9개의 컬럼이 있다.

product_cd: VARCHAR(10) (즉, 최대 길이 10까지 가변 길이의 문자열)
category_major_cd: VARCHAR(2)
category_major_name: VARCHAR(20)
category_medium_cd.VARCHAR(4)
category_medium_name: VARCHAR(20)
category_small_cd입니다. VARCHAR(6)
category_small_name: VARCHAR(20)
unit_price입니다. INTEGER
unit_cost: INTEGER

이 코드 블록의 목적은 데이터를 불러오기 전에 product_full 테이블의 구조를 정의하는 것이다.
 
설명:

이 코드는 '/tmp/data/S_product_full_UTF-8_header.tsv'에 있는 TSV(탭으로 구분된 값) 파일에서 데이터를 복사하여 현재 데이터베이스의 'product_full'에 삽입한다.

COPY 명령은 입력 파일이 CSV 파일이고, 헤더가 파일의 첫 번째 줄에 포함되어 있으며, 사용되는 구분 기호가 탭 문자('˶')임을 지정하고, ENCODING 매개 변수는 파일이 UTF-8로 인코딩되었음을 지정한다.

이 명령은 'product_full' 테이블이 이미 존재하고, 입력 파일의 데이터(product_cd, category_major_cd, category_major_name, category_medium_cd, category_medium_cd, category_medium_ name, category_small_cd, category_small_name, unit_price, unit_cost)와 동일한 구조를 가지고 있다고 가정한다. 이 명령은 파일의 데이터를 테이블에 삽입하고 파일 데이터의 각 행에 대해 테이블에 새 행을 생성한다.
 
 
설명:

이 코드는 product_full 테이블에서 처음 3개 행을 선택하는 SQL 쿼리를 실행하며, SELECT 문 뒤에 *를 붙였는데, 이는 테이블의 모든 열을 선택한다는 의미이며, LIMIT 3은 결과 집합을 테이블의 처음 3개 행으로 제한한다.

결과는 테이블에 지정된 열에 따라 product_full 테이블의 데이터를 표시한다. 정확한 출력은 테이블에 저장된 데이터에 따라 달라집니다.
 

Comment