데이터 사이언스 100번의 노크(구조화 데이터 처리편)- SQL Part 4 (Q61 to Q80)의 해설입니다.

참고(Reference) : 「데이터 사이언티스트 협회 스킬 정의 위원」의 「데이터 사이언스 100번의 노크(구조화 데이터 처리편)」입니다.

데이터 사이언티스트 소사이어티 깃허브 : https://github.com/The-Japan-DataScientist-Society/100knocks-preprocess

데이터 사이언스 100 노크 (구조화된 데이터 처리) URL : https://github.com/The-Japan-DataScientist-Society/100knocks-preprocess/blob/master/docker/work/answer/ans_preprocess_knock_SQL.ipynb

– 참고: 원래 데이터 사이언티스트 소사이어티(データサイエンティスト協会スキル定義委員)에서 만든 ipynb 파일을 DeepL 에서 일본어에서 영어로 번역한 파일입니다.

– 이 파일을 업데이트한 이유는 초보자부터 고급 엔지니어까지 SQL을 사용하고자 하는 모든 사람에게 유용한 이 실습을 전파하기 위해서입니다.

– 이 데이터는 일본어로 작성되었으므로 연습할 때 언어 문제가 발생할 수 있습니다. 그러나 크게 영향을 미치지 않으므로 걱정하지 마십시오.

처음에
사용법
데이터 가동 100번 노크

처음에

데이터베이스는 PostgreSQL13입니다.
먼저 다음 셀을 실행합니다.
셀에 %%sql을 명시하여 SQL을 발행할 수 있습니다.
jupyter에서는 describe 명령으로 테이블 구조를 확인할 수 없으므로, 테이블 구조를 확인하려면 limit을 지정한 SELECT 등으로 대체해 주세요.
익숙한 SQL 클라이언트를 사용해도 문제 없습니다(접속 정보는 아래와 같습니다).
- IP 주소 : Docker Desktop의 경우 localhost, Docker toolbox의 경우 192.168.99.100
- Port:5432
- database 이름: dsdojo_db
- 사용자 이름: padawan
- 비밀번호:padawan12345
대량 출력을 하면 Jupyter가 멈출 수 있으므로 출력 건수를 제한하는 것을 권장합니다(질문에도 출력 건수를 기재).
- 결과 확인을 위해 표시하는 양을 적절히 조절하여 작업을 가볍게 진행할 수 있는 기술도 데이터 처리에는 필요합니다.
대량의 결과가 출력된 경우, 파일이 무거워져 이후 열리지 않을 수 있습니다.
- 이 경우, 작업 결과는 사라지지만 파일을 GitHub에서 다시 불러와야 합니다.
- vim 에디터 등으로 대량 출력 범위를 삭제할 수도 있습니다.
이름, 주소 등은 더미 데이터이며 실제 존재하는 것이 아닙니다.

In [1]:

%load_ext sql
import os

pgconfig = {
    'host': 'db',
    'port': os.environ['PG_PORT'],
    'database': os.environ['PG_DATABASE'],
    'user': os.environ['PG_USER'],
    'password': os.environ['PG_PASSWORD'],
}
dsl = 'postgresql://{user}:{password}@{host}:{port}/{database}'.format(**pgconfig)

# Magic 명령어로 SQL을 작성하기 위한 환경 설정
%sql $dsl

Out[1]:

'Connected: padawan@dsdojo_db'

사용법

셀의 맨 앞에 %%sql을 기재하고, 두 번째 줄 이후에 SQL을 작성하면 Jupyter에서 PostgreSQL에 대해 SQL을 실행할 수 있습니다.

In [2]:

%%sql
SELECT '다음과 같이 실행됩니다' AS sample;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.

Out[2]:

sample
다음과 같이 실행됩니다

데이터 가동 100번 노크

S-061: 영수증 내역 데이터(df_receipt)의 매출 금액(amount)을 고객 ID(customer_id)별로 합산하고, 매출 금액 합계를 상수 대수화(하단 10)하여 고객 ID, 매출 금액 합계와 함께 10건씩 표시한다. 단, 고객 ID가 “Z”로 시작하는 것은 비회원을 의미하므로 제외하여 계산한다.

In [69]:

%%sql

SELECT
    customer_id,
    sum_amount,
    LOG(sum_amount + 0.5) AS log_amount
FROM
(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY
        customer_id
) AS sum_amount_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[69]:

customer_id	sum_amount	log_amount
CS001311000059	2302	3.3621996388688865
CS004614000122	248	2.3953263930693509
CS003512000043	298	2.4749443354653879
CS011615000061	246	2.3918169236132488
CS029212000033	3604	3.5568450298595813
CS007515000119	7157	3.8547613566936362
CS034515000123	3699	3.5681430316577019
CS004315000058	490	2.6906390117159673
CS026414000014	6671	3.8242234903608168
CS001615000099	768	2.8856438718357639

설명:

데이터베이스에서 데이터를 가져오는 SQL 쿼리입니다. 단계별로 분해해 보자.

쿼리는 %%sql magic 명령으로 시작하는데, 이는 Jupyter 노트북에서 다음 코드가 SQL 코드임을 지정하는 데 사용됩니다.

메인 쿼리에서는 customer_id, sum_amount, log_amount라는 세 개의 컬럼을 선택한다. 이 컬럼들은 괄호 안의 하위 쿼리에서 파생됩니다.

괄호 안의 하위 쿼리에서는 먼저 customer_id가 "Z"로 시작하는 모든 레코드를 필터링하여 제외한다. 이는 NOT LIKE 'Z%'라는 조건에 의해 수행됩니다.

필터링된 레코드는 GROUP BY 절을 사용하여 customer_id로 그룹화됩니다.

SUM 함수는 각 고객 ID의 총 금액을 계산하는 데 사용된다.

AS sum_amount 절은 계산된 컬럼의 이름을 sum_amount로 변경한다.

하위 쿼리는 sum_amount_tbl로 별칭이 지정된다.

마지막으로 외부 쿼리는 customer_id, sum_amount, sum_amount에 0.5를 더한 대수(base e)를 선택한다. 이것은 LOG 함수를 사용하여 계산되며, log_amount라는 별칭이 붙는다.

결과는 LIMIT 구문으로 10행으로 제한됩니다.

S-062: 영수증 내역 데이터(df_receipt)의 매출금액(amount)을 고객ID(customer_id)별로 합산하고, 매출금액 합계를 자연대수화(하단 e)하여 고객ID, 매출금액 합계와 함께 10건씩 표시한다. 단, 고객 ID가 “Z”로 시작하는 것은 비회원을 의미하므로 제외하여 계산한다.

In [70]:

%%sql

SELECT
    customer_id,
    sum_amount,
    LN(sum_amount + 0.5) AS log_amount
FROM
(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY
        customer_id
) AS sum_amount_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[70]:

customer_id	sum_amount	log_amount
CS001311000059	2302	7.7417507681294619
CS004614000122	248	5.5154428455366834
CS003512000043	298	5.6987699328326568
CS011615000061	246	5.5073619934827448
CS029212000033	3604	8.1899383438446333
CS007515000119	7157	8.8759160369722701
CS034515000123	3699	8.2159529543656056
CS004315000058	490	6.1954252790054178
CS026414000014	6671	8.8056000011824754
CS001615000099	768	6.6444405629786506

설명:

데이터베이스에서 데이터를 가져오는 SQL 쿼리입니다. 단계별로 분해해 보자.

쿼리는 %%sql magic 명령으로 시작하는데, 이는 Jupyter 노트북에서 다음 코드가 SQL 코드임을 지정하는 데 사용됩니다.

메인 쿼리에서는 customer_id, sum_amount, log_amount라는 세 개의 컬럼을 선택한다. 이 컬럼들은 괄호 안의 하위 쿼리에서 파생됩니다.

괄호 안의 하위 쿼리에서는 먼저 customer_id가 "Z"로 시작하는 모든 레코드를 필터링하여 제외한다. 이는 NOT LIKE 'Z%'라는 조건에 의해 수행됩니다.

필터링된 레코드는 GROUP BY 절을 사용하여 customer_id로 그룹화됩니다.

SUM 함수는 각 고객 ID의 총 금액을 계산하는 데 사용된다.

AS sum_amount 절은 계산된 컬럼의 이름을 sum_amount로 변경한다.

