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

참고(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-041: 영수증 내역 데이터(df_receipt)의 매출 금액(amount)을 날짜(sales_ymd)별로 집계하여, 지난번 매출이 있었던 날로부터의 매출 금액 증감을 계산하라. 그리고 결과를 10건 표시하라.

In [45]:

%%sql

WITH sales_amount_by_date AS (
    SELECT
        sales_ymd,
        SUM(amount) AS amount
    FROM receipt
    GROUP BY
        sales_ymd
),
sales_amount_by_date_with_lag as (
    SELECT
        sales_ymd,
        LAG(sales_ymd, 1) OVER(ORDER BY sales_ymd) lag_ymd,
        amount,
        LAG(amount, 1) OVER(ORDER BY sales_ymd) AS lag_amount
    FROM sales_amount_by_date
)
SELECT
    sales_ymd,
    amount,
    lag_ymd,
    lag_amount,
    amount - lag_amount AS diff_amount
FROM sales_amount_by_date_with_lag
ORDER BY
    sales_ymd
LIMIT 10
;

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

Out[45]:

sales_ymd	amount	lag_ymd	lag_amount	diff_amount
20170101	33723	None	None	None
20170102	24165	20170101	33723	-9558
20170103	27503	20170102	24165	3338
20170104	36165	20170103	27503	8662
20170105	37830	20170104	36165	1665
20170106	32387	20170105	37830	-5443
20170107	23415	20170106	32387	-8972
20170108	24737	20170107	23415	1322
20170109	26718	20170108	24737	1981
20170110	20143	20170109	26718	-6575

설명:

이 코드는 SQL 언어로 작성되었으며, 'WITH' 절을 사용하여 'sales_amount_by_date'와 'sales_amount_by_date_with_lag'라는 두 개의 임시 테이블을 생성하고 있다. 그리고 'sales_amount_by_date_with_lag' 테이블에서 특정 열을 선택하고 'LAG' 함수를 사용하여 연속된 날짜 간의 매출 금액 차이를 계산하고 있습니다.

구체적으로 이 코드는 다음과 같은 처리를 수행한다.

첫 번째 'WITH' 절은 'sales_amount_by_date'라는 임시 테이블을 생성하고 'RECIPATE' 테이블의 각 날짜별 매출 금액의 합계를 계산한다.

두 번째 'WITH' 절은 'sales_amount_by_date_with_lag'라는 또 다른 임시 테이블을 생성하고, 'LAG' 함수를 사용하여 전날의 매출 금액과 연속된 날짜 사이의 매출 금액의 차이를 계산한다.

마지막 'SELECT' 문에서는 'sales_amount_by_date_with_lag' 테이블에서 판매일, 판매금액, 전날, 이전 판매금액, 연속된 날짜의 판매금액 차이 등의 열을 선택한다.

그리고 결과는 판매 날짜의 오름차순으로 정렬되어 처음 10개의 행으로 제한된다.

요약하면, 이 코드는 "LAG" 함수를 사용하여 "영수증" 테이블의 연속된 날짜 사이의 판매 금액 차이를 계산하고 처음 10개의 행의 결과를 표시한다.

S-042: 영수증 내역 데이터(df_receipt)의 매출 금액(amount)을 날짜(sales_ymd)별로 집계하고, 각 날짜의 데이터에 대해 이전, 전전, 3회 전에 매출이 있었던 날의 데이터를 결합하라. 그리고 결과를 10건 표시하라.

In [46]:

%%sql

# 코드 예시 1: 세로형 케이스
WITH sales_amount_by_date AS (
    SELECT
        sales_ymd,
        SUM(amount) AS amount
    FROM receipt
    GROUP BY
        sales_ymd
),
sales_amount_lag_date AS (
    SELECT
        sales_ymd,
        LAG(sales_ymd, 3) OVER (ORDER BY sales_ymd) AS lag_date_3,
        amount
    FROM sales_amount_by_date
)
SELECT
    a.sales_ymd,
    a.amount,
    b.sales_ymd AS lag_ymd,
    b.amount AS lag_amount
FROM sales_amount_lag_date a
JOIN sales_amount_lag_date b
ON
    (
        a.lag_date_3 IS NULL
        OR a.lag_date_3 <= b.sales_ymd
    )
    AND b.sales_ymd < a.sales_ymd
ORDER BY
    sales_ymd,
    lag_ymd
LIMIT 10
;

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

Out[46]:

sales_ymd	amount	lag_ymd	lag_amount
20170102	24165	20170101	33723
20170103	27503	20170101	33723
20170103	27503	20170102	24165
20170104	36165	20170101	33723
20170104	36165	20170102	24165
20170104	36165	20170103	27503
20170105	37830	20170102	24165
20170105	37830	20170103	27503
20170105	37830	20170104	36165
20170106	32387	20170103	27503

설명:

이 코드도 SQL 언어로 작성되었으며, 'WITH' 절을 사용하여 'sales_amount_by_date'와 'sales_amount_lag_date'라는 두 개의 임시 테이블을 생성하고 있다. 그리고 'sales_amount_lag_date' 테이블에 대해 자체 결합을 수행하여 특정 조건에 따라 결과를 필터링하고 있습니다.

구체적으로 이 코드는 다음과 같은 처리를 수행하고 있습니다.

첫 번째 'WITH' 절은 'sales_amount_by_date'라는 임시 테이블을 생성하고, 'receive' 테이블의 각 날짜별 매출 금액의 합계를 계산한다.

두 번째 'WITH' 절은 'sales_amount_lag_date'라는 또 다른 임시 테이블을 생성하고 'LAG' 함수를 사용하여 'sales_amount_by_date' 테이블의 3일 전 판매일과 각 날짜의 판매금액을 계산한다.

마지막 'SELECT' 문은 'sales_amount_lag_date' 테이블에서 특정 열을 선택하여 자신과 자체 결합을 수행한다. 결합 조건은 한 행의 판매일이 다른 행의 판매일보다 작고, 두 번째 행의 판매일이 3일 전의 첫 번째 행의 판매일 이하인 행만 포함하도록 결과를 필터링한다.

그리고 결과는 판매일과 이전 판매일의 오름차순으로 정렬되어 처음 10개의 행으로 제한됩니다.

요약하면, 이 코드는 'receipt' 테이블의 각 날짜별 매출 날짜와 매출 금액이 포함된 테이블에 대해 자체 결합을 실행하고 있습니다. 결합 조건은 한 행의 매출 날짜가 다른 행의 매출 날짜보다 작고, 두 번째 행의 매출 날짜가 3일 전의 첫 번째 행의 매출 날짜 이하인 행만 포함하도록 결과를 필터링합니다. 결과는 처음 10개 행의 판매일, 판매금액, 이전 판매일, 이전 판매금액이 표시됩니다.

In [47]:

%%sql

# 코드 예시 2: 가로형 케이스
WITH sales_amount_by_date AS (
    SELECT
        sales_ymd,
        SUM(amount) AS amount
    FROM receipt
    GROUP BY
        sales_ymd
),
sales_amount_with_lag AS (
    SELECT
        sales_ymd,
        amount, 
        LAG(sales_ymd, 1) OVER (ORDER BY sales_ymd) AS lag_ymd_1,
        LAG(amount, 1) OVER (ORDER BY sales_ymd) AS lag_amount_1,
        LAG(sales_ymd, 2) OVER (ORDER BY sales_ymd) AS lag_ymd_2,
        LAG(amount, 2) OVER (ORDER BY sales_ymd) AS lag_amount_2,
        LAG(sales_ymd, 3) OVER (ORDER BY sales_ymd) AS lag_ymd_3,
        LAG(amount, 3) OVER (ORDER BY sales_ymd) AS lag_amount_3
    FROM sales_amount_by_date
)
SELECT
    sales_ymd,
    amount, 
    lag_ymd_1,
    lag_amount_1,
    lag_ymd_2,
    lag_amount_2,
    lag_ymd_3,
    lag_amount_3
FROM sales_amount_with_lag
WHERE
    lag_ymd_3 IS NOT NULL
ORDER BY
    sales_ymd
LIMIT 10
;

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

Out[47]:

sales_ymd	amount	lag_ymd_1	lag_amount_1	lag_ymd_2	lag_amount_2	lag_ymd_3	lag_amount_3
20170104	36165	20170103	27503	20170102	24165	20170101	33723
20170105	37830	20170104	36165	20170103	27503	20170102	24165
20170106	32387	20170105	37830	20170104	36165	20170103	27503
20170107	23415	20170106	32387	20170105	37830	20170104	36165
20170108	24737	20170107	23415	20170106	32387	20170105	37830
20170109	26718	20170108	24737	20170107	23415	20170106	32387
20170110	20143	20170109	26718	20170108	24737	20170107	23415
20170111	24287	20170110	20143	20170109	26718	20170108	24737
20170112	23526	20170111	24287	20170110	20143	20170109	26718
20170113	28004	20170112	23526	20170111	24287	20170110	20143

설명:

이 코드도 SQL 언어로 작성되었으며, 'WITH' 절을 사용하여 'sales_amount_by_date'라는 이름의 임시 테이블을 생성하고 있다. 다음으로 'LAG' 함수를 사용하여 'sales_amount_with_lag'라는 이름의 또 다른 임시 테이블에 추가 열을 생성하고 있다. 마지막으로 'sales_amount_with_lag' 테이블에서 특정 컬럼을 선택하여 특정 조건에 따라 결과를 필터링하고 있습니다.

구체적으로 이 코드에서는 다음과 같은 처리를 수행하고 있습니다.

첫 번째 "WITH" 절은 "sales_amount_by_date"라는 임시 테이블을 생성하고 "receive" 테이블의 각 날짜별 매출 금액의 합계를 계산한다.

두 번째 "WITH" 절은 "sales_amount_with_lag"라는 또 다른 임시 테이블을 생성하고 "sales_amount_by_date" 테이블의 각 날짜의 판매 금액과 이전 3 일간의 판매 금액을 계산하기 위해 "LAG" 함수를 사용합니다.

마지막 "SELECT" 문은 "sales_amount_with_lag" 테이블에서 특정 열을 선택하고, 3 일 전 날짜가 NULL이 아닌 행만 포함하도록 결과를 필터링한다. 즉, 테이블의 첫 두 행은 세 번째 이전 날짜가 없기 때문에 제외된다.

그런 다음 결과를 판매 날짜의 오름차순으로 정렬하여 처음 10 개 행으로 제한합니다.

요약하면, 이 코드는 "영수증" 테이블의 각 날짜의 판매 금액의 합계를 계산하고 "LAG" 함수를 사용하여 각 날짜의 이전 3 일의 판매 날짜와 판매 금액을 계산한다. 결과는 각 날짜의 판매일과 판매금액, 이전 3일간의 판매일과 판매금액을 표시한다. 결과는 처음 두 행은 세 번째 이전 날짜가 없기 때문에 제외하도록 필터링된다. 결과의 처음 10개의 행은 매출 날짜의 오름차순으로 표시됩니다.

S-043: 영수증 내역 데이터(df_receipt)와 고객 데이터(df_customer)를 결합하여 성별 코드(gender_cd)와 연령(age에서 계산)별 매출 금액(amount)을 합산한 매출 요약 데이터를 생성한다. 성별 코드는 0은 남성, 1은 여성, 9는 알 수 없음을 나타낸다.

단, 항목 구성은 연령, 여성 매출금액, 남성 매출금액, 남성 매출금액, 성별 미상 매출금액의 4개 항목으로 구성한다(세로로 연령, 가로로 성별 교차 집계). 또한 연령은 10세 단위의 계급으로 한다.

In [48]:

%%sql

DROP TABLE IF EXISTS sales_summary;

CREATE TABLE sales_summary AS
    WITH gender_era_amount AS (
        SELECT
            TRUNC(age / 10) * 10 AS era,
            c.gender_cd,
            SUM(r.amount) AS amount
        FROM customer c
        JOIN receipt r
        ON
            c.customer_id = r.customer_id
        GROUP BY
            era,
            c.gender_cd
    )
    SELECT
        era,
        SUM(CASE WHEN gender_cd = '0' THEN amount END) AS male,
        SUM(CASE WHEN gender_cd = '1' THEN amount END) AS female,
        SUM(CASE WHEN gender_cd = '9' THEN amount END) AS unknown
    FROM gender_era_amount
    GROUP BY
        era
    ORDER BY
        era
;

SELECT
    *
FROM sales_summary
;

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

Out[48]:

era	male	female	unknown
10.0	1591	149836	4317
20.0	72940	1363724	44328
30.0	177322	693047	50441
40.0	19355	9320791	483512
50.0	54320	6685192	342923
60.0	272469	987741	71418
70.0	13435	29764	2427
80.0	46360	262923	5111
90.0	None	6260	None

설명:

이 SQL 코드에서는 연령대(시대)와 성별에 따른 매출 금액의 합계를 정리한 sales_summary라는 테이블을 생성합니다.

먼저 고객 테이블과 영수증 테이블을 결합하여 gender_era_amount라는 공통 테이블 표현식(CTE)을 생성합니다. 이 CTE는 연령대(시대)와 성별에 따라 레코드를 그룹화하고 총 매출액을 계산한다.

그런 다음 gender_era_amount CTE의 데이터를 집계하여 sales_summary 테이블이 생성된다. 이 테이블에는 시대, 남성, 여성, 여성, 알 수 없음의 4개 컬럼이 있으며, SELECT 구문 내의 SUM(CASE...) 문은 성별별 매출 금액을 별도의 열로 피벗하는 데 사용됩니다.

마지막으로 SELECT 문을 사용하여 새로 생성된 sales_summary 테이블에서 모든 행을 가져옵니다.

S-044: 043에서 만든 매출 요약 데이터(df_sales_summary)는 성별 매출을 가로로 나열한 데이터였다. 이 데이터에서 성별을 세로로 가져와서 연령, 성별 코드, 매출 금액의 세 가지 항목으로 변환하라. 단, 성별 코드는 남성을 ’00’, 여성을 ’01’, 알 수 없음을 ’99’로 한다.

In [49]:

%%sql

-- SQL에 적합하지 않기 때문에 다소 강압적으로 기술한다(카테고리 수가 많을 때는 매우 긴 SQL이 될 수 있다는 점에 유의)
SELECT era, '00' AS gender_cd , male AS amount FROM sales_summary
UNION ALL
SELECT era, '01' AS gender_cd, female AS amount FROM sales_summary
UNION ALL
SELECT era, '99' AS gender_cd, unknown AS amount FROM sales_summary
;

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

Out[49]:

era	gender_cd	amount
10.0	00	1591
20.0	00	72940
30.0	00	177322
40.0	00	19355
50.0	00	54320
60.0	00	272469
70.0	00	13435
80.0	00	46360
90.0	00	None
10.0	01	149836
20.0	01	1363724
30.0	01	693047
40.0	01	9320791
50.0	01	6685192
60.0	01	987741
70.0	01	29764
80.0	01	262923
90.0	01	6260
10.0	99	4317
20.0	99	44328
30.0	99	50441
40.0	99	483512
50.0	99	342923
60.0	99	71418
70.0	99	2427
80.0	99	5111
90.0	99	None

설명:

이 코드는 SQL로 작성되었으며, sales_summary라는 테이블에서 데이터를 가져온다. 이 코드의 목적은 sales_summary 테이블의 데이터를 보다 쉽게 분석할 수 있는 형식으로 변환하는 것이다.

SELECT 문을 사용하여 sales_summary 테이블에서 데이터를 가져오고, UNION ALL 연산자를 사용하여 여러 SELECT 문 결과를 하나의 결과 집합으로 결합한다.

첫 번째 SELECT 문은 sales_summary 테이블에서 데이터를 가져와 era와 male 컬럼을 선택한다. 또한 gender_cd라는 새로운 컬럼도 포함되며, 이 컬럼의 값은 00이다. 이 컬럼은 문자열 리터럴 '00' AS gender_cd를 사용하여 추가되었으며, AS 키워드는 컬럼의 이름을 gender_cd로 변경하는 데 사용된다. 이 SELECT 문 결과 집합에는 era, gender_cd, amount의 세 개의 컬럼이 포함되어 있다.

두 번째 SELECT 문은 첫 번째 문과 비슷하지만, era와 female 열을 선택하고 gender_cd 열을 01로 설정하고 있다. 이 SELECT 문의 결과 집합에도 era, gender_cd, amount의 세 개의 컬럼이 포함되어 있다.

이 SELECT 문 결과 집합에도 era, gender_cd, amount 세 개의 컬럼이 포함되어 있다.

마지막으로 UNION ALL 연산자를 사용하여 3개의 SELECT 문 결과 집합을 하나의 결과 집합으로 합친다. 결과 테이블은 era와 gender_cd의 각 조합에 대해 하나의 행을 가지며, amount 열은 gender_cd의 값에 따라 남성, 여성 또는 알 수 없음 중 하나의 값을 포함하게 된다.

S-045: 고객 데이터(df_customer)의 생년월일(birth_day)은 날짜형으로 데이터를 보유하고 있다. 이를 YYYYMMDD 형식의 문자열로 변환하여 고객 ID(customer_id)와 함께 10건 표시하라.

In [50]:

%%sql

SELECT
    customer_id, 
    TO_CHAR(birth_day, 'YYYYMMDD') AS birth_day
FROM customer 
LIMIT 10
;

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

Out[50]:

customer_id	birth_day
CS021313000114	19810429
CS037613000071	19520401
CS031415000172	19761004
CS028811000001	19330327
CS001215000145	19950329
CS020401000016	19740915
CS015414000103	19770809
CS029403000008	19730817
CS015804000004	19310502
CS033513000180	19620711

설명:

이 코드는 SQL로 작성되었으며, customer라는 테이블에서 데이터를 가져온다. 이 코드의 목적은 customer 테이블에서 customer_id와 birth_day 컬럼을 가져오고, birth_day 컬럼을 특정 방식으로 서식을 지정하는 것이다.

SELECT 문을 사용하여 customer 테이블에서 데이터를 가져오고 LIMIT 절을 사용하여 반환되는 행 수를 10행으로 제한하고 있다.

TO_CHAR 함수는 birth_day 컬럼을 지정된 형식의 문자열로 변환하는 데 사용된다. 이 경우 형식은 'YYYYMMDD'이며, 생일의 년, 월, 일을 나타냅니다. 결과 문자열의 길이는 8자이며, 연도는 4자, 월은 2자, 일은 2자로 표현된다.

AS 키워드는 결과 컬럼의 이름을 birth_day로 변경하는 데 사용된다. 이 SELECT 문 결과 집합에는 customer_id와 birth_day라는 두 개의 열이 포함되어 있으며, birth_day 열에는 각 고객의 생년월일이 지정된 형식으로 포함되어 있습니다.

전체적으로 이 코드는 customer 테이블에서 customer_id와 birth_day 컬럼을 가져오고 birth_day 컬럼을 특정 방식으로 서식을 지정하고 싶을 때 유용하다. 이 형식화된 데이터는 분석이나 보고서 작성에 유용합니다.

S-046: 고객 데이터(df_customer)의 신청일(application_date)은 YYYYMMDD 형식의 문자열 형태로 데이터를 보유하고 있다. 이를 날짜형으로 변환하여 고객 ID(customer_id)와 함께 10건씩 표시하라.

In [51]:

%%sql

SELECT
    customer_id,
    TO_DATE(application_date, 'YYYYMMDD') AS application_date
FROM customer
LIMIT 10
;

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

Out[51]:

customer_id	application_date
CS021313000114	2015-09-05
CS037613000071	2015-04-14
CS031415000172	2015-05-29
CS028811000001	2016-01-15
CS001215000145	2017-06-05
CS020401000016	2015-02-25
CS015414000103	2015-07-22
CS029403000008	2015-05-15
CS015804000004	2015-06-07
CS033513000180	2015-07-28

설명:

이 코드는 SQL로 작성되었으며, customer라는 테이블에서 데이터를 가져온다. 이 코드의 목적은 customer 테이블에서 customer_id 열과 application_date 열을 가져와 application_date 열을 날짜 형식으로 변환하는 것이다.

SELECT 문을 사용하여 customer 테이블에서 데이터를 가져오고 LIMIT 절을 사용하여 반환되는 행 수를 10행으로 제한하고 있다.

TO_DATE 함수는 application_date 컬럼을 문자열에서 날짜 형식으로 변환하는 데 사용된다. 이 경우 형식은 'YYYYMMDD'이며, 신청일의 년, 월, 일을 나타낸다. 결과적으로 날짜 값은 yyyy-mm-dd 형식이 된다.

AS 키워드는 결과 컬럼의 이름을 application_date로 바꾸기 위해 사용된다. 이 SELECT 문 결과 집합에는 customer_id와 application_date라는 두 개의 컬럼이 포함되며, application_date 컬럼에는 각 고객의 신청 날짜가 날짜 형식으로 포함되어 있다.

전체적으로 이 코드는 고객 테이블에서 customer_id와 application_date 컬럼을 가져와 application_date 컬럼을 날짜 형식으로 변환하고 싶을 때 유용하다. 이 형식화된 데이터는 특히 날짜 계산이나 비교가 포함되는 경우 분석이나 보고서 작성에 유용합니다.

S-047: 영수증 내역 데이터(df_receipt)의 매출일(sales_ymd)은 YYYYMMDD 형식의 숫자형으로 데이터를 보유하고 있다. 이를 날짜형으로 변환하여 영수증 번호(receipt_no), 영수증 하위번호(receipt_sub_no)와 함께 10건씩 표시하라.

In [52]:

%%sql

SELECT  
    receipt_no, 
    receipt_sub_no,
    TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') AS sales_ymd
FROM receipt 
LIMIT 10
;

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

Out[52]:

receipt_no	receipt_sub_no	sales_ymd
112	1	2018-11-03
1132	2	2018-11-18
1102	1	2017-07-12
1132	1	2019-02-05
1102	2	2018-08-21
1112	1	2019-06-05
1102	2	2018-12-05
1102	1	2019-09-22
1112	2	2017-05-04
1102	1	2019-10-10

설명:

이 코드는 SQL로 작성되었으며, receipt라는 테이블에서 데이터를 가져온다. 이 코드의 목적은 reciport 테이블에서 reciport_no, reciport_sub_no, sales_ymd 열을 가져와 sales_ymd 열을 날짜 형식으로 변환하는 것이다.

SELECT 문은 RECIPATE 테이블에서 데이터를 가져오는 데 사용되며, LIMIT 구문은 반환되는 행의 수를 10으로 제한하는 데 사용된다.

CAST 함수는 sales_ymd 컬럼을 정수에서 문자열로 변환하는 데 사용된다. 결과 문자열에는 'YYYYMMDD' 형식으로 판매 날짜의 년, 월, 일이 포함될 것이다.

TO_DATE 함수는 sales_ymd의 문자열을 날짜 형식으로 변환하는 데 사용된다. 이 경우 형식은 'YYYYMMDD'이며, 이는 판매 날짜의 년, 월, 일을 나타낸다. 결과 날짜 값은 yyyy-mm-dd 형식이 된다.

AS 키워드는 결과 컬럼의 이름을 sales_ymd로 변경하는 데 사용된다. 이 SELECT 문 결과 집합에는 receive_no, receive_sub_no, sales_ymd의 세 개의 열이 포함되어 있으며, sales_ymd 열에는 각 영수증의 판매 날짜가 날짜 형식으로 포함되어 있습니다.

전체적으로 이 코드는 receipt 테이블에서 receive_no, receive_sub_no, sales_ymd 컬럼을 가져오고 sales_ymd 컬럼을 날짜 형식으로 변환하고 싶을 때 유용하다. 이 형식화된 데이터는 특히 날짜 계산이나 비교가 포함되는 경우 분석이나 보고서 작성에 유용하다.

S-048: 영수증 내역 데이터(df_receipt)의 매출 에포크 초(sales_epoch)는 숫자형 UNIX 초로 데이터를 보유하고 있다. 이를 날짜형으로 변환하여 영수증 번호(receipt_no), 영수증 서브번호(receipt_sub_no)와 함께 10건을 표시하라.

In [53]:

%%sql

SELECT 
    receipt_no,
    receipt_sub_no,
    CAST(TO_TIMESTAMP(sales_epoch) AS DATE) AS sales_ymd
FROM receipt 
LIMIT 10
;

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

Out[53]:

receipt_no	receipt_sub_no	sales_ymd
112	1	2018-11-03
1132	2	2018-11-18
1102	1	2017-07-12
1132	1	2019-02-05
1102	2	2018-08-21
1112	1	2019-06-05
1102	2	2018-12-05
1102	1	2019-09-22
1112	2	2017-05-04
1102	1	2019-10-10

설명:

이 코드는 SQL로 작성되었으며, receiport라는 테이블에서 데이터를 가져온다. 이 코드의 목적은 receive 테이블에서 receive_no, receive_sub_no, sales_epoch 열을 가져와 sales_epoch 열을 날짜 형식으로 변환하는 것이다.

SELECT 문을 사용하여 영수증 테이블에서 데이터를 가져오고 LIMIT 절을 사용하여 반환되는 행 수를 10행으로 제한하고 있다.

TO_TIMESTAMP 함수를 사용하여 sales_epoch 컬럼을 정수 값에서 타임스탬프 형식으로 변환하고 있다. 결과 타임스탬프는 1970년 1월 1일 00:00:00:00 UTC로부터 몇 초가 지났는지를 나타냅니다.

CAST 함수는 타임스탬프를 날짜 형식으로 변환하는 데 사용됩니다. 이 경우 결과 날짜 값은 yyyy-mm-dd 형식이 된다.

AS 키워드는 결과 컬럼의 이름을 sales_ymd로 변경하는 데 사용된다. 이 SELECT 문 결과 집합에는 receive_no, receive_sub_no, sales_ymd 세 개의 컬럼이 포함되어 있으며, sales_ymd 컬럼에는 각 영수증의 판매 날짜가 날짜 형식으로 포함되어 있다.

전체적으로 이 코드는 receipt 테이블에서 receipt_no, receive_sub_no 및 sales_epoch 열을 가져오고 sales_epoch 열을 날짜 형식으로 변환하고 싶을 때 유용하다. 이 형식화된 데이터는 특히 날짜 계산이나 비교가 포함된 경우 분석이나 보고서 작성에 유용하다.

S-049: 영수증 내역 데이터(df_receipt)의 매출 에포크 초(sales_epoch)를 날짜형으로 변환하여 ‘연도’만 추출하여 영수증 번호(receipt_no), 영수증 하위 번호(receipt_sub_no)와 함께 10건 표시하라.

In [54]:

%%sql

SELECT 
    receipt_no, 
    receipt_sub_no,
    EXTRACT(YEAR FROM TO_TIMESTAMP(sales_epoch)) AS sales_year
FROM receipt 
LIMIT 10
;

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

Out[54]:

receipt_no	receipt_sub_no	sales_year
112	1	2018
1132	2	2018
1102	1	2017
1132	1	2019
1102	2	2018
1112	1	2019
1102	2	2018
1102	1	2019
1112	2	2017
1102	1	2019

설명:

이 코드는 SQL로 작성되었으며, receiport라는 테이블에서 데이터를 가져온다. 이 코드의 목적은 receive 테이블에서 receive_no, receive_sub_no, sales_epoch 열을 가져오고, sales_epoch의 타임스탬프 열에서 연도를 추출하는 것이다.

영수증 테이블에서 데이터를 가져오기 위해 SELECT 문을 사용하고, LIMIT 절을 사용하여 반환되는 행 수를 10행으로 제한하고 있다.

TO_TIMESTAMP 함수를 사용하여 sales_epoch 열을 정수 값에서 타임스탬프 형식으로 변환한다. 결과 타임스탬프는 1970년 1월 1일 00:00:00:00 UTC로부터의 초 단위가 된다.

EXTRACT 함수는 sales_epoch의 타임스탬프에서 연도를 추출하는 데 사용된다. 결과 값은 연도를 나타내는 정수가 됩니다.

AS 키워드는 결과 컬럼의 이름을 sales_year로 변경하는 데 사용됩니다. 이 SELECT 문 결과 집합에는 receive_no, receive_sub_no, sales_year의 세 개의 열이 포함되어 있으며, sales_year 열에는 각 영수증의 판매일 연도가 포함되어 있다.

전체적으로 이 코드는 receipt 테이블에서 receipt_no, receive_sub_no 및 sales_epoch 열을 가져오고 sales_epoch 타임스탬프 열에서 연도를 추출하고 싶을 때 유용하다. 이 추출된 데이터는 판매일 연도에 따라 데이터를 그룹화하거나 필터링하는 데 유용하다.

S-050: 영수증 내역 데이터(df_receipt)의 매출 에포크 초(sales_epoch)를 날짜형으로 변환하여 ‘월’만 추출하여 영수증 번호(receipt_no), 영수증 하위 번호(receipt_sub_no)와 함께 10건을 표시한다. 단, ‘월’은 0으로 채워진 2자리로 추출한다.

In [55]:

%%sql

SELECT 
    receipt_no, 
    receipt_sub_no,
    TO_CHAR(
        EXTRACT(MONTH FROM TO_TIMESTAMP(sales_epoch)), 
        'FM00'
    ) AS sales_month
FROM receipt
LIMIT 10
;

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

Out[55]:

receipt_no	receipt_sub_no	sales_month
112	1	11
1132	2	11
1102	1	07
1132	1	02
1102	2	08
1112	1	06
1102	2	12
1102	1	09
1112	2	05
1102	1	10

설명:

이 코드는 SQL로 작성되었으며, receiport라는 테이블에서 데이터를 가져온다. 이 코드의 목적은 receiport 테이블에서 receiport_no, receiport_sub_no, sales_epoch 열을 가져오고, sales_epoch 타임스탬프 열에서 월을 추출하고, 결과 월을 앞의 0이 두 자리 숫자로 된 문자열로 포맷하는 것이다.

SELECT 문은 영수증 테이블에서 데이터를 가져오는 데 사용되며, LIMIT 구문은 반환되는 행 수를 10으로 제한하는 데 사용된다.

TO_TIMESTAMP 함수를 사용하여 sales_epoch 컬럼을 정수 값에서 타임스탬프 형식으로 변환하고 있다. 결과 타임스탬프는 1970년 1월 1일 00:00:00:00 UTC로부터의 초 단위가 된다.

EXTRACT 함수는 sales_epoch의 타임스탬프에서 월을 추출하는 데 사용된다. 결과 값은 월을 나타내는 정수가 됩니다.

TO_CHAR 함수는 추출된 월을 필요에 따라 앞의 0이 포함된 문자열로 변환하는 데 사용되며, 'FM00' 형식 문자열은 결과 문자열이 고정된 폭의 2자이며 앞의 0은 제거된다는 것을 지정한다.

AS 키워드는 결과 컬럼의 이름을 sales_month로 변경하는 데 사용된다. 이 SELECT 문 결과 집합에는 receive_no, receive_sub_no 및 sales_month의 세 개의 열이 포함되어 있으며, sales_month 열에는 각 영수증의 판매 날짜의 월이 맨 앞의 0을 포함한 2자리 문자열로 포함되어 있다.

전체적으로 이 코드는 reciport 테이블에서 reciport_no, reciport_sub_no 및 sales_epoch 열을 가져와 sales_epoch 타임스탬프 열에서 월을 추출하고 결과 월을 앞의 0이 포함된 2자리 문자열로 서식을 지정한다. 원하는 경우에 유용하다. 이 형식화된 데이터는 판매일 월을 기준으로 데이터를 그룹화하거나 필터링하는 데 유용하다.

S-051: 영수증 내역 데이터(df_receipt)의 매출 에포크 초를 날짜형으로 변환하여 ‘일’만 추출하여 영수증 번호(receipt_no), 영수증 하위 번호(receipt_sub_no)와 함께 10건 표시한다. 단, ‘일’은 0으로 채워진 2자리로 추출한다.

In [56]:

%%sql

SELECT 
    receipt_no, receipt_sub_no,
    TO_CHAR(EXTRACT(DAY FROM TO_TIMESTAMP(sales_epoch)), 'FM00') AS sales_day
FROM receipt LIMIT 10
;

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

Out[56]:

receipt_no	receipt_sub_no	sales_day
112	1	03
1132	2	18
1102	1	12
1132	1	05
1102	2	21
1112	1	05
1102	2	05
1102	1	22
1112	2	04
1102	1	10

설명:

이 코드는 SQL로 작성되었으며, receiport라는 테이블에서 데이터를 가져온다. 이 코드의 목적은 receiport 테이블에서 receiport_no, receiport_sub_no, sales_epoch 열을 가져오고, sales_epoch 타임스탬프 열에서 날짜를 추출하고, 결과 날짜를 앞의 0이 두 자리 숫자로 된 문자열로 포맷하는 것이다.

SELECT 문은 영수증 테이블에서 데이터를 가져오는 데 사용되며, LIMIT 구문은 반환되는 행 수를 10으로 제한하는 데 사용된다.

TO_TIMESTAMP 함수를 사용하여 sales_epoch 컬럼을 정수 값에서 타임스탬프 형식으로 변환하고 있다. 결과 타임스탬프는 1970년 1월 1일 00:00:00:00 UTC로부터의 초 단위가 된다.

EXTRACT 함수는 sales_epoch의 타임스탬프에서 날짜를 추출하는 데 사용됩니다. 결과 값은 날짜를 나타내는 정수가 됩니다.

TO_CHAR 함수는 추출된 날짜를 필요에 따라 앞의 0이 포함된 문자열로 변환하는 데 사용되며, 'FM00' 형식 문자열은 결과 문자열이 고정된 폭의 2자이며 앞의 0이 제거된다는 것을 지정한다.

AS 키워드는 결과 컬럼의 이름을 sales_day로 변경하는 데 사용된다. 이 SELECT 문 결과 집합에는 receive_no, receive_sub_no, sales_day의 세 개의 열이 포함되어 있으며, sales_day 열에는 각 영수증의 판매일 날짜가 맨 앞의 0을 포함한 2자리 문자열로 포함되어 있다.

전체적으로 이 코드는 reciport 테이블에서 reciport_no, reciport_sub_no, sales_epoch 컬럼을 가져오고, sales_epoch 타임스탬프 컬럼에서 날짜를 추출하고, 결과 날짜를 앞의 0이 포함된 두 자리 문자열로 포맷하고 싶을 때 유용하다. 이 형식화된 데이터는 판매일 날짜를 기준으로 데이터를 그룹화하거나 필터링하는 데 유용하다.

S-052: 영수증 내역 데이터(df_receipt)의 매출 금액(amount)을 고객 ID(customer_id)별로 합산한 후, 매출 금액 총합에 대해 2,000원 이하를 0, 2,000원보다 큰 금액을 1로 이분화하여 고객 ID, 매출 금액 총합과 함께 10건씩 표시한다. 단, 고객 ID가 “Z”로 시작하는 것은 비회원을 의미하므로 제외하여 계산한다.

In [57]:

%%sql

SELECT
    customer_id,
    SUM(amount) AS sum_amount,
    CASE
        WHEN SUM(amount) > 2000 THEN 1
        ELSE 0
    END AS sales_flg
FROM receipt
WHERE customer_id NOT LIKE 'Z%'
GROUP BY customer_id
LIMIT 10
;

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

Out[57]:

customer_id	sum_amount	sales_flg
CS001311000059	2302	1
CS004614000122	248	0
CS003512000043	298	0
CS011615000061	246	0
CS029212000033	3604	1
CS007515000119	7157	1
CS034515000123	3699	1
CS004315000058	490	0
CS026414000014	6671	1
CS001615000099	768	0

설명:

이 SQL 코드는 receive라는 테이블에서 데이터를 선택하고 있다. 이 코드의 목적은 RECIPATE 테이블의 각 고객의 매출 총액을 계산하는 것과 각 고객의 매출 총액이 2000보다 큰지 여부를 나타내는 플래그를 생성하는 것입니다.

SELECT 문은 customer_id 열, SUM 함수로 계산한 각 고객의 매출 총액, 매출 총액이 2000보다 크면 1, 그렇지 않으면 0을 반환하는 CASE 식을 가져온다. 플래그가 포함된 결과 컬럼은 sales_flg로 명명한다.

WHERE 절은 고객 ID가 'Z'로 시작하는 레코드를 필터링하여 제외한다. 이는 특정 고객을 계산이나 플래그 설정에서 제외하는 데 사용할 수 있습니다.

GROUP BY 구문은 고객별 총 매출 금액과 플래그가 계산되도록 결과를 고객 ID별로 그룹화합니다.

LIMIT 절은 출력을 처음 10개의 결과로 제한합니다.

전체적으로 이 코드는 영수증 테이블의 각 고객에 대한 총 판매액을 계산하고 총 판매액이 특정 임계값(이 경우 2000)보다 큰지 여부를 나타내는 플래그를 생성하고자 할 때 유용합니다. 이는 고액 쇼핑을 하는 고객을 식별하거나 고객의 매출 금액을 집계하여 분석해야 하는 다른 유형의 분석에 유용할 수 있습니다.

S-053: 고객 데이터(df_customer)의 우편번호(postal_cd)에 대해 도쿄(앞 3자리가 100~209인 것)를 1, 그 외의 것을 0으로 이진화하라. 또한 영수증 내역 데이터(df_receipt)와 결합하여 전체 기간 동안 매출 실적이 있는 고객 수를 생성한 이항대수별로 계산하라.

In [58]:

%%sql

WITH cust AS (
    SELECT
        customer_id,
        postal_cd,
        CASE
            WHEN CAST(SUBSTR(postal_cd, 1, 3) AS INTEGER) BETWEEN 100 AND 209 THEN 1
            ELSE 0
        END AS postal_flg
    FROM
        customer
),
rect AS(
    SELECT DISTINCT
        customer_id
    FROM
        receipt
)
SELECT 
    c.postal_flg, 
    COUNT(DISTINCT c.customer_id) AS customer_cnt
FROM
    cust c
JOIN
    rect r
USING (customer_id)
GROUP BY
    c.postal_flg
;

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

Out[58]:

postal_flg	customer_cnt
0	3906
1	4400

설명:

이 SQL 코드에는 cust라는 공통 테이블 표현식(CTE)이 포함되어 있으며, customer 테이블에서 데이터를 선택하여 postal_cd를 기반으로 각 고객의 postal_flg 플래그를 계산하고 있습니다.

postal_flg 플래그는 CASE 수식을 사용하여 계산되며, postal_cd의 첫 세 글자가 100에서 209(포함)까지인지 확인하고, 참이면 1, 거짓이면 0이라는 값을 할당한다. 이 플래그는 예를 들어 우편번호 범위에 따라 고객을 그룹화하는 데 사용할 수 있다.

두 번째 CTE인 rect는 recipate 테이블에서 개별 customer_id를 선택한다.

마지막 SELECT 문은 customer_id 열에서 cust와 rect CTE를 결합하고, postal_flg 열에서 결과를 그룹화하며, COUNT(DISTINCT c.customer_id) 함수는 각 postal_flg 값에 대해 서로 다른 고객 수를 계산한다. 를 계산합니다.

전체적으로 이 코드는 고객 테이블과 영수증 테이블에 저장된 데이터를 기반으로 우편번호 범위에 따라 고객을 그룹화하고 각 범위의 다른 고객 수를 계산하고 싶을 때 유용하다.

S-054: 고객 데이터(df_customer)의 주소(address)는 사이타마현, 지바현, 도쿄도, 가나가와현 중 하나이다. 도도부현별로 코드 값을 생성하여 고객 ID, 주소와 함께 10건씩 표시하라. 값은 사이타마현을 11, 지바현을 12, 도쿄도를 13, 가나가와현을 14로 한다.

In [59]:

%%sql

-- SQL에 적합하지 않기 때문에 다소 강압적으로 기술한다(카테고리 수가 많으면 매우 긴 SQL이 된다는 점에 주의) 
-- 코드 예시 1(고정으로 잘라내기)
SELECT
    customer_id,
    address,
    CASE SUBSTR(address,1, 3)
        WHEN '埼玉県' THEN '11'
        WHEN '千葉県' THEN '12'
        WHEN '東京都' THEN '13'
        WHEN '神奈川' THEN '14'
    END AS prefecture_cd
FROM
    customer
LIMIT 10
;

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

Out[59]:

customer_id	address	prefecture_cd
CS021313000114	神奈川県伊勢原市粟窪**********	14
CS037613000071	東京都江東区南砂**********	13
CS031415000172	東京都渋谷区代々木**********	13
CS028811000001	神奈川県横浜市泉区和泉町**********	14
CS001215000145	東京都大田区仲六郷**********	13
CS020401000016	東京都板橋区若木**********	13
CS015414000103	東京都江東区北砂**********	13
CS029403000008	千葉県浦安市海楽**********	12
CS015804000004	東京都江東区北砂**********	13
CS033513000180	神奈川県横浜市旭区善部町**********	14

설명:

이 SQL 코드는 customer 테이블에서 각 고객의 customer_id, address, prefecture_cd code를 선택한다.

이 코드의 CASE 표현식은 address 열의 첫 3글자를 체크하고 다음 조건에 따라 해당 prefecture_cd의 값을 할당한다.

주소란의 첫 3글자가 '사이타마현'이면 prefecture_cd 값은 '11'로 설정된다.

주소란의 첫 3글자가 '치바현'인 경우, prefecture_cd의 값은 '12'로 설정된다.

주소란의 첫 3글자가 '도쿄도'인 경우, prefecture_cd의 값은 '13'으로 설정된다.

주소란의 첫 3글자가 '가나가와'인 경우, prefecture_cd의 값은 '14'로 설정된다.

이 코드는 고객 테이블에 저장된 주소 정보를 바탕으로 고객을 도도부현별로 분류하고 싶을 때 유용하다. 결과값인 prefecture_cd 컬럼은 추가 분석이나 그룹화에 활용할 수 있다.

In [60]:

%%sql

-- 코드 예시 2 (정규 표현식 사용)
SELECT
    customer_id,
    address,
    CASE SUBSTRING(address, '^.*?[都道府県]')
        WHEN '埼玉県' THEN '11'
        WHEN '千葉県' THEN '12'
        WHEN '東京都' THEN '13'
        WHEN '神奈川県' THEN '14'
    END AS prefecture_cd
FROM
    customer
LIMIT 10
;

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

Out[60]:

customer_id	address	prefecture_cd
CS021313000114	神奈川県伊勢原市粟窪**********	14
CS037613000071	東京都江東区南砂**********	13
CS031415000172	東京都渋谷区代々木**********	13
CS028811000001	神奈川県横浜市泉区和泉町**********	14
CS001215000145	東京都大田区仲六郷**********	13
CS020401000016	東京都板橋区若木**********	13
CS015414000103	東京都江東区北砂**********	13
CS029403000008	千葉県浦安市海楽**********	12
CS015804000004	東京都江東区北砂**********	13
CS033513000180	神奈川県横浜市旭区善部町**********	14

설명:

이 SQL 코드는 customer 테이블에서 각 고객의 customer_id, address, prefecture_cd code를 선택한다.

이 코드의 CASE 표현식은 정규식 패턴 '^. *? [도도부현]'을 사용하여 주소열에서 도도부현 이름을 추출하고, 아래 조건에 따라 해당 도도부현_cd 값을 할당하고 있다.

추출된 도도부현 이름이 '사이타마현'인 경우, prefecture_cd 값은 '11'로 설정된다.
추출된 도도부현 이름이 '지바현'인 경우, prefecture_cd 값은 '12'로 설정된다.
추출된 도도부현 이름이 '도쿄도'인 경우, prefecture_cd의 값은 '13'으로 설정된다.
추출된 도도부현이 '가나가와현'이라면 prefecture_cd의 값은 '14'로 설정된다.

이 코드는 앞의 예제와 비슷하지만, SUBSTR 함수 대신 SUBSTRING 함수를 사용하여 주소란에서 도도부현 이름을 추출하고 있다. 정규 표현식 패턴 '^. *? [都道府県]」는 임의의 문자(^. *?) 로 시작해서 도, 시, 도, 부, 현 중 하나로 끝나는 문자열과 일치합니다. 이 코드는 고객 테이블의 주소 정보가 표준화되지 않아 도도부현 명칭의 형식이 제각각인 경우에 유효하다. 생성된 prefecture_cd 컬럼은 추가 분석 및 그룹화에 사용할 수 있다.

In [61]:

%%sql

-- 코드 예시 3 (LIKE 사용)
SELECT
    customer_id,
    address,
    CASE
        WHEN address LIKE '埼玉県%' THEN '11'
        WHEN address LIKE '千葉県%' THEN '12'
        WHEN address LIKE '東京都%' THEN '13'
        WHEN address LIKE '神奈川県%' THEN '14'
    END AS prefecture_cd
FROM
    customer
LIMIT 10
;

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

Out[61]:

customer_id	address	prefecture_cd
CS021313000114	神奈川県伊勢原市粟窪**********	14
CS037613000071	東京都江東区南砂**********	13
CS031415000172	東京都渋谷区代々木**********	13
CS028811000001	神奈川県横浜市泉区和泉町**********	14
CS001215000145	東京都大田区仲六郷**********	13
CS020401000016	東京都板橋区若木**********	13
CS015414000103	東京都江東区北砂**********	13
CS029403000008	千葉県浦安市海楽**********	12
CS015804000004	東京都江東区北砂**********	13
CS033513000180	神奈川県横浜市旭区善部町**********	14

설명:

이 SQL 코드는 customer라는 테이블에 대한 쿼리를 수행하여 처음 10행의 customer_id, address, prefecture_cd 컬럼을 선택하고 있습니다. 일치하도록 CASE 문장을 사용하여 작성되었습니다.

구체적으로 CASE 문장은 일본의 도도부현(사이타마현%, 지바현%, 도쿄도%, 가나가와현%) 뒤에 임의의 문자열이 이어지는 문자열 패턴과 LIKE 연산자를 사용하여 주소 컬럼을 확인한다. 주소 컬럼이 어느 한 패턴과 일치하면 해당 도도부현 코드(11, 12, 13, 14)가 prefecture_cd 컬럼에 부여된다.

이 코드는 앞서 제시한 다른 두 가지 예제와 비교하여 주소 컬럼에서 도도부현 코드를 추출하는 대체적인 방법이다.

S-055: 영수증 명세서(df_receipt) 데이터의 매출 금액(amount)을 고객 ID(customer_id)별로 합산하고, 그 합산 금액의 사분위수를 구하시오. 그 후, 고객별 매출금액 합계에 대해 아래 기준으로 카테고리 값을 생성하여 고객 ID, 매출금액 합계와 함께 10건씩 표시하라. 카테고리 값은 순서대로 1~4로 한다.

최소값 이상 1사분위수 미만 ・・・ 1을 부여

1사분위 이상 2사분위 미만 ・・・ 2를 부여

2사분위 이상 3사분위 미만 ・・・ 3을 부여

3사분위 이상 ・・・ 4을 부여

In [62]:

%%sql

WITH sales_amount AS(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    GROUP BY
        customer_id
),
sales_pct AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY sum_amount) AS pct25,
        PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY sum_amount) AS pct50,
        PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY sum_amount) AS pct75
    FROM
        sales_amount
)
SELECT
    a.customer_id,
    a.sum_amount,
    CASE
        WHEN a.sum_amount < pct25 THEN 1
        WHEN pct25 <= a.sum_amount AND a.sum_amount < pct50 THEN 2
        WHEN pct50 <= a.sum_amount AND a.sum_amount < pct75 THEN 3
        WHEN pct75 <= a.sum_amount THEN 4
    END AS pct_group
