๋ฐ์ดํฐ ์ฌ์ด์ธ์ค 100๋ฒ์ ๋ ธํฌ(๊ตฌ์กฐํ ๋ฐ์ดํฐ ์ฒ๋ฆฌํธ)- SQL Part5 (Q81 to Q100)์ ํด์ค์ ๋๋ค.
ย
์ฐธ๊ณ (Reference) : ใ๋ฐ์ดํฐ ์ฌ์ด์ธํฐ์คํธ ํํ ์คํฌ ์ ์ ์์ใ์ ใ๋ฐ์ดํฐ ์ฌ์ด์ธ์ค 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 ์๋ํฐ ๋ฑ์ผ๋ก ๋๋ ์ถ๋ ฅ ๋ฒ์๋ฅผ ์ญ์ ํ ์๋ ์์ต๋๋ค.
- ์ด๋ฆ, ์ฃผ์ ๋ฑ์ ๋๋ฏธ ๋ฐ์ดํฐ์ด๋ฉฐ ์ค์ ์กด์ฌํ๋ ๊ฒ์ด ์๋๋๋ค.
%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
'Connected: padawan@dsdojo_db'
์ฌ์ฉ๋ฒ
์ ์ ๋งจ ์์ %%sql์ ๊ธฐ์ฌํ๊ณ , ๋ ๋ฒ์งธ ์ค ์ดํ์ SQL์ ์์ฑํ๋ฉด Jupyter์์ PostgreSQL์ ๋ํด SQL์ ์คํํ ์ ์์ต๋๋ค.
%%sql
SELECT '๋ค์๊ณผ ๊ฐ์ด ์คํ๋ฉ๋๋ค' AS sample;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
sample |
---|
๋ค์๊ณผ ๊ฐ์ด ์คํ๋ฉ๋๋ค |
๋ฐ์ดํฐ ๊ฐ๋ 100๋ฒ ๋ ธํฌ
S-081: ๋จ๊ฐ(unit_price)์ ์๊ฐ(unit_cost)์ ๊ฒฐ์๊ฐ์ ๋ํด ๊ฐ๊ฐ์ ํ๊ท ๊ฐ์ผ๋ก ๋ณด์ํ ์๋ก์ด ์ํ ๋ฐ์ดํฐ๋ฅผ ์์ฑํ์์ค. ๋จ, ํ๊ท ๊ฐ์ 1์ ๋ฏธ๋ง์ ๋ฐ์ฌ๋ฆผํ๋ค(๋ฐ์ฌ๋ฆผ ๋๋ ์ง์๋ก ๋ฐ์ฌ๋ฆผํด๋ ๋ฌด๋ฐฉํ๋ค). ๋ณด์ ์ค์ ํ ๊ฐ ํญ๋ชฉ์ ๋ํด ๊ฒฐ์์ด ๋ฐ์ํ์ง ์์๋์ง๋ ํ์ธํด์ผ ํ๋ค.
%%sql
DROP TABLE IF EXISTS product_2;
CREATE TABLE product_2 AS (
SELECT
product_cd,
category_major_cd,
category_medium_cd,
category_small_cd,
COALESCE(unit_price, unit_avg) AS unit_price,
COALESCE(unit_cost, cost_avg) AS unit_cost
FROM
product
CROSS JOIN (
SELECT
ROUND(AVG(unit_price)) AS unit_avg,
ROUND(AVG(unit_cost)) AS cost_avg
FROM
product
) stats_product
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 10030 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ SQL ์ธ์ด๋ก ์์ฑ๋์์ผ๋ฉฐ, ๋ค์๊ณผ ๊ฐ์ ๋จ๊ณ๋ฅผ ์ํํฉ๋๋ค.
product_2๋ผ๋ ํ ์ด๋ธ์ด ์ด๋ฏธ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์กด์ฌํ๋ ๊ฒฝ์ฐ, ํด๋น ํ ์ด๋ธ์ ์ญ์ ํ๋ค. ์ด๋ ์๋ฌด๊ฒ๋ ์๋ ์ํ์์ ์์ํ๋๋ก ํ๊ธฐ ์ํด ์ํ๋ฉ๋๋ค.
CREATE TABLE ๋ฌธ์ ์ฌ์ฉํ์ฌ product_2๋ผ๋ ์๋ก์ด ํ ์ด๋ธ์ด ์์ฑ๋๋ค.
์ ํ ์ด๋ธ์ ๋ฐ์ดํฐ๋ SELECT ๋ฌธ์ ์ฌ์ฉํ์ฌ product ํ ์ด๋ธ์์ ๊ฐ์ ธ์จ๋ค.
SELECT ๋ฌธ์์๋ product_cd, category_major_cd, category_medium_cd, category_small_cd๋ผ๋ ์ด์ด product ํ ์ด๋ธ์์ ์ ํ๋๋ค.
SELECT ๋ฌธ์์ COALESCE ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๋ ๊ฐ์ ์๋ก์ด ์ด์ด ์์ฑ๋๋ฉฐ, COALESCE๋ ์์ ๋ชฉ๋ก์์ ์ฒซ ๋ฒ์งธ NULL์ด ์๋ ๊ฐ์ ๋ฐํํ๋ค. ์ด ๊ฒฝ์ฐ unit_price๊ฐ NULL์ธ ๊ฒฝ์ฐ ์ ์ฒด ์ํ์ ํ๊ท ๋จ๊ฐ(unit_avg)๊ฐ ๋์ ์ฌ์ฉ๋๋ค. ๋ง์ฐฌ๊ฐ์ง๋ก unit_cost๊ฐ NULL์ธ ๊ฒฝ์ฐ ์ ์ฒด ์ํ์ ํ๊ท ๋จ๊ฐ(cost_avg)๊ฐ ๋์ ์ฌ์ฉ๋๋ค.
SELECT ๋ฌธ์ FROM ๊ตฌ๋ฌธ์ ์ํ ํ ์ด๋ธ์ ๋ชจ๋ ์ํ์ ํ๊ท ๋จ๊ฐ์ ๋จ๊ฐ๋ฅผ ๊ณ์ฐํ๋ ํ์ ์ฟผ๋ฆฌ์ CROSS JOIN์ ์ํํ๋ฉฐ, ROUND ํจ์๋ ํ๊ท ๊ฐ์ ๊ฐ์ฅ ๊ฐ๊น์ด ์ ์๋ก ๋ฐ์ฌ๋ฆผํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
SELECT ๋ฌธ ๊ฒฐ๊ณผ๋ CREATE TABLE ๋ฌธ์ ์ํด ์๋ฌต์ ์ผ๋ก ์คํ๋๋ INSERT INTO ๋ฌธ์ผ๋ก product_2 ํ ์ด๋ธ์ ์ ๋ ฅํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
์ด ์ฝ๋์์๋ product_2๋ผ๋ ์๋ก์ด ํ ์ด๋ธ์ด ์์ฑ๋์ด product ํ ์ด๋ธ๊ณผ ๋์ผํ ์ด์ ํฌํจํ์ง๋ง, unit_price์ unit_cost ์ด์ null ๊ฐ์ ๋ชจ๋ ์ํ์ ๋ํด ๊ณ์ฐ๋ ํ๊ท ๋จ๊ฐ์ ๋จ๊ฐ๋ก ๊ฐ๊ฐ ๋์ฒด๋ฉ๋๋ค.
%%sql
SELECT
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_2
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
unit_price | unit_cost |
---|---|
0 | 0 |
์ค๋ช :
์ด ์ฝ๋๋ SQL ์ธ์ด๋ก ์์ฑ๋์์ผ๋ฉฐ, ๋ค์ ๋จ๊ณ๋ฅผ ์ํํ๋ค.
SELECT ๋ฌธ์ ์คํํ์ฌ product_2 ํ ์ด๋ธ์ ์กฐํํ๋ค.
SUM ํจ์๋ฅผ CASE ์๊ณผ ํจ๊ป ์ฌ์ฉํ์ฌ unit_price ์ด๊ณผ unit_cost ์ด์ NULL ๊ฐ์ ๊ฐ์๋ฅผ ๋ณ๋๋ก ๊ณ์ฐํ๋ค.
๊ฐ CASE ์์์ WHEN unit_price IS NULL ๋๋ WHEN unit_cost IS NULL์ ํด๋น ์ปฌ๋ผ์ ๊ฐ์ด NULL์ธ์ง ์ฌ๋ถ๋ฅผ ํ์ธํ๋ค.
์ปฌ๋ผ์ NULL ๊ฐ์ด ์์ผ๋ฉด CASE ํํ์์ 1๋ก ํ๊ฐ๋๊ณ , ๊ทธ๋ ์ง ์์ผ๋ฉด 0์ผ๋ก ํ๊ฐ๋๋ค.
๊ทธ๋ฐ ๋ค์ SUM ํจ์๊ฐ ๊ฐ ํ์ 1๊ณผ 0์ ํฉํ์ฌ unit_price์ unit_cost ์ด์ NULL ๊ฐ์ ์ด ๊ฐ์๋ฅผ ๊ฐ๊ฐ ๊ณ์ฐํ๋ค.
AS ํค์๋๋ ๊ฒฐ๊ณผ ์งํฉ์ ์ด์ ์๋ฏธ ์๋ ์ด๋ฆ, ์ฆ unit_price์ unit_cost๋ก ๋ง๋ค๊ธฐ ์ํด ์ฌ์ฉ๋๋ค.
๋ง์ง๋ง์ผ๋ก LIMIT ๊ตฌ๋ฌธ์ ์ถ๋ ฅ์ ์ฒ์ 10์ค๋ก ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋๋ product_2 ํ ์ด๋ธ์ unit_price ๋ฐ unit_cost ์ด์ NULL ๊ฐ์ ์๋ฅผ ํ์ํ๋ ๊ฒฐ๊ณผ ์งํฉ์ ๋ฐํํ๋ค. ์ด ์ถ๋ ฅ์ ์ด๋ฌํ ์ด์์ ๋ฐ์ดํฐ๊ฐ ๋๋ฝ๋ ํ์ด ๋ช ๊ฐ ์๋์ง ๋ณด์ฌ ์ฃผ๋ฉฐ, ์ฒ๋ฆฌํด์ผ ํ ๋ฐ์ดํฐ ํ์ง ๋ฌธ์ ๋ฅผ ์๋ณํ๋ ๋ฐ ์ ์ฉํฉ๋๋ค.
S-082: ๋จ๊ฐ(unit_price)์ ์๊ฐ(unit_cost)์ ๊ฒฐ์๊ฐ์ ๋ํด ๊ฐ๊ฐ์ ์ค์๊ฐ์ผ๋ก ๋ณด์ํ ์๋ก์ด ์ํ ๋ฐ์ดํฐ๋ฅผ ์์ฑํ์์ค. ๋จ, ์ค์๊ฐ์ 1์ ๋ฏธ๋ง์ ๋ฐ์ฌ๋ฆผํ๋ค(๋ฐ์ฌ๋ฆผ ๋๋ ์ง์๋ก ๋ฐ์ฌ๋ฆผํด๋ ๋ฌด๋ฐฉํ๋ค). ๋ณด์ ์ค์ ํ ๊ฐ ํญ๋ชฉ์ ๋ํด ๊ฒฐ์์ด ๋ฐ์ํ์ง ์์๋์ง๋ ํ์ธํด์ผ ํ๋ค.
%%sql
DROP TABLE IF EXISTS product_3;
CREATE TABLE product_3 AS (
SELECT
product_cd,
category_major_cd,
category_medium_cd,
category_small_cd,
COALESCE(unit_price, unit_med) AS unit_price,
COALESCE(unit_cost, cost_med) AS unit_cost
FROM
product
CROSS JOIN (
SELECT
ROUND(
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_price)
) AS unit_med,
ROUND(
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_cost)
) AS cost_med
FROM
product
) stats_product
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 10030 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ SQL ์ธ์ด๋ก ์์ฑ๋์์ผ๋ฉฐ, ๋ค์ ๋จ๊ณ๋ฅผ ์ํํ๋ค.
product_3์ด๋ผ๋ ํ ์ด๋ธ์ด ์ด๋ฏธ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์กด์ฌํ๋ ๊ฒฝ์ฐ ์ญ์ ํ๋ค. ์ด๋ ์๋ฌด๊ฒ๋ ์๋ ์ํ์์ ์์ํ๋๋ก ํ๊ธฐ ์ํ ๊ฒ์ด๋ค.
CREATE TABLE ๋ฌธ์ ์ฌ์ฉํ์ฌ product_3์ด๋ผ๋ ์ ํ ์ด๋ธ์ ์์ฑํ๋ค.
์ ํ ์ด๋ธ์ ๋ฐ์ดํฐ๋ SELECT ๋ฌธ์ ์ฌ์ฉํ์ฌ product ํ ์ด๋ธ์์ ๊ฐ์ ธ์จ๋ค.
SELECT ๋ฌธ์์๋ product_cd, category_major_cd, category_medium_cd, category_small_cd๋ผ๋ ์ด์ด product ํ ์ด๋ธ์์ ์ ํ๋๋ค.
SELECT ๋ฌธ์์ COALESCE ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๋ ๊ฐ์ ์๋ก์ด ์ด์ด ์์ฑ๋๋ฉฐ, COALESCE๋ ์์ ๋ชฉ๋ก์์ ์ฒซ ๋ฒ์งธ NULL์ด ์๋ ๊ฐ์ ๋ฐํํ๋ค. ์ด ๊ฒฝ์ฐ unit_price๊ฐ NULL์ธ ๊ฒฝ์ฐ ์ ์ฒด ์ํ์ ๋จ๊ฐ ์ค๊ฐ๊ฐ(unit_med)์ด ๋์ ์ฌ์ฉ๋๋ค. ๋ง์ฐฌ๊ฐ์ง๋ก unit_cost๊ฐ NULL์ธ ๊ฒฝ์ฐ, ์ ์ฒด ์ํ์ ๋จ๊ฐ ์ค๊ฐ๊ฐ(cost_med)์ด ๋์ ์ฌ์ฉ๋๋ค.
SELECT ๋ฌธ์ FROM ๊ตฌ๋ฌธ์ ์ํ ํ ์ด๋ธ์ ์ ์ฒด ์ํ ๋จ๊ฐ์ ์ค์๊ฐ๊ณผ ๋จ๊ฐ๋ฅผ ๊ณ์ฐํ๋ ํ์ ์ฟผ๋ฆฌ์์ CROSS JOIN์ ์ํํ๋ค. ์ค์๊ฐ ์ฐ์ถ์๋ PERCENTILE_CONT ํจ์๊ฐ ์ฌ์ฉ๋๋ฉฐ, WITHIN GROUP ๊ตฌ๋ฌธ์ ํ์ ์์๋ฅผ ์ง์ ํ์ฌ ํจ์๊ฐ ์ค์๊ฐ ์ฐ์ถ์ ์ฌ์ฉํ ๊ฐ์ ์ธ์ํ ์ ์๋๋ก ํ๋ค.
ROUND ํจ์๋ ์ค์๊ฐ์ ๊ฐ์ฅ ๊ฐ๊น์ด ์ ์๋ก ๋ฐ์ฌ๋ฆผํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
SELECT ๊ตฌ๋ฌธ์ ๊ฒฐ๊ณผ๋ CREATE TABLE ๊ตฌ๋ฌธ์ ์ํด ์๋ฌต์ ์ผ๋ก ์คํ๋๋ INSERT INTO ๊ตฌ๋ฌธ์ ํตํด product_3 ํ ์ด๋ธ์ ์ ๋ ฅํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
์ด ์ฝ๋์์๋ product_3์ด๋ผ๋ ์๋ก์ด ํ ์ด๋ธ์ด ์์ฑ๋์ด product ํ ์ด๋ธ๊ณผ ๋์ผํ ์ด์ ํฌํจํ์ง๋ง, unit_price์ unit_cost ์ด์ null ๊ฐ์ ๋ชจ๋ ์ ํ์์ ๊ณ์ฐ๋ ๊ฐ๊ฐ์ ๋จ๊ฐ์ ๋จ๊ฐ์ ์ค๊ฐ๊ฐ์ผ๋ก ๋์ฒด๋๋ค. ์ค๊ฐ๊ฐ์ ํ์ ์์๋ฅผ ์ง์ ํ๋ WITHIN GROUP ๊ตฌ๋ฌธ์ ๊ฐ์ง PERCENTILE_CONT ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ณ์ฐ๋๋ค.
%%sql
SELECT
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_3
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
unit_price | unit_cost |
---|---|
0 | 0 |
์ค๋ช :
์ด ์ฝ๋๋ SQL ์ธ์ด๋ก ์์ฑ๋์์ผ๋ฉฐ, ๋ค์๊ณผ ๊ฐ์ ๋จ๊ณ๋ฅผ ์ํํ๋ค.
SELECT ๋ฌธ์ ์คํํ์ฌ product_3 ํ ์ด๋ธ์ ์กฐํํ๋ค.
SUM ํจ์๋ฅผ CASE ์๊ณผ ํจ๊ป ์ฌ์ฉํ์ฌ unit_price ์ด๊ณผ unit_cost ์ด์ NULL ๊ฐ์ ๊ฐ์๋ฅผ ๋ณ๋๋ก ๊ณ์ฐํ๋ค.
๊ฐ CASE ์์์ WHEN unit_price IS NULL ๋๋ WHEN unit_cost IS NULL์ ๊ฐ ์ด์ ๊ฐ์ด NULL์ธ์ง ์ฌ๋ถ๋ฅผ ํ์ธํ๋ค.
์ปฌ๋ผ์ NULL ๊ฐ์ด ์์ผ๋ฉด CASE ํํ์์ 1๋ก ํ๊ฐ๋๊ณ , ๊ทธ๋ ์ง ์์ผ๋ฉด 0์ผ๋ก ํ๊ฐ๋๋ค.
๊ทธ๋ฐ ๋ค์ SUM ํจ์๊ฐ ๊ฐ ํ์ 1๊ณผ 0์ ํฉ์ฐํ์ฌ unit_price์ unit_cost ์ด์ NULL ๊ฐ์ ์ด ์๋ฅผ ๊ฐ๊ฐ ๊ณ์ฐํ๋ค.
AS ํค์๋๋ ๊ฒฐ๊ณผ ์งํฉ์ ์ด์ ์๋ฏธ ์๋ ์ด๋ฆ, ์ฆ unit_price์ unit_cost๋ก ๋ง๋ค๊ธฐ ์ํด ์ฌ์ฉ๋๋ค.
๋ง์ง๋ง์ผ๋ก LIMIT ๊ตฌ๋ฌธ์ ์ถ๋ ฅ์ ์ฒ์ 10์ค๋ก ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋๋ product_3 ํ ์ด๋ธ์ unit_price ๋ฐ unit_cost ์ด์ NULL ๊ฐ์ ์๋ฅผ ํ์ํ๋ ๊ฒฐ๊ณผ ์งํฉ์ ๋ฐํํ๋ค. ์ด ์ถ๋ ฅ์ ํด๋น ์ด์์ ๋ฐ์ดํฐ๊ฐ ๋๋ฝ๋ ํ์ด ๋ช ๊ฐ ์๋์ง ๋ณด์ฌ์ฃผ๊ธฐ ๋๋ฌธ์ ์ฒ๋ฆฌํด์ผ ํ ๋ฐ์ดํฐ ํ์ง ๋ฌธ์ ๋ฅผ ์๋ณํ๋ ๋ฐ ์ ์ฉํฉ๋๋ค. product_3 ํ ์ด๋ธ์ ์ด์ ๋จ๊ณ์์ ์์ฑ๋์์ผ๋ฉฐ, unit_price ๋ฐ unit_cost ์ด์ null ๊ฐ์ ์ ์ฒด ์ ํ์์ ๊ณ์ฐ๋ ๊ฐ ๋จ์๋น ๊ฐ๊ฒฉ๊ณผ ๋จ์๋น ํ๊ท ๊ฐ์ผ๋ก ๋์ฒด๋ฉ๋๋ค. ๋จ๊ฐ์ ๋จ๊ฐ์ ์ค๊ฐ๊ฐ์ผ๋ก ๋์ฒด๋์์ต๋๋ค.
S-083: ๋จ๊ฐ(unit_price)์ ์๊ฐ(unit_cost)์ ๊ฒฐ์๊ฐ์ ๋ํด ๊ฐ ์ํ์ ์นดํ ๊ณ ๋ฆฌ ์๋ถ๋ฅ ์ฝ๋(category_small_cd)๋ณ๋ก ์ฐ์ถํ ์ค๊ฐ๊ฐ์ผ๋ก ๋ณด์ํ ์๋ก์ด ์ํ ๋ฐ์ดํฐ๋ฅผ ์์ฑํ๋ค. ๋จ, ์ค์๊ฐ์ 1์ ๋ฏธ๋ง์ ๋ฐ์ฌ๋ฆผํ๋ค(๋ฐ์ฌ๋ฆผ ๋๋ ์ง์๋ก ๋ฐ์ฌ๋ฆผํด๋ ๋ฌด๋ฐฉํ๋ค). ๋ณด์ ์ค์ ํ ๊ฐ ํญ๋ชฉ์ ๋ํด ๊ฒฐ์์ด ๋ฐ์ํ์ง ์์๋์ง๋ ํ์ธํด์ผ ํ๋ค.
%%sql
DROP TABLE IF EXISTS product_4;
CREATE TABLE product_4 AS (
WITH category_median AS(
SELECT
category_small_cd,
ROUND(
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_price)
) AS unit_med,
ROUND(
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_cost)
) AS cost_med
FROM product
GROUP BY category_small_cd
)
SELECT
product_cd,
category_major_cd,
category_medium_cd,
category_small_cd,
COALESCE(unit_price, unit_med) AS unit_price,
COALESCE(unit_cost, cost_med) AS unit_cost
FROM
product
JOIN
category_median
USING(category_small_cd)
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 10030 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ SQL ์ธ์ด๋ก ์์ฑ๋์์ผ๋ฉฐ, ๋ค์๊ณผ ๊ฐ์ ๋จ๊ณ๋ฅผ ์ํํ๋ค.
DROP TABLE ๋ฌธ์ ์คํํ์ฌ product_4 ํ ์ด๋ธ์ด ์ด๋ฏธ ์กด์ฌํ๋ ๊ฒฝ์ฐ ์ญ์ ํ๋ค.
CREATE TABLE ๋ฌธ์ ์คํํ์ฌ product_4 ํ ์ด๋ธ์ ์์ฑํ๋ค.
category_median์ด๋ผ๋ ์ด๋ฆ์ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ด ์ ์๋๋ค. ์ด CTE๋ ORDER BY ์ ์ด ์๋ SELECT ๋ฌธ์ ์ฌ์ฉํ์ฌ ์ํ ํ ์ด๋ธ์ ํ์ ๊ฐ ์นดํ ๊ณ ๋ฆฌ ๋ด์ unit_price์ unit_cost ์ด๋ก ์ ๋ ฌํ๊ณ , PERCENTILE_CONT ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ฐ ์นดํ ๊ณ ๋ฆฌ์ unit_price์ unit_cost์ ์ค๊ฐ๊ฐ์ ๊ณ์ฐํ๋ค. GROUP BY ๊ตฌ๋ฌธ์ category_small_cd ์ด์ ๊ธฐ์ค์ผ๋ก ๋ฐ์ดํฐ๋ฅผ ๊ทธ๋ฃนํํ๊ณ ์๋ค.
๋ฉ์ธ SELECT ๋ฌธ์ product ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ category_small_cd ์ด์์ category_median CTE์ ๊ฒฐํฉํ๊ณ , COALESCE ํจ์๋ฅผ ์ฌ์ฉํ์ฌ unit_price์ unit_cost ์ด์ NULL ๊ฐ์ ํด๋น ์นดํ ๊ณ ๋ฆฌ์ ์ค์๊ฐ์ผ๋ก ๋์ฒดํ๋ค. ํด๋น ์นดํ ๊ณ ๋ฆฌ์ ์ค์๊ฐ์ผ๋ก ๋์ฒดํฉ๋๋ค.
๋ง์ง๋ง์ผ๋ก CREATE TABLE AS ๊ตฌ๋ฌธ์ ์ฌ์ฉํ์ฌ ๊ฒฐ๊ณผ ๋ฐ์ดํฐ๋ฅผ product_4 ํ ์ด๋ธ์ ์ฝ์ ํ๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋์์๋ product ํ ์ด๋ธ๊ณผ CTE category_median์ ๊ฒฐํฉํ์ฌ new table product_4๋ฅผ ์์ฑํ๊ณ , PERCENTILE_CONT ํจ์๋ฅผ ์ฌ์ฉํ์ฌ product ํ ์ด๋ธ์ ๊ฐ ์นดํ ๊ณ ๋ฆฌ์ ๋ํด unit_price์ unit_cost์ ์ค๊ฐ๊ฐ์ด ๊ณ์ฐ๋ฉ๋๋ค. ๊ฒฐ๊ณผ ํ ์ด๋ธ product_4์๋ unit_price์ unit_cost ์ด์ NULL ๊ฐ์ ํด๋น ์นดํ ๊ณ ๋ฆฌ์ ์ค์๊ฐ์ผ๋ก ๋์ฒดํ ์ํ ํ ์ด๋ธ์ ๋ฐ์ดํฐ๊ฐ ํฌํจ๋์ด ์์ต๋๋ค.
ย
%%sql
SELECT
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_4
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
unit_price | unit_cost |
---|---|
0 | 0 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, ๋ค์ ๋จ๊ณ๋ฅผ ์ํํ๋ค.
SELECT ๋ฌธ์ ์คํํ์ฌ product_4 ํ ์ด๋ธ์ unit_price ์ด๊ณผ unit_cost ์ด์ null ๊ฐ์ ํฉ๊ณ๋ฅผ ๊ฐ์ ธ์จ๋ค.
SUM ํจ์์ CASE ๋ฌธ์ unit_price ์ด๊ณผ unit_cost ์ด์ ๊ฐ ๊ฐ์ด null์ธ์ง ์ฌ๋ถ๋ฅผ ํ์ธํ๋ค. ์ด๋ ๊ฒ ํด์ SUM ํจ์๋ ๊ฐ ์ด์ NULL ๊ฐ์ ์ด ๊ฐ์๋ฅผ ๊ณ์ฐํ๋ค.
AS ํค์๋๋ ์ด์ ์ด๋ฆ์ ๊ฐ๊ฐ unit_price์ unit_cost๋ก ๋ณ๊ฒฝํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
LIMIT ๊ตฌ๋ฌธ์ ์ฟผ๋ฆฌ๊ฐ ๋ฐํํ๋ ํ ์๋ฅผ 10์ผ๋ก ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋๋ product_4 ํ ์ด๋ธ์ unit_price์ unit_cost ์ด์ null ๊ฐ์ ์ด ๊ฐ์๋ฅผ ๊ณ์ฐํฉ๋๋ค. ์ด๋ฅผ ํตํด ํ ์ด๋ธ์์ ๋๋ฝ๋ ๋ฐ์ดํฐ๋ฅผ ์๋ณํ๊ณ ์ถ๊ฐ ๋ฐ์ดํฐ ์ ๋ฆฌ ๋๋ ์ธํจํ ์ด์ ์ด ํ์ํ์ง ์ฌ๋ถ๋ฅผ ํ๋จํ๋ ๋ฐ ๋์์ด ๋ฉ๋๋ค.
S-084: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ์ ์ฒด ๊ณ ๊ฐ์ ๋ํด ์ ์ฒด ๊ธฐ๊ฐ์ ๋งค์ถ ๊ธ์ก์์ 2019๋ ๋งค์ถ ๊ธ์ก์ด ์ฐจ์งํ๋ ๋น์จ์ ๊ณ์ฐํ์ฌ ์๋ก์ด ๋ฐ์ดํฐ๋ฅผ ์์ฑํ๋ค. ๋จ, ๋งค์ถ ์ค์ ์ด ์๋ ๊ฒฝ์ฐ 0์ผ๋ก ์ฒ๋ฆฌํ๋ค. ๊ทธ๋ฆฌ๊ณ ๊ณ์ฐํ ๋น์จ์ด 0์ ์ด๊ณผํ๋ ๊ฒ์ ์ถ์ถํ์ฌ ๊ฒฐ๊ณผ๋ฅผ 10๊ฑด์ฉ ํ์ํ๋ผ. ๋ํ, ์์ฑ๋ ๋ฐ์ดํฐ์ ๊ฒฐ์์ด ์๋์ง ํ์ธํ๋ผ.
%%sql
DROP TABLE IF EXISTS sales_rate;
CREATE TABLE sales_rate AS(
WITH sales_amount_2019 AS (
SELECT
customer_id,
SUM(amount) AS sum_amount_2019
FROM
receipt
WHERE
sales_ymd BETWEEN 20190101 AND 20191231
GROUP BY
customer_id
),
sales_amount_all AS (
SELECT
customer_id,
SUM(amount) AS sum_amount_all
FROM
receipt
GROUP BY
customer_id
)
SELECT
a.customer_id,
COALESCE(b.sum_amount_2019, 0) AS sales_amount_2019,
COALESCE(c.sum_amount_all, 0) AS sales_amount_all,
CASE COALESCE(c.sum_amount_all, 0)
WHEN 0 THEN 0
ELSE COALESCE(b.sum_amount_2019, 0) * 1.0 / c.sum_amount_all
END AS sales_rate
FROM
customer a
LEFT JOIN
sales_amount_2019 b
ON a.customer_id = b.customer_id
LEFT JOIN
sales_amount_all c
ON a.customer_id = c.customer_id);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 21971 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์ด ์์ผ๋ฉฐ, ๋ค์๊ณผ ๊ฐ์ ๋จ๊ณ๋ฅผ ์ํํฉ๋๋ค.
SELECT ๋ฌธ์ด ์คํ๋์ด ์์์ฆ ํ ์ด๋ธ๊ณผ ๊ณ ๊ฐ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ต๋๋ค.
๋ ๊ฐ์ ํ์ ์ฟผ๋ฆฌ๊ฐ ์์ฑ๋์ด 2019๋ ๋ฐ ๋ชจ๋ ์ฐ๋์ ๊ฐ ๊ณ ๊ฐ์ ๋ํ ์ด ๋งค์ถ์ก์ ๊ณ์ฐํฉ๋๋ค.
SELECT ๋ฌธ์ LEFT JOIN ์ฐ์ฐ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ณ ๊ฐ ํ ์ด๋ธ๊ณผ ๋ ๊ฐ์ ํ์ ์ฟผ๋ฆฌ๋ฅผ ๊ฒฐํฉํ๋ค. ์ด๋ ๊ฒ ํ๋ฉด ์์์ฆ ํ ์ด๋ธ์ ๋งค์ถ ๋ฐ์ดํฐ๊ฐ ์๋ ๊ฒฝ์ฐ์๋ ๊ณ ๊ฐ ํ ์ด๋ธ์ ๋ชจ๋ ๊ณ ๊ฐ์ด ์ถ๋ ฅ์ ํฌํจ๋๋ค.
COALESCE ํจ์๋ ์์์ฆ ํ ์ด๋ธ์ ๊ณ ๊ฐ์ ๋ํ ๋งค์ถ ๋ฐ์ดํฐ๊ฐ ์๋ ๊ฒฝ์ฐ NULL ๊ฐ์ 0์ผ๋ก ๋์ฒดํด์ค๋ค.
CASE ๋ฌธ์ ๊ฐ ๊ณ ๊ฐ์ ๋งค์ถ ๋น์จ์ ๊ณ์ฐํฉ๋๋ค. ์ ์ฒด ์ฐ๋ ๋งค์ถ ๊ธ์ก์ ํฉ๊ณ๊ฐ 0์ธ์ง ํ์ธํ๊ณ , 0์ ๋ฐํํฉ๋๋ค. ๊ทธ๋ ์ง ์์ ๊ฒฝ์ฐ 2019๋ ๋งค์ถ์ก์ ์ ์ฒด ์ฐ๋ ๋งค์ถ์ก ํฉ๊ณ๋ก ๋๋๋ค.
๊ฒฐ๊ณผ๋ sales_rate๋ผ๋ ์ ํ ์ด๋ธ์ ์ฝ์ ๋๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋๋ ์์์ฆ ํ ์ด๋ธ์ ๋งค์ถ ๋ฐ์ดํฐ๋ฅผ ๊ธฐ๋ฐ์ผ๋ก ๊ฐ ๊ณ ๊ฐ์ ๋งค์ถ ๋น์จ์ ๊ณ์ฐํ๋ค. ํ๋งค์จ์ ์ ์ฒด ์ฐ๋ ํ๋งค ๊ธ์ก์ ์ด์ก์ ๋ํ 2019 ๋ ๋ ํ๋งค ๊ธ์ก์ ๋น์จ์ ๋๋ค. ๊ฒฐ๊ณผ๋ sales_rate๋ผ๋ ์ ํ ์ด๋ธ์ ์ ์ฅ๋ฉ๋๋ค.
%%sql
SELECT * FROM sales_rate
WHERE sales_rate > 0
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sales_amount_2019 | sales_amount_all | sales_rate |
---|---|---|---|
CS031415000172 | 2971 | 5088 | 0.58392295597484276730 |
CS015414000103 | 874 | 3122 | 0.27994875080076873799 |
CS011215000048 | 248 | 3444 | 0.07200929152148664344 |
CS029415000023 | 3767 | 5167 | 0.72904973872653377201 |
CS035415000029 | 5823 | 7504 | 0.77598614072494669510 |
CS023513000066 | 208 | 771 | 0.26977950713359273671 |
CS035513000134 | 463 | 1565 | 0.29584664536741214058 |
CS001515000263 | 216 | 216 | 1.00000000000000000000 |
CS006415000279 | 229 | 229 | 1.00000000000000000000 |
CS031415000106 | 215 | 7741 | 0.02777418938121689704 |
%%sql
SELECT
SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS unit_price,
SUM(CASE WHEN sales_amount_2019 IS NULL THEN 1 ELSE 0 END) AS unit_price,
SUM(CASE WHEN sales_amount_all IS NULL THEN 1 ELSE 0 END) AS unit_cost,
SUM(CASE WHEN sales_rate IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM sales_rate
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
unit_price | unit_price_1 | unit_cost | unit_cost_1 |
---|---|---|---|
0 | 0 | 0 | 0 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์ด ์์ผ๋ฉฐ, ๋ค์๊ณผ ๊ฐ์ ๋จ๊ณ๋ฅผ ์ํํ๋ค.
SELECT ๋ฌธ์ ์คํํ์ฌ sales_rate ํ ์ด๋ธ์์ sales_rate ๊ฐ์ด 0๋ณด๋ค ํฐ ๋ชจ๋ ํ์ ๊ฐ์ ธ์ต๋๋ค.
LIMIT ์ ์ ์ถ๋ ฅ์ ์ฒ์ 10๊ฐ์ ํ์ผ๋ก ์ ํํ๊ณ ์์ต๋๋ค.
๊ฒฐ๊ณผ๊ฐ ์ถ๋ ฅ์ผ๋ก ๋ฐํ๋ฉ๋๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋๋ sales_rate ํ ์ด๋ธ์์ sales_rate๊ฐ 0๋ณด๋ค ํฐ ๋ชจ๋ ํ, ์ฆ ๊ณ ๊ฐ์ด 2019๋ ์ ์ ์ด๋ ํ ๋ฒ ์ด์ ํ๋งค๋ฅผ ํ ๊ฒ์ ์๋ฏธํ๋ ๋ชจ๋ ํ์ ์ ํํ๋ค.
ย
S-085: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ๋ชจ๋ ๊ณ ๊ฐ์ ๋ํด ์ฐํธ๋ฒํธ(postal_cd)๋ฅผ ์ด์ฉํ์ฌ ์ง์ค์ฝ๋ ๋ฐ์ดํฐ(df_geocode)๋ฅผ ์ฐ๊ฒฐํ์ฌ ์๋ก์ด ๊ณ ๊ฐ ๋ฐ์ดํฐ๋ฅผ ์์ฑํ๋ค. ๋จ, ํ๋์ ์ฐํธ๋ฒํธ(postal_cd)์ ์ฌ๋ฌ ๊ฐ์ ๊ฒฝ๋(longitude), ์๋(latitude) ์ ๋ณด๊ฐ ์ฐ๊ฒฐ๋ ๊ฒฝ์ฐ์๋ ๊ฒฝ๋(longitude), ์๋(latitude)์ ํ๊ท ๊ฐ์ ์ฐ์ถํ์ฌ ์ฌ์ฉํด์ผ ํ๋ค. ๋ํ, ์์ฑ๋ ๊ฒฐ๊ณผ๋ฅผ ํ์ธํ๊ธฐ ์ํด 10๊ฐ์ ๊ฒฐ๊ณผ๋ฅผ ํ์ํ๋ค.
%%sql
DROP TABLE IF EXISTS customer_1;
CREATE TABLE customer_1 AS (
WITH geocode_avg AS(
SELECT
postal_cd,
AVG(longitude) AS m_longitude,
AVG(latitude) AS m_latitude
FROM
geocode
GROUP BY
postal_cd
)
SELECT
*
FROM
customer c
JOIN
geocode_avg g
USING(postal_cd)
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 21971 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, ๋ค์ ๋จ๊ณ๋ฅผ ์ํํฉ๋๋ค.
WITH ์ ์ ์ฌ์ฉํ์ฌ geocode ํ ์ด๋ธ์ ์ฌ์ฉํ์ฌ ๊ฐ ์ฐํธ๋ฒํธ์ ํ๊ท ๊ฒฝ๋์ ์๋๋ฅผ ๊ณ์ฐํ๋ ํ์ ์ฟผ๋ฆฌ๋ฅผ ์ ์ํ๋ค.
SELECT ๋ฌธ์ postal_cd ์ด์ ์ฌ์ฉํ์ฌ customer ํ ์ด๋ธ๊ณผ WITH ์ ์์ ์ ์ํ ํ์ ์ฟผ๋ฆฌ๋ฅผ ๊ฒฐํฉํ๋ค.
๊ฒฐํฉ ์์ ์ ๊ฒฐ๊ณผ๋ CREATE TABLE ๋ฌธ์ ์ฌ์ฉํ์ฌ customer_1์ด๋ผ๋ ์ ํ ์ด๋ธ์ ์ฝ์ ๋๋ค. ๊ฐ์ ์ด๋ฆ์ ํ ์ด๋ธ์ด ์ด๋ฏธ ์กด์ฌํ๋ ๊ฒฝ์ฐ ๋จผ์ DROP TABLE IF EXISTS ๋ฌธ์ ์ฌ์ฉํ์ฌ ์ญ์ ๋๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋์์๋ postal_cd ์ด์ ์ฌ์ฉํ์ฌ customer ํ ์ด๋ธ๊ณผ geocode ํ ์ด๋ธ์ ๊ฒฐํฉํ๊ณ , ๊ฐ ์ฐํธ๋ฒํธ์ ํ๊ท ๊ฒฝ๋์ ์๋๋ฅผ customer ํ ์ด๋ธ์ ์ถ๊ฐํ์ฌ customer_1์ด๋ผ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํฉ๋๋ค. ์์ฑ๋ ํ ์ด๋ธ์๋ customer ํ ์ด๋ธ์ ๋ชจ๋ ์ด๊ณผ ํ๊ท ๊ฒฝ๋์ ์๋๋ฅผ ์ํ ๋ ๊ฐ์ ์ด์ด ์ถ๊ฐ๋์ด ์์ต๋๋ค.
%%sql
SELECT * FROM customer_1 LIMIT 10;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
postal_cd | customer_id | customer_name | gender_cd | gender | birth_day | age | address | application_store_cd | application_date | status_cd | m_longitude | m_latitude |
---|---|---|---|---|---|---|---|---|---|---|---|---|
136-0076 | CS037613000071 | ๅ ญ่ง ้ ๅฝฆ | 9 | ไธๆ | 1952-04-01 | 66 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | S13037 | 20150414 | 0-00000000-0 | 139.8350200000000000 | 35.6719300000000000 |
151-0053 | CS031415000172 | ๅฎๅค็ฐ ่ฒด็พๅญ | 1 | ๅฅณๆง | 1976-10-04 | 42 | ๆฑไบฌ้ฝๆธ่ฐทๅบไปฃใ ๆจ********** | S13031 | 20150529 | D-20100325-C | 139.6896500000000000 | 35.6737400000000000 |
245-0016 | CS028811000001 | ๅ ไบ ใใใ | 1 | ๅฅณๆง | 1933-03-27 | 86 | ็ฅๅฅๅท็ๆจชๆตๅธๆณๅบๅๆณ็บ********** | S14028 | 20160115 | 0-00000000-0 | 139.4836000000000000 | 35.3912500000000000 |
144-0055 | CS001215000145 | ็ฐๅด ็พ็ด | 1 | ๅฅณๆง | 1995-03-29 | 24 | ๆฑไบฌ้ฝๅคง็ฐๅบไปฒๅ ญ้ท********** | S13001 | 20170605 | 6-20090929-2 | 139.7077500000000000 | 35.5408400000000000 |
136-0073 | CS015414000103 | ๅฅฅ้ ้ฝๅญ | 1 | ๅฅณๆง | 1977-08-09 | 41 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | S13015 | 20150722 | B-20100609-B | 139.8360100000000000 | 35.6781800000000000 |
136-0073 | CS015804000004 | ๆพ่ฐท ็ฑณ่ต | 0 | ็ทๆง | 1931-05-02 | 87 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | S13015 | 20150607 | 0-00000000-0 | 139.8360100000000000 | 35.6781800000000000 |
276-0022 | CS007403000016 | ไพ็ฐ ๆบ | 0 | ็ทๆง | 1975-08-18 | 43 | ๅ่็ๅ ซๅไปฃๅธไธ้ซ้********** | S12007 | 20150914 | 0-00000000-0 | 140.1326000000000000 | 35.7326400000000000 |
154-0015 | CS035614000014 | ๆฟๅ ่ใ ็พ | 1 | ๅฅณๆง | 1954-07-16 | 64 | ๆฑไบฌ้ฝไธ็ฐ่ฐทๅบๆกๆฐ็บ********** | S13035 | 20150804 | 0-00000000-0 | 139.6429700000000000 | 35.6288900000000000 |
223-0062 | CS011215000048 | ่ฆ็ฐ ๆฒ่ถ | 1 | ๅฅณๆง | 1992-02-01 | 27 | ็ฅๅฅๅท็ๆจชๆตๅธๆธฏๅๅบๆฅๅๆฌ็บ********** | S14011 | 20150228 | C-20100421-9 | 139.6294600000000000 | 35.5537000000000000 |
226-0021 | CS040412000191 | ๅทไบ ้ๆต | 1 | ๅฅณๆง | 1977-01-05 | 42 | ็ฅๅฅๅท็ๆจชๆตๅธ็ทๅบๅๅ ซๆ็บ********** | S14040 | 20151101 | 1-20091025-4 | 139.5396400000000000 | 35.5276300000000000 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, customer_1 ํ ์ด๋ธ์ ๋ํด ๊ฐ๋จํ SELECT ๋ฌธ์ ์คํํ๋ค.
SELECT ๋ฌธ์ ์์ผ๋์นด๋ ๋ฌธ์ *๋ฅผ ์ฌ์ฉํ์ฌ customer_1 ํ ์ด๋ธ์ ๋ชจ๋ ์ด๊ณผ ํ์ ๊ฐ์ ธ์จ๋ค.
LIMIT ์ ์ ์ฟผ๋ฆฌ๊ฐ ๋ฐํํ๋ ํ ์๋ฅผ ์ฒ์ 10๊ฐ์ ํ์ผ๋ก ์ ํํ๋ค.
์ด ํ ์ด๋ธ์๋ customer ํ ์ด๋ธ์ ๋ชจ๋ ์ด๊ณผ ๊ฐ ๊ณ ๊ฐ์ ์ฐํธ๋ฒํธ์ ํ๊ท ๊ฒฝ๋์ ์๋ ๋ ๊ฐ์ ์ด์ด ์ถ๊ฐ๋์ด ์์ต๋๋ค.
ย
S-086: 085์์ ์์ฑํ ์๋๊ฒฝ๋๋ณ ๊ณ ๊ฐ ๋ฐ์ดํฐ์ ๋ํด ํ์ ์ ์ฒญ ๋งค์ฅ ์ฝ๋(application_store_cd)๋ฅผ ํค๋ก ๋งค์ฅ ๋ฐ์ดํฐ(df_store)์ ๊ฒฐํฉํ๋ผ. ๊ทธ๋ฆฌ๊ณ ์ ์ฒญ ๋งค์ฅ์ ์๋(latitude)-๊ฒฝ๋ ์ ๋ณด(longitude)์ ๊ณ ๊ฐ ์ฃผ์(address)์ ์๋-๊ฒฝ๋๋ฅผ ์ด์ฉํ์ฌ ์ ์ฒญ ๋งค์ฅ๊ณผ ๊ณ ๊ฐ ์ฃผ์์ ๊ฑฐ๋ฆฌ(๋จ์: km)๋ฅผ ๊ตฌํ๊ณ , ๊ณ ๊ฐ ID(customer_id), ๊ณ ๊ฐ ์ฃผ์(address), ๋งค์ฅ ์ฃผ์(address)์ ํจ๊ป ํ์ํ๋ผ. ๊ณผ ํจ๊ป ํ์ํ๋ผ. ๊ณ์ฐ์์ ์๋์ ๊ฐ๋จํ ์์ ์ฌ์ฉํ๋, ๊ทธ ์ธ ์ ๋ฐ๋๊ฐ ๋์ ๋ฐฉ์์ ์ด์ฉํ ๋ผ์ด๋ธ๋ฌ๋ฆฌ๋ฅผ ์ฌ์ฉํด๋ ๋ฌด๋ฐฉํ๋ค. ๊ฒฐ๊ณผ๋ 10๊ฑด์ ํ์ํ๋ค.
์๋๏ผ๋ผ๋์๏ผ๏ผฯ๊ฒฝ๋๏ผ๋ผ๋์๏ผ๏ผฮป:๊ฑฐ๋ฆฌL=6371โarccosโก(sinโกฯ1โsinฯ2+cosโกฯ1โcosโกฯ2โcosโก(ฮป1โฮป2))
%%sql
SELECT
c.customer_id,
c.address AS customer_address,
s.address AS store_address,
6371 * ACOS(
SIN(RADIANS(c.m_latitude))
* SIN(RADIANS(s.latitude))
+ COS(RADIANS(c.m_latitude))
* COS(RADIANS(s.latitude))
* COS(RADIANS(c.m_longitude) - RADIANS(s.longitude))
) AS distance FROM
customer_1 c
JOIN
store s
ON
c.application_store_cd = s.store_cd
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | customer_address | store_address | distance |
---|---|---|---|
CS037613000071 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ไธไธ็ฎ | 1.4511822099658445 |
CS031415000172 | ๆฑไบฌ้ฝๆธ่ฐทๅบไปฃใ ๆจ********** | ๆฑไบฌ้ฝๆธ่ฐทๅบๅๅฐไบไธ็ฎ | 0.4117334789298223 |
CS028811000001 | ็ฅๅฅๅท็ๆจชๆตๅธๆณๅบๅๆณ็บ********** | ็ฅๅฅๅท็ๆจชๆตๅธ็ฌ่ฐทๅบไบใๆฉ็บ | 8.065196026704987 |
CS001215000145 | ๆฑไบฌ้ฝๅคง็ฐๅบไปฒๅ ญ้ท********** | ๆฑไบฌ้ฝๅคง็ฐๅบไปฒๅ ญ้ทไบไธ็ฎ | 1.2684209720729687 |
CS015414000103 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ไบไธ็ฎ | 1.449673414532165 |
CS015804000004 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ไบไธ็ฎ | 1.449673414532165 |
CS007403000016 | ๅ่็ๅ ซๅไปฃๅธไธ้ซ้********** | ๅ่็ไฝๅๅธไธๅฟๆดฅ | 1.9208032538419055 |
CS035614000014 | ๆฑไบฌ้ฝไธ็ฐ่ฐทๅบๆกๆฐ็บ********** | ๆฑไบฌ้ฝไธ็ฐ่ฐทๅบ็จ่ณๅไธ็ฎ | 1.0214681484997588 |
CS011215000048 | ็ฅๅฅๅท็ๆจชๆตๅธๆธฏๅๅบๆฅๅๆฌ็บ********** | ็ฅๅฅๅท็ๆจชๆตๅธๆธฏๅๅบๆฅๅๆฌ็บๅไธ็ฎ | 0.8182767808775093 |
CS040412000191 | ็ฅๅฅๅท็ๆจชๆตๅธ็ทๅบๅๅ ซๆ็บ********** | ็ฅๅฅๅท็ๆจชๆตๅธ็ทๅบ้ทๆดฅ็ฐใฟใชใฟๅฐไบไธ็ฎ | 3.6641233580823287 |
์ค๋ช :
์ด SQL ์ฝ๋๋ customer_1๊ณผ store ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ ๊ฐ ๊ณ ๊ฐ์ ์ฃผ์์ ํด๋น ๋งค์ฅ์ ์ฃผ์ ์ฌ์ด์ ๊ฑฐ๋ฆฌ๋ฅผ ๊ณ์ฐํ๋ค.
SELECT ๋ฌธ์ ๋ค์๊ณผ ๊ฐ์ ์ปฌ๋ผ์ ๊ฐ์ ธ์ต๋๋ค.
customer_1 ํ ์ด๋ธ์์ customer_id๋ฅผ ๊ฐ์ ธ์จ๋ค.
customer_1 ํ ์ด๋ธ์์ customer_id๋ฅผ ๊ฐ์ ธ์ customer_address๋ก ์ด๋ฆ์ ๋ฐ๊พผ๋ค.
store ํ ์ด๋ธ์ address(store_address๋ก ์ด๋ฆ ๋ณ๊ฒฝ).
๊ณ ๊ฐ๊ณผ ๋งค์ฅ ์ฌ์ด์ ๊ฑฐ๋ฆฌ ๊ณ์ฐ ๊ฐ์ผ๋ก Haversine์ ๊ณต์์ ์ฌ์ฉํ์ฌ ACOS, SIN, COS ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๋ ์ฃผ์์ ์๋์ ๊ฒฝ๋์ ์ ํ๊ณผ ์ฝ์ฌ์ธ์ ์ํฌ ์ฝ์ฌ์ธ์ ๊ฐ๊ฐ ๊ณ์ฐํ๋ค. ๊ทธ๋ฆฌ๊ณ ๊ทธ ๊ฒฐ๊ณผ์ ์ง๊ตฌ ๋ฐ๊ฒฝ(6371km)์ ๊ณฑํ์ฌ ํฌ๋ก๋ฏธํฐ ๋จ์์ ๊ฑฐ๋ฆฌ๋ฅผ ๊ตฌํฉ๋๋ค.
JOIN ๋ฌธ์ application_store_cd ์ด๊ณผ store_cd ์ด์ ์ผ์น๋ฅผ ๊ธฐ๋ฐ์ผ๋ก customer_1 ํ ์ด๋ธ๊ณผ store ํ ์ด๋ธ์ ํ์ ๊ฒฐํฉํ๊ณ , LIMIT ๊ตฌ๋ฌธ์ ๋ฐํ๋๋ ํ์ ์๋ฅผ 10์ผ๋ก ์ ํํ๋ค.
ย
S-087: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์๋ ๋ค๋ฅธ ๋งค์ฅ์์์ ์ ์ฒญ ๋ฑ์ผ๋ก ๋์ผ ๊ณ ๊ฐ์ด ์ฌ๋ฌ ๊ฐ ๋ฑ๋ก๋์ด ์๋ค. ์ด๋ฆ(customer_name)๊ณผ ์ฐํธ๋ฒํธ(postal_cd)๊ฐ ๊ฐ์ ๊ณ ๊ฐ์ ๋์ผ ๊ณ ๊ฐ์ผ๋ก ๊ฐ์ฃผํ์ฌ 1๊ณ ๊ฐ 1๋ ์ฝ๋๊ฐ ๋๋๋ก ์ด๋ฆ์ ๋ถ์ธ ๋ช ๋ชฉ ๊ณ ๊ฐ ๋ฐ์ดํฐ๋ฅผ ์์ฑํ๊ณ , ๊ณ ๊ฐ ๋ฐ์ดํฐ ๊ฐ์, ๋ช ๋ชฉ ๊ณ ๊ฐ ๋ฐ์ดํฐ ๊ฐ์, ์ค๋ณต ํ์๋ฅผ ๊ณ์ฐํ๋ผ. ๋จ, ๋์ผ ๊ณ ๊ฐ์ ๋ํด์๋ ๋งค์ถ ๊ธ์ก ํฉ๊ณ๊ฐ ๊ฐ์ฅ ๋์ ๊ฒ์ ๋จ๊ธฐ๊ณ , ๋งค์ถ ๊ธ์ก ํฉ๊ณ๊ฐ ๋์ผํ๊ฑฐ๋ ๋งค์ถ ์ค์ ์ด ์๋ ๊ณ ๊ฐ์ ๋ํด์๋ ๊ณ ๊ฐ ID(customer_id)์ ๋ฒํธ๊ฐ ์์ ๊ฒ์ ๋จ๊ธด๋ค.
%%sql
DROP TABLE IF EXISTS customer_u;
CREATE TABLE customer_u AS (
WITH sales_amount AS(
SELECT
c.customer_id,
c.customer_name,
c.postal_cd,
COALESCE(SUM(r.amount), 0) AS sum_amount
FROM
customer c
LEFT JOIN
receipt r
ON c.customer_id = r.customer_id
GROUP by
c.customer_id, c.customer_name, c.postal_cd
),
sales_ranking AS(
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY customer_name, postal_cd
ORDER BY sum_amount desc, customer_id ) AS ranking
FROM sales_amount
)
SELECT c.*
FROM
customer c
JOIN
sales_ranking r
ON
c.customer_id = r.customer_id
AND r.ranking = 1
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 21941 rows affected.
[]
์ค๋ช :
์ด SQL ์ฝ๋๋ "customer"์ "recipate" ๋ ํ ์ด๋ธ์ ๋ฐ์ดํฐ๋ฅผ ๊ฒฐํฉํ์ฌ "customer_u"๋ผ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํฉ๋๋ค. ๋จผ์ "sales_amount"๋ผ๋ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ ์ ์ํ๊ณ , "customer" ํ ์ด๋ธ๊ณผ "recipate" ํ ์ด๋ธ์ ์ผ์ชฝ ๊ฒฐํฉ์ ์ฌ์ฉํ์ฌ ๊ฒฐํฉํ์ฌ ๊ฐ ๊ณ ๊ฐ์ ์ด ๋งค์ถ์ก์ ๊ณ์ฐํฉ๋๋ค. ์ด CTE๋ COALESCE ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๋งค์ถ์ด ์๋ ๊ณ ๊ฐ์๊ฒ๋ 0์ ๋ฐํํ๋ค.
๊ทธ๋ฐ ๋ค์ "sales_ranking"์ด๋ผ๋ ์ด๋ฆ์ ๋ ๋ค๋ฅธ CTE๊ฐ ์ ์๋์ด customer_name๊ณผ postal_cd์ ๊ฐ ๊ทธ๋ฃน ๋ด์์ ์ด ํ๋งค ๊ธ์ก์ ๊ธฐ์ค์ผ๋ก ๊ฐ ๊ณ ๊ฐ์ ํ๋งค ์์๋ฅผ ๊ณ์ฐํ๊ณ ROW_NUMBER() ํจ์๋ฅผ ์ฌ์ฉํ์ฌ sum_amount๋ฅผ ๊ธฐ์ค์ผ๋ก ๋ด๋ฆผ์ฐจ์์ผ๋ก ๊ฐ ๊ณ ๊ฐ์๊ฒ ์์๋ฅผ ํ ๋นํ๊ณ ์์ต๋๋ค.
๋ง์ง๋ง์ผ๋ก SELECT ๋ฌธ์ 'customer' ํ ์ด๋ธ๊ณผ 'sales_ranking' CTE๋ฅผ ๊ฒฐํฉํ์ฌ ์์๊ฐ 1์ธ ํ๋ง ํํฐ๋งํ๋ค. ์ด๋ฅผ ํตํด customer_name๊ณผ postal_cd ๊ทธ๋ฃน๋ณ๋ก ์์ ๊ณ ๊ฐ๋ง ํจ๊ณผ์ ์ผ๋ก ์ ํ๋๋ค. ์์ฑ๋ ๋ฐ์ดํฐ๋ "customer_u"๋ผ๋ ์๋ก์ด ํ ์ด๋ธ์ ์ฝ์ ๋๋ค.
%%sql
SELECT
customer_cnt,
customer_u_cnt,
customer_cnt - customer_u_cnt AS diff
FROM
(SELECT COUNT(1) AS customer_cnt FROM customer) customer
CROSS JOIN (SELECT COUNT(1) AS customer_u_cnt FROM customer_u) customer_u
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
customer_cnt | customer_u_cnt | diff |
---|---|---|
21971 | 21941 | 30 |
์ค๋ช :
์ด SQL ์ฝ๋๋ ๋ ๊ฐ์ ์๋ก ๋ค๋ฅธ ํ ์ด๋ธ์ ๊ณ ๊ฐ ์๋ฅผ ์กฐํํ๊ณ ๊ทธ ์ฐจ์ด๋ฅผ ๊ณ์ฐํ๋ ์ฝ๋์ ๋๋ค.
์ฝ๋์ ์ฒซ ๋ฒ์งธ ๋ถ๋ถ์ "customer" ํ ์ด๋ธ์์ ์ด ๊ณ ๊ฐ ์๋ฅผ ์ ํํ๊ณ "customer_cnt"๋ผ๋ ๋ณ์นญ์ ๋ถ์ฌํ๋ ํ์ ์ฟผ๋ฆฌ์ ๋๋ค.
์ฝ๋์ ๋ ๋ฒ์งธ ๋ถ๋ถ์ "customer_u" ํ ์ด๋ธ์์ ์ด ๊ณ ๊ฐ ์๋ฅผ ์ ํํ๊ณ "customer_u_cnt"๋ผ๋ ์ด๋ฆ์ผ๋ก ๋ณ์นญ์ ์ง์ ํ๋ ๋ ๋ค๋ฅธ ํ์ ์ฟผ๋ฆฌ์ ๋๋ค.
๋ง์ง๋ง์ผ๋ก ์ธ๋ถ ์ฟผ๋ฆฌ์์๋ ๋ ๊ฐ์ ํ์ ์ฟผ๋ฆฌ์์ 'customer_cnt'์ 'customer_u_cnt'๋ฅผ ์ ํํ๊ณ ๋์ ์ฐจ์ด๋ฅผ 'diff'๋ก ๊ณ์ฐํ๋ค. ์ด๋ "customer_cnt"์์ "customer_u_cnt"๋ฅผ ๋นผ๋ฉด ๋๋ค.
๊ฒฐ๊ณผ๋ "customer_cnt", "customer_u_cnt", "diff"์ ์ธ ๊ฐ์ ์ด๋ก ๊ตฌ์ฑ๋ ํ ์ค๋ก, "customer_cnt" ์ด์ "customer" ํ ์ด๋ธ์ ์ด ๊ณ ๊ฐ ์๋ฅผ, "customer_u_cnt" ์ด์ "customer_u" ํ ์ด๋ธ์ ์ด ๊ณ ๊ฐ ์๋ฅผ, "diff" ์ด์ "customer_u" ํ ์ด๋ธ์ ์ด ๊ณ ๊ฐ ์๋ฅผ ๋ํ๋ ๋๋ค. ํ ์ด๋ธ์ ์ด ๊ณ ๊ฐ ์๋ฅผ, 'diff' ์ด์ ์ด ๋ ์นด์ดํธ์ ์ฐจ์ด๋ฅผ ํ์ํ๋ค.
S-088: 087์์ ์์ฑํ ๋ฐ์ดํฐ๋ฅผ ๋ฐํ์ผ๋ก ๊ณ ๊ฐ ๋ฐ์ดํฐ์ ํตํฉ๋ช ์นญ ID๋ฅผ ๋ถ์ฌํ ๋ฐ์ดํฐ๋ฅผ ์์ฑํ๋ค. ๋จ, ํตํฉ๋ช ์นญ ID๋ ์๋์ ์ฌ์์ผ๋ก ๋ถ์ฌํ๋ค.
- ์ค๋ณต๋์ง ์์ ๊ณ ๊ฐ : ๊ณ ๊ฐ ID(customer_id) ์ค์
- ์ค๋ณต๋๋ ๊ณ ๊ฐ: ์ด์ ์ค๋ฌธ์์ ์ถ์ถํ ๋ ์ฝ๋์ ๊ณ ๊ฐ ID๋ฅผ ์ค์ ํ๋ค.
๊ณ ๊ฐ ID์ ๊ณ ์ ๊ฑด์์ ํตํฉ๋ช ์นญ ID์ ๊ณ ์ ๊ฑด์ ์ฐจ์ด๋ ํ์ธํด์ผ ํ๋ค.
%%sql
DROP TABLE IF EXISTS customer_n;
CREATE TABLE customer_n AS (
SELECT
c.*,
u.customer_id AS integration_id
FROM
customer c
JOIN
customer_u u
ON c.customer_name = u.customer_name
AND c.postal_cd = u.postal_cd
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 21971 rows affected.
[]
์ค๋ช :
์ด SQL ์ฝ๋๋ ๊ธฐ์กด customer ํ ์ด๋ธ๊ณผ customer_u ํ ์ด๋ธ์ ๊ฒฐํฉํ์ฌ customer_n์ด๋ผ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๋ค.
JOIN ์กฐ๊ฑด์ customer_name๊ณผ postal_cd ์ปฌ๋ผ์ ๋์ผํ ๊ฐ์ ๊ฐ์ง customer_u ํ ์ด๋ธ ๊ฐ์ ํ์ ์ผ์น์ํจ๋ค. ์ผ์นํ๋ ํ์ ๋ํด ๊ฒฐ๊ณผ ํ ์ด๋ธ customer_n์ integration_id ์ด์ customer_u ํ ์ด๋ธ์ customer_id๋ก ์ค์ ๋๋ค.
๋ฐ๋ผ์ customer_n ํ ์ด๋ธ์๋ customer ํ ์ด๋ธ์ ๋ชจ๋ ํ๊ณผ customer_u ํ ์ด๋ธ์ customer_id ๊ฐ์ ํฌํจํ๋ integration_id ์ปฌ๋ผ์ด ์ถ๊ฐ๋๊ณ , customer_u ํ ์ด๋ธ์ customer_name๊ณผ postal_cd ๊ฐ์ด ์ผ์นํ์ง ์๋ ํ์ integration_id ์ปฌ๋ผ์ NULL ๊ฐ์ ๊ฐ์ง๊ฒ ๋๋ค.
ย
%%sql
SELECT COUNT(1) AS ID์์ ์ฐจ์ด FROM customer_n
WHERE customer_id != integration_id;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
ID์์ ์ฐจ์ด |
---|
30 |
์ค๋ช :
์ด SQL ์ฝ๋๋ "customer_n" ํ ์ด๋ธ์ "customer_id" ์ปฌ๋ผ๊ณผ "integration_id" ์ปฌ๋ผ์ด ๊ฐ์ง ์์ ํ ์๋ฅผ ์กฐํํ๋ค.
customer_n ํ ์ด๋ธ์ ์ด์ ์ฝ๋ ๋ธ๋ก์์ "customer" ํ ์ด๋ธ๊ณผ "customer_u" ํ ์ด๋ธ์ ๊ณ ๊ฐ์ ์ด๋ฆ๊ณผ ์ฐํธ๋ฒํธ๋ฅผ ๊ฒฐํฉ ์กฐ๊ฑด์ผ๋ก ๊ฒฐํฉํ์ฌ ์์ฑ๋์๋ค. "customer" ํ ์ด๋ธ์ ์๋ก์ด "integration_id" ์ปฌ๋ผ์ ์ถ๊ฐํ๊ณ "customer_u" ํ ์ด๋ธ์์ ํด๋น "customer_id" ๊ฐ์ ํฌํจํจ์ผ๋ก์จ ๋ ํ ์ด๋ธ์ ํจ๊ณผ์ ์ผ๋ก ํตํฉํ๋ค.
ํ์ฌ ์ฟผ๋ฆฌ๋ "customer_n" ํ ์ด๋ธ์ "customer_id" ๊ฐ์ด "integration_id" ๊ฐ๊ณผ ๊ฐ์ง ์์ ํ ์๋ฅผ ๊ณ์ฐํ๋ค. ์ด๋ 'customer' ํ ์ด๋ธ์ 'customer_u' ํ ์ด๋ธ์ ํด๋นํ๋ ๋ ์ฝ๋๊ฐ ์๊ฑฐ๋ 'customer_u' ํ ์ด๋ธ์ 'customer_id' ๊ฐ์ด ๋ค๋ฅธ ๋ ์ฝ๋๊ฐ ์๋ค๋ ๊ฒ์ ์๋ฏธํ๋ค. ์ด๋ ๋ฐ์ดํฐ ํตํฉ ๋ฌธ์ ๋ฅผ ์๋ณํ๊ณ ๋ถ์ผ์น๋ฅผ ์ถ๊ฐ๋ก ์กฐ์ฌํ๋ ๋ฐ ์ฌ์ฉํ ์ ์๋ค.
S-089: ์์ธก ๋ชจ๋ธ ๊ตฌ์ถ์ ์ํด ํ๋งค ์ค์ ์ด ์๋ ๊ณ ๊ฐ์ ํ์ต์ฉ ๋ฐ์ดํฐ์ ํ ์คํธ์ฉ ๋ฐ์ดํฐ๋ก ๋๋๊ณ ์ถ๋ค. ๊ฐ๊ฐ 8:2์ ๋น์จ๋ก ๋ฌด์์๋ก ๋ฐ์ดํฐ๋ฅผ ๋ถํ ํ๋ผ.
%%sql
SELECT SETSEED(0.1);
CREATE TEMP TABLE IF NOT EXISTS sales_customer AS (
SELECT
customer_id ,
ROW_NUMBER() OVER(ORDER BY RANDOM()) AS row
FROM
customer
JOIN
receipt
USING(customer_id)
GROUP BY customer_id
HAVING SUM(AMOUNT) > 0
);
DROP TABLE IF EXISTS customer_train;
CREATE TABLE customer_train AS
SELECT
customer.*
FROM
sales_customer
JOIN
customer
USING(customer_id)
WHERE
sales_customer.row <= (SELECT
COUNT(1)
FROM sales_customer) * 0.8
;
DROP TABLE IF EXISTS customer_test;
CREATE TABLE customer_test AS
SELECT
customer.*
FROM
sales_customer
JOIN
customer
USING(customer_id)
EXCEPT
SELECT * FROM customer_train
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected. 8306 rows affected. Done. 6644 rows affected. Done. 1662 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ SQL์ ์ฌ์ฉํ์ฌ ๊ณ ๊ฐ ํ๋งค ๋ฐ์ดํฐ๋ฅผ ๊ธฐ๋ฐ์ผ๋ก ํ ๋จธ์ ๋ฌ๋ ๋ชจ๋ธ์ ํ์ต ๋ฐ์ดํฐ ์ธํธ์ ํ ์คํธ ๋ฐ์ดํฐ ์ธํธ๋ฅผ ์์ฑํ๊ณ ์๋ค. ๊ฐ ํ์ด ์ํํ๋ ์์ ์ ๋ค์๊ณผ ๊ฐ์ต๋๋ค.
sql SELECT SETSEED(0.1); %%sql SELECT SETSEED(0.1);
์ฟผ๋ฆฌ์์ ์ฌ์ฉํ ๋์ ์์ฑ๊ธฐ์ ์จ์์ ์ค์ ํฉ๋๋ค. ์ด๋ ์ฟผ๋ฆฌ๋ฅผ ์คํํ ๋๋ง๋ค ๋์ผํ ๊ฒฐ๊ณผ๋ฅผ ์ป์ ์ ์๋๋ก ํ๊ธฐ ์ํจ์ด๋ค.
CREATE TEMP TABLE IF NOT EXISTS sales_customer AS ( SELECT customer_id, ROW_NUMBER() OVER(ORDER BY RANDOM()) AS row FROM customer JOIN receipt USING( customer_id) GROUP BY customer_id HAVING SUM(AMOUNT) > 0 );
์ด๊ฒ์ ๊ฐ ๊ณ ๊ฐ์ customer_id์ ๋ฌด์์๋ก ํ ๋น๋ ํ ๋ฒํธ๋ฅผ ํฌํจํ๋ sales_customer๋ผ๋ ์์ ํ ์ด๋ธ์ ์์ฑํ๊ณ , JOIN์ ์ฌ์ฉํ์ฌ customer์ receipate๋ผ๋ ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํ๋ค. ๋ฐ์ดํฐ๋ฅผ ๊ทธ๋ฃนํํ์ฌ ๊ตฌ๋งคํ ๊ณ ๊ฐ(SUM(AMOUNT) > 0)๋ง์ ํฌํจํ๋๋ก ๊ฒฐ๊ณผ๋ฅผ ํํฐ๋งํ๊ณ ์์ต๋๋ค.
DROP TABLE IF EXISTS customer_train; CREATE TABLE customer_train AS SELECT customer.* FROM sales_customer JOIN customer USING(customer_id) WHERE sales_customer.row <= (SELECT COUNT(1) FROM sales_customer) * 0.8 ;
์ด๋ ๊ฒ ํ๋ฉด sales_customer ํ ์ด๋ธ์ ๊ณ ๊ฐ ์ค 80%๋ฅผ ํฌํจํ๋ customer_train์ด๋ผ๋ ํ ์ด๋ธ์ด ์์ฑ๋ฉ๋๋ค. ์ด๋ sales_customer ํ ์ด๋ธ์ ํ ์๊ฐ sales_customer ํ ์ด๋ธ์ ์ด ํ ์์ 80% ์ดํ์ธ ๊ณ ๊ฐ์ ๋ํด ๊ณ ๊ฐ ํ ์ด๋ธ์์ ๋ชจ๋ ์ด์ ์ ํํ๋ค.
DROP TABLE IF EXISTS customer_test; CREATE TABLE customer_test AS SELECT customer.* FROM sales_customer JOIN customer USING(customer_id) EXCEPT SELECT * FROM customer_train ;
์ด๊ฒ์ sales_customer ํ ์ด๋ธ์์ ๋๋จธ์ง 20%์ ๊ณ ๊ฐ์ ํฌํจํ๋ customer_test๋ผ๋ ํ ์ด๋ธ์ ์์ฑํ๋ค. ์ด๋ customer_id๊ฐ sales_customer ํ ์ด๋ธ์ ์๊ณ customer_train ํ ์ด๋ธ์ ์๋ ๊ณ ๊ฐ์ customer ํ ์ด๋ธ์์ ๋ชจ๋ ์ปฌ๋ผ์ ์ ํํ๋ค. ์ด๋ ๊ฒ ํ๋ฉด ํ ์คํธ ์ธํธ์ ํธ๋ ์ด๋ ์ธํธ์ ์๋ ๊ณ ๊ฐ์ด ํ ์คํธ ์ธํธ์ ํฌํจ๋๋ค.
%%sql
SELECT
train_cnt * 1.0 / all_cnt as ํ์ต ๋ฐ์ดํฐ ๋น์จ,
test_cnt * 1.0 / all_cnt as ํ
์คํธ ๋ฐ์ดํฐ ๋น์จ
FROM
(SELECT COUNT(1) AS all_cnt FROM sales_customer) all_data
CROSS JOIN
(SELECT COUNT(1) AS train_cnt FROM customer_train) train_data
CROSS JOIN
(SELECT COUNT(1) AS test_cnt FROM customer_test) test_data
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
ํ์ต ๋ฐ์ดํฐ ๋น์จ | ํ ์คํธ ๋ฐ์ดํฐ ๋น์จ |
---|---|
0.79990368408379484710 | 0.20009631591620515290 |
์ค๋ช :
์ด ์ฝ๋๋ ์์ SQL ์ฝ๋์์ ์์ฑํ ํ ์ด๋ธ์ ๊ธฐ๋ฐ์ผ๋ก ํธ๋ ์ด๋ ์ธํธ์ ํ ์คํธ ์ธํธ์ ๋ฐ์ดํฐ ๋น์จ์ ๊ณ์ฐํ๊ณ ์์ต๋๋ค. ๊ฐ ํ์ด ๋ฌด์์ ํ๋์ง ๊ทธ ๋ด์ญ์ ์๊ฐํฉ๋๋ค.
sql SELECT train_cnt * 1.0 / all_cnt as Percentage of training data, test_cnt * 1.0 / all_cnt as Test Data Percentage
์ด๊ฒ์ ํ๋ จ ์ธํธ์ ํ ์คํธ ์ธํธ์ ๋ฐ์ดํฐ ๋น์จ์ ์ ํํ๋ ๋ฉ์ธ ์ฟผ๋ฆฌ๋ก, train_cnt, test_cnt, all_cnt ๋ณ์๋ฅผ ์ฌ์ฉํ๋ฉฐ, ์ด๋ ์๋์ ์๋ธ ์ฟผ๋ฆฌ์์ ๊ณ์ฐ๋๋ค.
FROM (SELECT COUNT(1) AS all_cnt FROM sales_customer) all_data CROSS JOIN (SELECT COUNT(1) AS train_cnt FROM customer_train) train_data CROSS JOIN ( SELECT COUNT(1) AS test_cnt FROM customer_test) test_data ;
์ด ์๋ธ์ฟผ๋ฆฌ๋ sales_customer ํ ์ด๋ธ์ ์ด ํ ์(all_cnt), customer_train ํ ์ด๋ธ์ ํ ์(train_cnt), customer_test ํ ์ด๋ธ์ ํ ์(test_cnt)๋ฅผ ๊ณ์ฐํ๋ค.
์ด๋ฌํ ํ์ ์ฟผ๋ฆฌ๋ฅผ ํ๋์ ํ ์ด๋ธ๋ก ํตํฉํ๋ ค๋ฉด CROSS JOIN ์ฐ์ฐ์๋ฅผ ์ฌ์ฉํ๋ค. ๊ฐ ํ์ ์ฟผ๋ฆฌ๋ ํ๋์ ํ๋ง ๋ฐํํ๋ฏ๋ก ํฌ๋ก์ค ์กฐ์ธ์ ๊ฒฐ๊ณผ๋ 3๊ฐ์ ์ด(all_cnt, train_cnt, test_cnt)์ ๊ฐ์ง ํ๋์ ํ์ด ๋๋ค.
๊ทธ๋ฐ ๋ค์ ๋ฉ์ธ ์ฟผ๋ฆฌ๋ train_cnt์ test_cnt๋ฅผ ๊ฐ๊ฐ all_cnt๋ก ๋๋์ด ํ๋ จ ์ธํธ์ ํ ์คํธ ์ธํธ์ ๋ฐ์ดํฐ ๋น์จ์ ๊ณ์ฐํ๋ค. ๊ฒฐ๊ณผ๋ฅผ ๋ถ๋ ์์์ ์ซ์๋ก ๋ณํํ๊ธฐ ์ํด * 1.0 ์์ ์ฌ์ฉํ์ฌ ๋ถ๋ ์์์ ์ ํ๋๋ก ๋๋์ ์ด ์ด๋ฃจ์ด์ง๋๋ก ๋ณด์ฅํ๋ค.
S-090: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)๋ 2017๋ 1์ 1์ผ๋ถํฐ 2019๋ 10์ 31์ผ๊น์ง์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ง๊ณ ์๋ค. ๋งค์ถ ๊ธ์ก(amount)์ ์๋ณ๋ก ์ง๊ณํ์ฌ ํ์ต์ฉ 12๊ฐ์, ํ ์คํธ์ฉ 6๊ฐ์์ ์๊ณ์ด ๋ชจ๋ธ ๊ตฌ์ถ์ฉ ๋ฐ์ดํฐ 3์ธํธ๋ฅผ ์์ฑํ๋ค.
%%sql
--SQL์ ์ ํฉํ์ง ์๊ธฐ ๋๋ฌธ์ ๋ค์ ๋ฌด๋ฆฌํ๊ฒ ๊ธฐ์ ํ๋ค(๋ถํ ์๊ฐ ๋ง์์ง๋ฉด SQL์ด ๊ธธ์ด์ง๊ธฐ ๋๋ฌธ์ ํ์ค์ ์ด์ง ์์)
-- ๋ํ ์ด ๋จ์์ ๋ฐ์ดํฐ ๋ฑ ์๊ณ์ด์ด ์ธ๋ฐํ๊ณ ์ฅ๊ธฐ๊ฐ์ ๊ฑธ์ณ ์๋ ๊ฒฝ์ฐ ๋ฐ์ดํฐ๊ฐ ๋ฐฉ๋ํด์ง๊ธฐ ๋๋ฌธ์ ์ฃผ์(์ด๋ฌํ ๊ฒฝ์ฐ ๋ฃจํ ์ฒ๋ฆฌ๋ก ๋ชจ๋ธ ํ์ต์ด ๊ฐ๋ฅํ ์ธ์ด๊ฐ ๋ฐ๋์งํจ)
-- ํ์ต ๋ฐ์ดํฐ์ ํ
์คํธ ๋ฐ์ดํฐ๋ฅผ ๊ตฌ๋ถํ๋ ํ๋๊ทธ๋ฅผ ๋ถ์ฌํ๋ค. ํ์ต ๋ฐ์ดํฐ(0)์ ํ
์คํธ ๋ฐ์ดํฐ(1)๋ฅผ ๊ตฌ๋ถํ๋ ํ๋๊ทธ ๋ถ์ฌ
-- ์ฌ์ ์ค๋น๋ก ์ฐ๋๋ณ ๋งค์ถ์ก์ ์ง๊ณํ์ฌ ์ฐ๋ฒ์ ๋ถ์ฌํ๋ค.
CREATE TEMP TABLE IF NOT EXISTS ts_amount AS (
SELECT
SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6) AS sales_ym,
SUM(amount) AS sum_amount,
ROW_NUMBER() OVER(
ORDER BY SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6)) AS rn
FROM
receipt
GROUP BY sales_ym
);
-- SQL์์๋ ํ๊ณ๊ฐ ์์ง๋ง, ์์ฑ ๋ฐ์ดํฐ์
์ ์ฆ๊ฐ์ ๋ฐ๋ผ ๊ฐ๋ฅํ ํ ๋ฐ๋ณตํด์ ์ฌ์ฉํ ์ ์๋๋ก ํจ
-- WITH ๊ตฌ๋ฌธ ๋ด LAG ํจ์์ ๋ํด ์ง์ฐ ๊ธฐ๊ฐ์ ๋ณ๊ฒฝํ๋ฉด ๋ฐ๋ณตํด์ ์ฌ์ฉํ ์ ์๋๋ก ์์ฑ
DROP TABLE IF EXISTS series_data_1 ;
CREATE TABLE series_data_1 AS (
WITH lag_amount AS (
SELECT
sales_ym,
sum_amount,
LAG(rn, 0) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ym,
sum_amount,
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18);
DROP TABLE IF EXISTS series_data_2 ;
CREATE TABLE series_data_2 AS (
WITH lag_amount AS (
SELECT
sales_ym,
sum_amount,
LAG(rn, 6) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ym,
sum_amount,
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18);
DROP TABLE IF EXISTS series_data_3 ;
CREATE TABLE series_data_3 AS (
WITH lag_amount AS (
SELECT
sales_ym,
sum_amount,
LAG(rn, 12) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ym,
sum_amount,
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18);
* postgresql://padawan:***@db:5432/dsdojo_db 34 rows affected. Done. 18 rows affected. Done. 18 rows affected. Done. 18 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋์์๋ ๋งค์ถ ๋ฐ์ดํฐ ์ง๊ณํ(ts_amount)์ ์๋์ฐ ํจ์๋ฅผ ์ ์ฉํ์ฌ 3๊ฐ์ ์๋ก์ด ํ ์ด๋ธ(series_data_1, series_data_2, series_data_3)์ ์์ฑํ๊ณ ์์ต๋๋ค. ์๋๋ ๊ฐ ํ์ด ํ๋ ์ผ์ ๋ด์ญ์ ๋๋ค.
%%sql
CREATE TEMP TABLE IF NOT EXISTS ts_amount AS
(
SELECT
SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6) AS sales_ym,
SUM(amount) AS sum_amount,
ROW_NUMBER() OVER(ORDER BY SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6)) AS rn
FROM
receipt
GROUP BY
sales_name
);;
์ด ์ฝ๋์์๋ ์๋ณ๋ก ๋งค์ถ ๋ฐ์ดํฐ๋ฅผ ์ ๋ฆฌํ๊ณ (SUBSTR ํจ์๋ฅผ ์ฌ์ฉํ์ฌ sales_ymd ์ด์์ ์ฐ๋์ ์์ ์ถ์ถ), ๋งค์ถ ์ด์ก์ ๊ณ์ฐํ๊ณ (SUM(amount)), ๊ฐ ํ์ ํ ๋ฒํธ๋ฅผ ๋ถ์ฌ(ROW_NUMBER() OVER(...)) AS rn) ์์ ํ ์ด๋ธ(ts_amount)์ ์์ฑํ๊ณ ์๋ค. ํ ๋ฒํธ๋ ์ดํ ์ฟผ๋ฆฌ์์ ์ฌ๋ฌ ๊ธฐ๊ฐ์ ๋ฐ์ดํฐ๋ฅผ ๊ฒฐํฉํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
DROP TABLE IF EXISTS series_data_1 ; DROP TABLE IF EXISTS series_data_1 ;
CREATE TABLE series_data_1 AS (
WITH lag_amount AS
(
SELECT
sales_ym์ ๋๋ค.
sum_amount๊ฐ ๋ฉ๋๋ค.
LAG(rn, 0) OVER (ORDER BY rn) AS rn
FROM
ts_amount
)
SELECT
sales_ym์ ๋๋ค.
sum_amount๊ฐ ๋ฉ๋๋ค.
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM
lag_amount
WHERE
rn BETWEEN 1 AND 18).
์ด ์ฝ๋๋ ํ์ฌ ๊ธฐ๊ฐ์ ๋งค์ถ ๋ฐ์ดํฐ์ ์ด์ ๊ธฐ๊ฐ์ ๋งค์ถ ๋ฐ์ดํฐ๋ฅผ (window ํจ์์ LAG ํจ์๋ฅผ ์ฌ์ฉํ์ฌ) ๊ฒฐํฉํ์ฌ ์๋ก์ด ํ ์ด๋ธ(series_data_1)์ ์์ฑํ๊ณ , ๊ฐ ํ์ด ํธ๋ ์ด๋ ์ธํธ(test_flg = 0)์ ์ํ๋์ง ํ ์คํธ ์ธํธ(test_flg = 1)์ ์ํ๋์ง ์ ๋ฐ๋ผ ๋ฐ์ด๋๋ฆฌ ํ๋๊ทธ(test_flg)๋ฅผ ํ ๋นํ๋ค. ์ด๋ ํ ๋ฒํธ(rn)๋ฅผ ์๊ณ๊ฐ(12)๊ณผ ๋น๊ตํ์ฌ ๋ฐ์ดํฐ๋ฅผ ๋ ๋ถ๋ถ์ผ๋ก ๋๋๋ค: ์ฒ์ 12๊ฐ์ ํ์ ํ๋ จ์, ๋๋จธ์ง 6๊ฐ์ ํ์ ํ ์คํธ์ ์ฌ์ฉ๋๋ค. ์ด๋ ๊ฒ ๋ง๋ค์ด์ง ํ๋ ์ด 18๊ฐ์ ํ์ผ๋ก ๊ตฌ์ฑ๋๋ค. ์ด ์ฝ๋์์๋ ์ด ๊ณผ์ ์ ๊ธฐ๊ฐ์ ๋ฐ๊ฟ์ ๋ ๋ฒ ๋ ๋ฐ๋ณตํ๊ณ ์์ต๋๋ค.
DROP TABLE IF EXISTS series_data_2 ;.
CREATE TABLE series_data_2 AS (
WITH lag_amount AS (
SELECT
sales_ym์ ๋๋ค.
sum_amount๋ก ํ๋ค.
LAG(rn, 6) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ym์ ๋๋ค.
sum_amount๋ก ํ๋ค.
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18);
DROP TABLE IF EXISTS series_data_3 ;
CREATE TABLE series_data_3 AS (
WITH lag_amount AS (
SELECT
sales_ym์ ๋๋ค.
sum_amount๋ก ํ๋ค.
LAG(rn, 12) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ym์ ๋๋ค.
sum_amount๋ก ํ๋ค.
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18).
series_data_2๋ 6๊ฐ์ ์ ๋งค์ถ ๋ฐ์ดํฐ๋ฅผ, series_data_3์ 1๋ ์ ๋งค์ถ ๋ฐ์ดํฐ๋ฅผ ์ฐธ์กฐ ๊ธฐ๊ฐ์ผ๋ก ํ๋ค.
%%sql
-- series_data_2์ series_data_3์ ํ์๋ฅผ ์๋ตํฉ๋๋ค.
SELECT * FROM series_data_1;
* postgresql://padawan:***@db:5432/dsdojo_db 18 rows affected.
sales_ym | sum_amount | test_flg |
---|---|---|
201701 | 902056 | 0 |
201702 | 764413 | 0 |
201703 | 962945 | 0 |
201704 | 847566 | 0 |
201705 | 884010 | 0 |
201706 | 894242 | 0 |
201707 | 959205 | 0 |
201708 | 954836 | 0 |
201709 | 902037 | 0 |
201710 | 905739 | 0 |
201711 | 932157 | 0 |
201712 | 939654 | 0 |
201801 | 944509 | 1 |
201802 | 864128 | 1 |
201803 | 946588 | 1 |
201804 | 937099 | 1 |
201805 | 1004438 | 1 |
201806 | 1012329 | 1 |
์ค๋ช :
์ด ์ฝ๋๋ ํ ์ด๋ธ series_data_1์์ ๋ชจ๋ ์ด๊ณผ ํ์ ์ ํํ๋ SQL ์ฟผ๋ฆฌ์ด๋ค. series_data_1 ํ ์ด๋ธ์ ์์ SQL ์ฝ๋ ๋ธ๋ก์์ CREATE TABLE ๋ฌธ์ ์ฌ์ฉํ์ฌ ์์ฑ๋์์ต๋๋ค.
series_data_1 ์์ฑ์์ ์ฟผ๋ฆฌ๋ ๊ฐ ์๋ณ ๊ธ์ก์ ํฉ๊ณ๋ฅผ ๊ณ์ฐํ๋ ์์ ํ ์ด๋ธ ts_amount๋ฅผ ์์ฑํ๊ณ , ์์ ๊ธฐ์ค์ผ๋ก ํ ๋ฒํธ๋ฅผ ํ ๋นํ๊ณ ์๋ค. ํ ๋ฒํธ์ LAG() ์ฐฝ ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ์ฟผ๋ฆฌ๋ ์๋ก์ด ํ ์ด๋ธ series_data_1์ ์์ฑํ๊ณ ๊ฐ ์์ ๋งค์ถ ๊ธ์ก๊ณผ ํด๋น ํ์ด ํ๋ จ ๋ฐ์ดํฐ์ ํ ์คํธ ๋ฐ์ดํฐ ์ค ์ด๋ ์ชฝ์ ํฌํจ๋๋์ง ํ์ํ๋ ํ๋๊ทธ๋ฅผ ์ ์ฅํ๋ค.
๋ง์ง๋ง์ผ๋ก ์ด ์ฝ๋ ๋ธ๋ก์ ์ฟผ๋ฆฌ๊ฐ series_data_1์์ ๋ชจ๋ ์ด๊ณผ ํ์ ์ ํํ์ฌ ์ด์ SQL ๋ฌธ์ฅ์ ๊ฒฐ๊ณผ๋ฅผ ํ์ํ ์ ์๋๋ก ํ๋ค.
S-091: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ๊ฐ ๊ณ ๊ฐ์ ๋ํด ๋งค์ถ ์ค์ ์ด ์๋ ๊ณ ๊ฐ ์์ ๋งค์ถ ์ค์ ์ด ์๋ ๊ณ ๊ฐ ์๊ฐ 1:1์ด ๋๋๋ก ์ธ๋์ํ๋ง์ผ๋ก ์ถ์ถํ๋ผ.
%%sql
SELECT SETSEED(0.1);
CREATE TEMP TABLE IF NOT EXISTS down_sampling AS (
WITH pre_table_1 AS(
SELECT
c.*
,COALESCE(r.sum_amount,0) AS sum_amount
FROM
customer c
LEFT JOIN (
SELECT
customer_id,
SUM(amount) AS sum_amount
FROM
receipt
GROUP BY
customer_id
) r
ON
c.customer_id=r.customer_id
)
,pre_table_2 AS(
SELECT
*
,CASE WHEN sum_amount > 0 THEN 1 ELSE 0 END AS is_buy_flag
,CASE WHEN sum_amount = 0 THEN 1 ELSE 0 END AS is_not_buy_flag
FROM
pre_table_1
)
,pre_table_3 AS(
SELECT
*
,ROW_NUMBER() OVER(PARTITION BY is_buy_flag ORDER BY RANDOM())
FROM
pre_table_2
CROSS JOIN
(SELECT SUM(is_buy_flag) AS buying FROM pre_table_2) AS t1
CROSS JOIN
(SELECT SUM(is_not_buy_flag) AS not_buying FROM pre_table_2) AS t2
)
SELECT
*
FROM
pre_table_3
WHERE
row_number <= buying
AND row_number <= not_buying
);
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected. 16612 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ ๊ณ ๊ฐ ๋ฐ์ดํฐ ๋ค์ด์ํ๋ง์ ์ํํ๋ SQL ์ฟผ๋ฆฌ์ ๋๋ค. ๋ค์ด์ํ๋ง์ ๋ฐ์ดํฐ ์งํฉ์์ ์ผ๋ถ ๋ฐ์ดํฐ ํฌ์ธํธ๋ฅผ ๋ฌด์์๋ก ์ ๊ฑฐํ์ฌ ํด๋์ค์ ๊ท ํ์ ๋ง์ถ๊ฑฐ๋ ๋ฐ์ดํฐ ์งํฉ์ ํฌ๊ธฐ๋ฅผ ์ค์ด๋ ๊ณผ์ ์ ๋๋ค.
์ฟผ๋ฆฌ์์๋ ์ฒซ ๋ฒ์งธ ๋ฌธ์์ ๋ฌด์์ ์๋ ๊ฐ์ ์ค์ ํ๊ณ ์๋ค. ๋ค์ ๋ฌธ์์๋ ๊ณ ๊ฐ ๋ฐ์ดํฐ์ ๊ฐ ๊ณ ๊ฐ์ ๊ธ์ก ํฉ๊ณ๊ฐ ํฌํจ๋ down_sampling์ด๋ผ๋ ์์ ํ ์ด๋ธ์ ์์ฑํ๊ณ ์์ต๋๋ค. ์ฒซ ๋ฒ์งธ CTE์ LEFT JOIN ๊ตฌ๋ฌธ์ ๊ณ ๊ฐ ํ ์ด๋ธ๊ณผ ์์์ฆ ํ ์ด๋ธ์ ๊ฒฐํฉํ์ฌ ๊ฐ ๊ณ ๊ฐ์ ๊ธ์ก ํฉ๊ณ๋ฅผ ๊ณ์ฐํฉ๋๋ค.
๋ ๋ฒ์งธ CTE์์๋ CASE ๋ฌธ์ ์ฌ์ฉํ์ฌ ๋ ๊ฐ์ ์ถ๊ฐ ์ด์ด ์์ฑ๋๊ณ ์์ต๋๋ค. ์ด ์ด์ ๊ณ ๊ฐ์ด ๊ตฌ๋งค๋ฅผ ํ๋์ง ์ฌ๋ถ๋ฅผ ๋ํ๋ด๋ ํ๋๊ทธ์ด๋ค. ๊ทธ๋ฐ ๋ค์ ์ธ ๋ฒ์งธ CTE์์ ROW_NUMBER() ์ฐฝ ํจ์๋ฅผ ์ฌ์ฉํ์ฌ is_buy_flag ์ด์ ๊ธฐ๋ฐ์ผ๋ก ๊ฐ ๊ณ ๊ฐ ๋ ์ฝ๋์ ํ ๋ฒํธ๋ฅผ ํ ๋นํ๊ณ ์๋ค.
๋ง์ง๋ง์ผ๋ก ์ฟผ๋ฆฌ์ ๋ง์ง๋ง ๋ฌธ์์ ์์ ํ ์ด๋ธ down_sampling์ด ์ ํ๋๋๋ฐ, ํ ๋ฒํธ๊ฐ ๊ตฌ๋งค ๊ณ ๊ฐ ์์ ๋น๊ตฌ๋งค ๊ณ ๊ฐ ์ ์ดํ์ธ ํ๋ง ์ ํ๋๋ค. ์ด๋ ๊ฒ ํ๋ฉด ๊ฒฐ๊ณผ ํ ์ด๋ธ์์ ๊ฐ ํด๋์ค์ ํ ์๊ฐ ๊ท ํ ์๊ฒ ๋ฐฐ์น๋ฉ๋๋ค.
ย
%%sql
SELECT is_buy_flag, COUNT(1) FROM down_sampling GROUP BY is_buy_flag;
* postgresql://padawan:***@db:5432/dsdojo_db 2 rows affected.
is_buy_flag | count |
---|---|
0 | 8306 |
1 | 8306 |
์ค๋ช :
์ด ์ฝ๋๋ down_sampling ์์ ํ ์ด๋ธ์ ๋ ์ฝ๋๋ฅผ is_buy_flag ์ด๋ก ๊ทธ๋ฃนํํ์ฌ ๊ฐ ๊ทธ๋ฃน ๋ด ๋ ์ฝ๋ ์๋ฅผ ๊ณ์ฐํ๋ SQL ์ฟผ๋ฆฌ๋ฅผ ์คํํ๋ค.
down_sampling ํ ์ด๋ธ์ ์์ ์ฝ๋ ์ ์์ customer ํ ์ด๋ธ๊ณผ receive ํ ์ด๋ธ์ amount ์ด์ customer_id๋ก ์ง๊ณํ๋ ํ์ ์ฟผ๋ฆฌ ์ฌ์ด์ ์ผ์ชฝ ๊ฒฐํฉ์ ์ํํ์ฌ ์์ฑ๋์๋ค. ๊ฒฐ๊ณผ ํ ์ด๋ธ์๋ ๊ณ ๊ฐ ์ ๋ณด ์ด๊ณผ ๊ฐ ๊ณ ๊ฐ์ด ๊ตฌ๋งคํ ๊ธ์ก์ ํฉ๊ณ๊ฐ ์์ต๋๋ค.
pre_table_2 ํ์ ์ฟผ๋ฆฌ๋ sum_amount ์ปฌ๋ผ์ ๊ธฐ๋ฐ์ผ๋ก ๋ ๊ฐ์ ์๋ก์ด ์ปฌ๋ผ์ ์์ฑํ๋๋ฐ, is_buy_flag ์ปฌ๋ผ์ 1ํ ์ด์ ๊ตฌ๋งค๋ฅผ ํ ๊ณ ๊ฐ์๊ฒ๋ 1์ด, ๊ตฌ๋งค๋ฅผ ํ์ง ์์ ๊ณ ๊ฐ์๊ฒ๋ 0์ด ์ค์ ๋๊ณ , is_not_buy_flag ์ปฌ๋ผ์ ๋ ๊ตฌ๋งค๋ฅผ ํ ๋ฒ๋ ํ์ง ์์ ๊ณ ๊ฐ์๊ฒ๋ 1, 1ํ ์ด์ ๊ตฌ๋งค๋ฅผ ํ ๊ณ ๊ฐ์๊ฒ๋ 0์ด ์ค์ ๋๋ค.
pre_table_3 ํ์ ์ฟผ๋ฆฌ๋ is_buy_flag ์ปฌ๋ผ๊ณผ ์์์ ์์์ ๋ฐ๋ผ pre_table_2 ํ ์ด๋ธ์ ๊ฐ ํ์ ๋ํด ํ ๋ฒํธ๋ฅผ ์์ฑํ๋ค. ๋ํ, ๋ ๊ฐ์ ๋ณ๋ ํ์ ์ฟผ๋ฆฌ์ ์ํ ๊ต์ฐจ ์กฐ์ธ์ ์ฌ์ฉํ์ฌ is_buy_flag๊ฐ 1์ผ ๋์ 0์ผ ๋ pre_table_2 ํ ์ด๋ธ์ ์ด ํ ์๋ฅผ ๊ณ์ฐํ๋ค.
๋ง์ง๋ง์ผ๋ก ๋ฉ์ธ SELECT ๋ฌธ์ is_buy_flag๊ฐ 1์ธ ํ๊ณผ 0์ธ ํ์ ์ด ๊ฐ์๋ณด๋ค ์ ์ ํ ๋ฒํธ๋ฅผ ๊ฐ์ง pre_table_3์ ๋ชจ๋ ํ์ ์ ํํ๋ค. ์ด๋ ๊ฒ ํ๋ฉด is_buy_flag์ ๊ฐ ๊ฐ์ ๋ํด ๋์ผํ ์์ ๋ ์ฝ๋๊ฐ ์กด์ฌํ๊ฒ ๋๋ค.
๋ฐ๋ผ์ SELECT ๋ฌธ ์ถ๋ ฅ์ down_sampling ํ ์ด๋ธ์ is_buy_flag๊ฐ 0์ธ ๋ ์ฝ๋์ 1์ธ ๋ ์ฝ๋์ ๊ฐ์๋ฅผ ๋ํ๋ธ๋ค.
S-092: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ์ฑ๋ณ์ ์ 3์ ๊ทํ์ผ๋ก ์ ๊ทํํ๋ผ.
%%sql
DROP TABLE IF EXISTS customer_std;
CREATE TABLE customer_std AS (
SELECT
customer_id,
customer_name,
gender_cd,
birth_day,
age,
postal_cd,
application_store_cd,
application_date,
status_cd
FROM
customer
);
DROP TABLE IF EXISTS gender_std;
CREATE TABLE gender_std AS (
SELECT distinct
gender_cd, gender
FROM
customer
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 21971 rows affected. Done. 3 rows affected.
[]
์ค๋ช :
์ด SQL ์ฝ๋๋ customer๋ผ๋ ๊ธฐ์กด ํ ์ด๋ธ์์ ๋ ๊ฐ์ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๋ค.
์ฝ๋์ ์ฒซ ๋ฒ์งธ ๋ถ๋ถ์์๋ customer_std๋ผ๋ ํ ์ด๋ธ์ด ์กด์ฌํ๋ ๊ฒฝ์ฐ ์ด๋ฅผ ์ญ์ ํ๊ณ customer_std๋ผ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํฉ๋๋ค. ์ด ์ ํ ์ด๋ธ์๋ customer ํ ์ด๋ธ๊ณผ ๋์ผํ ์ด์ด ์๋๋ฐ, customer_id, customer_name, gender_cd, birth_day, age, postal_cd, application_store_cd, application_date, status_cd๋ก ๊ตฌ์ฑ๋์ด ์๋ค. ์ด ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๋ ๋ชฉ์ ์ ์ ๊ณต๋ ์ฝ๋์์ ๋ช ํํ์ง ์๋ค. ๋ฐ์ดํฐ๋ฅผ ํ์คํํ๊ฑฐ๋ ์ ์ฒ๋ฆฌํ๊ธฐ ์ํด ์์ฑ๋ ๊ฒ์ผ ์๋ ์๋ค.
์ฝ๋์ ๋ ๋ฒ์งธ ๋ถ๋ถ์ gender_std๋ผ๋ ํ ์ด๋ธ์ด ์กด์ฌํ๋ค๋ฉด ์ด๋ฅผ ์ญ์ ํ๊ณ gender_std๋ผ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๋ค. ์๋ก์ด ํ ์ด๋ธ์๋ gender_cd์ gender๋ผ๋ ๋ ๊ฐ์ ์ปฌ๋ผ์ด ์๋๋ฐ, gender_cd ์ปฌ๋ผ์ ๊ณ ๊ฐ ํ ์ด๋ธ์์ ์ฐพ์ ๋ชจ๋ ์ฑ๋ณ ์ฝ๋์ ๋ช ํํ ๋ชฉ๋ก์ด๊ณ , gender ์ปฌ๋ผ์ ํด๋น ์ฑ๋ณ์ ๋ํ ์ค๋ช ์ด๋ค. ์ด ํ ์ด๋ธ์ ์ฑ๋ณ ์ฝ๋๋ฅผ ๊ฐ๊ฐ์ ์ฑ๋ณ ์ค๋ช ์ ๋งคํํ๋ ๋ฐ ์ฌ์ฉ๋ ์ ์๋ค.
%%sql
-- ๋ฐ์ดํฐ ๋ด์ฉ ํ์ธ
SELECT * FROM customer_std LIMIT 3;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
customer_id | customer_name | gender_cd | birth_day | age | postal_cd | application_store_cd | application_date | status_cd |
---|---|---|---|---|---|---|---|---|
CS021313000114 | ๅคง้ ใใๅญ | 1 | 1981-04-29 | 37 | 259-1113 | S14021 | 20150905 | 0-00000000-0 |
CS037613000071 | ๅ ญ่ง ้ ๅฝฆ | 9 | 1952-04-01 | 66 | 136-0076 | S13037 | 20150414 | 0-00000000-0 |
CS031415000172 | ๅฎๅค็ฐ ่ฒด็พๅญ | 1 | 1976-10-04 | 42 | 151-0053 | S13031 | 20150529 | D-20100325-C |
์ค๋ช :
์ด ์ฝ๋์์๋ ์์ ์์ฑํ customer_std ํ ์ด๋ธ์์ ์ฒ์ 3๊ฐ ํ์ ์ ํํ๋๋ฐ, customer_std ํ ์ด๋ธ์ customer ํ ์ด๋ธ์ ๋ณต์ฌ๋ณธ์ผ๋ก ์ปฌ๋ผ์ ํ์ ์งํฉ์ ๊ฐ์ง๊ณ ์์ต๋๋ค. ์ด๋ ๋ฐ์ดํฐ๊ฐ ์ ํ ์ด๋ธ๋ก ์ฌ๋ฐ๋ฅด๊ฒ ๊ฐ์ ธ์๋์ง ํ์ธํ๊ธฐ ์ํ ๊ธฐ๋ณธ์ ์ธ ์ ๊ฒ์ ๋๋ค.
%%sql
-- ๋ฐ์ดํฐ ๋ด์ฉ ํ์ธ
SELECT * FROM gender_std LIMIT 3;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
gender_cd | gender |
---|---|
0 | ็ทๆง |
9 | ไธๆ |
1 | ๅฅณๆง |
์ค๋ช :
์ด ์ฝ๋๋ ์ด์ ์ฝ๋ ๋ธ๋ก์์ ์์ฑ๋ gender_std ํ ์ด๋ธ์์ ์ฒ์ 3๊ฐ์ ํ์ ์ ํํ๋ SQL ์ฟผ๋ฆฌ๋ฅผ ์คํํ๋ค.
LIMIT ์ ์ ์ฟผ๋ฆฌ์์ ๋ฐํ๋๋ ํ ์๋ฅผ 3ํ์ผ๋ก ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, gender_std ํ ์ด๋ธ์ SELECT DISTINCT ๋ฌธ์ ์ฌ์ฉํ์ฌ ์์ฑ๋์๊ธฐ ๋๋ฌธ์ gender_cd์ ๊ณ ์ ํ ๊ฐ๊ณผ ๊ณ ๊ฐ ํ ์ด๋ธ์์ ํด๋น ์ฑ๋ณ์ ๊ฐ๋ง ํฌํจํ๊ฒ ๋ฉ๋๋ค. ์์ต๋๋ค. ๋ฐ๋ผ์ ์ด ์ฟผ๋ฆฌ๋ gender_std ํ ์ด๋ธ์์ ์ฒ์ ์ธ ๊ฐ์ ๊ณ ์ ํ ํ์ ๋ฐํํ๋ค.
์ด ์ฟผ๋ฆฌ์ ์ถ๋ ฅ์ gender_std ํ ์ด๋ธ์ ์ฒ์ ์ธ ํ์ gender_cd์ gender ๊ฐ์ ํ์ํฉ๋๋ค.
S-093: ์ํ ๋ฐ์ดํฐ(df_product)๋ ๊ฐ ์นดํ ๊ณ ๋ฆฌ์ ์ฝ๋ ๊ฐ๋ง ๋ณด์ ํ๊ณ ์นดํ ๊ณ ๋ฆฌ ์ด๋ฆ์ ๋ณด์ ํ์ง ์๋๋ค. ์นดํ ๊ณ ๋ฆฌ ๋ฐ์ดํฐ(df_category)์ ๊ฒฐํฉํ์ฌ ๋น์ ๊ทํํ์ฌ ์นดํ ๊ณ ๋ฆฌ ์ด๋ฆ์ ๋ณด์ ํ ์๋ก์ด ์ํ ๋ฐ์ดํฐ๋ฅผ ์์ฑํ๋ค.
%%sql
DROP TABLE IF EXISTS product_full;
CREATE TABLE product_full AS (
SELECT
p.product_cd,
p.category_major_cd,
c.category_major_name,
p.category_medium_cd,
c.category_medium_name,
p.category_small_cd,
c.category_small_name,
p.unit_price,
p.unit_cost
FROM
product p
JOIN
category c
USING(category_small_cd)
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 10030 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ "product"์ "category" ๋ ํ ์ด๋ธ์ ๊ฒฐํฉํ์ฌ "product_full"์ด๋ผ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๋ค.
product ํ ์ด๋ธ์๋ ์ํ ์ฝ๋, ์นดํ ๊ณ ๋ฆฌ ์ฝ๋, ๋จ๊ฐ, ๋จ๊ฐ ๋ฑ ๊ฐ ์ํ์ ๋ํ ์ ๋ณด๊ฐ ์ ์ฅ๋์ด ์๋ค.
category ํ ์ด๋ธ์๋ ๊ฐ ์นดํ ๊ณ ๋ฆฌ ๋ ๋ฒจ์ ์นดํ ๊ณ ๋ฆฌ ์ฝ๋์ ์นดํ ๊ณ ๋ฆฌ ์ด๋ฆ์ด ์ ์ฅ๋๋ค.
JOIN ๊ตฌ๋ฌธ์ category_small_cd ์ด์ ๊ธฐ์ค์ผ๋ก ๋ ํ ์ด๋ธ์ ์ฐ๊ฒฐํ๊ณ , USING ํค์๋๋ฅผ ์ฌ์ฉํ์ฌ ๋ ํ ์ด๋ธ์์ category_small_cd์ ๊ฐ์ด ๋์ผํ ํ์ ์ผ์น์ํค๋ ์ฟผ๋ฆฌ๋ฅผ ์ํํ๋ค.
์๋ก์ด ํ ์ด๋ธ 'product_full'์๋ 'product' ํ ์ด๋ธ์ ๋ชจ๋ ์ปฌ๋ผ๊ณผ 'category' ํ ์ด๋ธ์ ์นดํ ๊ณ ๋ฆฌ ์ฝ๋๋ฅผ ๊ฒ์ํ์ฌ ์ป์ ์นดํ ๊ณ ๋ฆฌ ์ด๋ฆ ์ปฌ๋ผ์ด ์ถ๊ฐ๋๋ค.
ย
%%sql
-- ๋ฐ์ดํฐ ๋ด์ฉ ํ์ธ
SELECT * FROM product_full LIMIT 3;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
product_cd | category_major_cd | category_major_name | category_medium_cd | category_medium_name | category_small_cd | category_small_name | unit_price | unit_cost |
---|---|---|---|---|---|---|---|---|
P040101001 | 04 | ๆฃ่ | 0401 | ๅพก้ฃฏ้ก | 040101 | ๅผๅฝ้ก | 198 | 149 |
P040101002 | 04 | ๆฃ่ | 0401 | ๅพก้ฃฏ้ก | 040101 | ๅผๅฝ้ก | 218 | 164 |
P040101003 | 04 | ๆฃ่ | 0401 | ๅพก้ฃฏ้ก | 040101 | ๅผๅฝ้ก | 230 | 173 |
์ค๋ช :
์ด ์ฝ๋๋ product์ category๋ผ๋ ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํ์ฌ product_full์ด๋ผ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๋ค.
JOIN ๊ตฌ๋ฌธ์ ๊ณตํต ์ด์ธ category_small_cd๋ฅผ ๊ธฐ์ค์ผ๋ก ๋ ํ ์ด๋ธ์ ๊ฒฐํฉํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด๋ ๊ฒฐ๊ณผ ํ ์ด๋ธ์ category_small_cd ๊ฐ์ด ๋ ํ ์ด๋ธ์์ ๋์ผํ ํ๋ง ์์์ ์๋ฏธํฉ๋๋ค.
๋ ํ ์ด๋ธ์์ ์ ํํ ์ปฌ๋ผ์ product_cd, category_major_cd, category_major_name, category_medium_cd, category_medium_name, category_small_cd, category_small_name, unit_price, unit_cost์ ๋๋ค.
๊ฒฐ๊ณผ ํ ์ด๋ธ product_full์๋ ๊ฒฐํฉ ์กฐ๊ฑด์ ๋ง์กฑํ๋ ๋ชจ๋ ํ์ ๋ํด ์ด๋ฌํ ์ปฌ๋ผ์ด ํฌํจ๋ ๊ฒ์ด๋ค.
LIMIT ์ ์ ์ถ๋ ฅ์ ํ์๋๋ ํ์ ์๋ฅผ 3๊ฐ๋ก ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค.
ย
S-094: 093์์ ์์ฑํ ์นดํ ๊ณ ๋ฆฌ๋ช ์ํ ๋ฐ์ดํฐ๋ฅผ ๋ค์๊ณผ ๊ฐ์ ์ฌ์์ผ๋ก ํ์ผ ์ถ๋ ฅํ๋ค.
| ํ์ผ ํ์| ํค๋ ์ ๋ฌด| ๋ฌธ์ ์ธ์ฝ๋ฉ||:โ:|:โ:|:โ:|CSV(์ผํ๋ก ๊ตฌ๋ถ) | ์์ | UTF-8|ํ์ผ ์ถ๋ ฅ ๊ฒฝ๋ก๊ฐ ์๋์ ๊ฐ์์ผ ํ๋ค(COPY ๋ช ๋ น์ ๊ถํ์ ๋ถ์ฌ๋์ด ์์ด์ผ ํ๋ค).| ์ถ๋ ฅ์ฒ||:โ:||/tmp/data|ยโปโ/tmp/dataโ๋ฅผ ์ง์ ํ๋ฉด Jupyter์ โ/work/dataโ์ ๊ณต์ ํ๋๋ก ๋์ด ์๋ค.
%%sql
COPY product_full TO '/tmp/data/S_product_full_UTF-8_header.csv'
WITH CSV HEADER ENCODING 'UTF-8';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ product_full ํ ์ด๋ธ์ ๋ด์ฉ์ "/tmp/data" ๋๋ ํ ๋ฆฌ์ ์๋ "S_product_full_UTF-8_header.csv"๋ผ๋ ์ด๋ฆ์ CSV ํ์ผ๋ก ๋ด๋ณด๋ด๋ ์ฝ๋์ ๋๋ค. ๋ช ๋ น์ผ๋ก, ํ์ผ์ด๋ ํ ์ด๋ธ ๊ฐ์ ๋ฐ์ดํฐ๋ฅผ ๋ณต์ฌํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ์ด ๊ฒฝ์ฐ TO ํค์๋๋ก ๋ฐ์ดํฐ๋ฅผ ๋ณต์ฌํ ํ์ผ์ ์ง์ ํ๊ณ , CSV ํค์๋๋ ํ์ผ ํ์์ ์ผํ๋ก ๊ตฌ๋ถ๋ ๊ฐ์ผ๋ก ์ง์ ํ๋ฉฐ, HEADER ํค์๋๋ ํ์ผ์ ์ฒซ ๋ฒ์งธ ํ์ ์ด ํค๋๋ฅผ ํฌํจํ ๊ฒ์ ์ง์ ํ๋ค. ๋ง์ง๋ง์ผ๋ก ENCODING ํค์๋๋ ์ถ๋ ฅ ํ์ผ์ ๋ฌธ์ ์ธ์ฝ๋ฉ์ UTF-8๋ก ์ง์ ํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
S-095: 093์์ ์์ฑํ ์นดํ ๊ณ ๋ฆฌ๋ช ์ํ ๋ฐ์ดํฐ๋ฅผ ์๋ ์ฌ์์ผ๋ก ํ์ผ ์ถ๋ ฅํ๋ผ.
| ํ์ผ ํ์| ํค๋ ์ ๋ฌด| ๋ฌธ์ ์ธ์ฝ๋ฉ||:โ:|:โ:|:โ:|CSV(์ผํ๋ก ๊ตฌ๋ถ) | ์์ | UTF-8|ยPostgreSQL์์๋ Shift_JIS๋ฅผ ์ง์ ํ๋ฉด CP932์ ํด๋นํ๋ค. ํ์ผ ์ถ๋ ฅ ๊ฒฝ๋ก๋ฅผ ์๋์ ๊ฐ์ด ์ง์ ํ๋ค(COPY ๋ช ๋ น์ ๊ถํ์ ๋ถ์ฌ๋จ).
ย
| ์ถ๋ ฅ์ฒ||:โ:||/tmp/data|ยโปโ/tmp/dataโ๋ฅผ ์ง์ ํ๋ฉด Jupyter์ โ/work/dataโ์ ๊ณต์ ํ๋๋ก ๋์ด ์๋ค.
%%sql
COPY product_full TO '/tmp/data/S_product_full_SJIS_header.csv'
WITH CSV HEADER ENCODING 'SJIS';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ PostgreSQL์ COPY ๋ช ๋ น์ ์ฌ์ฉํ์ฌ product_full ํ ์ด๋ธ์ ๋ด์ฉ์ S_product_full_SJIS_header.csv๋ผ๋ ์ด๋ฆ์ CSV ํ์ผ๋ก ๋ด๋ณด๋ด๋ ์ฝ๋์ ๋๋ค. ์ ์ด ์ด๋ฆ์ ํฌํจํ ํค๋ ํ์ ํฌํจํ๋๋ก ์ง์ ํ๊ณ , ENCODING 'SJIS' ์ต์ ์ ์ถ๋ ฅ ํ์ผ์ด Shift-JIS ๋ฌธ์ ์ธ์ฝ๋ฉ์ ์ฌ์ฉํ์ฌ ์ธ์ฝ๋ฉ๋๋๋ก ์ง์ ํฉ๋๋ค.
Shift-JIS๋ ์ผ๋ณธ์์ ์ผ๋ฐ์ ์ผ๋ก ์ฌ์ฉ๋๋ ๋ฌธ์ ์ฝ๋์ด๋ฉฐ, CSV ํ์ผ์ ์ฌ์ฉํ ์ ์๋ ๋ช ๊ฐ์ง ๋ฌธ์ ์ฝ๋ ์ค ํ๋์ด๋ฉฐ, Shift-JIS ์ธ์ฝ๋ฉ์ CSV ํ์ผ๋ก ๋ฐ์ดํฐ๋ฅผ ๋ด๋ณด๋ด๋ฉด ์ด ํน์ ์ธ์ฝ๋ฉ์ด ํ์ํ ๋ค๋ฅธ ์์ฉ ํ๋ก๊ทธ๋จ์ด๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ํ์ผ์ ์ฝ๊ฒ ๊ฐ์ ธ์ฌ ์ ์์ต๋๋ค.
S-096: 093์์ ์์ฑํ ์นดํ ๊ณ ๋ฆฌ๋ช ์ํ ๋ฐ์ดํฐ๋ฅผ ์๋ ์ฌ์์ผ๋ก ํ์ผ ์ถ๋ ฅํ๋ผ.
| ํ์ผ ํ์| ํค๋ ์ ๋ฌด| ๋ฌธ์ ์ธ์ฝ๋ฉ||:โ:|:โ:|:โ:|CSV(์ผํ๋ก ๊ตฌ๋ถ) | ์์ | UTF-8|ยํ์ผ ์ถ๋ ฅ ๊ฒฝ๋ก๋ฅผ ์๋์ ๊ฐ์ด ์ค์ ํ๋ค(COPY ๋ช ๋ น์ด์ ๋ํ ๊ถํ์ ์ด๋ฏธ ๋ถ์ฌ๋์ด ์๋ค).ย| ์ถ๋ ฅ์ฒ||:โ:||/tmp/data|ยโปโ/tmp/dataโ๋ฅผ ์ง์ ํ๋ฉด Jupyter์ โ/work/dataโ์ ๊ณต์ ํ๋๋ก ๋์ด ์๋ค.
ย
%%sql
COPY product_full TO '/tmp/data/S_product_full_UTF-8_noh.csv'
WITH CSV ENCODING 'UTF-8';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ product_full ํ ์ด๋ธ์ ๋ด์ฉ์ ์ถ๋ ฅ ํ์ผ์ ํค๋ ํ์ ํฌํจํ์ง ์๊ณ /tmp/data/ ๋๋ ํ ๋ฆฌ์ ์๋ S_product_full_UTF-8_no.csv๋ผ๋ CSV ํ์ผ๋ก ๋ด๋ณด๋ด๊ณ ์๋ค.
Postgres์์๋ COPY ๋ช ๋ น์ ์ฌ์ฉํ์ฌ /ํ์ผ์์ /ํ์ผ๋ก ๋ฐ์ดํฐ๋ฅผ ๋ณต์ฌํฉ๋๋ค. WITH CSV ์ต์ ์ ํ์ผ์ด ์ผํ๋ก ๊ตฌ๋ถ๋ ๊ฐ(CSV) ํ์์์ ์ง์ ํ๊ณ , ENCODING ์ต์ ์ ํ์ผ์ ๋ฌธ์ ์ธ์ฝ๋ฉ์ ์ง์ ํ๊ณ , UTF-8์ ๋ค์ํ ์ธ์ด์ ๋ฌธ์๋ฅผ ์ง์ํ๋ UTF-8์ ์ง์ ํ๋ค. ์ ๋ฌธ์๋ฅผ ๊ด๋ฒ์ํ๊ฒ ์ง์ํ๋ UTF-8 ์ธ์ฝ๋ฉ์ ์ฌ์ฉํ๋ ํ์ผ์์ ๋ํ๋ธ๋ค.
HEADER ์ต์ ์ด ์ง์ ๋์ง ์์๊ธฐ ๋๋ฌธ์ ์ถ๋ ฅ ํ์ผ์๋ ํค๋ ๋ผ์ธ์ด ํฌํจ๋์ง ์๋๋ค. ๋ฐ๋ผ์ ์ถ๋ ฅ ํ์ผ์๋ product_full ํ ์ด๋ธ์ ๋ฐ์ดํฐ ํ๋ง CSV ํ์์ผ๋ก ํฌํจ๋๋ค.
S-097: 094์์ ์์ฑํ ์๋ ํ์์ ํ์ผ์ ๋ถ๋ฌ์ ๋ฐ์ดํฐ 3๊ฑด์ ํ์ํ์ฌ ์ ๋๋ก ์ ๋ ฅ๋์๋์ง ํ์ธํ๋ค.
| ํ์ผ ํ์| ํค๋ ์ ๋ฌด| ๋ฌธ์ ์ธ์ฝ๋ฉ||:โ:|:โ:|:โ:|CSV(์ผํ๋ก ๊ตฌ๋ถ) | ์์ | UTF-8|ย
ย
%%sql
DROP TABLE IF EXISTS product_full;
CREATE TABLE product_full (
product_cd VARCHAR(10),
category_major_cd VARCHAR(2),
category_major_name VARCHAR(20),
category_medium_cd VARCHAR(4),
category_medium_name VARCHAR(20),
category_small_cd VARCHAR(6),
category_small_name VARCHAR(20),
unit_price INTEGER,
unit_cost INTEGER
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. Done.
[]
์ค๋ช :
์ด SQL ์ฝ๋๋ product_full ํ ์ด๋ธ์ด ์กด์ฌํ๋ ๊ฒฝ์ฐ ์ด๋ฅผ ์ญ์ ํ๊ณ ๊ฐ์ ์ด๋ฆ์ ์ ํ ์ด๋ธ์ ์์ฑํ๋ค. ์ ํ ์ด๋ธ์๋ product_cd, category_major_cd, category_major_name, category_medium_cd, category_medium_name, category_small_cd, category_small_name, category_small_name, category_small_name, it_price, it_cost์ small_name, it_price, it_cost์ 8๊ฐ์ ์ด์ด ์๋ค. ์ฒ์ 7๊ฐ์ ์ด์ ๊ธธ์ด๊ฐ ๋ค๋ฅธ VARCHAR ํ์ ์ด๊ณ , ๋ง์ง๋ง 2๊ฐ์ ์ด์ INTEGER ํ์ ์ด๋ค.
์ปฌ๋ผ์ ๋ฐ์ดํฐ ํ์ ๊ณผ ๊ธธ์ด๋ฅผ ์ง์ ํจ์ผ๋ก์จ ์ฝ๋๋ product_full ํ ์ด๋ธ์ ์คํค๋ง๋ฅผ ์ ์ํ๋ ๊ฒ์ด๋ค. ์ด๋ ๊ฒ ํ๋ฉด ๊ฐ ์ปฌ๋ผ์ด ํน์ ๋ฐ์ดํฐ ์ ํ๊ณผ ๊ธธ์ด๋ฅผ ๊ฐ์ง๊ฒ ๋์ด ์ด ํ ์ด๋ธ์ ๋ํ ์ฟผ๋ฆฌ ์ฑ๋ฅ์ด ํฅ์๋๊ณ , ์๋ชป๋ ์ ํ๊ณผ ๊ธธ์ด์ ๋ฐ์ดํฐ๊ฐ ์ฝ์ ๋์์ ๋ ๋ฐ์ํ ์ ์๋ ์ค๋ฅ๋ฅผ ๋ฐฉ์งํ ์ ์๋ค.
%%sql
COPY product_full FROM '/tmp/data/S_product_full_UTF-8_header.csv'
WITH CSV HEADER ENCODING 'UTF-8';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ '/tmp/data/S_product_full_UTF-8_header.csv'์ ์๋ CSV ํ์ผ์์ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ด๋ฏธ ์์ฑ๋ 'product_full'์ด๋ผ๋ ์๋ก์ด ํ ์ด๋ธ๋ก ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค.
PostgreSQL์ COPY ๋ช ๋ น์ ํ์ผ์์ ํ ์ด๋ธ๋ก ๋๋ ๊ทธ ๋ฐ๋๋ก ๋ฐ์ดํฐ๋ฅผ ๋ณต์ฌํ ์ ์๋๋ฐ, FROM ํค์๋๋ CSV ํ์ผ์ ๊ฒฝ๋ก๋ฅผ ์ง์ ํ๊ณ , WITH ํค์๋๋ COPY ๋ช ๋ น์ ์ต์ ์ ์ง์ ํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด ๊ฒฝ์ฐ CSV๋ ํ์ผ์ด ์ผํ๋ก ๊ตฌ๋ถ๋ ๊ฐ(Comma-Separated Value) ํ์์์, HEADER๋ ํ์ผ์ ์ฒซ ๋ฒ์งธ ํ์ด ์ด ์ด๋ฆ์ ํฌํจํ๊ณ ์์์, ENCODING 'UTF-8'์ ํ์ผ์์ ์ฌ์ฉ๋๋ ๋ฌธ์ ์ธ์ฝ๋ฉ์ ์ง์ ํ๋ ๊ฒ์ ๊ฐ๊ฐ ์ง์ ํ๋ค.
๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ด๋ฏธ 'product_full' ํ ์ด๋ธ์ด ์์ฑ๋์ด ์์ผ๋ฏ๋ก, COPY ๋ช ๋ น์ CSV ํ์ผ์ ๋ฐ์ดํฐ๋ฅผ ๊ธฐ์กด ํ ์ด๋ธ์ ์ฝ์ ํ๋ค.
%%sql
SELECT * FROM product_full LIMIT 3;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
product_cd | category_major_cd | category_major_name | category_medium_cd | category_medium_name | category_small_cd | category_small_name | unit_price | unit_cost |
---|---|---|---|---|---|---|---|---|
P040101001 | 04 | ๆฃ่ | 0401 | ๅพก้ฃฏ้ก | 040101 | ๅผๅฝ้ก | 198 | 149 |
P040101002 | 04 | ๆฃ่ | 0401 | ๅพก้ฃฏ้ก | 040101 | ๅผๅฝ้ก | 218 | 164 |
P040101003 | 04 | ๆฃ่ | 0401 | ๅพก้ฃฏ้ก | 040101 | ๅผๅฝ้ก | 230 | 173 |
์ค๋ช :
์ด SQL ์ฝ๋๋ product_full ํ ์ด๋ธ์์ ์ฒ์ ์ธ ์ค์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๋ ๊ฐ๋จํ SELECT ๋ฌธ์ด๋ค. ๋ฐ์ดํฐ๋ ์ด์ ์ CSV ํ์ผ์์ COPY ๋ช ๋ น์ ์ฌ์ฉํ์ฌ ํ ์ด๋ธ์ ๋ก๋๋์์ต๋๋ค. ์ด ์ฟผ๋ฆฌ๋ LIMIT ์ ์ ์ฌ์ฉํ์ฌ ๋ฐํ๋๋ ํ ์๋ฅผ 3ํ์ผ๋ก ์ ํํ๊ณ ์๋ค.
SELECT ๋ฌธ์ product_full ํ ์ด๋ธ์์ ๋ชจ๋ ์ด์ ๊ฐ์ ธ์ต๋๋ค. ์ด ํ ์ด๋ธ์๋ ์ฝ๋, ์นดํ ๊ณ ๋ฆฌ, ๊ฐ๊ฒฉ ๋ฑ ์ํ์ ๋ํ ์ ๋ณด๊ฐ ํฌํจ๋์ด ์๋ค. ๋ฐ์ดํฐ๋ product_cd, category_major_cd, category_major_name, category_medium_cd, category_medium_name, category_small_cd, category_small_cd, category_small_name, it_price name, it_price, it_cost ๋ฑ์ ์ด๋ก ์ ๋ฆฌํ๊ณ ์๋ค.
S-098: 096์์ ์์ฑํ ์๋ ํ์์ ํ์ผ์ ๋ถ๋ฌ์์ ๋ฐ์ดํฐ 3๊ฑด์ ํ์ํ์ฌ ์ ๋๋ก ์ ๋ ฅ๋์๋์ง ํ์ธํ๋ค.
| ํ์ผ ํ์| ํค๋ ์ ๋ฌด| ๋ฌธ์ ์ธ์ฝ๋ฉ||:โ:|:โ:|:โ:|CSV(์ผํ๋ก ๊ตฌ๋ถ) | ์์ | UTF-8|
%%sql
DROP TABLE IF EXISTS product_full;
CREATE TABLE product_full (
product_cd VARCHAR(10),
category_major_cd VARCHAR(2),
category_major_name VARCHAR(20),
category_medium_cd VARCHAR(4),
category_medium_name VARCHAR(20),
category_small_cd VARCHAR(6),
category_small_name VARCHAR(20),
unit_price INTEGER,
unit_cost INTEGER
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. Done.
[]
์ค๋ช :
์ด ์ฝ๋๋ product_full์ด๋ผ๋ ์ด๋ฆ์ ํ ์ด๋ธ์ด ์กด์ฌํ๋ ๊ฒฝ์ฐ ์ด๋ฅผ ์ญ์ ํ๊ณ ๋์ผํ ์ด๋ฆ๊ณผ ์คํค๋ง๋ฅผ ๊ฐ์ง ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๋ค. ์ ํ ์ด๋ธ์๋ 9๊ฐ์ ์ปฌ๋ผ์ด ์์ต๋๋ค.
product_cd ์ปฌ๋ผ์ VARCHAR(10) ํ์ ์ด๋ค.
category_major_cd ์ปฌ๋ผ์ VARCHAR(2) ํ์ ์ด๋ค.
category_major_name ์ปฌ๋ผ์ VARCHAR(20) ํ์ ์ด๋ค.
category_medium_cd ์นผ๋ผ์ VARCHAR(4) ํ์ ์ด๋ค.
category_medium_name ์นผ๋ผ์ VARCHAR(20) ํ์ ์ด๋ค.
category_small_cd ์นผ๋ผ์ VARCHAR(6) ํ์ ์ด๋ค.
category_small_name ์นผ๋ผ์ VARCHAR(20) ํ์ ์ด๋ค.
unit_price ์ปฌ๋ผ์ INTEGER ํ์ ์ด๋ค.
unit_cost ์ปฌ๋ผ์ INTEGER ํ์ ์ด๋ค.
VARCHAR ๋ฐ์ดํฐ ํ์ ์ ์ง์ ๋ ์ต๋ ๊ธธ์ด์ ๊ฐ๋ณ ๊ธธ์ด ๋ฌธ์์ด์ ์ ์ฅํ๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, INTEGER ๋ฐ์ดํฐ ํ์ ์ ์ ์๋ฅผ ์ ์ฅํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
์ด ์ฝ๋๋ ์ง์ ๋ ์คํค๋ง๋ก ๋น ํ ์ด๋ธ์ ์์ฑํ๊ณ ๋ฐ์ดํฐ๋ฅผ ํ ์ด๋ธ์ ์ถ๊ฐํ์ง ์๋๋ค.
ย
%%sql
COPY product_full FROM '/tmp/data/S_product_full_UTF-8_noh.csv'
WITH CSV ENCODING 'UTF-8';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ PostgreSQL์ COPY ๋ช ๋ น์ ์ฌ์ฉํ์ฌ 'S_product_full_UTF-8_no.csv'๋ผ๋ CSV ํ์ผ์์ 'product_full'์ด๋ผ๋ ํ ์ด๋ธ๋ก ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค.
WITH CSV ์ต์ ์ ๋ฐ์ดํฐ๊ฐ CSV ํ์์์ ์ง์ ํ๊ณ , ENCODING ์ต์ ์ ํ์ผ์ ๋ฌธ์ ์ธ์ฝ๋ฉ(์ด ๊ฒฝ์ฐ UTF-8)์ ์ง์ ํฉ๋๋ค.
ํ์ผ์๋ ํค๋ ํ์ด ์์ผ๋ฏ๋ก 'product_full' ํ ์ด๋ธ์ ์์ฑํ๊ธฐ ์ ์ SQL ๋ฌธ์์ ์ด ์ด๋ฆ๊ณผ ์ ํ์ ๋ช ์์ ์ผ๋ก ์ง์ ํด์ผ ํ๋ค.
๋ฐ์ดํฐ๊ฐ ํ ์ด๋ธ๋ก ์ํฌํธ๋๋ฉด SELECT ๋ฌธ์ผ๋ก 'product_full' ํ ์ด๋ธ์ ์ฒซ ์ธ ํ์ ๊ฐ์ ธ์์ ๋ฐ์ดํฐ๊ฐ ์ ๋๋ก ์ํฌํธ๋์๋์ง ํ์ธํ๋ค.
%%sql
SELECT * FROM product_full LIMIT 3;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
product_cd | category_major_cd | category_major_name | category_medium_cd | category_medium_name | category_small_cd | category_small_name | unit_price | unit_cost |
---|---|---|---|---|---|---|---|---|
P040101001 | 04 | ๆฃ่ | 0401 | ๅพก้ฃฏ้ก | 040101 | ๅผๅฝ้ก | 198 | 149 |
P040101002 | 04 | ๆฃ่ | 0401 | ๅพก้ฃฏ้ก | 040101 | ๅผๅฝ้ก | 218 | 164 |
P040101003 | 04 | ๆฃ่ | 0401 | ๅพก้ฃฏ้ก | 040101 | ๅผๅฝ้ก | 230 | 173 |
์ค๋ช :
์ด ์ฝ๋๋ SQL์ SELECT ๋ฌธ์ ์ฌ์ฉํ์ฌ "product_full" ํ ์ด๋ธ์์ ์ฒ์ 3๊ฐ์ ํ์ ์ ํํ๋ฉฐ, SELECT ๋ฌธ์ "product_full" ํ ์ด๋ธ์์ ๋ชจ๋ ์ด(product_cd, category_major_cd, category_major_name, category_medium_cd, category_small_cd, category_small_name, unit_price, unit_cost)์ ์ถ์ถํ์ฌ LIMIT ํค์๋๋ก ์ถ๋ ฅ์ ์ถ๋ ฅํ๋ค. major_name, category_medium_cd, category_medium_name, category_small_cd, category_small_name, unit_price, unit_cost)๋ฅผ ๋ชจ๋ ๊ฐ์ ธ์ค๊ณ LIMIT ํค์๋๋ก ์ถ๋ ฅ์ ์ฒ์ ์ธ ์ค๋ก ์ ํํฉ๋๋ค. ๊ฒฐ๊ณผ๋ "product_full" ํ ์ด๋ธ์ ์ฒซ ์ธ ํ์ ํ์ํฉ๋๋ค.
S-099: 093์์ ์์ฑํ ์นดํ ๊ณ ๋ฆฌ๋ช ์ํ ๋ฐ์ดํฐ๋ฅผ ์๋ ์ฌ์์ผ๋ก ํ์ผ ์ถ๋ ฅํ๋ผ.
| ํ์ผ ํ์| ํค๋ ์ ๋ฌด| ๋ฌธ์ ์ธ์ฝ๋ฉ||:โ:|:โ:|:โ:|TSV(ํญ์ผ๋ก ๊ตฌ๋ถ)ย | ์์ | UTF-8|ยํ์ผ ์ถ๋ ฅ ๊ฒฝ๋ก๋ฅผ ์๋์ ๊ฐ์ด ์ค์ ํ๋ค(COPY ๋ช ๋ น์ด์ ๋ํ ๊ถํ์ ์ด๋ฏธ ๋ถ์ฌ๋์ด ์๋ค).ย| ์ถ๋ ฅ์ฒ||:โ:||/tmp/data|ยโปโ/tmp/dataโ๋ฅผ ์ง์ ํ๋ฉด Jupyter์ โ/work/dataโ์ ๊ณต์ ํ๋๋ก ๋์ด ์๋ค.
%%sql
COPY product_full TO '/tmp/data/S_product_full_UTF-8_header.tsv'
WITH CSV HEADER DELIMITER E'\t' ENCODING 'UTF-8';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
[]
์ค๋ช :
์ ํ product_full ํ ์ด๋ธ์ ๋ด์ฉ์ ์ผํ ๋์ ํญ(หถ~~~~)์ ๊ตฌ๋ถ ๊ธฐํธ๋ก ์ฌ์ฉํ์ฌ CSV ํ์์ผ๋ก ๋ด๋ณด๋ด๊ธฐ ํฉ๋๋ค. ๋ด๋ณด๋ธ ํ์ผ์ /tmp/data ๋๋ ํ ๋ฆฌ์ ์ ์ฅ๋๋ฉฐ, S_product_full_UTF-8_header.tsv๋ผ๋ ์ด๋ฆ์ผ๋ก ์ ์ฅ๋๋ฉฐ, HEADER ์ต์ ์ด ํฌํจ๋์ด ์๊ธฐ ๋๋ฌธ์ ํ์ผ์ ์ฒซ ๋ฒ์งธ ์ค์ ์ปฌ๋ผ ํค๋๊ฐ ํฌํจ๋์ด ์์ต๋๋ค.
๋ค์์ ์ฝ๋์ ๊ฐ๋ณ ์ปดํฌ๋ํธ ๋ถ์์ ๋๋ค.
COPY product_full: product_full ํ ์ด๋ธ์ ๋ฐ์ดํฐ๋ฅผ ํ์ผ๋ก ๋ณต์ฌํ ๊ฒ์ ์ง์ ํฉ๋๋ค.
TO '/tmp/data/S_product_full_UTF-8_header.tsv': ๋ด๋ณด๋ผ ํ์ผ์ ๊ฒฝ๋ก์ ์ด๋ฆ์ ์ง์ ํ๋ค.
WITH CSV: ๋ด๋ณด๋ผ ํ์ผ์ด CSV ํ์์์ ์ง์ ํฉ๋๋ค.
HEADER: ํ์ผ์ ์ฒซ ๋ฒ์งธ ์ค์ ์ปฌ๋ผ ํค๋๋ฅผ ํฌํจํ๋๋ก ์ง์ ํฉ๋๋ค.
DELIMITER E' \t': ํญ ๋ฌธ์๋ฅผ ๊ตฌ๋ถ ๊ธฐํธ๋ก ์ฌ์ฉํ๋๋ก ์ง์ ํ๋ค.
ENCODING 'UTF-8': ํ์ผ์ ๋ด๋ณด๋ผ ๋ ์ฌ์ฉํ ๋ฌธ์ ์ธ์ฝ๋ฉ์ ์ง์ ํฉ๋๋ค.
ย
S-100: 099์์ ์์ฑํ ์๋ ํ์์ ํ์ผ์ ๋ถ๋ฌ์์ ๋ฐ์ดํฐ 3๊ฑด์ ํ์ํ์ฌ ์ ๋๋ก ์ ๋ ฅ๋์๋์ง ํ์ธํ๋ค.
| ํ์ผ ํ์| ํค๋ ์ ๋ฌด| ๋ฌธ์ ์ธ์ฝ๋ฉ||:โ:|:โ:|:โ:|TSV(ํญ์ผ๋ก ๊ตฌ๋ถ) | ์์ | UTF-8|ย
%%sql
DROP TABLE IF EXISTS product_full;
CREATE TABLE product_full (
product_cd VARCHAR(10),
category_major_cd VARCHAR(2),
category_major_name VARCHAR(20),
category_medium_cd VARCHAR(4),
category_medium_name VARCHAR(20),
category_small_cd VARCHAR(6),
category_small_name VARCHAR(20),
unit_price INTEGER,
unit_cost INTEGER
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. Done.
[]
์ค๋ช :
์ด SQL ์ฝ๋ ๋ธ๋ก์ ๋จผ์ product_full์ด๋ผ๋ ํ ์ด๋ธ์ด ์กด์ฌํ ๊ฒฝ์ฐ ์ด๋ฅผ ์ญ์ ํ๊ณ ๋์ผํ ์ด๋ฆ๊ณผ ์คํค๋ง๋ฅผ ๊ฐ์ง ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๋ค.
product_full ํ ์ด๋ธ์ ์คํค๋ง์๋ ๋ค์๊ณผ ๊ฐ์ ๋ฐ์ดํฐ ํ์ ์ ๊ฐ์ง 9๊ฐ์ ์ปฌ๋ผ์ด ์๋ค.
product_cd: VARCHAR(10) (์ฆ, ์ต๋ ๊ธธ์ด 10๊น์ง ๊ฐ๋ณ ๊ธธ์ด์ ๋ฌธ์์ด)
category_major_cd: VARCHAR(2)
category_major_name: VARCHAR(20)
category_medium_cd.VARCHAR(4)
category_medium_name: VARCHAR(20)
category_small_cd์ ๋๋ค. VARCHAR(6)
category_small_name: VARCHAR(20)
unit_price์ ๋๋ค. INTEGER
unit_cost: INTEGER
์ด ์ฝ๋ ๋ธ๋ก์ ๋ชฉ์ ์ ๋ฐ์ดํฐ๋ฅผ ๋ถ๋ฌ์ค๊ธฐ ์ ์ product_full ํ ์ด๋ธ์ ๊ตฌ์กฐ๋ฅผ ์ ์ํ๋ ๊ฒ์ด๋ค.
%%sql
COPY product_full FROM '/tmp/data/S_product_full_UTF-8_header.tsv'
WITH CSV HEADER DELIMITER E'\t' ENCODING 'UTF-8';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
[]
์ค๋ช :
์ด ์ฝ๋๋ '/tmp/data/S_product_full_UTF-8_header.tsv'์ ์๋ TSV(ํญ์ผ๋ก ๊ตฌ๋ถ๋ ๊ฐ) ํ์ผ์์ ๋ฐ์ดํฐ๋ฅผ ๋ณต์ฌํ์ฌ ํ์ฌ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ 'product_full'์ ์ฝ์ ํ๋ค.
COPY ๋ช ๋ น์ ์ ๋ ฅ ํ์ผ์ด CSV ํ์ผ์ด๊ณ , ํค๋๊ฐ ํ์ผ์ ์ฒซ ๋ฒ์งธ ์ค์ ํฌํจ๋์ด ์์ผ๋ฉฐ, ์ฌ์ฉ๋๋ ๊ตฌ๋ถ ๊ธฐํธ๊ฐ ํญ ๋ฌธ์('หถ')์์ ์ง์ ํ๊ณ , ENCODING ๋งค๊ฐ ๋ณ์๋ ํ์ผ์ด UTF-8๋ก ์ธ์ฝ๋ฉ๋์์์ ์ง์ ํ๋ค.
์ด ๋ช ๋ น์ 'product_full' ํ ์ด๋ธ์ด ์ด๋ฏธ ์กด์ฌํ๊ณ , ์ ๋ ฅ ํ์ผ์ ๋ฐ์ดํฐ(product_cd, category_major_cd, category_major_name, category_medium_cd, category_medium_cd, category_medium_ name, category_small_cd, category_small_name, unit_price, unit_cost)์ ๋์ผํ ๊ตฌ์กฐ๋ฅผ ๊ฐ์ง๊ณ ์๋ค๊ณ ๊ฐ์ ํ๋ค. ์ด ๋ช ๋ น์ ํ์ผ์ ๋ฐ์ดํฐ๋ฅผ ํ ์ด๋ธ์ ์ฝ์ ํ๊ณ ํ์ผ ๋ฐ์ดํฐ์ ๊ฐ ํ์ ๋ํด ํ ์ด๋ธ์ ์ ํ์ ์์ฑํ๋ค.
%%sql
SELECT * FROM product_full LIMIT 3;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
product_cd | category_major_cd | category_major_name | category_medium_cd | category_medium_name | category_small_cd | category_small_name | unit_price | unit_cost |
---|---|---|---|---|---|---|---|---|
P040101001 | 04 | ๆฃ่ | 0401 | ๅพก้ฃฏ้ก | 040101 | ๅผๅฝ้ก | 198 | 149 |
P040101002 | 04 | ๆฃ่ | 0401 | ๅพก้ฃฏ้ก | 040101 | ๅผๅฝ้ก | 218 | 164 |
P040101003 | 04 | ๆฃ่ | 0401 | ๅพก้ฃฏ้ก | 040101 | ๅผๅฝ้ก | 230 | 173 |
์ค๋ช :
์ด ์ฝ๋๋ product_full ํ ์ด๋ธ์์ ์ฒ์ 3๊ฐ ํ์ ์ ํํ๋ SQL ์ฟผ๋ฆฌ๋ฅผ ์คํํ๋ฉฐ, SELECT ๋ฌธ ๋ค์ *๋ฅผ ๋ถ์๋๋ฐ, ์ด๋ ํ ์ด๋ธ์ ๋ชจ๋ ์ด์ ์ ํํ๋ค๋ ์๋ฏธ์ด๋ฉฐ, LIMIT 3์ ๊ฒฐ๊ณผ ์งํฉ์ ํ ์ด๋ธ์ ์ฒ์ 3๊ฐ ํ์ผ๋ก ์ ํํ๋ค.
๊ฒฐ๊ณผ๋ ํ ์ด๋ธ์ ์ง์ ๋ ์ด์ ๋ฐ๋ผ product_full ํ ์ด๋ธ์ ๋ฐ์ดํฐ๋ฅผ ํ์ํ๋ค. ์ ํํ ์ถ๋ ฅ์ ํ ์ด๋ธ์ ์ ์ฅ๋ ๋ฐ์ดํฐ์ ๋ฐ๋ผ ๋ฌ๋ผ์ง๋๋ค.
Comment