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

참고(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-021: 영수증 내역 데이터(df_receipt)에 대해 건수를 세어라.

In [23]:

%%sql
-- 코드 예시 1
SELECT COUNT(1) FROM receipt;

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

Out[23]:

count
104681

설명:

이 코드는 "receipt"라는 테이블에서 행 수를 선택하는 SQL 쿼리입니다.

SELECT 키워드는 데이터베이스에서 데이터를 가져온다는 것을 나타내기 위해 사용된다.

COUNT 함수는 'receipt' 테이블의 행 수를 계산하는 데 사용됩니다. 이 경우 'COUNT(1)' 문은 각 행의 첫 번째 열의 값이 NULL이 아닌 테이블의 행 수를 계산하는 데 사용된다. 이는 어떤 열이 NULL인지에 관계없이 테이블의 모든 행을 계산하는 "COUNT(*)"를 사용하는 것과 동일하다.

FROM 키워드는 데이터를 가져 오려는 테이블을 나타내는 데 사용되며, 이 경우 "receipt" 테이블을 나타냅니다.

즉, 요약하면 이 쿼리는 "receive" 테이블의 행 수를 선택하고 반환하는 것이다.

In [24]:

%%sql
-- 코드 예시 2（*이라도 OK）
SELECT COUNT(*) FROM receipt;

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

Out[24]:

count
104681

설명:

이 코드는 "receipt"라는 테이블에서 행 수를 선택하는 SQL 쿼리입니다.

SELECT 키워드는 데이터베이스에서 데이터를 가져온다는 것을 나타내기 위해 사용된다.

COUNT 함수는 "receipt" 테이블의 행 수를 계산하는 데 사용됩니다. 이 경우 "COUNT(*)"문은 어떤 열이 NULL인지 여부에 관계없이 테이블의 모든 행을 계산하는 데 사용됩니다.

FROM 키워드는 데이터를 가져 오려는 테이블, 이 경우 "RECIPATE" 테이블을 나타내는 데 사용됩니다.

즉, 요약하면, 이 쿼리는 "RECIPATE" 테이블의 모든 행을 선택하고 그 개수를 반환한다.

S-022: 영수증 내역 데이터(df_receipt)의 고객 ID(customer_id)에 대해 고유 건수를 세어라.

In [25]:

%%sql

SELECT
    COUNT(DISTINCT customer_id)
FROM receipt
;

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

Out[25]:

count
8307

설명:

이 코드는 "receipt"라는 테이블에서 다른 고객 ID의 개수를 선택하는 SQL 쿼리이다.

sql은 이 코드가 SQL 코드임을 나타내는 Jupyter Notebook의 셀 매직 명령이다.

SELECT 키워드는 데이터베이스에서 데이터를 가져온다는 것을 나타내기 위해 사용된다.

COUNT 함수는 'RECIPATE' 테이블에서 다른 고객 ID의 수를 계산하는 데 사용된다. 이 경우 'COUNT(DISTINCT customer_id)' 문은 테이블 내 고유한 고객 ID의 수를 계산하는 데 사용된다.

DISTINCT 키워드는 'customer_id' 열의 고유한 값만 계산하도록 지정하는 데 사용된다.

FROM 키워드는 데이터를 가져 오려는 테이블을 나타내는 데 사용되며, 이 경우 "receipt" 테이블을 나타냅니다.

즉, 요약하면 이 쿼리는 'receive' 테이블의 고유한 고객 ID 개수를 선택하여 반환한다.

S-023: 영수증 내역 데이터(df_receipt)에 대해 매장 코드(store_cd)별 판매 금액(amount)과 판매 수량(quantity)을 합산하라.

In [26]:

%%sql

SELECT store_cd
    , SUM(amount) AS amount
    , SUM(quantity) AS quantity
FROM receipt
group by store_cd
;

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

Out[26]:

store_cd	amount	quantity
S12007	638761	2099
S13017	748221	2376
S13043	587895	1881
S13052	100314	250
S13016	793773	2432
S14027	714550	2303
S13009	808870	2486
S14022	651328	2047
S13019	827833	2541
S13039	611888	1981
S14046	412646	1354
S13003	764294	2197
S14028	786145	2458
S14045	458484	1398
S13002	727821	2340
S14042	534689	1935
S13004	779373	2390
S13038	708884	2337
S12014	725167	2358
S14021	699511	2231
S14006	712839	2284
S13001	811936	2347
S14023	727630	2258
S14025	755581	2394
S13035	715869	2219
S14048	234276	769
S14012	720600	2412
S14024	736323	2417
S13041	728266	2233
S14026	824537	2503
S14010	790361	2290
S13015	780873	2248
S13008	809288	2491
S14049	230808	788
S14050	167090	580
S13031	705968	2336
S12013	787513	2425
S13044	520764	1729
S14033	725318	2282
S12029	794741	2555
S13037	693087	2344
S12030	684402	2403
S13032	790501	2491
S14034	653681	2024
S14040	701858	2233
S13005	629876	2004
S14047	338329	1041
S14011	805724	2434
S13018	790535	2562
S13020	796383	2383
S13051	107452	354
S14036	203694	635

설명:

SQL 코드가 두 번 반복된다.

이 코드는 "receipt"라는 테이블에서 "store_cd", "amount", "quantity"라는 세 개의 열을 선택한다. 이 테이블에는 특정 매장의 매출 데이터가 포함되어 있다.

SUM 함수를 사용하여 각 매장의 "금액" 열과 "수량" 열의 합계를 계산한다.

GROUP BY 구문은 매출 데이터를 매장별로 그룹화하는 데 사용된다. 즉, 이 쿼리는 'store_cd' 컬럼의 고유한 매장 코드별로 '금액'과 '수량'의 합계를 반환한다.

첫 번째 코드가 두 번째 코드와 완전히 동일하기 때문에 중복 또는 실수일 수 있습니다. 그럼에도 불구하고 이 쿼리는 'receipt' 테이블에서 각 매장의 판매 상품의 총 금액과 수량을 선택하여 반환합니다.

S-024: 영수증 내역 데이터(df_receipt)에 대해 고객 ID(customer_id)별로 가장 최근 매출 날짜(sales_ymd)를 구하여 10건 표시하라.

In [27]:

%%sql

SELECT
    customer_id,
    MAX(sales_ymd)
FROM receipt
GROUP BY customer_id
LIMIT 10
;

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

Out[27]:

customer_id	max
CS001311000059	20180211
CS004614000122	20181228
CS003512000043	20180106
CS011615000061	20190503
CS029212000033	20180621
CS007515000119	20190511
CS034515000123	20190708
CS004315000058	20170517
CS026414000014	20190720
CS001615000099	20170729

설명:

이 코드는 "receipt"라는 테이블에서 각 고객의 최대 매출일(sales_ymd)을 선택하는 SQL 쿼리이다.

sql은 이 코드가 SQL 코드임을 나타내는 Jupyter Notebook의 셀 매직 명령어이다.

SELECT 키워드는 데이터베이스에서 데이터를 가져온다는 것을 나타내기 위해 사용된다.

customer_id와 MAX(sales_ymd)는 'RECIPATE' 테이블에서 선택하고자 하는 컬럼이며, MAX 함수는 'customer_id' 컬럼의 고유한 고객 ID마다 'sales_ymd' 컬럼의 최대값을 선택하기 위해 사용된다.

FROM 키워드는 데이터를 가져올 테이블을 나타내는 데 사용되며, 이 경우 'receive' 테이블이다.

GROUP BY 구문은 고객 ID별로 매출 데이터를 그룹화하는 데 사용된다. 즉, 이 쿼리는 'customer_id' 열의 고유한 고객 ID별로 최대 매출 날짜를 반환한다.

LIMIT 키워드는 쿼리가 반환하는 결과의 수를 제한하는 데 사용된다. 이 경우 'LIMIT 10' 문은 결과를 상위 10개의 행으로 제한한다.

즉, 요약하면, 이 쿼리는 'receipt' 테이블에서 각 고객의 최대 판매일을 선택하여 반환하고, 결과를 상위 10명의 고객으로 제한한다.

S-025: 영수증 내역 데이터(df_receipt)에 대해 고객 ID(customer_id)별로 가장 오래된 매출 날짜(sales_ymd)를 구하여 10건 표시하라.

In [28]:

%%sql

SELECT
    customer_id,
    MIN(sales_ymd)
FROM receipt
GROUP BY customer_id
LIMIT 10
;

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

Out[28]:

customer_id	min
CS001311000059	20180211
CS004614000122	20181228
CS003512000043	20180106
CS011615000061	20190503
CS029212000033	20170318
CS007515000119	20170201
CS034515000123	20170527
CS004315000058	20170517
CS026414000014	20170718
CS001615000099	20170729

설명:

이 코드는 "receipt"라는 테이블에서 각 고객의 최소 매출일(sales_ymd)을 선택하는 SQL 쿼리이다.

sql은 이 코드가 SQL 코드임을 나타내는 Jupyter Notebook의 셀 매직 명령어이다.

SELECT 키워드는 데이터베이스에서 데이터를 가져온다는 것을 나타내기 위해 사용된다.

customer_id와 MIN(sales_ymd)은 'RECIPATE' 테이블에서 선택하고자 하는 컬럼이며, MIN 함수는 'customer_id' 컬럼의 고유한 고객 ID마다 'sales_ymd' 컬럼의 최소값을 선택하기 위해 사용된다.

FROM 키워드는 데이터를 가져올 테이블을 지정하는 데 사용되며, 이 경우 'receive' 테이블에서 데이터를 가져온다.

GROUP BY 구문은 고객 ID별로 매출 데이터를 그룹화하는 데 사용된다. 즉, 이 쿼리는 'customer_id' 열의 고유한 고객 ID별로 최소 매출 날짜 값을 반환한다.

LIMIT 키워드는 쿼리가 반환하는 결과의 수를 제한하는 데 사용된다. 이 경우 'LIMIT 10' 문은 결과를 상위 10개의 행으로 제한합니다.

즉, 요약하면 이 쿼리는 'receipt' 테이블에서 각 고객의 최소 매출일을 선택하여 반환하고 결과를 상위 10명의 고객으로 제한하고 있다.

S-026: 영수증 내역 데이터(df_receipt)에 대해 고객 ID(customer_id)별로 가장 최근 매출 날짜(sales_ymd)와 가장 오래된 매출 날짜(sales_ymd)를 구하고, 양자가 서로 다른 데이터 10건을 표시하라.

In [29]:

%%sql

SELECT
    customer_id,
    MAX(sales_ymd),
    MIN(sales_ymd)
FROM receipt
GROUP BY customer_id
HAVING MAX(sales_ymd) != MIN(sales_ymd)
LIMIT 10
;

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

Out[29]:

customer_id	max	min
CS029212000033	20180621	20170318
CS007515000119	20190511	20170201
CS034515000123	20190708	20170527
CS026414000014	20190720	20170718
CS010515000082	20181204	20180518
CS019315000045	20170920	20170423
CS008513000099	20190308	20170722
CS007615000070	20191025	20170929
CS025415000155	20191026	20170314
CS016414000063	20190617	20170109

설명:

이 코드는 "receipt"라는 테이블에서 고객 ID, 최대 매출일, 최소 매출일을 선택하는 SQL 쿼리이다.

sql은 이 코드가 SQL 코드임을 나타내는 Jupyter Notebook의 셀 매직 명령어이다.

SELECT 키워드는 데이터베이스에서 데이터를 가져온다는 것을 나타내기 위해 사용된다.

customer_id, MAX(sales_ymd), MIN(sales_ymd)은 'recipe' 테이블에서 선택하고자 하는 컬럼이며, MAX는 'customer_id' 컬럼의 고유한 고객 ID별로 'sales_ymd' 컬럼의 최대값을 선택하는 데 사용되며, 'MIN'은 'recipe' 컬럼의 최대값을 선택하는 데 사용된다. 'MIN'은 'customer_id' 열의 고유한 고객 ID별로 'sales_ymd' 열의 최소값을 선택하기 위해 사용된다.

FROM 키워드는 데이터를 가져오고자 하는 테이블을 나타내는 데 사용되며, 이 경우 'receive' 테이블이다.

GROUP BY 구문은 고객 ID별로 매출 데이터를 그룹화하는 데 사용된다. 즉, 이 쿼리는 "customer_id" 열의 고유한 고객 ID별로 매출 날짜의 최대값과 최소값을 반환한다.

HAVING 절은 특정 조건에 따라 결과를 필터링하는 데 사용된다. 이 경우, 주어진 고객 ID에 대해 최대 판매일과 최소 판매일이 같지 않아야 한다는 조건이 있다. 즉, 이 쿼리는 서로 다른 날짜에 구매한 고객만 반환하게 됩니다.

LIMIT 키워드는 쿼리가 반환하는 결과의 수를 제한하는 데 사용된다. 이 경우 'LIMIT 10' 문은 결과를 상위 10개의 행으로 제한한다.

즉, 이 쿼리는 '영수증' 테이블에서 서로 다른 날짜에 구매한 고객의 고객 ID, 최대 판매일, 최소 판매일을 선택하여 반환하고, 결과를 상위 10명의 고객으로 제한하고 있다.

S-027: 영수증 내역 데이터(df_receipt)에 대해 매장 코드(store_cd)별 매출 금액(amount)의 평균을 계산하여 내림차순으로 TOP5를 표시하라.

In [30]:

%%sql

SELECT
    store_cd,
    AVG(amount) AS avg_amount
FROM receipt
GROUP BY store_cd
ORDER BY avg_amount DESC
LIMIT 5
;

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

Out[30]:

store_cd	avg_amount
S13052	402.8674698795180723
S13015	351.1119604316546763
S13003	350.9155188246097337
S14010	348.7912621359223301
S13001	348.4703862660944206

설명:

이 코드는 "receipt"라는 테이블에서 매장 코드와 그 평균 매출액을 선택하는 SQL 쿼리이다.

sql은 이 코드가 SQL 코드임을 나타내는 Jupyter Notebook의 셀 매직 명령어이다.

SELECT 키워드는 데이터베이스에서 데이터를 가져온다는 것을 나타내기 위해 사용된다.

"store_cd"와 "AVG(amount) AS avg_amount"는 "receipt" 테이블에서 선택하고자 하는 컬럼이며, AVG 함수는 "store_cd" 컬럼의 고유한 매장 코드별 평균 판매 금액을 계산하는 데 사용되며, AS 키워드는 이 컬럼에 "AVG(amount) AS avg_amount"를 추가하는 데 사용된다. avg_amount'라는 별칭을 부여하는 데 사용됩니다. 이 이름은 쿼리 결과에 표시되는 이름입니다.

FROM 키워드는 데이터를 가져올 테이블을 나타내는 데 사용되며, 이 경우 "receipt" 테이블이다.

GROUP BY 구문은 판매 데이터를 매장 코드별로 그룹화하는 데 사용됩니다. 즉, 이 쿼리는 'store_cd' 열의 고유한 매장 코드별로 평균 매출 금액을 계산하는 것이다.

ORDER BY 구문은 평균 매출액을 기준으로 결과를 내림차순으로 정렬하는 데 사용됩니다. 즉, 평균 매출 금액이 가장 높은 매장이 결과의 상단에 표시되는 것입니다.

LIMIT 키워드는 쿼리가 반환하는 결과의 수를 제한하는 데 사용됩니다. 이 경우 'LIMIT 5' 문장은 결과를 상위 5개 매장으로 제한합니다.

즉, 이 쿼리는 '영수증' 테이블에서 매장 코드와 평균 매출액을 선택하고, 평균 매출액을 기준으로 내림차순으로 정렬하여 상위 5개 매장으로만 결과를 제한하여 반환합니다.

S-028: 영수증 내역 데이터(df_receipt)에 대해 매장 코드(store_cd)별 매출 금액(amount)의 중앙값을 계산하여 내림차순으로 TOP5를 표시하라.

In [31]:

%%sql

SELECT 
    store_cd, 
    PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY amount) AS amount_50per