하위 쿼리는 sum_amount_tbl로 별칭이 지정된다.

마지막으로 외부 쿼리는 customer_id, sum_amount, sum_amount의 자연대수(바닥 e)에 0.5를 더한 값을 선택한다. 이것은 LN 함수를 사용하여 계산되며, log_amount라는 별칭이 붙는다.

결과는 LIMIT 구문으로 10줄로 제한된다.

S-063: 상품 데이터(df_product)의 단가(unit_price)와 원가(unit_cost)로부터 각 상품의 이익액을 산출하고, 그 결과를 10건 표시하시오.

In [71]:

%%sql

SELECT
    product_cd, 
    unit_price, 
    unit_cost,
    unit_price - unit_cost AS unit_profit
FROM
    product
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[71]:

product_cd	unit_price	unit_cost	unit_profit
P040101001	198	149	49
P040101002	218	164	54
P040101003	230	173	57
P040101004	248	186	62
P040101005	268	201	67
P040101006	298	224	74
P040101007	338	254	84
P040101008	420	315	105
P040101009	498	374	124
P040101010	580	435	145

설명:

데이터베이스에서 데이터를 가져오는 SQL 쿼리입니다. 단계별로 분해해 보겠습니다.

쿼리는 %%sql 매직 명령으로 시작하며, 이는 Jupyter 노트북에서 다음 코드가 SQL 코드임을 지정하는 데 사용됩니다.

메인 쿼리에서는 product_cd, unit_price, unit_cost, unit_profit의 4개 컬럼을 선택한다. 이 컬럼들은 product 테이블에서 파생된다.

FROM 절은 제품 테이블을 지정한다.

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

이 쿼리에서는 단가에서 단가를 빼서 단위 수익을 계산합니다. 이는 - 연산자를 사용하여 수행됩니다.

쿼리 결과에는 product 테이블의 처음 10개의 행인 product_cd, unit_price, unit_cost, unit_profit이 포함된다.

전체적으로 이 쿼리는 선택한 제품의 가격, 비용 및 이익에 대한 정보를 얻는다.

S-064: 상품 데이터(df_product)의 단가(unit_price)와 원가(unit_cost)에서 각 상품의 전체 평균 수익률을 계산하시오. 단, 단가와 원가에는 결손이 발생한다는 점에 유의하라.

In [72]:

%%sql

SELECT
    AVG((unit_price * 1.0 - unit_cost) / unit_price) AS unit_profit_rate
FROM
    product
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.

Out[72]:

unit_profit_rate
0.24911389885177001279

설명:

이 코드는 SQL을 사용하여 'product'라는 데이터베이스 테이블의 상위 10개 상품의 평균 단가 수익률을 조회하는 코드입니다. 다음은 코드의 각 부분에 대한 설명입니다.

"%%sql"은 Jupyter notebook의 매직 명령어로, 아래 코드를 SQL로 해석하도록 노트북에 지시합니다.

"SELECT"는 데이터베이스에서 데이터를 가져온다는 것을 나타내는 SQL 키워드입니다.

"AVG"는 주어진 값 집합의 평균을 계산하는 SQL 함수입니다.

"((unit_price * 1.0 - unit_cost) / unit_price)"는 각 상품의 단위당 수익률을 계산하는 계산식입니다. 단가에서 단가를 빼고 그 결과를 단가로 나누는 방식이며, 1.0의 곱셈은 나눗셈이 정수가 아닌 부동소수점 값을 반환하도록 하기 위해 1.0을 곱하는 것입니다.

"AS unit_profit_rate"는 계산된 값을 "unit_profit_rate"라는 컬럼에 대입합니다.

"FROM product"는 "product" 테이블에서 데이터를 가져올 것을 지정한다.

"LIMIT 10"은 반환되는 행 수를 10행으로 제한한다. 즉, 어떤 기준(코드 스니펫에서는 지정되지 않음)에 따라 상위 10개 제품의 결과만 표시한다는 의미이다.

S-065: 상품 데이터(df_product)의 각 상품에 대해 수익률이 30%가 되는 새로운 단가를 구하시오. 단, 1원 미만은 반올림한다. 그리고 10개의 결과를 표시하고, 수익률이 대략 30% 정도인 것을 확인하라. 단, 단가(unit_price)와 원가(unit_cost)에는 적자가 발생한다는 점에 유의하라.

In [73]:

%%sql

WITH new_price_tbl AS (
    SELECT
        product_cd, 
        unit_price, 
        unit_cost,
        TRUNC(unit_cost / 0.7) AS new_price
    FROM
        product
) 
SELECT
    *,
    (new_price - unit_cost) / new_price AS new_profit_rate
FROM
    new_price_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[73]:

product_cd	unit_price	unit_cost	new_price	new_profit_rate
P040101001	198	149	212	0.29716981132075471698
P040101002	218	164	234	0.29914529914529914530
P040101003	230	173	247	0.29959514170040485830
P040101004	248	186	265	0.29811320754716981132
P040101005	268	201	287	0.29965156794425087108
P040101006	298	224	320	0.30000000000000000000
P040101007	338	254	362	0.29834254143646408840
P040101008	420	315	450	0.30000000000000000000
P040101009	498	374	534	0.29962546816479400749
P040101010	580	435	621	0.29951690821256038647

설명:

이 코드에서는 SQL을 사용하여 "product"라는 데이터베이스 테이블의 수정된 버전을 조회하고 있습니다. 수정 내용은 각 상품의 단가에서 새로운 가격을 산출하고, 이 새로운 가격을 사용하여 새로운 이윤율을 산출하는 것입니다. 다음은 코드의 각 부분을 설명합니다.

"%%sql"은 Jupyter notebook의 매직 명령으로, 다음 코드를 SQL로 해석하도록 노트북에 지시한다.

"WITH new_price_tbl AS (...)" 는 "new_price_tbl"이라는 CTE(Common Table Expression)를 설정합니다. 이를 통해 후속 쿼리에서 사용할 수 있는 하위 쿼리를 정의할 수 있습니다.

"( SELECT product_cd, unit_price, unit_cost, TRUNC(unit_cost / 0.7) AS new_price FROM product )"는 'product' 테이블에서 데이터를 선택하고, 단가를 기준으로 새로운 가격을 계산하여 'new price'라는 새로운 열에 할당하는 하위 쿼리이며, 'TRUNC' 함수를 사용하여 계산된 새로운 가격을 가장 가까운 정수로 반올림하고 있다. 이는 회사가 제품 가격을 정수로 결정하고 싶다는 가정을 기반으로 한다.

"SELECT *, (new_price - unit_cost) / new_price AS new_profit_rate FROM new_price_tbl LIMIT 10 ;"은 "new_price_tbl"의 모든 열을 선택하는 메인 쿼리이다. 의 새로운 수익률을 계산합니다. 새로운 수익률은 새로운 가격에서 단가를 빼고 그 결과를 새로운 가격으로 나누어 'new_profit_rate'라는 새로운 열에 대입하여 계산됩니다. 즉, 어떤 기준(코드 스니펫에서 지정하지 않은)에 따라 상위 10개 상품의 결과만 표시하고 싶다는 의미입니다.

S-066: 상품 데이터(df_product)의 각 상품에 대해 수익률이 30%가 되는 새로운 단가를 구하시오. 이번에는 1원 미만은 반올림한다(반올림 또는 짝수로 반올림해도 무방하다). 그리고 10개의 결과를 표시하게 하고, 수익률이 대략 30% 정도인 것을 확인하라. 단, 단가(unit_price)와 원가(unit_cost)에는 결손이 발생한다는 점에 유의한다.

In [74]:

%%sql

WITH new_price_tbl AS (
    SELECT
        product_cd, 
        unit_price, 
        unit_cost,
        ROUND(unit_cost / 0.7) AS new_price
    FROM
        product
) 
SELECT
    *,
    (new_price - unit_cost) / new_price AS new_profit_rate
FROM
    new_price_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[74]:

product_cd	unit_price	unit_cost	new_price	new_profit_rate
P040101001	198	149	213	0.30046948356807511737
P040101002	218	164	234	0.29914529914529914530
P040101003	230	173	247	0.29959514170040485830
P040101004	248	186	266	0.30075187969924812030
P040101005	268	201	287	0.29965156794425087108
P040101006	298	224	320	0.30000000000000000000
P040101007	338	254	363	0.30027548209366391185
P040101008	420	315	450	0.30000000000000000000
P040101009	498	374	534	0.29962546816479400749
P040101010	580	435	621	0.29951690821256038647