FROM sales_amount a
CROSS JOIN sales_pct p
LIMIT 10
;

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

Out[62]:

customer_id	sum_amount	pct_group
CS001311000059	2302	3
CS004614000122	248	1
CS003512000043	298	1
CS011615000061	246	1
CS029212000033	3604	3
CS007515000119	7157	4
CS034515000123	3699	4
CS004315000058	490	1
CS026414000014	6671	4
CS001615000099	768	2

설명:

이 SQL 코드는 다음 작업을 수행합니다.

SUM 함수를 사용하여 영수증 테이블의 금액 열을 고객 ID별로 합산하고 고객 ID별로 그룹화하여 각 고객의 판매 금액 합계를 계산합니다. 결과 테이블은 sales_amount라는 이름의 하위 쿼리로 저장됩니다.

창 함수인 PERCENTILE_CONT 함수를 사용하여 sales_amount 하위 쿼리에서 sum_amount 열의 사분위수(25위, 50위, 75위)를 계산한다.

CROSS JOIN 연산으로 sales_amount 하위 쿼리와 sales_pct 하위 쿼리를 결합하여 sales_amount 테이블의 각 행에 대한 사분위수 값을 얻는다.

마지막으로 새로운 열 pct_group을 계산하여 총 매출액을 기준으로 각 고객에게 다음과 같이 백분위수 그룹을 할당한다.