FROM receipt
GROUP BY store_cd
ORDER BY amount_50per DESC
LIMIT 5
;

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

Out[31]:

store_cd	amount_50per
S13052	190.0
S14010	188.0
S14050	185.0
S13003	180.0
S13018	180.0

설명:

다음은 "receipt"라는 이름의 테이블에서 데이터를 가져오는 SQL 쿼리이다.

이 쿼리는 테이블에서 두 개의 컬럼을 선택합니다." store_cd"와 "amount_50per"다. "store_cd"열은 매장 코드를 나타내고, "amount_50per"열은 각 매장 내 영수증 1장당 사용 금액의 중간값을 계산한다.

중앙값 계산에 사용하는 함수는 'PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY amount)'이다. 이 함수는 '금액' 열을 오름차순으로 정렬하여 50퍼센타일 값을 계산한다. 즉, 순서대로 정렬된 금액 목록의 중간 값을 찾는 것이다.

이 쿼리는 'GROUP BY' 구문으로 결과를 상점 코드별로 그룹화하고, 'ORDER BY' 구문으로 금액의 중간값 내림차순으로 결과를 정렬한다. 그리고 'LIMIT' 절을 사용하여 출력을 상위 5건으로 제한하고 있습니다.

즉, 이 쿼리는 영수증 한 장당 사용 금액의 중간값이 가장 높은 상위 5개 매장을 반환합니다.