설명:

이 코드는 SQL로 작성되었으며, 'new_price_tbl'이라는 공통 테이블 표현식(CTE)을 사용하여 'product'라는 테이블에서 데이터를 선택하는 데 사용됩니다. 이 코드는 각 상품의 단가를 0.7로 나누어 새로운 가격을 계산하고, 새로운 가격에서 단가를 빼고, 그 결과를 새로운 가격으로 나누어 새로운 이윤율을 계산한다.

아래는 이 코드의 단계별 분석입니다.

WITH new_price_tbl AS (...) : 괄호로 둘러싸인 SELECT 문에서 데이터를 일시적으로 저장하는 데 사용되는 'new_price_tbl'이라는 공통 테이블 표현식(CTE)을 정의하고 있다.

SELECT product_cd, unit_price, unit_cost, ROUND(unit_cost / 0.7) AS new_price FROM product: 상품 코드, 단가, 단가를 선택하고 각 상품의 단가를 0.7로 나눈 후 그 결과를 반올림하여 새로운 가격을 계산합니다. 가격을 계산합니다.

SELECT *, (new_price - unit_cost) / new_price AS new_profit_rate FROM new_price_tbl LIMIT 10: 'new_price_tbl' CTE에서 모든 컬럼을 선택하고 새 가격에서 단가를 뺀 후, 그 결과를 새로운 가격으로 나누어 새로운 이윤율을 계산한다. 마지막으로 출력을 처음 10줄로 제한하고 있다.

요약하면, 이 코드는 CTE를 사용하여 필요한 데이터를 일시적으로 저장하고, 그 데이터에 대해 산술 연산을 수행하여 각 상품의 새로운 가격과 새로운 이윤율을 계산하고 있다.

S-067: 상품 데이터(df_product)의 각 상품에 대해 수익률이 30%가 되는 새로운 단가를 구하시오. 이번에는 1원 미만은 반올림한다. 그리고 10개의 결과를 표시하고, 수익률이 대략 30% 정도인 것을 확인하라. 단, 단가(unit_price)와 원가(unit_cost)에는 적자가 발생하고 있다는 점에 유의하라.

In [75]:

%%sql

WITH new_price_tbl AS (
    SELECT
        product_cd, 
        unit_price, 
        unit_cost,
        CEIL(unit_cost / 0.7) AS new_price
    FROM
        product
) 
SELECT
    *,
    (new_price - unit_cost) / new_price AS new_profit_rate
FROM
    new_price_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[75]:

product_cd	unit_price	unit_cost	new_price	new_profit_rate
P040101001	198	149	213	0.30046948356807511737
P040101002	218	164	235	0.30212765957446808511
P040101003	230	173	248	0.30241935483870967742
P040101004	248	186	266	0.30075187969924812030
P040101005	268	201	288	0.30208333333333333333
P040101006	298	224	320	0.30000000000000000000
P040101007	338	254	363	0.30027548209366391185
P040101008	420	315	450	0.30000000000000000000
P040101009	498	374	535	0.30093457943925233645
P040101010	580	435	622	0.30064308681672025723

설명:

이 코드는 이전 코드와 비슷하지만 새 가격을 반올림하는 대신 CEIL 함수를 사용하여 가장 가까운 정수로 반올림합니다.

아래는 코드의 단계별 분석입니다.

WITH new_price_tbl AS (...) : 괄호로 둘러싸인 SELECT 문에서 데이터를 일시적으로 저장하는 데 사용되는 'new_price_tbl'이라는 공통 테이블 표현식(CTE)을 정의하고 있다.

SELECT product_cd, unit_price, unit_cost, CEIL(unit_cost / 0.7) AS new_price FROM product: 상품 코드, 단가, 단가를 선택하고 각 상품의 단가를 0.7로 나눈 후, CEIL 함수로 소수점 이하를 반올림하여 반올림하여 새로운 가격을 계산합니다.

SELECT *, (new_price - unit_cost) / new_price AS new_profit_rate FROM new_price_tbl LIMIT 10: 'new_price_tbl' CTE에서 모든 컬럼을 선택하고, 새 가격에서 단가를 뺀 후, 그 결과를 새 가격으로 나누어 새로운 수익률을 계산한다. 마지막으로 출력을 처음 10줄로 제한하고 있다.

요약하면, 이 코드는 CTE를 사용하여 필요한 데이터를 일시적으로 저장하고, 그 데이터에 대해 산술 연산을 수행하여 각 상품의 새로운 가격과 새로운 이윤율을 계산하고 있다. 단, 새로운 가격은 이전 코드처럼 반올림하지 않고 CEIL 함수를 사용하여 가장 가까운 정수로 반올림한다.

S-068: 상품 데이터(df_product)의 각 상품에 대해 소비세율 10%의 부가세 포함 금액을 구하고, 1원 미만의 단수는 절사하여 10개의 결과를 표시하시오. 단, 단가(unit_price)에는 결손이 발생한다는 점에 유의하라.

In [76]:

%%sql

SELECT
    product_cd,
    unit_price,
    TRUNC(unit_price * 1.1) AS tax_price
FROM
    product
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[76]:

product_cd	unit_price	tax_price
P040101001	198	217
P040101002	218	239
P040101003	230	253
P040101004	248	272
P040101005	268	294
P040101006	298	327
P040101007	338	371
P040101008	420	462
P040101009	498	547
P040101010	580	638

설명:

이 코드는 SQL로 작성되었으며, 'product'라는 이름의 테이블에서 데이터를 선택하는 데 사용됩니다. 이 코드에서는 상품 코드, 단가를 선택하고 각 상품의 부가세 포함 가격을 계산합니다. 단가에 1.1을 곱하고 결과를 반올림하여 소수점 이하를 제거합니다.

다음은 코드의 단계별 분석입니다.

SELECT product_cd, unit_price, TRUNC(unit_price * 1.1) AS tax_price FROM product: 상품 코드, 단가를 선택하여 각 상품의 세금 포함 가격을 계산합니다. 단가에 1.1을 곱하고 TRUNC 함수로 소수점 이하를 반올림합니다.

FROM product: 데이터를 선택할 '상품' 테이블을 지정합니다.

LIMIT 10: 출력을 처음 10줄로 제한합니다.

요약하면, 이 코드에서는 각 상품의 단가에 1.1을 곱하여 부가세 포함 가격을 계산하고, TRUNC 함수로 소수점 이하를 반올림하고 있습니다.

S-069: 영수증 내역 데이터(df_receipt)와 상품 데이터(df_product)를 결합하여 고객별 전체 상품 판매금액 합계와 카테고리 대분류 코드(category_major_cd)가 “07”(병조림 통조림)인 상품의 판매금액 합계를 계산한 후, 양자의 비율을 구하시오. 추출 대상은 카테고리 대분류 코드 “07”(병조림 통조림)의 판매 실적이 있는 고객으로 한정하고, 결과를 10건만 표시한다.

In [77]:

%%sql

WITH amount_all AS(
    SELECT
        customer_id,
        SUM(amount) AS sum_all
    FROM
        receipt
    GROUP BY
        customer_id
),
amount_07 AS (
    SELECT
        r.customer_id,
        SUM(r.amount) AS sum_07
    FROM
        receipt r
    JOIN
        product p
    ON
        r.product_cd = p.product_cd
    WHERE
        p.category_major_cd = '07'
    GROUP BY
        customer_id
)
SELECT
    amount_all.customer_id,
    sum_all,
    sum_07,
    sum_07 * 1.0 / sum_all AS sales_rate
FROM
    amount_all
JOIN
    amount_07
ON
    amount_all.customer_id = amount_07.customer_id
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[77]:

customer_id	sum_all	sum_07	sales_rate
CS001311000059	2302	102	0.04430929626411815812
CS011615000061	246	98	0.39837398373983739837
CS029212000033	3604	3604	1.00000000000000000000
CS007515000119	7157	2832	0.39569652088864049183
CS034515000123	3699	1202	0.32495268991619356583
CS026414000014	6671	3142	0.47099385399490331285
CS001615000099	768	318	0.41406250000000000000
CS010515000082	1482	553	0.37314439946018893387
CS019315000045	813	380	0.46740467404674046740
CS008513000099	1322	210	0.15885022692889561271