1:sum_amount가 25번째 백분위수보다 작은 경우(pct25)
2:sum_amount가 25백분위수 이상 50백분위수 미만인 경우(pct50).
3:sum_amount가 50번째 백분위수 이상 75번째 백분위수 미만인 경우(pct75).
sum_amount가 75 백분위수 이상이면 4.

최종 결과는 처음 10개의 행으로 제한된다.

S-056: 고객 데이터(df_customer)의 나이(age)를 기준으로 10세 단위로 연령을 계산하여 고객 ID(customer_id), 생년월일(birth_day)과 함께 10건씩 표시한다. 단, 60세 이상은 모두 60대 이상으로 한다. 연령을 나타내는 카테고리 명칭은 임의대로 한다.

In [63]:

%%sql

SELECT
    customer_id,
    birth_day,
    -- 확인용 항목
    -- age,
    LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60) AS era
FROM
    customer
GROUP BY
    customer_id,
    birth_day
-- 확인 조건
-- HAVING LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60) >= 60
LIMIT 10
;

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

Out[63]:

customer_id	birth_day	era
CS001105000001	2000-01-14	10
CS001112000009	2006-08-24	10
CS001112000019	2001-01-31	10
CS001112000021	2001-12-15	10
CS001112000023	2004-01-26	10
CS001112000024	2001-01-16	10
CS001112000029	2005-01-24	10
CS001112000030	2003-03-02	10
CS001113000004	2003-02-22	10
CS001113000010	2005-05-09	10