S-029: 영수증 내역 데이터(df_receipt)에 대해 매장 코드(store_cd)별로 상품 코드(product_cd)의 최빈값을 구하여 10건씩 표시하라.

In [32]:

%%sql

-- 코드 예시 1: window 함수나 분석 함수에서 가장 빈번한 값 집계하기
WITH product_cnt AS (
    SELECT
        store_cd,
        product_cd,
        COUNT(1) AS mode_cnt
    FROM receipt
    GROUP BY
        store_cd,
        product_cd
),
product_mode AS (
    SELECT
        store_cd,
        product_cd,
        mode_cnt,
        RANK() OVER(PARTITION BY store_cd ORDER BY mode_cnt DESC) AS rnk
    FROM product_cnt
)
SELECT
    store_cd,
    product_cd,
    mode_cnt
FROM product_mode
WHERE
    rnk = 1
ORDER BY
    store_cd,
    product_cd
LIMIT 10
;

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

Out[32]:

store_cd	product_cd	mode_cnt
S12007	P060303001	72
S12013	P060303001	107
S12014	P060303001	65
S12029	P060303001	92
S12030	P060303001	115
S13001	P060303001	67
S13002	P060303001	78
S13003	P071401001	65
S13004	P060303001	88
S13005	P040503001	36

설명:

"receipt"라는 테이블에서 데이터를 가져오는 SQL 쿼리입니다.

이 쿼리에서는 "product_cnt"라는 CTE(Common Table Expression)를 사용하여 각 상품이 각 매장에서 판매된 횟수를 계산하고, "GROUP BY" 구문을 사용하여 "store_cd"와 "product_cd"로 데이터를 그룹화하고, "COUNT(1)" 함수를 사용하여 각 그룹별 행 수를 계산합니다. COUNT(1)' 함수를 사용하여 각 그룹의 행 수를 계산한다. 결과는 "mode_cnt" 컬럼에 저장된다.