설명:

이 코드는 SQL로 작성되었으며, 'amount_all'과 'amount_07'이라는 두 개의 공통 테이블 표현식(CTE)을 사용하여 'receive'와 'product'라는 두 개의 테이블에서 데이터를 선택하는 데 사용된다. 이 코드는 각 고객이 사용한 금액의 합계와 메이저 카테고리 코드가 '07'인 상품에 사용한 금액의 합계를 계산한다. 그런 다음 두 CTE를 결합하여 각 고객의 카테고리 '07' 상품의 판매율을 계산한다.

다음은 코드의 단계별 예시입니다.

WITH amount_all AS(...) 를 사용합니다. 이것은 괄호로 둘러싸인 SELECT 문에서 데이터를 일시적으로 저장하는 데 사용되는 'amount_all'이라는 공통 테이블 표현식(CTE)을 정의하고 있다.

SELECT customer_id, SUM(amount) AS sum_all FROM receipt GROUP BY customer_id: 'receiption' 테이블에서 고객 ID와 각 고객이 사용한 금액의 합계를 선택하고 그 결과를 고객 ID별로 그룹화한다. 그룹화합니다.

또한, amount_07 AS(...) 를 사용합니다. 이는 괄호로 둘러싸인 SELECT 문에서 데이터를 일시적으로 저장하는 데 사용되는 'amount_07'이라는 이름의 또 다른 CTE를 정의하고 있다.

SELECT r.customer_id, SUM(r.amount) AS sum_07 FROM receipt r JOIN product p ON r.product_cd = p.product_cd WHERE p.category_major_cd = '07' GROUP BY customer_id: 고객 ID와 메이저 카테고리 코드가 '07'인 제품에 지출된 금액의 합계를 'receiport'와 'product' 테이블에서 선택하고, 제품 코드에서 두 테이블을 결합하고, 메이저 카테고리 코드 '07'로 제품을 필터링한 후 고객 ID로 결과를 그룹화한다.

SELECT amount_all.customer_id, sum_all, sum_07, sum_07, sum_07 * 1.0 / sum_all AS sales_rate FROM amount_all JOIN amount_07 ON amount_all.customer_id = amount_07. customer_id LIMIT 10: 고객 ID, 각 고객이 사용한 모든 금액의 합계, 각 고객이 카테고리 '07' 제품에 사용한 금액의 합계, 카테고리 '07' 제품에 사용한 금액의 합계를 고객이 사용한 모든 금액의 합계로 나눈 값을 통해 각 고객의 카테고리 '07' 제품의 판매율을 선택한다. 마지막으로 고객 ID로 2개의 CTE를 결합하여 출력을 처음 10줄로 제한한다.

요약하면, 이 코드는 먼저 각 고객의 전체 소비 금액의 합계와 각 고객의 "07"제품에 대한 소비 금액의 합계를 계산하고, 후자를 전자로 나누어 각 고객의 "07"제품의 판매율을 계산한다. 이는 두 개의 CTE를 사용하여 필요한 데이터를 일시적으로 저장하고, 그 데이터에 대해 산술 연산과 결합을 수행함으로써 실현된다.

S-070: 영수증 내역 데이터(df_receipt)의 매출일(sales_ymd)에 대해 고객 데이터(df_customer)의 회원가입일(application_date)로부터의 경과일수를 계산하여 고객 ID(customer_id), 매출일, 회원가입일과 함께 10건을 표시하라(sales_ymd는 수치, application_date는 문자열로 데이터를 보관하고 있다는 점에 유의).

In [78]:

%%sql

WITH receipt_distinct AS (
    SELECT distinct
        customer_id,
        sales_ymd
    FROM
        receipt
)
SELECT
    c.customer_id,
    r.sales_ymd,
    c.application_date,
    EXTRACT(DAY FROM (TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD') 
                - TO_TIMESTAMP(c.application_date, 'YYYYMMDD'))) AS elapsed_days
FROM
    receipt_distinct r
JOIN
    customer c
ON
    r.customer_id = c.customer_id
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[78]:

customer_id	sales_ymd	application_date	elapsed_days
CS017515000010	20171117	20150803	837
CS019515000097	20190630	20141124	1679
CS008515000005	20170714	20150216	879
CS026414000097	20170809	20150430	832
CS034514000008	20181012	20150807	1162
CS029415000089	20180409	20150723	991
CS019411000012	20190314	20141213	1552
CS015614000006	20190802	20150211	1633
CS007515000053	20170712	20150325	840
CS024615000041	20170729	20150918	680

설명:

이 코드는 SQL로 작성되었으며, 'receipt_distinct'라는 공통 테이블 표현식(CTE)을 사용하여 'receipt'와 'customer'라는 두 개의 테이블에서 데이터를 선택하는 데 사용됩니다. 이 코드는 고객의 신청일과 해당 고객이 별도의 판매일 사이에 경과한 일수를 계산하고 있다.

다음은 코드의 단계별 분석입니다.

WITH receipt_distinct AS(...) 라고 합니다. 이는 괄호로 둘러싸인 SELECT 문에서 데이터를 일시적으로 저장하는 데 사용되는 'receipt_distinct'라는 이름의 공통 테이블 표현식(CTE)을 정의하고 있습니다.

SELECT distinct customer_id, sales_ymd FROM receipt: 'receipt' 테이블에서 distinct customer ID와 sales year-month-day를 선택한다.

SELECT c.customer_id, r.sales_ymd, c.application_date, EXTRACT(DAY FROM (TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD') - TO_THEMESTAMP(c.application_date, EXTRACT(DAY FROM (TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD') - TO_THEMESTAMP(c.application_date)) TIMESTAMP(c.application_date, 'YYYYMMDD'))) )) AS elapsed_days FROM receipt_distinct r JOIN customer c ON r.customer_id = c.customer_id LIMIT 10: 이 는 고객 ID, 판매일자, 신청일, 고객의 신청일로부터 해당 고객이 판매한 명세서 날짜까지의 경과일수를 선택한다. 경과일수 계산은 판매일자와 신청일을 타임스탬프로 변환하여 그 차이를 일수로 계산한다. 마지막으로 'receipt_distinct' CTE와 'customer' 테이블을 고객 ID로 결합하고 출력을 처음 10줄로 제한한다.

요약하면, 이 코드에서는 먼저 'receipt' 테이블에서 고객 ID와 매출 연/월/일을 선택하고, 이를 'customer' 테이블과 결합하여 고객의 신청일을 가져와서 고객의 신청일과 해당 고객의 개별 매출일 사이에 경과한 일수를 계산하고 있다. 다음으로 판매일과 신청일을 타임스탬프로 변환하고 그 차이를 일수로 계산하는 계산이 이루어진다. 이는 CTE를 이용해 필요한 데이터를 일시적으로 저장하고, 해당 데이터에 대해 산술 연산과 결합을 수행함으로써 실현된다.

S-071: 영수증 내역 데이터(df_receipt)의 매출일(sales_ymd)에 대해 고객 데이터(df_customer)의 회원가입일(application_date)로부터의 경과 개월 수를 계산하여 고객 ID(customer_id), 매출일, 회원가입일과 함께 10건 표시 (sales_ymd는 숫자, application_date는 문자열로 데이터를 보관하는 점에 유의) 1개월 미만은 반올림한다.

In [79]:

%%sql

WITH receipt_distinct AS (
    SELECT DISTINCT
        customer_id,
        sales_ymd
    FROM
        receipt
),
time_age_tbl AS(
    SELECT
        c.customer_id,
        r.sales_ymd,
        c.application_date,
        AGE(TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD'),
                    TO_TIMESTAMP(c.application_date, 'YYYYMMDD')) AS time_age
    FROM
        receipt_distinct r
    JOIN
        customer c
    ON
        r.customer_id = c.customer_id
)
SELECT
    customer_id, 
    sales_ymd, 
    application_date,
    EXTRACT(YEAR FROM time_age) * 12 
        + EXTRACT(MONTH FROM time_age) AS elapsed_months
FROM
    time_age_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[79]:

customer_id	sales_ymd	application_date	elapsed_months
CS017515000010	20171117	20150803	27
CS019515000097	20190630	20141124	55
CS008515000005	20170714	20150216	28
CS026414000097	20170809	20150430	27
CS034514000008	20181012	20150807	38
CS029415000089	20180409	20150723	32
CS019411000012	20190314	20141213	51
CS015614000006	20190802	20150211	53
CS007515000053	20170712	20150325	27
CS024615000041	20170729	20150918	22

설명:

이 코드는 SQL로 작성되었으며, 'receive_distinct'와 'time_age_tbl'이라는 두 개의 공통 테이블 표현식(CTE)을 사용하여 'receive'와 'customer'라는 두 개의 테이블에서 데이터를 선택하는 데 사용된다. 이 코드는 고객의 신청일과 해당 고객이 별도의 판매를 한 날짜 사이의 경과 개월 수를 계산한다.

다음은 코드의 단계별 예시입니다.

WITH receipt_distinct AS (...) : 괄호로 둘러싸인 SELECT 문에서 데이터를 일시적으로 저장하는 데 사용되는 'receipt_distinct'라는 이름의 공통 테이블 표현식(CTE)을 정의하고 있다.

SELECT DISTINCT customer_id, sales_ymd FROM receipt: 'receipt' 테이블에서 distinct customer ID와 sales year-month-day를 선택한다.

WITH time_age_tbl AS (...) : 이것은 괄호로 둘러싸인 SELECT 문의 데이터를 일시적으로 저장하기 위해 사용되는 'time_age_tbl'이라는 이름의 또 다른 CTE를 정의하는 것이다.