설명:

이 코드는 customer 테이블에서 customer_id, birth_day, era(계산 필드)를 선택하고, era 필드는 고객의 나이(명시적으로 선택되지 않았지만 계산에 사용됨)를 결정하고, 10세 미만으로 잘린 나이와 60 사이의 낮은 값을 가져와서 계산한다. 낮은 값을 취하여 계산된다.

결과는 customer_id와 birth_day로 그룹화된다. 그런 다음 HAVING 절을 사용하여 연령이 60세 미만인 고객을 필터링한다.

요컨대, 이 코드는 고객 테이블에서 고객 정보를 선택하고, 나이와 시대를 계산하고, customer_id와 birth_day로 결과를 그룹화하여 연령이 60세 미만인 고객을 필터링하고 있다.

S-057: 056의 추출 결과와 성별 코드(gender_cd)에 따라 성별×연령의 조합을 나타내는 카테고리 데이터를 새로 만들어 10개를 표시하라. 조합을 나타내는 카테고리의 값은 임의로 정한다.

In [64]:

%%sql

-- 성별 코드 1자리와 연대 코드 2자리를 연결한 성연령 코드를 생성한다.
SELECT
    customer_id,
    birth_day,
    gender_cd || TO_CHAR(LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60), 'FM00') AS gender_era