그런 다음 이 쿼리는 "product_mode"라는 또 다른 CTE를 사용하여 "RANK() OVER(PARTITION BY store_cd ORDER BY mode_cnt DESC)" 함수를 사용하여 각 매장의 상품의 "mode_cnt" 값의 순위를 매긴다. 이 함수는 각 상품의 "mode_cnt" 값에 따라 각 매장 내 순위를 부여한다. 모드 카운트가 가장 높은 상품에 1순위를 부여한다.

마지막으로 이 쿼리는 "WHERE rnk = 1" 구문으로 결과를 필터링하여 각 매장에서 가장 많이 팔리는 상품을 선택하고 "ORDER BY" 구문으로 매장 및 상품 코드별로 정렬한다. 'LIMIT' 구문으로 출력을 처음 10줄로 제한하고, 각 매장의 인기 상품의 매장 코드, 상품 코드, 모드 수를 반환한다.

요약하면, 이 쿼리는 판매 횟수를 기준으로 각 매장에서 가장 많이 팔린 상품을 찾아 상위 10개 상품의 매장 코드, 상품 코드, 모드 수를 각각 반환한다.

In [33]:

%%sql

-- 코드 예시 2: MODE()를 사용하는 간단한 경우(빠르지만, 최대값이 여러 개일 경우 한 개만 선택됨)
SELECT
    store_cd,
    MODE() WITHIN GROUP(ORDER BY product_cd)
FROM receipt
GROUP BY store_cd
ORDER BY store_cd
LIMIT 10
;

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

Out[33]:

store_cd	mode
S12007	P060303001
S12013	P060303001
S12014	P060303001
S12029	P060303001
S12030	P060303001
S13001	P060303001
S13002	P060303001
S13003	P071401001
S13004	P060303001
S13005	P040503001

설명:

다음은 "receipt"라는 테이블에서 데이터를 가져오는 SQL 쿼리이다.

이 쿼리에서는 두 개의 컬럼을 선택한다." store_cd"와 각 매장의 "product_cd" 열의 모드입니다.

'MODE() WITHIN GROUP(ORDER BY product_cd)' 함수는 'store_cd'의 각 그룹 내 'product_cd' 값의 모드를 계산한다. mode는 데이터 집합에서 가장 빈번하게 나타나는 값을 의미하며, ORDER BY 구문은 모드를 계산하기 전에 product_cd 값을 오름차순으로 정렬할 것을 지정한다.

이 쿼리는 'GROUP BY' 절을 사용하여 'store_cd'로 데이터를 그룹화하고, 'ORDER BY' 절을 사용하여 'store_cd'로 결과를 정렬한다. 또한 'LIMIT' 구문으로 출력을 처음 10줄로 제한하고 있다.

즉, 이 쿼리는 각 매장의 'product_cd' 값의 모드를 반환하는데, 이는 각 매장에서 가장 많이 판매되는 상품을 나타낸다. 결과는 매장 코드 순으로 정렬되며 상위 10개 매장으로 제한된다.

S-030: 영수증 내역 데이터(df_receipt)에 대해 매장 코드(store_cd)별 매출 금액(amount)의 분산을 계산하여 내림차순으로 5개씩 표시하시오.

In [34]:

%%sql

SELECT
    store_cd,
    VAR_POP(amount) AS vars_amount
FROM receipt
GROUP BY store_cd
ORDER BY vars_amount DESC 
LIMIT 5
;

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

Out[34]:

store_cd	vars_amount
S13052	440088.701311269173
S14011	306314.558163888889
S14034	296920.081011283873
S13001	295431.993329035348
S13015	295294.361115940880

설명:

다음은 "receipt"라는 테이블에서 데이터를 가져오는 SQL 쿼리이다.

이 쿼리에서는 두 개의 컬럼을 선택한다." store_cd"와 각 매장의 "amount" 컬럼의 모분산이다.

'VAR_POP(amount)' 함수는 'store_cd'의 각 그룹 내 'amount' 값의 분산을 계산한다. 분산은 데이터 세트의 확산과 분산을 나타내는 지표이며, "VAR_POP"의 "POP"는 "population"을 의미하며, 분산 계산을 위해 전체 데이터 세트가 사용됨을 의미한다.

이 쿼리는 "GROUP BY" 절을 사용하여 "store_cd"로 데이터를 그룹화하고 "ORDER BY" 절을 사용하여 각 매장의 "amount" 값의 분산을 포함하는 "vars_amount" 열로 결과를 내림차순으로 정렬하고 "LIMIT" 절을 사용하여 출력을 처음 5개 행으로 제한한다. 행으로 출력을 제한합니다.

즉, 이 쿼리는 '금액' 값의 모집단 분산이 가장 큰 상위 5개 매장을 반환하는데, 이는 이들 매장이 영수증 한 장당 사용 금액의 확산과 분산이 가장 크다는 것을 의미한다.

S-031: 영수증 내역 데이터(df_receipt)에 대해 매장 코드(store_cd)별 매출 금액(amount)의 표준편차를 계산하여 내림차순으로 5건씩 표시하라.

In [35]:

%%sql

SELECT
    store_cd,
    STDDEV_POP(amount) as stds_amount
FROM receipt
GROUP BY store_cd
ORDER BY stds_amount DESC
LIMIT 5
;

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

Out[35]:

store_cd	stds_amount
S13052	663.391815830787
S14011	553.456916267101
S14034	544.903735545357
S13001	543.536561170484
S13015	543.409938366921

설명:

다음은 "receipt"라는 테이블에서 데이터를 가져오는 SQL 쿼리이다.

이 쿼리에서는 두 개의 컬럼을 선택합니다." store_cd"와 각 매장의 "amount" 컬럼의 모표준편차이다.

"STDDEV_POP(amount)" 함수는 "store_cd"의 각 그룹 내 "amount" 값의 표준편차를 계산한다. 표준편차는 데이터 세트의 확산과 분산을 나타내는 지표로, "STDDEV_POP"의 "POP"는 "population"을 의미하며, 표준편차 계산에 전체 데이터 세트가 사용됨을 의미한다.

이 쿼리는 "GROUP BY" 구문을 사용하여 "store_cd"로 데이터를 그룹화하고 "ORDER BY" 구문을 사용하여 내림차순으로 각 매장의 "금액"의 표준편차를 포함하는 "stds_amount" 열에 결과를 정렬하고 "LIMIT" 구문을 사용하여 출력을 처음 5줄로 제한한다.