SELECT c.customer_id, r.sales_ymd, c.application_date, AGE(TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD'), TO_TIMESTAMP(c. application_date, 'YYYYMMMDD')))) AS time_age FROM receipt_distinct r JOIN customer c ON r.customer_id = c.customer_id: 고객 ID, 매출일자, 신청일, 그리고 고객의 신청일로부터 해당 고객의 신청일로부터 해당 고객이 진행한 개별 판매일까지의 경과 시간을 선택하는 것이다. 경과시간 계산은 판매일자와 신청일을 타임스탬프로 변환하고 AGE 함수를 사용하여 그 차이를 계산한다. 마지막으로 'receipt_distinct' CTE와 'customer' 테이블을 고객 ID로 결합한다.

SELECT customer_id, sales_ymd, application_date, EXTRACT(YEAR FROM time_age) * 12 + EXTRACT(MONTH FROM time_age) AS elapsed_months FROM time_age_. tbl LIMIT 10: 고객 ID, 판매일자, 신청일, 고객의 신청일로부터 해당 고객이 진행한 개별 판매일까지의 경과 개월 수를 선택한다. 경과 개월 수 계산은 경과시간의 년 성분과 월 성분을 추출하여 월수로 변환합니다. 마지막으로 출력을 처음 10줄로 제한하고 있습니다.

요약하면, 이 코드에서는 먼저 'receive' 테이블에서 고객 ID와 매출 연도를 선택하고, 'customer' 테이블과 결합하여 고객의 신청일을 가져와서 고객의 신청일로부터 해당 고객의 개별 매출일까지의 경과 개월 수를 계산합니다. 그런 다음 CTE와 AGE 함수를 사용하여 판매일과 신청일 사이의 경과 시간을 계산하고 그 결과를 월 단위로 변환한다. 이는 두 개의 CTE를 사용하여 필요한 데이터를 일시적으로 저장하고, 해당 데이터에 대해 산술 연산과 결합을 수행함으로써 이루어진다.

S-072: 영수증 내역 데이터(df_receipt)의 매출일(df_customer)에 대해 고객 데이터(df_customer)의 회원가입 신청일(application_date)로부터의 경과년수를 계산하여 고객 ID(customer_id), 매출일, 회원가입 신청일과 함께 10건 (sales_ymd는 수치, application_date는 문자열로 데이터를 보관하고 있다는 점에 유의) 1년 미만은 반올림한다.

In [80]:

%%sql

WITH receipt_distinct AS (
    SELECT distinct
        customer_id,
        sales_ymd
    FROM
        receipt
)
SELECT
    c.customer_id,
    r.sales_ymd,
    c.application_date,
    EXTRACT(YEAR FROM AGE(
        TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD'), 
        TO_TIMESTAMP(c.application_date, 'YYYYMMDD'))) AS elapsed_years
FROM
    receipt_distinct r
JOIN
    customer c
ON
    r.customer_id = c.customer_id
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[80]:

customer_id	sales_ymd	application_date	elapsed_years
CS017515000010	20171117	20150803	2
CS019515000097	20190630	20141124	4
CS008515000005	20170714	20150216	2
CS026414000097	20170809	20150430	2
CS034514000008	20181012	20150807	3
CS029415000089	20180409	20150723	2
CS019411000012	20190314	20141213	4
CS015614000006	20190802	20150211	4
CS007515000053	20170712	20150325	2
CS024615000041	20170729	20150918	1

설명:

이 SQL 코드는 구매 고객의 고객 ID, 판매일, 신청일을 취득하고 신청일과 판매일 사이의 경과 연수를 계산한다.

이 코드에서는 먼저 receipt_distinct라는 공통 테이블 식을 생성하고, receipt 테이블에서 고객 ID와 판매 날짜의 개별 조합을 선택합니다. 이렇게 하면 각 고객이 여러 번 구매하더라도 한 번만 계산되도록 보장할 수 있다.

메인 쿼리에서는 receipt_distinct 테이블에서 고객 ID, 판매일, 신청일을 선택하고 EXTRACT 함수를 사용하여 신청일과 판매일 사이의 경과 연도를 계산하고 AGE 함수로 두 날짜의 시간차를 계산하고 EXTRACT 함수로 결과의 간격에서 연도 성분을 추출한다.

결과는 각 고객의 고객 ID, 판매일, 신청일, 경과 연도를 나타내는 표로 만들어지며, LIMIT 구문은 결과 집합의 첫 10행으로 출력을 제한하는 데 사용된다.

S-073: 영수증 내역 데이터(df_receipt)의 매출일(sales_ymd)에 대해 고객 데이터(df_customer)의 회원가입일(application_date)로부터의 에포크 초 단위의 경과 시간을 계산하여 고객 ID(customer_id), 매출일, 회원가입일과 함께 10건을 표시한다. 과 함께 10건을 표시하라(단, sales_ymd는 수치, application_date는 문자열로 데이터를 보유하고 있다는 점에 유의). 단, 시간 정보는 보유하지 않으므로 각 날짜는 0시 0분 0초로 표시한다.

In [81]:

%%sql

WITH receipt_distinct AS (
    SELECT distinct
        customer_id,
        sales_ymd
    FROM
        receipt
)
SELECT
    c.customer_id,
    r.sales_ymd,
    c.application_date,
    EXTRACT(EPOCH FROM 
            TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD') - 
            TO_TIMESTAMP(c.application_date, 'YYYYMMDD')
    ) AS elapsed_epoch
FROM
    receipt_distinct r
JOIN
    customer c
ON
    r.customer_id = c.customer_id
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[81]:

customer_id	sales_ymd	application_date	elapsed_epoch
CS017515000010	20171117	20150803	72316800.000000
CS019515000097	20190630	20141124	145065600.000000
CS008515000005	20170714	20150216	75945600.000000
CS026414000097	20170809	20150430	71884800.000000
CS034514000008	20181012	20150807	100396800.000000
CS029415000089	20180409	20150723	85622400.000000
CS019411000012	20190314	20141213	134092800.000000
CS015614000006	20190802	20150211	141091200.000000
CS007515000053	20170712	20150325	72576000.000000
CS024615000041	20170729	20150918	58752000.000000

설명:

이 SQL 코드는 구매 고객의 고객 ID, 판매일, 신청일을 가져와 신청일과 판매일 사이의 경과 시간을 계산한다.

이 코드에서는 먼저 receipt_distinct라는 공통 테이블 식을 만들고, receipt 테이블에서 고객 ID와 판매 날짜의 개별 조합을 선택합니다. 이렇게 하면 각 고객이 여러 번 구매하더라도 한 번만 계산되도록 보장할 수 있다.

다음으로 메인 쿼리에서 receipt_distinct 테이블에서 고객 ID, 판매일, 신청일을 선택하고 EXTRACT 함수와 TO_TIMESTAMP 함수를 사용하여 신청일과 판매일 사이의 경과 초를 계산합니다. 를 사용하여 결과의 간격에서 초를 추출하고 있습니다.

결과는 각 고객의 고객 ID, 판매일, 신청일, 경과 초수를 나타내는 표이며, LIMIT 구문은 결과 집합의 처음 10줄로 출력을 제한하는 데 사용된다.

S-074: 영수증 내역 데이터(df_receipt)의 매출일(sales_ymd)에 대해 해당 주 월요일부터의 경과일수를 계산하여 매출일, 직전 월요일까지 10건씩 표시하라(sales_ymd는 수치로 데이터를 보관하고 있다는 점에 유의).

In [82]:

%%sql

WITH elapsed_days_tbl AS (
    SELECT
        TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') AS sales_ymd,
        EXTRACT(DOW FROM (
            TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') - 1)) AS elapsed_days
    FROM
        receipt
)
SELECT
    sales_ymd,
    elapsed_days,
    sales_ymd - CAST(elapsed_days AS INTEGER) AS monday
FROM 
    elapsed_days_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[82]:

sales_ymd	elapsed_days	monday
2018-11-03	5	2018-10-29
2018-11-18	6	2018-11-12
2017-07-12	2	2017-07-10
2019-02-05	1	2019-02-04
2018-08-21	1	2018-08-20
2019-06-05	2	2019-06-03
2018-12-05	2	2018-12-03
2019-09-22	6	2019-09-16
2017-05-04	3	2017-05-01
2019-10-10	3	2019-10-07

설명:

이 SQL 코드는 elapsed_days_tbl이라는 공통 테이블 표현식(CTE)을 정의하는 것으로 시작한다. 이 CTE는 영수증 테이블의 각 sales_ymd 날짜의 요일(0에서 6까지의 숫자, 0은 일요일)을 추출한다.

메인 쿼리에서는 elapsed_days_tbl CTE에서 sales_ymd와 elapsed_days가 선택되어 있다. 그런 다음 이 코드에서는 sales_ymd에서 elapsed_days를 빼서 새로운 날짜를 계산한다. 이는 elapsed_days를 정수로 변환하고 sales_ymd에서 빼는 방식으로 이루어집니다. 결과 날짜는 sales_ymd의 날짜에 해당하는 주 월요일이 된다.

코드 마지막에 있는 LIMIT 10 구문은 출력을 처음 10줄로 제한한다.

S-075: 고객 데이터(df_customer)에서 무작위로 1%의 데이터를 추출하여 맨 앞부터 10개를 표시하라.

In [83]:

%%sql

-- 코드 예시 1 (단순하게 할 경우. 단, 1.0% 전후로 건수 변동이 있음)
SELECT * FROM customer WHERE RANDOM() <= 0.01
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[83]:

customer_id	customer_name	gender_cd	gender	birth_day	age	postal_cd	address	application_store_cd	application_date	status_cd
CS019415000117	宮脇芽以	1	女性	1974-07-10	44	173-0036	東京都板橋区向原**********	S13019	20141114	C-20100720-D
CS040513000111	寺西奈央	1	女性	1966-06-03	52	226-0027	神奈川県横浜市緑区長津田**********	S14040	20150728	6-20090622-6
CS029402000041	浅利俊二	0	男性	1975-08-15	43	134-0013	東京都江戸川区江戸川**********	S12029	20150220	0-00000000-0
CS019712000008	梅本ヒカル	1	女性	1945-04-14	73	173-0037	東京都板橋区小茂根**********	S13019	20150613	0-00000000-0
CS015713000077	長沢結衣	1	女性	1947-10-09	71	136-0075	東京都江東区新砂**********	S13015	20150308	0-00000000-0
CS008515000014	野沢あさみ	1	女性	1959-06-09	59	157-0067	東京都世田谷区喜多見**********	S13008	20150219	9-20091212-B
CS031514000047	原口礼子	1	女性	1963-07-26	55	151-0064	東京都渋谷区上原**********	S13031	20150927	8-20090607-6
CS004313000412	春日瞳	1	女性	1984-07-26	34	176-0024	東京都練馬区中村**********	S13004	20170525	0-00000000-0
CS035513000155	板倉昌代	1	女性	1960-06-12	58	157-0075	東京都世田谷区砧公園**********	S13035	20150625	0-00000000-0
CS003512000587	大山沙知絵	1	女性	1959-04-01	59	214-0014	神奈川県川崎市多摩区登戸**********	S13003	20170306	0-00000000-0

설명:

이 SQL 코드는 customer 테이블에서 10명의 고객 중 임의의 하위 집합을 선택하는 것으로, RANDOM() 함수는 테이블의 각 행에 대해 0에서 1 사이의 임의의 값을 생성하고, WHERE 구문은 생성된 값이 0.01 이하(선택될 확률 1%)인지 여부에 따라 행을 필터링합니다. 에 따라 행을 필터링합니다.

이는 테이블의 다른 조건이나 순서에 관계없이 각 행이 선택될 확률이 동일하다는 것을 의미하며, LIMIT 절은 조건을 충족하는 처음 10개의 행으로 출력을 제한하는 데 사용된다.

In [84]:

%%sql

-- 코드 예시 2 (정중하게 한다면. 카운트를 만들어 출력 횟수를 고정)
WITH customer_tmp AS(
    SELECT
        *
        ,ROW_NUMBER() OVER(ORDER BY RANDOM()) AS row
        ,COUNT(*) OVER() AS cnt
    FROM customer
)
SELECT 
    customer_id
    ,customer_name
    ,gender_cd
    ,gender
    ,birth_day
    ,age
    ,postal_cd
    ,address
    ,application_store_cd
    ,application_date
    ,status_cd
FROM customer_tmp
WHERE row <= cnt * 0.01
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[84]:

customer_id	customer_name	gender_cd	gender	birth_day	age	postal_cd	address	application_store_cd	application_date	status_cd
CS027514000069	土屋さやか	9	不明	1967-02-10	52	251-0033	神奈川県藤沢市片瀬山**********	S14027	20141213	A-20101018-B
CS031504000012	向井剛基	0	男性	1966-09-19	52	151-0062	東京都渋谷区元代々木町**********	S13031	20150310	0-00000000-0
CS028403000022	新垣三郎	0	男性	1973-09-12	45	246-0012	神奈川県横浜市瀬谷区東野**********	S14028	20150905	0-00000000-0
CS001415000103	水谷千夏	1	女性	1976-12-08	42	144-0051	東京都大田区西蒲田**********	S13001	20150509	A-20100925-C
CS019313000145	早美由美子	1	女性	1985-02-20	34	173-0033	東京都板橋区大山西町**********	S13019	20141226	0-00000000-0
CS002412000346	荒川美嘉	1	女性	1973-03-22	46	187-0045	東京都小平市学園西町**********	S13002	20160808	0-00000000-0
CS003415000271	稲垣遥	1	女性	1975-12-14	43	201-0001	東京都狛江市西野川**********	S13003	20160630	A-20090325-8
CS029502000052	岡崎優一	0	男性	1963-03-28	56	134-0085	東京都江戸川区南葛西**********	S12029	20150803	0-00000000-0
CS002515000290	大山みあ	1	女性	1959-06-05	59	185-0023	東京都国分寺市西元町**********	S13002	20160627	C-20100730-B
CS009314000030	西川奈々	1	女性	1983-05-15	35	158-0091	東京都世田谷区中町**********	S13009	20150519	E-20100910-D

설명:

이 코드는 SQL 프로그래밍 언어를 사용하여 "customer" 테이블에서 임의의 고객 하위 집합을 선택하는 코드입니다.

이 코드에서는 먼저 "customer" 테이블에서 모든 열을 선택하고 ROW_NUMBER 함수와 COUNT 함수를 사용하여 두 개의 열을 추가하여 "customer_tmp"라는 임시 테이블을 생성합니다.

ROW_NUMBER 함수는 ORDER BY RANDOM() 구문의 결과에 따라 테이블의 각 행에 고유한 정수를 할당한다. 이렇게 하면 테이블의 행 순서가 효과적으로 무작위화된다.

OVER() 절이 있는 COUNT 함수는 테이블의 총 행 수를 계산하고 각 행의 이 수를 포함하는 "cnt"라는 새로운 열을 생성한다.

그런 다음 메인 SELECT 문은 ROW_NUMBER 함수로 생성된 "row" 열로 필터링하여 임시 테이블에서 고객의 하위 집합을 선택한다. 구체적으로 COUNT 함수로 생성된 "cnt" 열에 의해 결정되는 처음 1%의 행을 선택한다.

마지막 절 "LIMIT 10"은 결과를 하위 집합의 처음 10 개 행으로 제한하여 원래 "customer"테이블에서 10 명의 고객 무작위 샘플을 효과적으로 반환합니다.

전체적으로 이 코드는 테스트 및 탐색적 분석 목적으로 큰 테이블에서 무작위 고객 샘플을 선택하는 간단한 방법이다. 샘플링 비율(이 경우 1%)을 조정하여 더 큰 샘플이나 더 작은 샘플을 만들 수 있습니다.

S-076: 고객 데이터(df_customer)에서 성별 코드(gender_cd)의 비율에 따라 무작위로 10%의 데이터를 층화 추출하고, 성별 코드별로 건수를 집계하라.

In [85]:

%%sql

-- 코드 예시 1
WITH cusotmer_random AS (
    SELECT
        customer_id,
        gender_cd,
        cnt
    FROM (
        SELECT
            gender_cd,
            ARRAY_AGG(customer_id ORDER BY RANDOM()) AS customer_r,
            COUNT(1) AS cnt
        FROM
            customer
        GROUP BY gender_cd
    )sample, UNNEST(customer_r) AS customer_id
),
cusotmer_rownum AS(
    SELECT
        * ,
        ROW_NUMBER() OVER(PARTITION BY gender_cd) AS rn
    FROM
        cusotmer_random
)
SELECT
    gender_cd,
    COUNT(1) AS customer_num
FROM
    cusotmer_rownum
WHERE
    rn <= cnt * 0.1
GROUP BY
    gender_cd
;

 * postgresql://padawan:***@db:5432/dsdojo_db
3 rows affected.

Out[85]:

gender_cd	customer_num
0	298
1	1791
9	107

설명:

이 SQL 코드는 "customer"라는 테이블에서 임의의 고객 샘플을 선택하고, 샘플 내 각 성별에 해당하는 고객 수를 계산하고 있다.

아래는 코드의 각 파트가 수행하는 작업의 내역입니다.

WITH 구문: 이 구문은 후속 SELECT 문에서 사용할 두 개의 임시 테이블인 'customer_random'과 'customer_rownum'을 생성한다.

customer_random 테이블. 이 테이블은 'customer' 테이블에서 고객의 무작위 샘플을 선택하고 각 고객에게 성별에 따라 'customer_r' 값을 할당하며, 'cnt' 열은 각 성별 그룹의 총 고객 수를 계산한다.

customer_rownum 테이블. 이 테이블은 'customer_random' 테이블의 각 고객에게 성별로 구분된 행 번호("rn")를 추가한다.

SELECT 문. 이 문은 각 성별 그룹에서 행 번호가 해당 성별 그룹 내 총 고객 수의 10% 이하인 고객 수를 계산한다(즉, 행 번호 기준 상위 10%의 고객이다). 결과는 'gender_cd'와 'customer_num'의 두 열을 가진 표로, 'gender_cd'는 성별 코드, 'customer_num'은 해당 성별 그룹에서 행 번호로 상위 10%에 속하는 고객 수이다.

전체적으로 이 코드는 더 큰 데이터 세트에서 고객의 무작위 샘플을 선택하고 그 성별 분포를 분석하는 데 편리하다.

In [86]:

%%sql

-- 코드 예시 2
WITH cusotmer_random AS (
SELECT 
    * , 
    ROW_NUMBER() OVER(PARTITION BY gender_cd ORDER BY RANDOM()) AS rn,
    COUNT(1) OVER(PARTITION BY gender_cd) cnt
FROM 
    customer
)
SELECT
    gender_cd,
    COUNT(1) AS customer_num
FROM
    cusotmer_random
WHERE 
    rn <= cnt * 0.1
GROUP BY
    gender_cd
;

 * postgresql://padawan:***@db:5432/dsdojo_db
3 rows affected.

Out[86]:

gender_cd	customer_num
9	107
0	298
1	1791

설명:

이 코드는 SQL 프로그래밍 언어를 사용하여 "customer" 테이블에서 고객의 하위 집합을 무작위로 선택하고 성별에 따라 그룹화하여 각 성별 그룹의 고객 수를 반환하는 코드입니다.

이 코드에서는 먼저 'customer' 테이블에서 모든 열을 선택하고 ROW_NUMBER 함수와 COUNT 함수를 사용하여 두 개의 열을 추가하여 'cusotmer_random'이라는 임시 테이블을 생성합니다.

ROW_NUMBER 함수는 ORDER BY RANDOM() 구문의 결과에 따라 테이블의 각 행에 고유한 정수를 할당한다. 이는 테이블의 행 순서를 효과적으로 무작위화합니다.

ROW_NUMBER 함수의 PARTITION BY gender_cd 구문은 각 성별 그룹에 대해 개별적으로 무작위 순서를 보장한다.

OVER(PARTITION BY gender_cd) 절이 있는 COUNT 함수는 각 성별 그룹의 총 행 수를 계산하고 각 행의 이 카운트를 포함하는 'cnt'라는 새로운 열을 생성한다.

다음으로 메인 SELECT 문은 ROW_NUMBER 함수로 생성된 'rn' 열로 필터링하여 임시 테이블에서 고객의 하위 집합을 선택한다. 구체적으로 COUNT 함수로 생성된 "cnt" 컬럼에 의해 결정되는 각 성별 그룹 행의 처음 10%를 선택한다.

WHERE 절은 'cusotmer_random' 테이블의 행을 필터링하여 'rn' 값이 각 성별 그룹의 'cnt' 값의 10% 이하인 것만 포함한다.

마지막으로, 메인 SELECT 문은 gender_cd로 행을 그룹화하고 각 그룹의 행 수를 반환하여 효과적으로 무작위 샘플의 각 성별 그룹의 고객 수를 제공합니다.

전체적으로 이 코드는 큰 테이블에서 고객의 하위 집합을 무작위로 선택하면서 샘플이 원래 테이블과 마찬가지로 각 성별 그룹의 비율을 포함하도록 보장하는 방법이다. 샘플링 비율(이 경우 10%)은 더 크거나 작은 샘플을 만들기 위해 조정할 수 있습니다.

S-077: 영수증 명세서 데이터(df_receipt)의 매출 금액을 고객 단위로 합산하고, 합산한 매출 금액의 편차를 추출하라. 단, 이상값은 매출금액 합계를 로그화한 후 평균과 표준편차를 계산하여 그 평균에서 3σ 이상 벗어난 것으로 한다(자연대수, 상용대수 모두 가능). 결과는 10건 표시하라.

In [87]:

%%sql

WITH sales_amount AS(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount,
        LN(SUM(amount) + 0.5) AS log_sum_amount
    FROM
        receipt
    GROUP BY 
        customer_id
)
SELECT 
    customer_id,
    sum_amount,
    log_sum_amount
FROM
    sales_amount
CROSS JOIN (
    SELECT
        AVG(log_sum_amount) AS avg_amount,
        STDDEV_POP(log_sum_amount) AS std_amount
    FROM sales_amount   
) stats_amount
WHERE
    ABS(log_sum_amount - avg_amount) / std_amount > 3
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.

Out[87]:

customer_id	sum_amount	log_sum_amount
ZZ000000000000	12395003	16.332804005823312

설명:

이 코드는 SQL 프로그래밍 언어를 사용하여 대수 변환된 구매 금액을 기준으로 구매 금액 합계가 표준에서 크게 벗어난 고객을 식별합니다.

이 코드에서는 먼저 "영수증" 테이블에서 customer_id와 구매 금액의 합계를 선택하고 customer_id로 그룹화하여 "sales_amount"라는 임시 테이블을 생성합니다. 또한, 구매 금액 합계는 LN 함수를 사용하여 대수 변환하여 0의 대수를 취하지 않도록 0.5를 더합니다.

다음으로 메인 SELECT 문은 임시 테이블에서 customer_id, sum_amount, log_sum_amount 컬럼을 선택한다. 또한 CROSS JOIN을 사용하여 "stats_amount"라는 또 다른 임시 테이블에 참여한다." "stats_amount"는 "sales_amount" 테이블에서 log_sum_amount 열의 평균과 모표준편차를 선택하여 생성한다.

WHERE 절은 "sales_amount" 테이블의 행을 필터링하여 log_sum_amount와 평균 log_sum_amount의 차이의 절대값을 log_sum_amount의 표준편차로 나누어 3보다 큰 것들만 포함한다. 이는 구매 금액이 평균값에서 3표준편차 이상 떨어져 있는 고객을 식별하는 방법이며, 통계 분석에서 이상값을 식별하는 일반적인 임계값이다.

마지막으로, 메인 SELECT 문은 출력을 10줄로 제한한다.

전체적으로 이 코드는 구매 금액을 대수 척도로 변환하고 통계적 이상치 탐지 기준을 적용하여 데이터 세트의 평균 구매 금액에 비해 현저하게 큰 구매 또는 작은 구매를 한 고객을 식별하는 방법이다.

S-078: 영수증 내역 데이터(df_receipt)의 매출 금액(amount)을 고객 단위로 합산하고, 합산된 매출 금액의 외곽값을 추출한다. 단, 고객 ID가 “Z”로 시작하는 것은 비회원을 의미하므로 제외하여 계산한다. 여기서 이상값은 1사분위와 3사분위의 차이인 IQR을 이용하여 ‘1사분위수 -1.5×IQR’ 이하 또는 ‘3사분위수+1.5×IQR’을 초과하는 것으로 한다. 결과는 10건 표시한다.

In [88]:

%%sql

WITH sales_amount AS(
    SELECT 
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY 
        customer_id
)
SELECT
    customer_id,
    sum_amount
FROM
    sales_amount
CROSS JOIN (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY sum_amount) AS amount_25per,
        PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY sum_amount) AS amount_75per
    FROM sales_amount   
) stats_amount
WHERE
    sum_amount < amount_25per - (amount_75per - amount_25per) * 1.5 
    OR amount_75per + (amount_75per - amount_25per) * 1.5 < sum_amount
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.

Out[88]:

customer_id	sum_amount
CS013415000226	8362
CS011415000041	9454
CS014514000004	8872
CS021514000008	12839
CS014515000007	9763
CS040415000220	10158
CS028415000161	8465
CS034515000173	10074
CS022515000065	12903
CS007514000094	15735

설명:

이 코드는 SQL 프로그래밍 언어를 사용하여 데이터베이스를 쿼리하고 있다. 구체적으로 CTE(Common Table Expression)를 사용하여 'sales_amount'라는 임시 테이블을 생성하고 'receive' 테이블의 각 고객에 대한 총 매출액을 계산한다.

CTE의 WHERE 구문은 ID가 "Z"로 시작하는 고객을 필터링하여 제외하며, GROUP BY 구문은 고객 ID별로 매출을 그룹화한다.

메인 쿼리에서는 'sales_amount' 테이블과 PERCENTILE_CONT 함수를 사용하여 매출 금액의 25번째 백분위수와 75번째 백분위수를 계산하는 서브 쿼리 사이에 CROSS JOIN이 수행된다. 이 백분위수는 "stats_amount"라는 임시 테이블에 저장된다.

마지막으로 메인 쿼리의 WHERE 구문에서 25분위수나 75분위수에서 사분위수 범위(IQR)의 1.5배 이상 떨어져 있는 매출 금액을 필터링하여 제외한다. 이는 데이터 세트의 이상값을 식별하는 일반적인 방법이다. 결과는 상위 10개 라인으로 제한됩니다.

전체적으로 이 코드는 데이터베이스를 조회하여 사분위수 범위 내의 위치에 따라 동종업계에 비해 비정상적으로 높은 또는 낮은 매출 금액을 가진 고객을 식별하는 것이다.

S-079: 상품 데이터(df_product)의 각 항목에 대해 결손 수를 확인하라.

In [89]:

%%sql

SELECT 
    SUM(
        CASE WHEN product_cd IS NULL THEN 1 ELSE 0 END
    ) AS product_cd,
    SUM(
        CASE WHEN category_major_cd IS NULL THEN 1 ELSE 0 END
    ) AS category_major_cd,
    SUM(
        CASE WHEN category_medium_cd IS NULL THEN 1 ELSE 0 END
    ) AS category_medium_cd,
    SUM(
        CASE WHEN category_small_cd IS NULL THEN 1 ELSE 0 END
    ) AS category_small_cd,
    SUM(
        CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END
    ) AS unit_price,
    SUM(
        CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END
    ) AS unit_cost
FROM product LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.

Out[89]:

product_cd	category_major_cd	category_medium_cd	category_small_cd	unit_price	unit_cost
0	0	0	0	7	7

설명:

이 코드는 SQL 프로그래밍 언어를 사용하여 데이터베이스를 쿼리하고 있다. 구체적으로 "product" 테이블을 조회하고 SUM 문과 CASE 문을 조합하여 테이블의 각 열에서 누락된 값(NULL)의 개수를 계산하고 있습니다.

이 쿼리에서는 6개의 컬럼을 선택하고 테이블의 컬럼 이름과 일치하는 별칭을 부여하고 있다. 'product_cd', 'category_major_cd', 'category_medium_cd', 'category_small_cd', 'unit_price', 'unit_cost'.

각 컬럼에 대해 CASE 문은 값이 NULL인지 여부를 확인하고, NULL이면 1을, 그렇지 않으면 0을 반환한다. 그리고 SUM 함수로 1과 0을 합하여 각 열의 NULL 값의 총 개수를 구한다.

LIMIT 10 구문은 결과를 테이블의 처음 10개의 행으로 제한하지만, 이 경우 요약 통계에만 관심이 있기 때문에 별 의미가 없을 것입니다.

전체적으로 이 코드는 "product" 테이블의 누락된 데이터를 확인하고 각 열의 누락된 값의 수에 대한 간단한 요약을 얻을 수 있는 간단한 방법이다.

S-080: 상품 데이터(df_product) 중 어느 한 항목에 결손이 발생한 레코드를 모두 삭제한 새로운 상품 데이터를 생성한다. 또한, 삭제 전후의 건수를 표시하고, 079에서 확인한 건수만큼 감소한 것도 확인해야 한다.

In [90]:

%%sql

DROP TABLE IF EXISTS product_1;
CREATE TABLE product_1 AS (
    SELECT * FROM product
    WHERE unit_price IS NOT NULL AND unit_cost IS NOT NULL
);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
10023 rows affected.

Out[90]:

[]

In [91]:

%%sql

SELECT '삭제전', COUNT(1) FROM product;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.

Out[91]:

?column?	count
삭제전	10030

In [92]:

%%sql

SELECT '삭제후', COUNT(1) FROM product_1;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.

Out[92]:

?column?	count
삭제후	10023

설명:

이 코드는 SQL 프로그래밍 언어를 사용하여 'product' 테이블에서 'unit_price' 열과 'unit_cost' 열이 모두 NULL이 아닌 행만 선택하여 'product_1'이라는 새로운 테이블을 생성하고 있다.

첫 번째 행의 'DROP TABLE IF EXISTS product_1;'은 'product_1' 테이블이 이미 존재하는지 확인하고, 존재하는 경우 삭제하여 새로 생성하는 테이블과 충돌을 피하기 위한 안전장치이다.

두 번째 행의 'CREATE TABLE product_1 AS (...) ;'는 'product_1'이라는 새로운 테이블을 생성하고, 그 내용은 SELECT 문 결과를 기반으로 한다는 것을 지정한다.

SELECT 문은 product 테이블에서 모든 열을 선택하지만, WHERE 구문이 포함되어 unit_price 또는 unit_cost 열이 NULL인 행을 필터링한다.

전체적으로 이 코드는 원래의 "product" 테이블에서 "unit_price"와 "unit_cost" 두 열에 모두 유효한 데이터가 있는 행만 포함하는 새 테이블을 생성합니다. 이는 데이터를 정리하고 분석 및 모델링에 대비하기 위한 일반적인 방법이다. 누락된 데이터는 일부 분석 기법에서 문제를 일으킬 수 있기 때문이다.