FROM
    customer
GROUP BY
    customer_id,
    birth_day
LIMIT 10
;

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

Out[64]:

customer_id	birth_day	gender_era
CS001105000001	2000-01-14	010
CS001112000009	2006-08-24	110
CS001112000019	2001-01-31	110
CS001112000021	2001-12-15	110
CS001112000023	2004-01-26	110
CS001112000024	2001-01-16	110
CS001112000029	2005-01-24	110
CS001112000030	2003-03-02	110
CS001113000004	2003-02-22	110
CS001113000010	2005-05-09	110

설명:

이 SQL 코드는 customer_id, birth_day, 그리고 gender_cd와 era로 구성된 연결 문자열을 선택한다. era는 고객의 나이를 10년 단위로 반올림한 것으로 최대 era는 60세까지이며, gender_era의 문자열은 gender_cd와 era를 연결하여 생성됩니다.

SQL 코드는 customer_id와 birth_day로 결과를 그룹화하고 결과의 처음 10줄만 반환한다.

전체적으로 이 코드는 연령과 성별을 포함한 고객 데이터의 요약을 생성하고 시대 차원을 추가한다. 생성된 테이블은 연령과 성별에 따라 고객을 추가로 분석하거나 세분화하는 데 사용할 수 있다.

S-058: 고객 데이터(df_customer)의 성별 코드(gender_cd)를 더미 변수로 만들어 고객 ID(customer_id)와 함께 10건 표시하라.