즉, 이 쿼리는 '금액' 값의 모표준편차가 가장 높은 상위 5개 매장을 반환하므로, 이들 매장은 영수증 한 장당 사용 금액의 확산과 분산이 가장 높다는 것을 의미한다.

S-032: 영수증 내역 데이터(df_receipt)의 매출 금액(amount)에 대해 25% 단위로 백분위수 값을 구하라.

In [36]:

%%sql

SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY amount) AS amount_25per,
    PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY amount) AS amount_50per,
    PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY amount) AS amount_75per,
    PERCENTILE_CONT(1.0) WITHIN GROUP(ORDER BY amount) AS amount_100per
FROM receipt
;

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

Out[36]:

amount_25per	amount_50per	amount_75per	amount_100per
102.0	170.0	288.0	10925.0

설명:

"recipate" 테이블의 "amount" 컬럼의 백분위수 값을 계산하기 위해 사용하는 SQL 코드이다. 이 코드에서는 PERCENTILE_CONT 함수를 사용하여 연속적인 백분위수를 계산하고 있다. 이는 백분위수 값이 데이터 포인트 간의 보간값이 아니라 데이터 집합의 실제 값에 해당한다는 것을 의미합니다.

이 코드에서는 25번째 백분위수(amount_25per), 50번째 백분위수(amount_50per), 75번째 백분위수(amount_75per), 100번째 백분위수(amount_100per)의 네 가지 백분위수 값을 지정하여 계산하고 있다. 이 백분위수는 백분위수를 계산하기 전에 WITHIN GROUP 절을 사용하여 'amount' 열을 오름차순으로 정렬합니다.

코드 마지막에 있는 SELECT 문은 계산된 백분위수 값을 쿼리에서 가져와 한 줄의 출력으로 반환한다. 이 코드는 'RECIPATE' 테이블의 'AMOUNT' 컬럼의 분포를 분석하여 데이터 내 이상값이나 비정상적인 패턴을 식별하는 데 유용하다.

S-033: 영수증 내역 데이터(df_receipt)에 대해 매장 코드(store_cd)별 매출 금액(amount)의 평균을 계산하여 330개 이상을 추출하라.

In [37]:

%%sql

SELECT
    store_cd,
    AVG(amount) AS avg_amount
FROM receipt
GROUP BY store_cd
HAVING
    AVG(amount) >= 330
;

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

Out[37]:

store_cd	avg_amount
S13052	402.8674698795180723
S13019	330.2086158755484643
S13003	350.9155188246097337
S14045	330.0820734341252700
S13004	330.9439490445859873
S13001	348.4703862660944206
S14026	332.3405884723901653
S14010	348.7912621359223301
S13015	351.1119604316546763
S12013	330.1941299790356394
S14047	330.0770731707317073
S14011	335.7183333333333333
S13020	337.8799321170980059

설명:

다음은 "receipt" 테이블의 각 매장의 평균 거래금액을 조회하고 조건에 따라 결과를 필터링하는 데 사용하는 SQL 코드입니다.

이 코드에서는 먼저 SELECT 문에서 조회할 두 개의 컬럼('store_cd' 컬럼과 각 매장의 '금액' 컬럼의 평균값)을 지정하고, AVG 함수를 사용하여 각 매장의 'amount' 컬럼의 평균을 계산한 후, AS 키워드를 사용하여 계산된 컬럼에 'avg_amount' 라는 별칭을 부여하고 있습니다. 라는 별칭을 부여하고 있습니다.

다음 코드 라인에서는 GROUP BY 구문을 사용하여 'store_cd' 열로 데이터를 그룹화하여 평균 거래 금액이 각 매장에서 개별적으로 계산됨을 의미합니다.

그런 다음 이 코드는 HAVING 절을 사용하여 조건에 따라 결과를 필터링한다. 구체적으로 각 매장의 평균 거래액이 330 이상일 것을 조건으로 한다. 이 조건을 만족하지 않는 매장은 출력에 포함되지 않는다.

결과적으로 출력되는 것은 두 개의 컬럼을 가진 테이블이다. 'store_cd'와 'avg_amount' 두 개의 컬럼을 가진 테이블이 출력된다. 각 행은 HAVING 절에서 지정한 조건을 만족하는 'receipt' 테이블의 매장에 해당하며, 'avg_amount' 열에는 해당 매장의 평균 거래 금액이 포함된다. 이 코드는 평균 거래금액을 기준으로 다른 매장의 성과를 분석하는 데 유용하다.

S-034: 영수증 내역 데이터(df_receipt)에 대해 고객 ID(customer_id)별로 매출 금액(amount)을 합산하여 전체 고객의 평균을 구하시오. 단, 고객 ID가 “Z”로 시작하는 것은 비회원을 의미하므로 제외하여 계산한다.

In [38]:

%%sql

WITH customer_amount AS (
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY customer_id
)
SELECT
    AVG(sum_amount)
FROM customer_amount
;

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

Out[38]:

avg
2547.7422345292559595

설명:

"영수증" 테이블에서 ID가 "Z"로 시작하는 고객을 제외한 각 고객이 사용한 총 금액의 평균을 계산하는 SQL 코드입니다.

이 코드는 WITH 절을 사용하여 정의된 공통 테이블 표현식(CTE)으로 시작한다. 이 CTE는 'customer_amount'라는 이름으로 'receive' 테이블의 각 고객의 'customer_id' 열과 'amount' 열의 합계를 구하는 SELECT 문이 포함되어 있으며, WHERE 절은 NOT LIKE 연산자를 사용하여 ID가 'Z'로 시작하는 고객을 필터링하고 있습니다. 시작하는 고객을 필터링하고 있습니다. 결과는 "customer_id" 컬럼으로 그룹화되며, 결과 테이블에는 두 개의 컬럼이 있습니다. "customer_id"와 "sum_amount"(각 고객이 사용한 금액의 합계)입니다.

다음 코드 라인은 "customer_amount" CTE에서 "sum_amount" 컬럼의 평균값을 가져오는 것으로, SELECT 문은 AVG 함수를 사용하여 ID가 "Z"로 시작하지 않는 각 고객이 사용한 총 금액의 평균을 나타내는 "sum_amount" 컬럼의 평균값을 계산합니다. 를 계산합니다.

결과적으로 출력되는 것은 "customer_amount" CTE의 "sum_amount" 열의 평균을 나타내는 하나의 값입니다. 이 코드는 고객의 평균 지출 패턴을 분석하고 데이터에서 이상값이나 비정상적인 패턴을 식별하는 데 유용하다.

S-035: 영수증 내역 데이터(df_receipt)에 대해 고객 ID(customer_id)별로 매출 금액(amount)을 합산하여 전체 고객의 평균을 구하고, 평균 이상 쇼핑을 한 고객을 추출하여 10건씩 표시하시오. 단, 고객 ID가 “Z”로 시작하는 것은 비회원을 의미하므로 제외하여 계산한다.

In [39]:

%%sql

WITH customer_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 customer_amount
WHERE
    sum_amount >= (
        SELECT
            AVG(sum_amount)
        FROM customer_amount
    )
LIMIT 10
;

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

Out[39]:

customer_id	sum_amount
CS029212000033	3604
CS007515000119	7157
CS034515000123	3699
CS026414000014	6671
CS007615000070	2975
CS016414000063	6207
CS012514000018	2562
CS029515000142	3420
CS015215000021	3090
CS039814000011	8031

설명:

ID가 "Z"로 시작하는 고객을 제외하고 "receipt" 테이블의 고객 평균 총액보다 더 많이 사용한 상위 10명의 고객의 "customer_id"와 "sum_amount" 열을 검색하는 SQL 코드입니다.

이 코드는 WITH 절을 사용하여 정의된 공통 테이블 표현식(CTE)으로 시작합니다. 이 CTE는 'customer_amount'라는 이름으로 'receive' 테이블의 각 고객의 'customer_id' 열과 'amount' 열의 합계를 구하는 SELECT 문이 포함되어 있으며, WHERE 절은 NOT LIKE 연산자를 사용하여 ID가 'Z'로 시작하는 고객을 필터링하고 있습니다. 시작하는 고객을 필터링하고 있습니다. 결과는 "customer_id" 컬럼으로 그룹화되며, 결과 테이블에는 두 개의 컬럼이 있습니다. "customer_id"와 "sum_amount"(각 고객이 사용한 금액의 합계)입니다.

다음 코드에서는 "customer_amount" CTE에서 "customer_id"와 "sum_amount" 컬럼을 가져오는데, "sum_amount"가 CTE의 모든 고객에 대해 계산한 평균 "sum_amount" 이상인 고객에 대해서만 가져옵니다. 를 사용합니다. 이는 AVG 함수를 사용하여 평균 'sum_amount'를 계산하고 WHERE 절을 사용하여 'customer_amount' CTE를 필터링하는 하위 쿼리를 사용하여 구현됩니다.

이 쿼리의 결과는 LIMIT 절을 사용하여 상위 10개의 행으로 제한된다.

그 결과 두 개의 컬럼을 가진 테이블이 출력된다. 'customer_id'와 'sum_amount' 두 개의 열을 가진 테이블이 출력된다. 각 행은 ID가 "Z"로 시작하는 고객을 제외하고 "receive" 테이블의 고객 평균 총액보다 더 많은 금액을 사용한 고객에 해당한다. 테이블은 "sum_amount" 컬럼을 기준으로 내림차순으로 정렬되며, 상위 10개의 행만 출력에 포함된다. 이 코드는 고액 쇼핑을 하는 고객을 식별하고 그들의 소비 패턴을 분석하는 데 유용하다.

S-036: 영수증 내역 데이터(df_receipt)와 매장 데이터(df_store)를 내부적으로 결합하여 영수증 내역 데이터의 모든 항목과 매장 데이터의 매장명(store_name)을 10건씩 표시하라.

In [40]:

%%sql

SELECT
    r.*,
    s.store_name
FROM receipt r
JOIN store s
ON
    r.store_cd = s.store_cd
LIMIT 10
;

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

Out[40]:

sales_ymd	sales_epoch	store_cd	receipt_no	receipt_sub_no	customer_id	product_cd	quantity	amount	store_name
20181103	1541203200	S14006	112	1	CS006214000001	P070305012	1	158	葛が谷店
20181118	1542499200	S13008	1132	2	CS008415000097	P070701017	1	81	成城店
20170712	1499817600	S14028	1102	1	CS028414000014	P060101005	1	170	二ツ橋店
20190205	1549324800	S14042	1132	1	ZZ000000000000	P050301001	1	25	新山下店
20180821	1534809600	S14025	1102	2	CS025415000050	P060102007	1	90	大和店
20190605	1559692800	S13003	1112	1	CS003515000195	P050102002	1	138	狛江店
20181205	1543968000	S14024	1102	2	CS024514000042	P080101005	1	30	三田店
20190922	1569110400	S14040	1102	1	CS040415000178	P070501004	1	128	長津田店
20170504	1493856000	S13020	1112	2	ZZ000000000000	P071302010	1	770	十条仲原店
20191010	1570665600	S14027	1102	1	CS027514000015	P071101003	1	680	南藤沢店

설명:

다음은 "receipt" 테이블과 "store" 테이블의 데이터를 포함하는 결합 테이블에서 처음 10개의 행을 가져오는 SQL 코드이다. 결과 테이블에는 "receive" 테이블의 모든 컬럼과 "store" 테이블의 추가 컬럼 "store_name"이 포함될 것이다.

코드는 가져올 컬럼을 지정하는 SELECT 문으로 시작한다. 이 경우 SELECT 문은 와일드카드(*)를 사용하여 'receipt' 테이블에서 모든 컬럼을 가져온다. 또한 SELECT 문에서는 출력 테이블에 포함된 'store' 테이블의 'store_name' 컬럼을 지정하고 있다.

FROM 구문은 'receipt' 테이블에서 데이터를 가져올 것을 지정하고, JOIN 구문은 'store' 테이블을 'receipt' 테이블에 'store_cd' 컬럼을 결합 조건으로 결합할 것을 지정한다. 이는 "store_cd" 열의 일치하는 값을 기준으로 "receive" 테이블과 "store" 테이블의 행이 하나의 테이블로 결합됨을 의미한다.

결과적으로 출력되는 것은 "receipt" 테이블의 모든 컬럼과 "store" 테이블의 추가 컬럼 "store_name"을 포함하는 테이블이다. 이 테이블은 LIMIT 절을 사용하여 처음 10개의 행으로 제한됩니다. 이 코드는 관련 두 테이블의 데이터를 분석하고 데이터를 하나의 테이블로 결합하여 분석하는 데 편리합니다.

S-037: 상품 데이터(df_product)와 카테고리 데이터(df_category)를 내부적으로 결합하여 상품 데이터의 전체 항목과 카테고리 데이터의 카테고리 소분류 이름(category_small_name) 10개를 표시하라.