In [65]:

%%sql

-- 카테고리 수가 많을 경우 매우 긴 SQL이 될 수 있다는 점에 주의
-- 카테고리를 하나 줄이고 싶다면 CASE 문장을 하나만 빼면 OK
SELECT
    customer_id,
    CASE WHEN gender_cd = '0' THEN '1' ELSE '0' END AS gender_cd_0,
    CASE WHEN gender_cd = '1' THEN '1' ELSE '0' END AS gender_cd_1,
    CASE WHEN gender_cd = '9' THEN '1' ELSE '0' END AS gender_cd_9
FROM
    customer
LIMIT 10
;

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

Out[65]:

customer_id	gender_cd_0	gender_cd_1	gender_cd_9
CS021313000114	0	1	0
CS037613000071	0	0	1
CS031415000172	0	1	0
CS028811000001	0	1	0
CS001215000145	0	1	0
CS020401000016	1	0	0
CS015414000103	0	1	0
CS029403000008	1	0	0
CS015804000004	1	0	0
CS033513000180	0	1	0

설명:

이 SQL 코드는 customer_id를 선택하고 gender_cd_0, gender_cd_1, gender_cd_9라는 3개의 새로운 열을 생성한다. 이 새로운 컬럼은 CASE 문장을 사용하여 만들어졌습니다.