In [41]:

%%sql

SELECT
    p.*,
    c.category_small_name
FROM product p
JOIN category c
ON
    p.category_small_cd = c.category_small_cd
LIMIT 10
;

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

Out[41]:

product_cd	category_major_cd	category_medium_cd	category_small_cd	unit_price	unit_cost	category_small_name
P040101001	04	0401	040101	198	149	弁当類
P040101002	04	0401	040101	218	164	弁当類
P040101003	04	0401	040101	230	173	弁当類
P040101004	04	0401	040101	248	186	弁当類
P040101005	04	0401	040101	268	201	弁当類
P040101006	04	0401	040101	298	224	弁当類
P040101007	04	0401	040101	338	254	弁当類
P040101008	04	0401	040101	420	315	弁当類
P040101009	04	0401	040101	498	374	弁当類
P040101010	04	0401	040101	580	435	弁当類

설명:

이 코드는 관계형 데이터베이스를 관리하고 쿼리하는 데 사용되는 프로그래밍 언어인 SQL로 작성되었습니다.

이 코드에서는 두 개의 테이블에서 데이터를 선택하고 있습니다. "product"와 "category"이며, "JOIN" 키워드를 사용하여 "category_small_cd" 컬럼의 값이 일치하는 두 테이블의 행을 결합하고 있습니다. 이렇게 하면 두 테이블의 컬럼을 포함하는 새로운 테이블이 생성된다.

'SELECT' 키워드는 출력에 포함할 컬럼을 선택하는 데 사용된다. 이 경우 'product' 테이블의 모든 컬럼('p.*' 구문으로 지정)과 'category' 테이블의 'category_small_name' 컬럼이 선택된다.

'LIMIT' 키워드는 출력을 처음 10줄로 제한한다. 이는 대규모 데이터 세트를 다룰 때 쿼리를 빠르게 테스트하거나 데이터 샘플을 가져오는 데 유용하다.

즉, 이 코드는 'product' 테이블과 'category' 테이블을 결합하여 'category_small_cd' 값이 일치하는 처음 10개의 행을 반환하고, 'product' 테이블의 모든 열과 'category' 테이블의 'category_small_name' 열을 포함한 'product' 테이블의 모든 열을 반환한다. small_name' 열을 포함하게 된다.

S-038: 고객 데이터(df_customer)와 영수증 내역 데이터(df_receipt)에서 고객별 매출 금액의 총합을 구하여 10건을 표시하시오. 단, 판매 실적이 없는 고객에 대해서는 판매금액을 0으로 표시한다. 또한, 고객은 성별 코드(gender_cd)가 여성(1)인 고객을 대상으로 하며, 비회원(고객 ID가 “Z”로 시작하는 고객)은 제외한다.

In [42]:

%%sql

WITH customer_amount AS (
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM receipt
    GROUP BY
        customer_id
),
customer_data AS (
    SELECT
        customer_id
    FROM customer
    WHERE
        gender_cd = '1'
        AND customer_id NOT LIKE 'Z%'
)
SELECT
    c.customer_id,
    COALESCE(a.sum_amount, 0)
FROM customer_data c
LEFT OUTER JOIN customer_amount a
ON
    c.customer_id = a.customer_id
LIMIT 10
;

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

Out[42]:

customer_id	coalesce
CS021313000114	0
CS031415000172	5088
CS028811000001	0
CS001215000145	875
CS015414000103	3122
CS033513000180	868
CS035614000014	0
CS011215000048	3444
CS009413000079	0
CS040412000191	210

설명:

이 코드도 SQL로 작성되었으며 이전 코드보다 조금 더 복잡하다. 이 코드는 세 개의 테이블에서 데이터를 선택한다." receive", "customer", "customer_amount"의 3개 테이블에서 데이터를 선택합니다.

이 코드에서는 "WITH" 절을 사용하여 두 개의 하위 쿼리를 정의하고 있다. 'customer_amount'와 'customer_data'입니다.

'customer_amount' 서브쿼리는 'SUM' 함수를 사용하여 'customer_id' 별로 'recipate' 테이블의 'amount' 열을 집계하고 그 결과에 'sum_amount'라는 별칭을 부여한다. 이 하위 쿼리는 두 개의 컬럼을 포함하는 새로운 테이블을 생성한다. 'customer_id'와 'sum_amount' 두 개의 컬럼을 포함하는 새로운 테이블이 생성됩니다.

'customer_data' 하위 쿼리는 'customer' 테이블에서 'gender_cd'가 '1'과 같고 'customer_id'가 문자 'Z'로 시작하지 않는 모든 행을 선택한다. 이 하위 쿼리는 하나의 컬럼을 포함하는 새로운 테이블을 생성한다. 'customer_id'를 포함하는 새 테이블을 생성한다.

마지막 쿼리는 'LEFT OUTER JOIN' 연산으로 'customer_data' 테이블과 'customer_amount' 테이블을 결합한다. "customer_data" 테이블의 모든 행과 "customer_amount" 테이블에 일치하는 행이 있으면 이를 반환하고, "customer_amount" 테이블에 일치하는 행이 없으면 "COALESCE" 함수를 사용하여 "sum_amount" 값을 0으로 설정한다. amount' 값을 0으로 대체한다.

SELECT 구문은 출력에 포함할 두 개의 컬럼을 선택한다. 'customer_data' 테이블의 'customer_id'와 'customer_amount' 테이블의 'COALESCE(a.sum_amount, 0)'('customer_amount'와 일치하는 행이 없으면 0) 'LIMIT' 구문은 출력을 처음 10개의 행으로 제한한다. 10행으로 제한한다.

즉, 이 코드는 'customer_id'가 'Z'로 시작하지 않는 남성 고객에 대해 'customer_data' 테이블의 처음 10개의 행과 해당 영수증 합계(또는 영수증이 없는 경우 0)를 반환한다.

S-039: 영수증 내역 데이터(df_receipt)에서 매출 일수가 많은 고객 상위 20건을 추출한 데이터와 매출 금액 합계가 많은 고객 상위 20건을 추출한 데이터를 각각 생성하고, 이 두 가지를 완전히 외부 결합하라. 단, 비회원(고객 ID가 “Z”로 시작하는 고객)은 제외한다.

In [43]:

%%sql