CASE 문은 조건문으로, 일련의 조건을 평가하고 해당 결과를 반환한다. 이 경우 각 고객에 대해 코드는 gender_cd 열의 값을 확인하고, gender_cd가 '0'인 경우 코드는 gender_cd_0 열에 '1'을 반환하고 다른 열에 '0'을 반환하고, gender_cd가 '1'인 경우 코드는 gender_cd_1 열에 '1'을 반환하고 다른 열에는 '0'을 반환하며, gender_cd가 '9'인 경우 코드는 gender_cd_9 열에 '1'을 반환하고 다른 열에는 '0'을 반환한다.

결과 출력은 customer_id, gender_cd_0, gender_cd_1, gender_cd_9 컬럼을 가지며, 각 고객에 대해 새로운 컬럼 중 하나만 고객의 성별을 나타내는 '1'이라는 값을 가지게 된다.

S-059: 영수증 내역 데이터(df_receipt)의 매출 금액(amount)을 고객 ID(customer_id)별로 합산하고, 매출 금액 합계를 평균 0, 표준편차 1로 표준화하여 고객 ID, 매출 금액 합계와 함께 10건씩 표시하라. 표준화에 사용하는 표준편차는 분산 제곱근 또는 불균형 분산 제곱근 중 어느 것이든 상관없다. 단, 고객 ID가 “Z”로 시작하는 것은 비회원을 의미하므로 제외하여 계산한다.