WITH customer_data AS (
    select
        customer_id,
        sales_ymd,
        amount
    FROM receipt
    WHERE
        customer_id NOT LIKE 'Z%'
),
customer_days AS (
    select
        customer_id,
        COUNT(DISTINCT sales_ymd) come_days
    FROM customer_data
    GROUP BY
        customer_id
    ORDER BY
        come_days DESC
    LIMIT 20
),
customer_amount AS (
    SELECT
        customer_id,
        SUM(amount) buy_amount
    FROM customer_data
    GROUP BY
        customer_id
    ORDER BY
        buy_amount DESC
    LIMIT 20
)
SELECT
    COALESCE(d.customer_id, a.customer_id) customer_id,
    d.come_days,
    a.buy_amount
FROM customer_days d
FULL OUTER JOIN customer_amount a
ON
    d.customer_id = a.customer_id
;

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

Out[43]:

customer_id	come_days	buy_amount
CS040214000008	23	None
CS015415000185	22	20153
CS010214000010	22	18585
CS028415000007	21	19127
CS010214000002	21	None
CS017415000097	20	23086
CS016415000141	20	18372
CS021514000045	19	None
CS022515000226	19	None
CS031414000051	19	19202
CS039414000052	19	None
CS014214000023	19	None
CS021515000172	19	None
CS031414000073	18	None
CS007515000107	18	None
CS014415000077	18	None
CS021515000056	18	None
CS032415000209	18	None
CS021515000211	18	None
CS022515000028	18	None
CS011415000006	None	16094
CS016415000101	None	16348
CS030415000034	None	15468
CS021515000089	None	17580
CS034415000047	None	16083
CS006515000023	None	18372
CS038415000104	None	17847
CS015515000034	None	15300
CS032414000072	None	16563
CS011414000106	None	18338
CS001605000009	None	18925
CS009414000059	None	15492
CS035414000024	None	17615
CS007514000094	None	15735

설명:

이 코드도 SQL로 작성되었으며 이전 코드보다 조금 더 복잡하다. "receipt" 테이블에서 데이터를 선택하고 3개의 하위 쿼리를 생성합니다. 'customer_data', 'customer_days', 'customer_amount'입니다.

customer_data 하위 쿼리는 'customer_id'가 'Z'로 시작하지 않는 'receive' 테이블의 행을 선택하고 세 개의 컬럼에 별칭을 부여한다." customer_id", "sales_ymd", "amount"의 세 열에 별칭을 부여한다. 이 하위 쿼리는 3개의 컬럼을 포함하는 새로운 테이블을 생성한다. 'customer_id', 'sales_ymd', 'amount' 3개의 컬럼을 포함하는 새 테이블이 생성된다.

customer_days 서브쿼리는 'customer_data' 서브쿼리의 'sales_ymd' 컬럼을 'COUNT(DISTINCT sales_ymd)' 함수를 사용하여 'customer_id' 별로 집계하고, 그 결과에 'come_days'라는 별칭을 붙인다. 붙입니다. 그리고 결과를 "come_days"로 내림차순으로 정렬하여 출력을 상위 20개 행으로 제한합니다. 이 하위 쿼리는 두 개의 컬럼을 포함하는 새로운 테이블을 생성합니다. "customer_id"와 "come_days" 두 개의 컬럼을 포함하는 새 테이블을 생성합니다.

'customer_amount' 하위 쿼리는 'customer_data' 하위 쿼리의 'amount' 컬럼을 'SUM' 함수를 사용하여 'customer_id' 별로 집계하고 그 결과에 'buy_amount'라는 별칭을 부여한다. 그리고 결과를 "buy_amount"로 내림차순으로 정렬하여 출력을 상위 20개 행으로 제한합니다. 이 하위 쿼리는 두 개의 컬럼을 포함하는 새로운 테이블을 생성한다. "customer_id"와 "buy_amount" 두 개의 컬럼을 포함하는 새 테이블이 생성됩니다.

마지막 쿼리는 "FULL OUTER JOIN" 연산을 사용하여 "customer_days" 테이블과 "customer_amount" 테이블을 결합한다. 두 테이블의 모든 행, 두 테이블의 일치하는 행, 일치하지 않는 경우 NULL 값을 반환하고, "COALESCE" 함수를 사용하여 "customer_id"의 NULL 값을 다른 테이블의 해당 값으로 바꾼다.

SELECT 구문은 출력에 포함할 3개의 컬럼을 선택한다." COALESCE(d.customer_id, a.customer_id)"에서 customer_id를, "customer_days" 테이블에서 "d.come_days"를, "customer_amount"에서 "a.buy_amount"를 가져오는 것이다.

즉, 이 코드에서는 'come_days'(=방문일수가 가장 많은 상위 20명의 고객과 'buy_amount'(=매장에서 사용한 총액)가 가장 많은 상위 20명의 고객이 customer_id와 함께 반환됩니다. 고객이 두 테이블에 모두 나타나는 경우, 출력에는 한 번만 나타납니다.

S-040: 모든 매장과 모든 상품을 조합한 데이터를 생성하고 싶다. 매장 데이터(df_store)와 상품 데이터(df_product)를 직적분하여 건수를 계산하라.

In [44]:

%%sql

SELECT
    COUNT(1)
FROM store
CROSS JOIN product
;

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

Out[44]:

count
531590

설명:

이 SQL 코드는 "store" 테이블과 "product" 테이블 간의 데카르트 곱을 수행하여 생성된 결과 집합의 행 개수를 구한다.

코드는 COUNT 함수를 지정한 SELECT 문으로 시작하며, 카운트할 값은 '1', 즉 행 수를 카운트하는 것을 의미한다.

FROM 절에서는 쿼리에서 사용할 두 개의 테이블, "store"와 "product"를 지정하고 있다. 하지만 두 테이블을 어떤 매칭 조건에 따라 결합하는 것이 아니라 CROSS JOIN 키워드가 사용되었습니다. 즉, "store" 테이블의 각 행과 "product" 테이블의 각 행이 짝을 이루어 데카르트 곱이 된다.

결과적으로 출력되는 것은 데카르트 곱의 행 수 개수를 포함하는 하나의 열을 가진 하나의 행이 된다. 이 개수는 "store" 테이블의 행 수와 "product" 테이블의 행 수를 곱한 것이다. 이 유형의 쿼리는 일반적으로 두 개 이상의 테이블에서 얻을 수 있는 데이터 조합 가능성의 총 수를 계산하는 데 사용된다.

데이터 사이언스 100번의 노크(구조화 데이터 처리편) – SQL Part 2 (Q21 to Q40)

처음에

사용법

데이터 가동 100번 노크

Comment