In [66]:

%%sql

-- 코드 예시 1 (STDDEV_POP로 표준화)
WITH sales_amount AS(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY
        customer_id
),
stats_amount AS (
    SELECT
        AVG(sum_amount) AS avg_amount,
        STDDEV_POP(sum_amount) AS stddev_amount
    FROM
        sales_amount
)
SELECT
    customer_id,
    sum_amount,
    (sum_amount - avg_amount) / stddev_amount AS std_amount
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10
;

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

Out[66]:

customer_id	sum_amount	std_amount
CS001311000059	2302	-0.09032946448390523062
CS004614000122	248	-0.84533488878695858131
CS003512000043	298	-0.82695598361891930792
CS011615000061	246	-0.84607004499368015224
CS029212000033	3604	0.38825722609183744835
CS007515000119	7157	1.6942622273327082
CS034515000123	3699	0.42317714591111206778
CS004315000058	490	-0.75638098777364849812
CS026414000014	6671	1.5156192690993665
CS001615000099	768	-0.65419427503935013810

설명:

이 코드는 SQL 언어로 작성되었으며, SQL을 지원하는 데이터베이스 관리 시스템을 사용합니다.

이 코드는 recipate라는 테이블에 대해 데이터 분석 작업을 실행하고 있다. 이 테이블에는 customer_id, 금액 등의 열을 가진 트랜잭션 데이터가 포함되어 있습니다.

이 코드는 CTE(Common Table Expression)를 사용하여 두 개의 임시 테이블 sales_amount와 stats_amount를 생성하고 있다.

sales_amount라는 이름의 첫 번째 CTE는 문자 'Z'로 시작하지 않는 customer_id를 가진 각 고객이 사용한 금액의 합계를 계산하고 있다. 거래 데이터를 customer_id로 그룹화하여 각 customer_id의 금액 합계를 계산하고 있습니다.

stats_amount라는 이름의 두 번째 CTE는 각 고객이 사용한 총 금액의 평균과 표준편차를 계산하고 있습니다.

마지막 SELECT 문은 sales_amount 테이블에서 컬럼을 선택하고 크로스 조인을 사용하여 stats_amount 테이블과 결합합니다. 또한 총 금액에서 평균 금액을 빼고 이를 표준편차로 나누어 각 고객이 사용한 표준화된 금액을 계산하고 있습니다.

마지막 LIMIT 10은 출력을 처음 10행으로 제한하고 있다.

전체적으로 이 코드는 거래 데이터에 대한 통계 분석을 수행하여 'Z'로 시작하는 customer_id가 없는 각 고객이 사용한 표준화 된 금액을 계산하고 있습니다.

In [67]:

%%sql

-- 코드 예시 2 (STDDEV_SAMP로 표준화, 코드 예시 2와 약간 값이 달라짐)
WITH sales_amount AS(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY
        customer_id
),
stats_amount AS (
    SELECT
        AVG(sum_amount) AS avg_amount,
        STDDEV_SAMP(sum_amount) AS stddev_amount
    FROM
        sales_amount
)
SELECT
    customer_id,
    sum_amount,
    (sum_amount - avg_amount) / stddev_amount AS std_amount
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10
;

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

Out[67]:

customer_id	sum_amount	std_amount
CS001311000059	2302	-0.09032402671702291270
CS004614000122	248	-0.84528400025253654164
CS003512000043	298	-0.82690620148098070841
CS011615000061	246	-0.84601911220339877497
CS029212000033	3604	0.38823385329429098451
CS007515000119	7157	1.6941602340010485
CS034515000123	3699	0.42315167096024706764
CS004315000058	490	-0.75633545419820630882
CS026414000014	6671	1.5155280299415258
CS001615000099	768	-0.65415489302835587608

설명:

이 코드는 이전 코드와 매우 유사하지만, stats_amount CTE에서 한 가지 중요한 차이점이 있다.

표준편차 계산에 STDDEV_POP 함수를 사용하는 대신 STDDEV_SAMP 함수를 사용합니다.

STDDEV_SAMP는 데이터 샘플을 기반으로 모집단의 표준편차 추정치인 샘플 표준편차를 계산합니다.

반면 STDDEV_POP은 전체 모집단을 분석할 수 있다고 가정하고 정확한 모집단 표준편차를 계산한다.

즉, 이 코드의 출력은 이전 코드와 비슷하지만 표준편차 계산은 전체 모집단이 아닌 데이터 샘플을 기반으로 한다.

일반적으로 데이터 세트가 매우 큰 경우 샘플과 모집단의 표준 편차의 차이가 작습니다. 그러나 데이터 세트가 작으면 그 차이가 커질 수 있습니다.

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

In [68]:

%%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
),
stats_amount AS (
    SELECT
        MAX(sum_amount) AS max_amount,
        MIN(sum_amount) AS min_amount
    FROM
        sales_amount
)
SELECT
    customer_id,
    sum_amount,
    1.0 * (sum_amount - min_amount)
            / (max_amount -  min_amount) AS scale_amount
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10
;

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

Out[68]:

customer_id	sum_amount	scale_amount
CS001311000059	2302	0.09697601668404588113
CS004614000122	248	0.00773375043448036149
CS003512000043	298	0.00990615224191866528
CS011615000061	246	0.00764685436218282934
CS029212000033	3604	0.15354535974973931178
CS007515000119	7157	0.30791623218630517901
CS034515000123	3699	0.15767292318387208898
CS004315000058	490	0.01824817518248175182
CS026414000014	6671	0.28680048661800486618
CS001615000099	768	0.03032672923183872089

설명:

이 코드도 영수증 테이블을 분석하지만, 표준화 된 금액을 계산하는 대신 각 고객이 사용한 계량된 금액을 계산합니다.

이 코드에서는 sales_amount와 stats_amount라는 두 개의 CTE를 사용하는데, 이는 이전 코드와 유사하다.

첫 번째 CTE는 문자 'Z'로 시작하지 않는 customer_id를 가진 각 고객이 사용한 총 금액을 계산하고, 두 번째 CTE는 임의의 고객이 사용한 총 금액의 최대값과 최소값을 계산하고 있습니다.

마지막 SELECT 문은 sales_amount 테이블에서 컬럼을 선택하고 크로스 조인을 사용하여 stats_amount 테이블과 결합하고 있다.

또한 각 고객이 사용한 스케일링된 금액을 계산합니다. 이는 사용한 총 금액에서 사용한 최소 금액을 뺀 후 사용한 총 금액 범위로 나눈 값으로, 최대 금액과 최소 금액의 차이입니다.

수식 앞의 1.0 *는 결과를 부동 소수점 숫자로 변환하는 데 사용됩니다. 수식에 사용된 값 중 하나가 부동 소수점 숫자일 때 필요합니다.

마지막 LIMIT 10은 출력을 처음 10행으로 제한합니다.

전체적으로 이 코드는 문자 'Z'로 시작하는 customer_id가 없는 각 고객이 지출한 금액(scaled amount)을 구하는 데이터 분석 작업을 수행하고 있다. 스케일링된 금액은 0에서 1 사이의 값으로, 모든 고객이 사용한 총액 범위에 대한 각 고객이 사용한 총액의 비율을 나타낸다.