๋ฐ์ดํฐ ์ฌ์ด์ธ์ค 100๋ฒ์ ๋ ธํฌ(๊ตฌ์กฐํ ๋ฐ์ดํฐ ์ฒ๋ฆฌํธ)- SQL Part 4 (Q61 to Q80)์ ํด์ค์ ๋๋ค.
ย
์ฐธ๊ณ (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-061: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ ๊ธ์ก(amount)์ ๊ณ ๊ฐ ID(customer_id)๋ณ๋ก ํฉ์ฐํ๊ณ , ๋งค์ถ ๊ธ์ก ํฉ๊ณ๋ฅผ ์์ ๋์ํ(ํ๋จ 10)ํ์ฌ ๊ณ ๊ฐ ID, ๋งค์ถ ๊ธ์ก ํฉ๊ณ์ ํจ๊ป 10๊ฑด์ฉ ํ์ํ๋ค. ๋จ, ๊ณ ๊ฐ ID๊ฐ โZโ๋ก ์์ํ๋ ๊ฒ์ ๋นํ์์ ์๋ฏธํ๋ฏ๋ก ์ ์ธํ์ฌ ๊ณ์ฐํ๋ค.
%%sql
SELECT
customer_id,
sum_amount,
LOG(sum_amount + 0.5) AS log_amount
FROM
(
SELECT
customer_id,
SUM(amount) AS sum_amount
FROM
receipt
WHERE
customer_id NOT LIKE 'Z%'
GROUP BY
customer_id
) AS sum_amount_tbl
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sum_amount | log_amount |
---|---|---|
CS001311000059 | 2302 | 3.3621996388688865 |
CS004614000122 | 248 | 2.3953263930693509 |
CS003512000043 | 298 | 2.4749443354653879 |
CS011615000061 | 246 | 2.3918169236132488 |
CS029212000033 | 3604 | 3.5568450298595813 |
CS007515000119 | 7157 | 3.8547613566936362 |
CS034515000123 | 3699 | 3.5681430316577019 |
CS004315000058 | 490 | 2.6906390117159673 |
CS026414000014 | 6671 | 3.8242234903608168 |
CS001615000099 | 768 | 2.8856438718357639 |
์ค๋ช :
๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๋ SQL ์ฟผ๋ฆฌ์ ๋๋ค. ๋จ๊ณ๋ณ๋ก ๋ถํดํด ๋ณด์.
์ฟผ๋ฆฌ๋ %%sql magic ๋ช ๋ น์ผ๋ก ์์ํ๋๋ฐ, ์ด๋ Jupyter ๋ ธํธ๋ถ์์ ๋ค์ ์ฝ๋๊ฐ SQL ์ฝ๋์์ ์ง์ ํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค.
๋ฉ์ธ ์ฟผ๋ฆฌ์์๋ customer_id, sum_amount, log_amount๋ผ๋ ์ธ ๊ฐ์ ์ปฌ๋ผ์ ์ ํํ๋ค. ์ด ์ปฌ๋ผ๋ค์ ๊ดํธ ์์ ํ์ ์ฟผ๋ฆฌ์์ ํ์๋ฉ๋๋ค.
๊ดํธ ์์ ํ์ ์ฟผ๋ฆฌ์์๋ ๋จผ์ customer_id๊ฐ "Z"๋ก ์์ํ๋ ๋ชจ๋ ๋ ์ฝ๋๋ฅผ ํํฐ๋งํ์ฌ ์ ์ธํ๋ค. ์ด๋ NOT LIKE 'Z%'๋ผ๋ ์กฐ๊ฑด์ ์ํด ์ํ๋ฉ๋๋ค.
ํํฐ๋ง๋ ๋ ์ฝ๋๋ GROUP BY ์ ์ ์ฌ์ฉํ์ฌ customer_id๋ก ๊ทธ๋ฃนํ๋ฉ๋๋ค.
SUM ํจ์๋ ๊ฐ ๊ณ ๊ฐ ID์ ์ด ๊ธ์ก์ ๊ณ์ฐํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
AS sum_amount ์ ์ ๊ณ์ฐ๋ ์ปฌ๋ผ์ ์ด๋ฆ์ sum_amount๋ก ๋ณ๊ฒฝํ๋ค.
ํ์ ์ฟผ๋ฆฌ๋ sum_amount_tbl๋ก ๋ณ์นญ์ด ์ง์ ๋๋ค.
๋ง์ง๋ง์ผ๋ก ์ธ๋ถ ์ฟผ๋ฆฌ๋ customer_id, sum_amount, sum_amount์ 0.5๋ฅผ ๋ํ ๋์(base e)๋ฅผ ์ ํํ๋ค. ์ด๊ฒ์ LOG ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ณ์ฐ๋๋ฉฐ, log_amount๋ผ๋ ๋ณ์นญ์ด ๋ถ๋๋ค.
๊ฒฐ๊ณผ๋ LIMIT ๊ตฌ๋ฌธ์ผ๋ก 10ํ์ผ๋ก ์ ํ๋ฉ๋๋ค.
S-062: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ๊ธ์ก(amount)์ ๊ณ ๊ฐID(customer_id)๋ณ๋ก ํฉ์ฐํ๊ณ , ๋งค์ถ๊ธ์ก ํฉ๊ณ๋ฅผ ์์ฐ๋์ํ(ํ๋จ e)ํ์ฌ ๊ณ ๊ฐID, ๋งค์ถ๊ธ์ก ํฉ๊ณ์ ํจ๊ป 10๊ฑด์ฉ ํ์ํ๋ค. ๋จ, ๊ณ ๊ฐ ID๊ฐ โZโ๋ก ์์ํ๋ ๊ฒ์ ๋นํ์์ ์๋ฏธํ๋ฏ๋ก ์ ์ธํ์ฌ ๊ณ์ฐํ๋ค.
%%sql
SELECT
customer_id,
sum_amount,
LN(sum_amount + 0.5) AS log_amount
FROM
(
SELECT
customer_id,
SUM(amount) AS sum_amount
FROM
receipt
WHERE
customer_id NOT LIKE 'Z%'
GROUP BY
customer_id
) AS sum_amount_tbl
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sum_amount | log_amount |
---|---|---|
CS001311000059 | 2302 | 7.7417507681294619 |
CS004614000122 | 248 | 5.5154428455366834 |
CS003512000043 | 298 | 5.6987699328326568 |
CS011615000061 | 246 | 5.5073619934827448 |
CS029212000033 | 3604 | 8.1899383438446333 |
CS007515000119 | 7157 | 8.8759160369722701 |
CS034515000123 | 3699 | 8.2159529543656056 |
CS004315000058 | 490 | 6.1954252790054178 |
CS026414000014 | 6671 | 8.8056000011824754 |
CS001615000099 | 768 | 6.6444405629786506 |
์ค๋ช :
๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๋ SQL ์ฟผ๋ฆฌ์ ๋๋ค. ๋จ๊ณ๋ณ๋ก ๋ถํดํด ๋ณด์.
์ฟผ๋ฆฌ๋ %%sql magic ๋ช ๋ น์ผ๋ก ์์ํ๋๋ฐ, ์ด๋ Jupyter ๋ ธํธ๋ถ์์ ๋ค์ ์ฝ๋๊ฐ SQL ์ฝ๋์์ ์ง์ ํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค.
๋ฉ์ธ ์ฟผ๋ฆฌ์์๋ customer_id, sum_amount, log_amount๋ผ๋ ์ธ ๊ฐ์ ์ปฌ๋ผ์ ์ ํํ๋ค. ์ด ์ปฌ๋ผ๋ค์ ๊ดํธ ์์ ํ์ ์ฟผ๋ฆฌ์์ ํ์๋ฉ๋๋ค.
๊ดํธ ์์ ํ์ ์ฟผ๋ฆฌ์์๋ ๋จผ์ customer_id๊ฐ "Z"๋ก ์์ํ๋ ๋ชจ๋ ๋ ์ฝ๋๋ฅผ ํํฐ๋งํ์ฌ ์ ์ธํ๋ค. ์ด๋ NOT LIKE 'Z%'๋ผ๋ ์กฐ๊ฑด์ ์ํด ์ํ๋ฉ๋๋ค.
ํํฐ๋ง๋ ๋ ์ฝ๋๋ GROUP BY ์ ์ ์ฌ์ฉํ์ฌ customer_id๋ก ๊ทธ๋ฃนํ๋ฉ๋๋ค.
SUM ํจ์๋ ๊ฐ ๊ณ ๊ฐ ID์ ์ด ๊ธ์ก์ ๊ณ์ฐํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
AS sum_amount ์ ์ ๊ณ์ฐ๋ ์ปฌ๋ผ์ ์ด๋ฆ์ sum_amount๋ก ๋ณ๊ฒฝํ๋ค.
ํ์ ์ฟผ๋ฆฌ๋ sum_amount_tbl๋ก ๋ณ์นญ์ด ์ง์ ๋๋ค.
๋ง์ง๋ง์ผ๋ก ์ธ๋ถ ์ฟผ๋ฆฌ๋ customer_id, sum_amount, sum_amount์ ์์ฐ๋์(๋ฐ๋ฅ e)์ 0.5๋ฅผ ๋ํ ๊ฐ์ ์ ํํ๋ค. ์ด๊ฒ์ LN ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ณ์ฐ๋๋ฉฐ, log_amount๋ผ๋ ๋ณ์นญ์ด ๋ถ๋๋ค.
๊ฒฐ๊ณผ๋ LIMIT ๊ตฌ๋ฌธ์ผ๋ก 10์ค๋ก ์ ํ๋๋ค.
S-063: ์ํ ๋ฐ์ดํฐ(df_product)์ ๋จ๊ฐ(unit_price)์ ์๊ฐ(unit_cost)๋ก๋ถํฐ ๊ฐ ์ํ์ ์ด์ต์ก์ ์ฐ์ถํ๊ณ , ๊ทธ ๊ฒฐ๊ณผ๋ฅผ 10๊ฑด ํ์ํ์์ค.
%%sql
SELECT
product_cd,
unit_price,
unit_cost,
unit_price - unit_cost AS unit_profit
FROM
product
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
product_cd | unit_price | unit_cost | unit_profit |
---|---|---|---|
P040101001 | 198 | 149 | 49 |
P040101002 | 218 | 164 | 54 |
P040101003 | 230 | 173 | 57 |
P040101004 | 248 | 186 | 62 |
P040101005 | 268 | 201 | 67 |
P040101006 | 298 | 224 | 74 |
P040101007 | 338 | 254 | 84 |
P040101008 | 420 | 315 | 105 |
P040101009 | 498 | 374 | 124 |
P040101010 | 580 | 435 | 145 |
์ค๋ช :
๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๋ SQL ์ฟผ๋ฆฌ์ ๋๋ค. ๋จ๊ณ๋ณ๋ก ๋ถํดํด ๋ณด๊ฒ ์ต๋๋ค.
์ฟผ๋ฆฌ๋ %%sql ๋งค์ง ๋ช ๋ น์ผ๋ก ์์ํ๋ฉฐ, ์ด๋ Jupyter ๋ ธํธ๋ถ์์ ๋ค์ ์ฝ๋๊ฐ SQL ์ฝ๋์์ ์ง์ ํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค.
๋ฉ์ธ ์ฟผ๋ฆฌ์์๋ product_cd, unit_price, unit_cost, unit_profit์ 4๊ฐ ์ปฌ๋ผ์ ์ ํํ๋ค. ์ด ์ปฌ๋ผ๋ค์ product ํ ์ด๋ธ์์ ํ์๋๋ค.
FROM ์ ์ ์ ํ ํ ์ด๋ธ์ ์ง์ ํ๋ค.
LIMIT ์ ์ ์ฟผ๋ฆฌ๊ฐ ๋ฐํํ๋ ํ ์๋ฅผ 10์ผ๋ก ์ ํํ๋ค.
์ด ์ฟผ๋ฆฌ์์๋ ๋จ๊ฐ์์ ๋จ๊ฐ๋ฅผ ๋นผ์ ๋จ์ ์์ต์ ๊ณ์ฐํฉ๋๋ค. ์ด๋ - ์ฐ์ฐ์๋ฅผ ์ฌ์ฉํ์ฌ ์ํ๋ฉ๋๋ค.
์ฟผ๋ฆฌ ๊ฒฐ๊ณผ์๋ product ํ ์ด๋ธ์ ์ฒ์ 10๊ฐ์ ํ์ธ product_cd, unit_price, unit_cost, unit_profit์ด ํฌํจ๋๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฟผ๋ฆฌ๋ ์ ํํ ์ ํ์ ๊ฐ๊ฒฉ, ๋น์ฉ ๋ฐ ์ด์ต์ ๋ํ ์ ๋ณด๋ฅผ ์ป๋๋ค.
ย
S-064: ์ํ ๋ฐ์ดํฐ(df_product)์ ๋จ๊ฐ(unit_price)์ ์๊ฐ(unit_cost)์์ ๊ฐ ์ํ์ ์ ์ฒด ํ๊ท ์์ต๋ฅ ์ ๊ณ์ฐํ์์ค. ๋จ, ๋จ๊ฐ์ ์๊ฐ์๋ ๊ฒฐ์์ด ๋ฐ์ํ๋ค๋ ์ ์ ์ ์ํ๋ผ.
%%sql
SELECT
AVG((unit_price * 1.0 - unit_cost) / unit_price) AS unit_profit_rate
FROM
product
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
unit_profit_rate |
---|
0.24911389885177001279 |
์ค๋ช :
์ด ์ฝ๋๋ SQL์ ์ฌ์ฉํ์ฌ 'product'๋ผ๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค ํ ์ด๋ธ์ ์์ 10๊ฐ ์ํ์ ํ๊ท ๋จ๊ฐ ์์ต๋ฅ ์ ์กฐํํ๋ ์ฝ๋์ ๋๋ค. ๋ค์์ ์ฝ๋์ ๊ฐ ๋ถ๋ถ์ ๋ํ ์ค๋ช ์ ๋๋ค.
"%%sql"์ Jupyter notebook์ ๋งค์ง ๋ช ๋ น์ด๋ก, ์๋ ์ฝ๋๋ฅผ SQL๋ก ํด์ํ๋๋ก ๋ ธํธ๋ถ์ ์ง์ํฉ๋๋ค.
"SELECT"๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค๋ ๊ฒ์ ๋ํ๋ด๋ SQL ํค์๋์ ๋๋ค.
"AVG"๋ ์ฃผ์ด์ง ๊ฐ ์งํฉ์ ํ๊ท ์ ๊ณ์ฐํ๋ SQL ํจ์์ ๋๋ค.
"((unit_price * 1.0 - unit_cost) / unit_price)"๋ ๊ฐ ์ํ์ ๋จ์๋น ์์ต๋ฅ ์ ๊ณ์ฐํ๋ ๊ณ์ฐ์์ ๋๋ค. ๋จ๊ฐ์์ ๋จ๊ฐ๋ฅผ ๋นผ๊ณ ๊ทธ ๊ฒฐ๊ณผ๋ฅผ ๋จ๊ฐ๋ก ๋๋๋ ๋ฐฉ์์ด๋ฉฐ, 1.0์ ๊ณฑ์ ์ ๋๋์ ์ด ์ ์๊ฐ ์๋ ๋ถ๋์์์ ๊ฐ์ ๋ฐํํ๋๋ก ํ๊ธฐ ์ํด 1.0์ ๊ณฑํ๋ ๊ฒ์ ๋๋ค.
"AS unit_profit_rate"๋ ๊ณ์ฐ๋ ๊ฐ์ "unit_profit_rate"๋ผ๋ ์ปฌ๋ผ์ ๋์ ํฉ๋๋ค.
"FROM product"๋ "product" ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ฌ ๊ฒ์ ์ง์ ํ๋ค.
"LIMIT 10"์ ๋ฐํ๋๋ ํ ์๋ฅผ 10ํ์ผ๋ก ์ ํํ๋ค. ์ฆ, ์ด๋ค ๊ธฐ์ค(์ฝ๋ ์ค๋ํซ์์๋ ์ง์ ๋์ง ์์)์ ๋ฐ๋ผ ์์ 10๊ฐ ์ ํ์ ๊ฒฐ๊ณผ๋ง ํ์ํ๋ค๋ ์๋ฏธ์ด๋ค.
ย
S-065: ์ํ ๋ฐ์ดํฐ(df_product)์ ๊ฐ ์ํ์ ๋ํด ์์ต๋ฅ ์ด 30%๊ฐ ๋๋ ์๋ก์ด ๋จ๊ฐ๋ฅผ ๊ตฌํ์์ค. ๋จ, 1์ ๋ฏธ๋ง์ ๋ฐ์ฌ๋ฆผํ๋ค. ๊ทธ๋ฆฌ๊ณ 10๊ฐ์ ๊ฒฐ๊ณผ๋ฅผ ํ์ํ๊ณ , ์์ต๋ฅ ์ด ๋๋ต 30% ์ ๋์ธ ๊ฒ์ ํ์ธํ๋ผ. ๋จ, ๋จ๊ฐ(unit_price)์ ์๊ฐ(unit_cost)์๋ ์ ์๊ฐ ๋ฐ์ํ๋ค๋ ์ ์ ์ ์ํ๋ผ.
%%sql
WITH new_price_tbl AS (
SELECT
product_cd,
unit_price,
unit_cost,
TRUNC(unit_cost / 0.7) AS new_price
FROM
product
)
SELECT
*,
(new_price - unit_cost) / new_price AS new_profit_rate
FROM
new_price_tbl
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
product_cd | unit_price | unit_cost | new_price | new_profit_rate |
---|---|---|---|---|
P040101001 | 198 | 149 | 212 | 0.29716981132075471698 |
P040101002 | 218 | 164 | 234 | 0.29914529914529914530 |
P040101003 | 230 | 173 | 247 | 0.29959514170040485830 |
P040101004 | 248 | 186 | 265 | 0.29811320754716981132 |
P040101005 | 268 | 201 | 287 | 0.29965156794425087108 |
P040101006 | 298 | 224 | 320 | 0.30000000000000000000 |
P040101007 | 338 | 254 | 362 | 0.29834254143646408840 |
P040101008 | 420 | 315 | 450 | 0.30000000000000000000 |
P040101009 | 498 | 374 | 534 | 0.29962546816479400749 |
P040101010 | 580 | 435 | 621 | 0.29951690821256038647 |
์ค๋ช :
์ด ์ฝ๋์์๋ SQL์ ์ฌ์ฉํ์ฌ "product"๋ผ๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค ํ ์ด๋ธ์ ์์ ๋ ๋ฒ์ ์ ์กฐํํ๊ณ ์์ต๋๋ค. ์์ ๋ด์ฉ์ ๊ฐ ์ํ์ ๋จ๊ฐ์์ ์๋ก์ด ๊ฐ๊ฒฉ์ ์ฐ์ถํ๊ณ , ์ด ์๋ก์ด ๊ฐ๊ฒฉ์ ์ฌ์ฉํ์ฌ ์๋ก์ด ์ด์ค์จ์ ์ฐ์ถํ๋ ๊ฒ์ ๋๋ค. ๋ค์์ ์ฝ๋์ ๊ฐ ๋ถ๋ถ์ ์ค๋ช ํฉ๋๋ค.
"%%sql"์ Jupyter notebook์ ๋งค์ง ๋ช ๋ น์ผ๋ก, ๋ค์ ์ฝ๋๋ฅผ SQL๋ก ํด์ํ๋๋ก ๋ ธํธ๋ถ์ ์ง์ํ๋ค.
"WITH new_price_tbl AS (...)" ๋ "new_price_tbl"์ด๋ผ๋ CTE(Common Table Expression)๋ฅผ ์ค์ ํฉ๋๋ค. ์ด๋ฅผ ํตํด ํ์ ์ฟผ๋ฆฌ์์ ์ฌ์ฉํ ์ ์๋ ํ์ ์ฟผ๋ฆฌ๋ฅผ ์ ์ํ ์ ์์ต๋๋ค.
"( SELECT product_cd, unit_price, unit_cost, TRUNC(unit_cost / 0.7) AS new_price FROM product )"๋ 'product' ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํ๊ณ , ๋จ๊ฐ๋ฅผ ๊ธฐ์ค์ผ๋ก ์๋ก์ด ๊ฐ๊ฒฉ์ ๊ณ์ฐํ์ฌ 'new price'๋ผ๋ ์๋ก์ด ์ด์ ํ ๋นํ๋ ํ์ ์ฟผ๋ฆฌ์ด๋ฉฐ, 'TRUNC' ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ณ์ฐ๋ ์๋ก์ด ๊ฐ๊ฒฉ์ ๊ฐ์ฅ ๊ฐ๊น์ด ์ ์๋ก ๋ฐ์ฌ๋ฆผํ๊ณ ์๋ค. ์ด๋ ํ์ฌ๊ฐ ์ ํ ๊ฐ๊ฒฉ์ ์ ์๋ก ๊ฒฐ์ ํ๊ณ ์ถ๋ค๋ ๊ฐ์ ์ ๊ธฐ๋ฐ์ผ๋ก ํ๋ค.
"SELECT *, (new_price - unit_cost) / new_price AS new_profit_rate FROM new_price_tbl LIMIT 10 ;"์ "new_price_tbl"์ ๋ชจ๋ ์ด์ ์ ํํ๋ ๋ฉ์ธ ์ฟผ๋ฆฌ์ด๋ค. ์ ์๋ก์ด ์์ต๋ฅ ์ ๊ณ์ฐํฉ๋๋ค. ์๋ก์ด ์์ต๋ฅ ์ ์๋ก์ด ๊ฐ๊ฒฉ์์ ๋จ๊ฐ๋ฅผ ๋นผ๊ณ ๊ทธ ๊ฒฐ๊ณผ๋ฅผ ์๋ก์ด ๊ฐ๊ฒฉ์ผ๋ก ๋๋์ด 'new_profit_rate'๋ผ๋ ์๋ก์ด ์ด์ ๋์ ํ์ฌ ๊ณ์ฐ๋ฉ๋๋ค. ์ฆ, ์ด๋ค ๊ธฐ์ค(์ฝ๋ ์ค๋ํซ์์ ์ง์ ํ์ง ์์)์ ๋ฐ๋ผ ์์ 10๊ฐ ์ํ์ ๊ฒฐ๊ณผ๋ง ํ์ํ๊ณ ์ถ๋ค๋ ์๋ฏธ์ ๋๋ค.
S-066: ์ํ ๋ฐ์ดํฐ(df_product)์ ๊ฐ ์ํ์ ๋ํด ์์ต๋ฅ ์ด 30%๊ฐ ๋๋ ์๋ก์ด ๋จ๊ฐ๋ฅผ ๊ตฌํ์์ค. ์ด๋ฒ์๋ 1์ ๋ฏธ๋ง์ ๋ฐ์ฌ๋ฆผํ๋ค(๋ฐ์ฌ๋ฆผ ๋๋ ์ง์๋ก ๋ฐ์ฌ๋ฆผํด๋ ๋ฌด๋ฐฉํ๋ค). ๊ทธ๋ฆฌ๊ณ 10๊ฐ์ ๊ฒฐ๊ณผ๋ฅผ ํ์ํ๊ฒ ํ๊ณ , ์์ต๋ฅ ์ด ๋๋ต 30% ์ ๋์ธ ๊ฒ์ ํ์ธํ๋ผ. ๋จ, ๋จ๊ฐ(unit_price)์ ์๊ฐ(unit_cost)์๋ ๊ฒฐ์์ด ๋ฐ์ํ๋ค๋ ์ ์ ์ ์ํ๋ค.
%%sql
WITH new_price_tbl AS (
SELECT
product_cd,
unit_price,
unit_cost,
ROUND(unit_cost / 0.7) AS new_price
FROM
product
)
SELECT
*,
(new_price - unit_cost) / new_price AS new_profit_rate
FROM
new_price_tbl
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
product_cd | unit_price | unit_cost | new_price | new_profit_rate |
---|---|---|---|---|
P040101001 | 198 | 149 | 213 | 0.30046948356807511737 |
P040101002 | 218 | 164 | 234 | 0.29914529914529914530 |
P040101003 | 230 | 173 | 247 | 0.29959514170040485830 |
P040101004 | 248 | 186 | 266 | 0.30075187969924812030 |
P040101005 | 268 | 201 | 287 | 0.29965156794425087108 |
P040101006 | 298 | 224 | 320 | 0.30000000000000000000 |
P040101007 | 338 | 254 | 363 | 0.30027548209366391185 |
P040101008 | 420 | 315 | 450 | 0.30000000000000000000 |
P040101009 | 498 | 374 | 534 | 0.29962546816479400749 |
P040101010 | 580 | 435 | 621 | 0.29951690821256038647 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, 'new_price_tbl'์ด๋ผ๋ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ ์ฌ์ฉํ์ฌ 'product'๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ์ด ์ฝ๋๋ ๊ฐ ์ํ์ ๋จ๊ฐ๋ฅผ 0.7๋ก ๋๋์ด ์๋ก์ด ๊ฐ๊ฒฉ์ ๊ณ์ฐํ๊ณ , ์๋ก์ด ๊ฐ๊ฒฉ์์ ๋จ๊ฐ๋ฅผ ๋นผ๊ณ , ๊ทธ ๊ฒฐ๊ณผ๋ฅผ ์๋ก์ด ๊ฐ๊ฒฉ์ผ๋ก ๋๋์ด ์๋ก์ด ์ด์ค์จ์ ๊ณ์ฐํ๋ค.
์๋๋ ์ด ์ฝ๋์ ๋จ๊ณ๋ณ ๋ถ์์ ๋๋ค.
WITH new_price_tbl AS (...) : ๊ดํธ๋ก ๋๋ฌ์ธ์ธ SELECT ๋ฌธ์์ ๋ฐ์ดํฐ๋ฅผ ์ผ์์ ์ผ๋ก ์ ์ฅํ๋ ๋ฐ ์ฌ์ฉ๋๋ 'new_price_tbl'์ด๋ผ๋ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ ์ ์ํ๊ณ ์๋ค.
SELECT product_cd, unit_price, unit_cost, ROUND(unit_cost / 0.7) AS new_price FROM product: ์ํ ์ฝ๋, ๋จ๊ฐ, ๋จ๊ฐ๋ฅผ ์ ํํ๊ณ ๊ฐ ์ํ์ ๋จ๊ฐ๋ฅผ 0.7๋ก ๋๋ ํ ๊ทธ ๊ฒฐ๊ณผ๋ฅผ ๋ฐ์ฌ๋ฆผํ์ฌ ์๋ก์ด ๊ฐ๊ฒฉ์ ๊ณ์ฐํฉ๋๋ค. ๊ฐ๊ฒฉ์ ๊ณ์ฐํฉ๋๋ค.
SELECT *, (new_price - unit_cost) / new_price AS new_profit_rate FROM new_price_tbl LIMIT 10: 'new_price_tbl' CTE์์ ๋ชจ๋ ์ปฌ๋ผ์ ์ ํํ๊ณ ์ ๊ฐ๊ฒฉ์์ ๋จ๊ฐ๋ฅผ ๋บ ํ, ๊ทธ ๊ฒฐ๊ณผ๋ฅผ ์๋ก์ด ๊ฐ๊ฒฉ์ผ๋ก ๋๋์ด ์๋ก์ด ์ด์ค์จ์ ๊ณ์ฐํ๋ค. ๋ง์ง๋ง์ผ๋ก ์ถ๋ ฅ์ ์ฒ์ 10์ค๋ก ์ ํํ๊ณ ์๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋๋ CTE๋ฅผ ์ฌ์ฉํ์ฌ ํ์ํ ๋ฐ์ดํฐ๋ฅผ ์ผ์์ ์ผ๋ก ์ ์ฅํ๊ณ , ๊ทธ ๋ฐ์ดํฐ์ ๋ํด ์ฐ์ ์ฐ์ฐ์ ์ํํ์ฌ ๊ฐ ์ํ์ ์๋ก์ด ๊ฐ๊ฒฉ๊ณผ ์๋ก์ด ์ด์ค์จ์ ๊ณ์ฐํ๊ณ ์๋ค.
S-067: ์ํ ๋ฐ์ดํฐ(df_product)์ ๊ฐ ์ํ์ ๋ํด ์์ต๋ฅ ์ด 30%๊ฐ ๋๋ ์๋ก์ด ๋จ๊ฐ๋ฅผ ๊ตฌํ์์ค. ์ด๋ฒ์๋ 1์ ๋ฏธ๋ง์ ๋ฐ์ฌ๋ฆผํ๋ค. ๊ทธ๋ฆฌ๊ณ 10๊ฐ์ ๊ฒฐ๊ณผ๋ฅผ ํ์ํ๊ณ , ์์ต๋ฅ ์ด ๋๋ต 30% ์ ๋์ธ ๊ฒ์ ํ์ธํ๋ผ. ๋จ, ๋จ๊ฐ(unit_price)์ ์๊ฐ(unit_cost)์๋ ์ ์๊ฐ ๋ฐ์ํ๊ณ ์๋ค๋ ์ ์ ์ ์ํ๋ผ.
%%sql
WITH new_price_tbl AS (
SELECT
product_cd,
unit_price,
unit_cost,
CEIL(unit_cost / 0.7) AS new_price
FROM
product
)
SELECT
*,
(new_price - unit_cost) / new_price AS new_profit_rate
FROM
new_price_tbl
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
product_cd | unit_price | unit_cost | new_price | new_profit_rate |
---|---|---|---|---|
P040101001 | 198 | 149 | 213 | 0.30046948356807511737 |
P040101002 | 218 | 164 | 235 | 0.30212765957446808511 |
P040101003 | 230 | 173 | 248 | 0.30241935483870967742 |
P040101004 | 248 | 186 | 266 | 0.30075187969924812030 |
P040101005 | 268 | 201 | 288 | 0.30208333333333333333 |
P040101006 | 298 | 224 | 320 | 0.30000000000000000000 |
P040101007 | 338 | 254 | 363 | 0.30027548209366391185 |
P040101008 | 420 | 315 | 450 | 0.30000000000000000000 |
P040101009 | 498 | 374 | 535 | 0.30093457943925233645 |
P040101010 | 580 | 435 | 622 | 0.30064308681672025723 |
์ค๋ช :
์ด ์ฝ๋๋ ์ด์ ์ฝ๋์ ๋น์ทํ์ง๋ง ์ ๊ฐ๊ฒฉ์ ๋ฐ์ฌ๋ฆผํ๋ ๋์ CEIL ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ฐ์ฅ ๊ฐ๊น์ด ์ ์๋ก ๋ฐ์ฌ๋ฆผํฉ๋๋ค.
์๋๋ ์ฝ๋์ ๋จ๊ณ๋ณ ๋ถ์์ ๋๋ค.
WITH new_price_tbl AS (...) : ๊ดํธ๋ก ๋๋ฌ์ธ์ธ SELECT ๋ฌธ์์ ๋ฐ์ดํฐ๋ฅผ ์ผ์์ ์ผ๋ก ์ ์ฅํ๋ ๋ฐ ์ฌ์ฉ๋๋ 'new_price_tbl'์ด๋ผ๋ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ ์ ์ํ๊ณ ์๋ค.
SELECT product_cd, unit_price, unit_cost, CEIL(unit_cost / 0.7) AS new_price FROM product: ์ํ ์ฝ๋, ๋จ๊ฐ, ๋จ๊ฐ๋ฅผ ์ ํํ๊ณ ๊ฐ ์ํ์ ๋จ๊ฐ๋ฅผ 0.7๋ก ๋๋ ํ, CEIL ํจ์๋ก ์์์ ์ดํ๋ฅผ ๋ฐ์ฌ๋ฆผํ์ฌ ๋ฐ์ฌ๋ฆผํ์ฌ ์๋ก์ด ๊ฐ๊ฒฉ์ ๊ณ์ฐํฉ๋๋ค.
SELECT *, (new_price - unit_cost) / new_price AS new_profit_rate FROM new_price_tbl LIMIT 10: 'new_price_tbl' CTE์์ ๋ชจ๋ ์ปฌ๋ผ์ ์ ํํ๊ณ , ์ ๊ฐ๊ฒฉ์์ ๋จ๊ฐ๋ฅผ ๋บ ํ, ๊ทธ ๊ฒฐ๊ณผ๋ฅผ ์ ๊ฐ๊ฒฉ์ผ๋ก ๋๋์ด ์๋ก์ด ์์ต๋ฅ ์ ๊ณ์ฐํ๋ค. ๋ง์ง๋ง์ผ๋ก ์ถ๋ ฅ์ ์ฒ์ 10์ค๋ก ์ ํํ๊ณ ์๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋๋ CTE๋ฅผ ์ฌ์ฉํ์ฌ ํ์ํ ๋ฐ์ดํฐ๋ฅผ ์ผ์์ ์ผ๋ก ์ ์ฅํ๊ณ , ๊ทธ ๋ฐ์ดํฐ์ ๋ํด ์ฐ์ ์ฐ์ฐ์ ์ํํ์ฌ ๊ฐ ์ํ์ ์๋ก์ด ๊ฐ๊ฒฉ๊ณผ ์๋ก์ด ์ด์ค์จ์ ๊ณ์ฐํ๊ณ ์๋ค. ๋จ, ์๋ก์ด ๊ฐ๊ฒฉ์ ์ด์ ์ฝ๋์ฒ๋ผ ๋ฐ์ฌ๋ฆผํ์ง ์๊ณ CEIL ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ฐ์ฅ ๊ฐ๊น์ด ์ ์๋ก ๋ฐ์ฌ๋ฆผํ๋ค.
S-068: ์ํ ๋ฐ์ดํฐ(df_product)์ ๊ฐ ์ํ์ ๋ํด ์๋น์ธ์จ 10%์ ๋ถ๊ฐ์ธ ํฌํจ ๊ธ์ก์ ๊ตฌํ๊ณ , 1์ ๋ฏธ๋ง์ ๋จ์๋ ์ ์ฌํ์ฌ 10๊ฐ์ ๊ฒฐ๊ณผ๋ฅผ ํ์ํ์์ค. ๋จ, ๋จ๊ฐ(unit_price)์๋ ๊ฒฐ์์ด ๋ฐ์ํ๋ค๋ ์ ์ ์ ์ํ๋ผ.
%%sql
SELECT
product_cd,
unit_price,
TRUNC(unit_price * 1.1) AS tax_price
FROM
product
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
product_cd | unit_price | tax_price |
---|---|---|
P040101001 | 198 | 217 |
P040101002 | 218 | 239 |
P040101003 | 230 | 253 |
P040101004 | 248 | 272 |
P040101005 | 268 | 294 |
P040101006 | 298 | 327 |
P040101007 | 338 | 371 |
P040101008 | 420 | 462 |
P040101009 | 498 | 547 |
P040101010 | 580 | 638 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, 'product'๋ผ๋ ์ด๋ฆ์ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ์ด ์ฝ๋์์๋ ์ํ ์ฝ๋, ๋จ๊ฐ๋ฅผ ์ ํํ๊ณ ๊ฐ ์ํ์ ๋ถ๊ฐ์ธ ํฌํจ ๊ฐ๊ฒฉ์ ๊ณ์ฐํฉ๋๋ค. ๋จ๊ฐ์ 1.1์ ๊ณฑํ๊ณ ๊ฒฐ๊ณผ๋ฅผ ๋ฐ์ฌ๋ฆผํ์ฌ ์์์ ์ดํ๋ฅผ ์ ๊ฑฐํฉ๋๋ค.
๋ค์์ ์ฝ๋์ ๋จ๊ณ๋ณ ๋ถ์์ ๋๋ค.
SELECT product_cd, unit_price, TRUNC(unit_price * 1.1) AS tax_price FROM product: ์ํ ์ฝ๋, ๋จ๊ฐ๋ฅผ ์ ํํ์ฌ ๊ฐ ์ํ์ ์ธ๊ธ ํฌํจ ๊ฐ๊ฒฉ์ ๊ณ์ฐํฉ๋๋ค. ๋จ๊ฐ์ 1.1์ ๊ณฑํ๊ณ TRUNC ํจ์๋ก ์์์ ์ดํ๋ฅผ ๋ฐ์ฌ๋ฆผํฉ๋๋ค.
FROM product: ๋ฐ์ดํฐ๋ฅผ ์ ํํ '์ํ' ํ ์ด๋ธ์ ์ง์ ํฉ๋๋ค.
LIMIT 10: ์ถ๋ ฅ์ ์ฒ์ 10์ค๋ก ์ ํํฉ๋๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋์์๋ ๊ฐ ์ํ์ ๋จ๊ฐ์ 1.1์ ๊ณฑํ์ฌ ๋ถ๊ฐ์ธ ํฌํจ ๊ฐ๊ฒฉ์ ๊ณ์ฐํ๊ณ , TRUNC ํจ์๋ก ์์์ ์ดํ๋ฅผ ๋ฐ์ฌ๋ฆผํ๊ณ ์์ต๋๋ค.
S-069: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ์ํ ๋ฐ์ดํฐ(df_product)๋ฅผ ๊ฒฐํฉํ์ฌ ๊ณ ๊ฐ๋ณ ์ ์ฒด ์ํ ํ๋งค๊ธ์ก ํฉ๊ณ์ ์นดํ ๊ณ ๋ฆฌ ๋๋ถ๋ฅ ์ฝ๋(category_major_cd)๊ฐ โ07โ(๋ณ์กฐ๋ฆผ ํต์กฐ๋ฆผ)์ธ ์ํ์ ํ๋งค๊ธ์ก ํฉ๊ณ๋ฅผ ๊ณ์ฐํ ํ, ์์์ ๋น์จ์ ๊ตฌํ์์ค. ์ถ์ถ ๋์์ ์นดํ ๊ณ ๋ฆฌ ๋๋ถ๋ฅ ์ฝ๋ โ07โ(๋ณ์กฐ๋ฆผ ํต์กฐ๋ฆผ)์ ํ๋งค ์ค์ ์ด ์๋ ๊ณ ๊ฐ์ผ๋ก ํ์ ํ๊ณ , ๊ฒฐ๊ณผ๋ฅผ 10๊ฑด๋ง ํ์ํ๋ค.
%%sql
WITH amount_all AS(
SELECT
customer_id,
SUM(amount) AS sum_all
FROM
receipt
GROUP BY
customer_id
),
amount_07 AS (
SELECT
r.customer_id,
SUM(r.amount) AS sum_07
FROM
receipt r
JOIN
product p
ON
r.product_cd = p.product_cd
WHERE
p.category_major_cd = '07'
GROUP BY
customer_id
)
SELECT
amount_all.customer_id,
sum_all,
sum_07,
sum_07 * 1.0 / sum_all AS sales_rate
FROM
amount_all
JOIN
amount_07
ON
amount_all.customer_id = amount_07.customer_id
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sum_all | sum_07 | sales_rate |
---|---|---|---|
CS001311000059 | 2302 | 102 | 0.04430929626411815812 |
CS011615000061 | 246 | 98 | 0.39837398373983739837 |
CS029212000033 | 3604 | 3604 | 1.00000000000000000000 |
CS007515000119 | 7157 | 2832 | 0.39569652088864049183 |
CS034515000123 | 3699 | 1202 | 0.32495268991619356583 |
CS026414000014 | 6671 | 3142 | 0.47099385399490331285 |
CS001615000099 | 768 | 318 | 0.41406250000000000000 |
CS010515000082 | 1482 | 553 | 0.37314439946018893387 |
CS019315000045 | 813 | 380 | 0.46740467404674046740 |
CS008513000099 | 1322 | 210 | 0.15885022692889561271 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, 'amount_all'๊ณผ 'amount_07'์ด๋ผ๋ ๋ ๊ฐ์ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ ์ฌ์ฉํ์ฌ 'receive'์ 'product'๋ผ๋ ๋ ๊ฐ์ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด ์ฝ๋๋ ๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ๊ธ์ก์ ํฉ๊ณ์ ๋ฉ์ด์ ์นดํ ๊ณ ๋ฆฌ ์ฝ๋๊ฐ '07'์ธ ์ํ์ ์ฌ์ฉํ ๊ธ์ก์ ํฉ๊ณ๋ฅผ ๊ณ์ฐํ๋ค. ๊ทธ๋ฐ ๋ค์ ๋ CTE๋ฅผ ๊ฒฐํฉํ์ฌ ๊ฐ ๊ณ ๊ฐ์ ์นดํ ๊ณ ๋ฆฌ '07' ์ํ์ ํ๋งค์จ์ ๊ณ์ฐํ๋ค.
๋ค์์ ์ฝ๋์ ๋จ๊ณ๋ณ ์์์ ๋๋ค.
WITH amount_all AS(...) ๋ฅผ ์ฌ์ฉํฉ๋๋ค. ์ด๊ฒ์ ๊ดํธ๋ก ๋๋ฌ์ธ์ธ SELECT ๋ฌธ์์ ๋ฐ์ดํฐ๋ฅผ ์ผ์์ ์ผ๋ก ์ ์ฅํ๋ ๋ฐ ์ฌ์ฉ๋๋ 'amount_all'์ด๋ผ๋ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ ์ ์ํ๊ณ ์๋ค.
SELECT customer_id, SUM(amount) AS sum_all FROM receipt GROUP BY customer_id: 'receiption' ํ ์ด๋ธ์์ ๊ณ ๊ฐ ID์ ๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ๊ธ์ก์ ํฉ๊ณ๋ฅผ ์ ํํ๊ณ ๊ทธ ๊ฒฐ๊ณผ๋ฅผ ๊ณ ๊ฐ ID๋ณ๋ก ๊ทธ๋ฃนํํ๋ค. ๊ทธ๋ฃนํํฉ๋๋ค.
๋ํ, amount_07 AS(...) ๋ฅผ ์ฌ์ฉํฉ๋๋ค. ์ด๋ ๊ดํธ๋ก ๋๋ฌ์ธ์ธ SELECT ๋ฌธ์์ ๋ฐ์ดํฐ๋ฅผ ์ผ์์ ์ผ๋ก ์ ์ฅํ๋ ๋ฐ ์ฌ์ฉ๋๋ 'amount_07'์ด๋ผ๋ ์ด๋ฆ์ ๋ ๋ค๋ฅธ CTE๋ฅผ ์ ์ํ๊ณ ์๋ค.
SELECT r.customer_id, SUM(r.amount) AS sum_07 FROM receipt r JOIN product p ON r.product_cd = p.product_cd WHERE p.category_major_cd = '07' GROUP BY customer_id: ๊ณ ๊ฐ ID์ ๋ฉ์ด์ ์นดํ ๊ณ ๋ฆฌ ์ฝ๋๊ฐ '07'์ธ ์ ํ์ ์ง์ถ๋ ๊ธ์ก์ ํฉ๊ณ๋ฅผ 'receiport'์ 'product' ํ ์ด๋ธ์์ ์ ํํ๊ณ , ์ ํ ์ฝ๋์์ ๋ ํ ์ด๋ธ์ ๊ฒฐํฉํ๊ณ , ๋ฉ์ด์ ์นดํ ๊ณ ๋ฆฌ ์ฝ๋ '07'๋ก ์ ํ์ ํํฐ๋งํ ํ ๊ณ ๊ฐ ID๋ก ๊ฒฐ๊ณผ๋ฅผ ๊ทธ๋ฃนํํ๋ค.
SELECT amount_all.customer_id, sum_all, sum_07, sum_07, sum_07 * 1.0 / sum_all AS sales_rate FROM amount_all JOIN amount_07 ON amount_all.customer_id = amount_07. customer_id LIMIT 10: ๊ณ ๊ฐ ID, ๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ๋ชจ๋ ๊ธ์ก์ ํฉ๊ณ, ๊ฐ ๊ณ ๊ฐ์ด ์นดํ ๊ณ ๋ฆฌ '07' ์ ํ์ ์ฌ์ฉํ ๊ธ์ก์ ํฉ๊ณ, ์นดํ ๊ณ ๋ฆฌ '07' ์ ํ์ ์ฌ์ฉํ ๊ธ์ก์ ํฉ๊ณ๋ฅผ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ๋ชจ๋ ๊ธ์ก์ ํฉ๊ณ๋ก ๋๋ ๊ฐ์ ํตํด ๊ฐ ๊ณ ๊ฐ์ ์นดํ ๊ณ ๋ฆฌ '07' ์ ํ์ ํ๋งค์จ์ ์ ํํ๋ค. ๋ง์ง๋ง์ผ๋ก ๊ณ ๊ฐ ID๋ก 2๊ฐ์ CTE๋ฅผ ๊ฒฐํฉํ์ฌ ์ถ๋ ฅ์ ์ฒ์ 10์ค๋ก ์ ํํ๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋๋ ๋จผ์ ๊ฐ ๊ณ ๊ฐ์ ์ ์ฒด ์๋น ๊ธ์ก์ ํฉ๊ณ์ ๊ฐ ๊ณ ๊ฐ์ "07"์ ํ์ ๋ํ ์๋น ๊ธ์ก์ ํฉ๊ณ๋ฅผ ๊ณ์ฐํ๊ณ , ํ์๋ฅผ ์ ์๋ก ๋๋์ด ๊ฐ ๊ณ ๊ฐ์ "07"์ ํ์ ํ๋งค์จ์ ๊ณ์ฐํ๋ค. ์ด๋ ๋ ๊ฐ์ CTE๋ฅผ ์ฌ์ฉํ์ฌ ํ์ํ ๋ฐ์ดํฐ๋ฅผ ์ผ์์ ์ผ๋ก ์ ์ฅํ๊ณ , ๊ทธ ๋ฐ์ดํฐ์ ๋ํด ์ฐ์ ์ฐ์ฐ๊ณผ ๊ฒฐํฉ์ ์ํํจ์ผ๋ก์จ ์คํ๋๋ค.
S-070: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ์ผ(sales_ymd)์ ๋ํด ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ํ์๊ฐ์ ์ผ(application_date)๋ก๋ถํฐ์ ๊ฒฝ๊ณผ์ผ์๋ฅผ ๊ณ์ฐํ์ฌ ๊ณ ๊ฐ ID(customer_id), ๋งค์ถ์ผ, ํ์๊ฐ์ ์ผ๊ณผ ํจ๊ป 10๊ฑด์ ํ์ํ๋ผ(sales_ymd๋ ์์น, application_date๋ ๋ฌธ์์ด๋ก ๋ฐ์ดํฐ๋ฅผ ๋ณด๊ดํ๊ณ ์๋ค๋ ์ ์ ์ ์).
%%sql
WITH receipt_distinct AS (
SELECT distinct
customer_id,
sales_ymd
FROM
receipt
)
SELECT
c.customer_id,
r.sales_ymd,
c.application_date,
EXTRACT(DAY FROM (TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD')
- TO_TIMESTAMP(c.application_date, 'YYYYMMDD'))) AS elapsed_days
FROM
receipt_distinct r
JOIN
customer c
ON
r.customer_id = c.customer_id
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sales_ymd | application_date | elapsed_days |
---|---|---|---|
CS017515000010 | 20171117 | 20150803 | 837 |
CS019515000097 | 20190630 | 20141124 | 1679 |
CS008515000005 | 20170714 | 20150216 | 879 |
CS026414000097 | 20170809 | 20150430 | 832 |
CS034514000008 | 20181012 | 20150807 | 1162 |
CS029415000089 | 20180409 | 20150723 | 991 |
CS019411000012 | 20190314 | 20141213 | 1552 |
CS015614000006 | 20190802 | 20150211 | 1633 |
CS007515000053 | 20170712 | 20150325 | 840 |
CS024615000041 | 20170729 | 20150918 | 680 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, 'receipt_distinct'๋ผ๋ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ ์ฌ์ฉํ์ฌ 'receipt'์ 'customer'๋ผ๋ ๋ ๊ฐ์ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ์ด ์ฝ๋๋ ๊ณ ๊ฐ์ ์ ์ฒญ์ผ๊ณผ ํด๋น ๊ณ ๊ฐ์ด ๋ณ๋์ ํ๋งค์ผ ์ฌ์ด์ ๊ฒฝ๊ณผํ ์ผ์๋ฅผ ๊ณ์ฐํ๊ณ ์๋ค.
๋ค์์ ์ฝ๋์ ๋จ๊ณ๋ณ ๋ถ์์ ๋๋ค.
WITH receipt_distinct AS(...) ๋ผ๊ณ ํฉ๋๋ค. ์ด๋ ๊ดํธ๋ก ๋๋ฌ์ธ์ธ SELECT ๋ฌธ์์ ๋ฐ์ดํฐ๋ฅผ ์ผ์์ ์ผ๋ก ์ ์ฅํ๋ ๋ฐ ์ฌ์ฉ๋๋ 'receipt_distinct'๋ผ๋ ์ด๋ฆ์ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ ์ ์ํ๊ณ ์์ต๋๋ค.
SELECT distinct customer_id, sales_ymd FROM receipt: 'receipt' ํ ์ด๋ธ์์ distinct customer ID์ sales year-month-day๋ฅผ ์ ํํ๋ค.
SELECT c.customer_id, r.sales_ymd, c.application_date, EXTRACT(DAY FROM (TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD') - TO_THEMESTAMP(c.application_date, EXTRACT(DAY FROM (TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD') - TO_THEMESTAMP(c.application_date)) TIMESTAMP(c.application_date, 'YYYYMMDD'))) )) AS elapsed_days FROM receipt_distinct r JOIN customer c ON r.customer_id = c.customer_id LIMIT 10: ์ด ๋ ๊ณ ๊ฐ ID, ํ๋งค์ผ์, ์ ์ฒญ์ผ, ๊ณ ๊ฐ์ ์ ์ฒญ์ผ๋ก๋ถํฐ ํด๋น ๊ณ ๊ฐ์ด ํ๋งคํ ๋ช ์ธ์ ๋ ์ง๊น์ง์ ๊ฒฝ๊ณผ์ผ์๋ฅผ ์ ํํ๋ค. ๊ฒฝ๊ณผ์ผ์ ๊ณ์ฐ์ ํ๋งค์ผ์์ ์ ์ฒญ์ผ์ ํ์์คํฌํ๋ก ๋ณํํ์ฌ ๊ทธ ์ฐจ์ด๋ฅผ ์ผ์๋ก ๊ณ์ฐํ๋ค. ๋ง์ง๋ง์ผ๋ก 'receipt_distinct' CTE์ 'customer' ํ ์ด๋ธ์ ๊ณ ๊ฐ ID๋ก ๊ฒฐํฉํ๊ณ ์ถ๋ ฅ์ ์ฒ์ 10์ค๋ก ์ ํํ๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋์์๋ ๋จผ์ 'receipt' ํ ์ด๋ธ์์ ๊ณ ๊ฐ ID์ ๋งค์ถ ์ฐ/์/์ผ์ ์ ํํ๊ณ , ์ด๋ฅผ 'customer' ํ ์ด๋ธ๊ณผ ๊ฒฐํฉํ์ฌ ๊ณ ๊ฐ์ ์ ์ฒญ์ผ์ ๊ฐ์ ธ์์ ๊ณ ๊ฐ์ ์ ์ฒญ์ผ๊ณผ ํด๋น ๊ณ ๊ฐ์ ๊ฐ๋ณ ๋งค์ถ์ผ ์ฌ์ด์ ๊ฒฝ๊ณผํ ์ผ์๋ฅผ ๊ณ์ฐํ๊ณ ์๋ค. ๋ค์์ผ๋ก ํ๋งค์ผ๊ณผ ์ ์ฒญ์ผ์ ํ์์คํฌํ๋ก ๋ณํํ๊ณ ๊ทธ ์ฐจ์ด๋ฅผ ์ผ์๋ก ๊ณ์ฐํ๋ ๊ณ์ฐ์ด ์ด๋ฃจ์ด์ง๋ค. ์ด๋ CTE๋ฅผ ์ด์ฉํด ํ์ํ ๋ฐ์ดํฐ๋ฅผ ์ผ์์ ์ผ๋ก ์ ์ฅํ๊ณ , ํด๋น ๋ฐ์ดํฐ์ ๋ํด ์ฐ์ ์ฐ์ฐ๊ณผ ๊ฒฐํฉ์ ์ํํจ์ผ๋ก์จ ์คํ๋๋ค.
ย
S-071: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ์ผ(sales_ymd)์ ๋ํด ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ํ์๊ฐ์ ์ผ(application_date)๋ก๋ถํฐ์ ๊ฒฝ๊ณผ ๊ฐ์ ์๋ฅผ ๊ณ์ฐํ์ฌ ๊ณ ๊ฐ ID(customer_id), ๋งค์ถ์ผ, ํ์๊ฐ์ ์ผ๊ณผ ํจ๊ป 10๊ฑด ํ์ (sales_ymd๋ ์ซ์, application_date๋ ๋ฌธ์์ด๋ก ๋ฐ์ดํฐ๋ฅผ ๋ณด๊ดํ๋ ์ ์ ์ ์) 1๊ฐ์ ๋ฏธ๋ง์ ๋ฐ์ฌ๋ฆผํ๋ค.
%%sql
WITH receipt_distinct AS (
SELECT DISTINCT
customer_id,
sales_ymd
FROM
receipt
),
time_age_tbl AS(
SELECT
c.customer_id,
r.sales_ymd,
c.application_date,
AGE(TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD'),
TO_TIMESTAMP(c.application_date, 'YYYYMMDD')) AS time_age
FROM
receipt_distinct r
JOIN
customer c
ON
r.customer_id = c.customer_id
)
SELECT
customer_id,
sales_ymd,
application_date,
EXTRACT(YEAR FROM time_age) * 12
+ EXTRACT(MONTH FROM time_age) AS elapsed_months
FROM
time_age_tbl
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sales_ymd | application_date | elapsed_months |
---|---|---|---|
CS017515000010 | 20171117 | 20150803 | 27 |
CS019515000097 | 20190630 | 20141124 | 55 |
CS008515000005 | 20170714 | 20150216 | 28 |
CS026414000097 | 20170809 | 20150430 | 27 |
CS034514000008 | 20181012 | 20150807 | 38 |
CS029415000089 | 20180409 | 20150723 | 32 |
CS019411000012 | 20190314 | 20141213 | 51 |
CS015614000006 | 20190802 | 20150211 | 53 |
CS007515000053 | 20170712 | 20150325 | 27 |
CS024615000041 | 20170729 | 20150918 | 22 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, 'receive_distinct'์ 'time_age_tbl'์ด๋ผ๋ ๋ ๊ฐ์ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ ์ฌ์ฉํ์ฌ 'receive'์ 'customer'๋ผ๋ ๋ ๊ฐ์ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด ์ฝ๋๋ ๊ณ ๊ฐ์ ์ ์ฒญ์ผ๊ณผ ํด๋น ๊ณ ๊ฐ์ด ๋ณ๋์ ํ๋งค๋ฅผ ํ ๋ ์ง ์ฌ์ด์ ๊ฒฝ๊ณผ ๊ฐ์ ์๋ฅผ ๊ณ์ฐํ๋ค.
๋ค์์ ์ฝ๋์ ๋จ๊ณ๋ณ ์์์ ๋๋ค.
WITH receipt_distinct AS (...) : ๊ดํธ๋ก ๋๋ฌ์ธ์ธ SELECT ๋ฌธ์์ ๋ฐ์ดํฐ๋ฅผ ์ผ์์ ์ผ๋ก ์ ์ฅํ๋ ๋ฐ ์ฌ์ฉ๋๋ 'receipt_distinct'๋ผ๋ ์ด๋ฆ์ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ ์ ์ํ๊ณ ์๋ค.
SELECT DISTINCT customer_id, sales_ymd FROM receipt: 'receipt' ํ ์ด๋ธ์์ distinct customer ID์ sales year-month-day๋ฅผ ์ ํํ๋ค.
WITH time_age_tbl AS (...) : ์ด๊ฒ์ ๊ดํธ๋ก ๋๋ฌ์ธ์ธ SELECT ๋ฌธ์ ๋ฐ์ดํฐ๋ฅผ ์ผ์์ ์ผ๋ก ์ ์ฅํ๊ธฐ ์ํด ์ฌ์ฉ๋๋ 'time_age_tbl'์ด๋ผ๋ ์ด๋ฆ์ ๋ ๋ค๋ฅธ CTE๋ฅผ ์ ์ํ๋ ๊ฒ์ด๋ค.
SELECT c.customer_id, r.sales_ymd, c.application_date, AGE(TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD'), TO_TIMESTAMP(c. application_date, 'YYYYMMMDD')))) AS time_age FROM receipt_distinct r JOIN customer c ON r.customer_id = c.customer_id: ๊ณ ๊ฐ ID, ๋งค์ถ์ผ์, ์ ์ฒญ์ผ, ๊ทธ๋ฆฌ๊ณ ๊ณ ๊ฐ์ ์ ์ฒญ์ผ๋ก๋ถํฐ ํด๋น ๊ณ ๊ฐ์ ์ ์ฒญ์ผ๋ก๋ถํฐ ํด๋น ๊ณ ๊ฐ์ด ์งํํ ๊ฐ๋ณ ํ๋งค์ผ๊น์ง์ ๊ฒฝ๊ณผ ์๊ฐ์ ์ ํํ๋ ๊ฒ์ด๋ค. ๊ฒฝ๊ณผ์๊ฐ ๊ณ์ฐ์ ํ๋งค์ผ์์ ์ ์ฒญ์ผ์ ํ์์คํฌํ๋ก ๋ณํํ๊ณ AGE ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ทธ ์ฐจ์ด๋ฅผ ๊ณ์ฐํ๋ค. ๋ง์ง๋ง์ผ๋ก 'receipt_distinct' CTE์ 'customer' ํ ์ด๋ธ์ ๊ณ ๊ฐ ID๋ก ๊ฒฐํฉํ๋ค.
SELECT customer_id, sales_ymd, application_date, EXTRACT(YEAR FROM time_age) * 12 + EXTRACT(MONTH FROM time_age) AS elapsed_months FROM time_age_. tbl LIMIT 10: ๊ณ ๊ฐ ID, ํ๋งค์ผ์, ์ ์ฒญ์ผ, ๊ณ ๊ฐ์ ์ ์ฒญ์ผ๋ก๋ถํฐ ํด๋น ๊ณ ๊ฐ์ด ์งํํ ๊ฐ๋ณ ํ๋งค์ผ๊น์ง์ ๊ฒฝ๊ณผ ๊ฐ์ ์๋ฅผ ์ ํํ๋ค. ๊ฒฝ๊ณผ ๊ฐ์ ์ ๊ณ์ฐ์ ๊ฒฝ๊ณผ์๊ฐ์ ๋ ์ฑ๋ถ๊ณผ ์ ์ฑ๋ถ์ ์ถ์ถํ์ฌ ์์๋ก ๋ณํํฉ๋๋ค. ๋ง์ง๋ง์ผ๋ก ์ถ๋ ฅ์ ์ฒ์ 10์ค๋ก ์ ํํ๊ณ ์์ต๋๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋์์๋ ๋จผ์ 'receive' ํ ์ด๋ธ์์ ๊ณ ๊ฐ ID์ ๋งค์ถ ์ฐ๋๋ฅผ ์ ํํ๊ณ , 'customer' ํ ์ด๋ธ๊ณผ ๊ฒฐํฉํ์ฌ ๊ณ ๊ฐ์ ์ ์ฒญ์ผ์ ๊ฐ์ ธ์์ ๊ณ ๊ฐ์ ์ ์ฒญ์ผ๋ก๋ถํฐ ํด๋น ๊ณ ๊ฐ์ ๊ฐ๋ณ ๋งค์ถ์ผ๊น์ง์ ๊ฒฝ๊ณผ ๊ฐ์ ์๋ฅผ ๊ณ์ฐํฉ๋๋ค. ๊ทธ๋ฐ ๋ค์ CTE์ AGE ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ํ๋งค์ผ๊ณผ ์ ์ฒญ์ผ ์ฌ์ด์ ๊ฒฝ๊ณผ ์๊ฐ์ ๊ณ์ฐํ๊ณ ๊ทธ ๊ฒฐ๊ณผ๋ฅผ ์ ๋จ์๋ก ๋ณํํ๋ค. ์ด๋ ๋ ๊ฐ์ CTE๋ฅผ ์ฌ์ฉํ์ฌ ํ์ํ ๋ฐ์ดํฐ๋ฅผ ์ผ์์ ์ผ๋ก ์ ์ฅํ๊ณ , ํด๋น ๋ฐ์ดํฐ์ ๋ํด ์ฐ์ ์ฐ์ฐ๊ณผ ๊ฒฐํฉ์ ์ํํจ์ผ๋ก์จ ์ด๋ฃจ์ด์ง๋ค.
S-072: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ์ผ(df_customer)์ ๋ํด ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ํ์๊ฐ์ ์ ์ฒญ์ผ(application_date)๋ก๋ถํฐ์ ๊ฒฝ๊ณผ๋ ์๋ฅผ ๊ณ์ฐํ์ฌ ๊ณ ๊ฐ ID(customer_id), ๋งค์ถ์ผ, ํ์๊ฐ์ ์ ์ฒญ์ผ๊ณผ ํจ๊ป 10๊ฑด (sales_ymd๋ ์์น, application_date๋ ๋ฌธ์์ด๋ก ๋ฐ์ดํฐ๋ฅผ ๋ณด๊ดํ๊ณ ์๋ค๋ ์ ์ ์ ์) 1๋ ๋ฏธ๋ง์ ๋ฐ์ฌ๋ฆผํ๋ค.
%%sql
WITH receipt_distinct AS (
SELECT distinct
customer_id,
sales_ymd
FROM
receipt
)
SELECT
c.customer_id,
r.sales_ymd,
c.application_date,
EXTRACT(YEAR FROM AGE(
TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD'),
TO_TIMESTAMP(c.application_date, 'YYYYMMDD'))) AS elapsed_years
FROM
receipt_distinct r
JOIN
customer c
ON
r.customer_id = c.customer_id
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sales_ymd | application_date | elapsed_years |
---|---|---|---|
CS017515000010 | 20171117 | 20150803 | 2 |
CS019515000097 | 20190630 | 20141124 | 4 |
CS008515000005 | 20170714 | 20150216 | 2 |
CS026414000097 | 20170809 | 20150430 | 2 |
CS034514000008 | 20181012 | 20150807 | 3 |
CS029415000089 | 20180409 | 20150723 | 2 |
CS019411000012 | 20190314 | 20141213 | 4 |
CS015614000006 | 20190802 | 20150211 | 4 |
CS007515000053 | 20170712 | 20150325 | 2 |
CS024615000041 | 20170729 | 20150918 | 1 |
์ค๋ช :
์ด SQL ์ฝ๋๋ ๊ตฌ๋งค ๊ณ ๊ฐ์ ๊ณ ๊ฐ ID, ํ๋งค์ผ, ์ ์ฒญ์ผ์ ์ทจ๋ํ๊ณ ์ ์ฒญ์ผ๊ณผ ํ๋งค์ผ ์ฌ์ด์ ๊ฒฝ๊ณผ ์ฐ์๋ฅผ ๊ณ์ฐํ๋ค.
์ด ์ฝ๋์์๋ ๋จผ์ receipt_distinct๋ผ๋ ๊ณตํต ํ ์ด๋ธ ์์ ์์ฑํ๊ณ , receipt ํ ์ด๋ธ์์ ๊ณ ๊ฐ ID์ ํ๋งค ๋ ์ง์ ๊ฐ๋ณ ์กฐํฉ์ ์ ํํฉ๋๋ค. ์ด๋ ๊ฒ ํ๋ฉด ๊ฐ ๊ณ ๊ฐ์ด ์ฌ๋ฌ ๋ฒ ๊ตฌ๋งคํ๋๋ผ๋ ํ ๋ฒ๋ง ๊ณ์ฐ๋๋๋ก ๋ณด์ฅํ ์ ์๋ค.
๋ฉ์ธ ์ฟผ๋ฆฌ์์๋ receipt_distinct ํ ์ด๋ธ์์ ๊ณ ๊ฐ ID, ํ๋งค์ผ, ์ ์ฒญ์ผ์ ์ ํํ๊ณ EXTRACT ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ์ ์ฒญ์ผ๊ณผ ํ๋งค์ผ ์ฌ์ด์ ๊ฒฝ๊ณผ ์ฐ๋๋ฅผ ๊ณ์ฐํ๊ณ AGE ํจ์๋ก ๋ ๋ ์ง์ ์๊ฐ์ฐจ๋ฅผ ๊ณ์ฐํ๊ณ EXTRACT ํจ์๋ก ๊ฒฐ๊ณผ์ ๊ฐ๊ฒฉ์์ ์ฐ๋ ์ฑ๋ถ์ ์ถ์ถํ๋ค.
๊ฒฐ๊ณผ๋ ๊ฐ ๊ณ ๊ฐ์ ๊ณ ๊ฐ ID, ํ๋งค์ผ, ์ ์ฒญ์ผ, ๊ฒฝ๊ณผ ์ฐ๋๋ฅผ ๋ํ๋ด๋ ํ๋ก ๋ง๋ค์ด์ง๋ฉฐ, LIMIT ๊ตฌ๋ฌธ์ ๊ฒฐ๊ณผ ์งํฉ์ ์ฒซ 10ํ์ผ๋ก ์ถ๋ ฅ์ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
ย
S-073: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ์ผ(sales_ymd)์ ๋ํด ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ํ์๊ฐ์ ์ผ(application_date)๋ก๋ถํฐ์ ์ํฌํฌ ์ด ๋จ์์ ๊ฒฝ๊ณผ ์๊ฐ์ ๊ณ์ฐํ์ฌ ๊ณ ๊ฐ ID(customer_id), ๋งค์ถ์ผ, ํ์๊ฐ์ ์ผ๊ณผ ํจ๊ป 10๊ฑด์ ํ์ํ๋ค. ๊ณผ ํจ๊ป 10๊ฑด์ ํ์ํ๋ผ(๋จ, sales_ymd๋ ์์น, application_date๋ ๋ฌธ์์ด๋ก ๋ฐ์ดํฐ๋ฅผ ๋ณด์ ํ๊ณ ์๋ค๋ ์ ์ ์ ์). ๋จ, ์๊ฐ ์ ๋ณด๋ ๋ณด์ ํ์ง ์์ผ๋ฏ๋ก ๊ฐ ๋ ์ง๋ 0์ 0๋ถ 0์ด๋ก ํ์ํ๋ค.
%%sql
WITH receipt_distinct AS (
SELECT distinct
customer_id,
sales_ymd
FROM
receipt
)
SELECT
c.customer_id,
r.sales_ymd,
c.application_date,
EXTRACT(EPOCH FROM
TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD') -
TO_TIMESTAMP(c.application_date, 'YYYYMMDD')
) AS elapsed_epoch
FROM
receipt_distinct r
JOIN
customer c
ON
r.customer_id = c.customer_id
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sales_ymd | application_date | elapsed_epoch |
---|---|---|---|
CS017515000010 | 20171117 | 20150803 | 72316800.000000 |
CS019515000097 | 20190630 | 20141124 | 145065600.000000 |
CS008515000005 | 20170714 | 20150216 | 75945600.000000 |
CS026414000097 | 20170809 | 20150430 | 71884800.000000 |
CS034514000008 | 20181012 | 20150807 | 100396800.000000 |
CS029415000089 | 20180409 | 20150723 | 85622400.000000 |
CS019411000012 | 20190314 | 20141213 | 134092800.000000 |
CS015614000006 | 20190802 | 20150211 | 141091200.000000 |
CS007515000053 | 20170712 | 20150325 | 72576000.000000 |
CS024615000041 | 20170729 | 20150918 | 58752000.000000 |
์ค๋ช :
์ด SQL ์ฝ๋๋ ๊ตฌ๋งค ๊ณ ๊ฐ์ ๊ณ ๊ฐ ID, ํ๋งค์ผ, ์ ์ฒญ์ผ์ ๊ฐ์ ธ์ ์ ์ฒญ์ผ๊ณผ ํ๋งค์ผ ์ฌ์ด์ ๊ฒฝ๊ณผ ์๊ฐ์ ๊ณ์ฐํ๋ค.
์ด ์ฝ๋์์๋ ๋จผ์ receipt_distinct๋ผ๋ ๊ณตํต ํ ์ด๋ธ ์์ ๋ง๋ค๊ณ , receipt ํ ์ด๋ธ์์ ๊ณ ๊ฐ ID์ ํ๋งค ๋ ์ง์ ๊ฐ๋ณ ์กฐํฉ์ ์ ํํฉ๋๋ค. ์ด๋ ๊ฒ ํ๋ฉด ๊ฐ ๊ณ ๊ฐ์ด ์ฌ๋ฌ ๋ฒ ๊ตฌ๋งคํ๋๋ผ๋ ํ ๋ฒ๋ง ๊ณ์ฐ๋๋๋ก ๋ณด์ฅํ ์ ์๋ค.
๋ค์์ผ๋ก ๋ฉ์ธ ์ฟผ๋ฆฌ์์ receipt_distinct ํ ์ด๋ธ์์ ๊ณ ๊ฐ ID, ํ๋งค์ผ, ์ ์ฒญ์ผ์ ์ ํํ๊ณ EXTRACT ํจ์์ TO_TIMESTAMP ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ์ ์ฒญ์ผ๊ณผ ํ๋งค์ผ ์ฌ์ด์ ๊ฒฝ๊ณผ ์ด๋ฅผ ๊ณ์ฐํฉ๋๋ค. ๋ฅผ ์ฌ์ฉํ์ฌ ๊ฒฐ๊ณผ์ ๊ฐ๊ฒฉ์์ ์ด๋ฅผ ์ถ์ถํ๊ณ ์์ต๋๋ค.
๊ฒฐ๊ณผ๋ ๊ฐ ๊ณ ๊ฐ์ ๊ณ ๊ฐ ID, ํ๋งค์ผ, ์ ์ฒญ์ผ, ๊ฒฝ๊ณผ ์ด์๋ฅผ ๋ํ๋ด๋ ํ์ด๋ฉฐ, LIMIT ๊ตฌ๋ฌธ์ ๊ฒฐ๊ณผ ์งํฉ์ ์ฒ์ 10์ค๋ก ์ถ๋ ฅ์ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
S-074: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ์ผ(sales_ymd)์ ๋ํด ํด๋น ์ฃผ ์์์ผ๋ถํฐ์ ๊ฒฝ๊ณผ์ผ์๋ฅผ ๊ณ์ฐํ์ฌ ๋งค์ถ์ผ, ์ง์ ์์์ผ๊น์ง 10๊ฑด์ฉ ํ์ํ๋ผ(sales_ymd๋ ์์น๋ก ๋ฐ์ดํฐ๋ฅผ ๋ณด๊ดํ๊ณ ์๋ค๋ ์ ์ ์ ์).
%%sql
WITH elapsed_days_tbl AS (
SELECT
TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') AS sales_ymd,
EXTRACT(DOW FROM (
TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') - 1)) AS elapsed_days
FROM
receipt
)
SELECT
sales_ymd,
elapsed_days,
sales_ymd - CAST(elapsed_days AS INTEGER) AS monday
FROM
elapsed_days_tbl
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
sales_ymd | elapsed_days | monday |
---|---|---|
2018-11-03 | 5 | 2018-10-29 |
2018-11-18 | 6 | 2018-11-12 |
2017-07-12 | 2 | 2017-07-10 |
2019-02-05 | 1 | 2019-02-04 |
2018-08-21 | 1 | 2018-08-20 |
2019-06-05 | 2 | 2019-06-03 |
2018-12-05 | 2 | 2018-12-03 |
2019-09-22 | 6 | 2019-09-16 |
2017-05-04 | 3 | 2017-05-01 |
2019-10-10 | 3 | 2019-10-07 |
์ค๋ช :
์ด SQL ์ฝ๋๋ elapsed_days_tbl์ด๋ผ๋ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ ์ ์ํ๋ ๊ฒ์ผ๋ก ์์ํ๋ค. ์ด CTE๋ ์์์ฆ ํ ์ด๋ธ์ ๊ฐ sales_ymd ๋ ์ง์ ์์ผ(0์์ 6๊น์ง์ ์ซ์, 0์ ์ผ์์ผ)์ ์ถ์ถํ๋ค.
๋ฉ์ธ ์ฟผ๋ฆฌ์์๋ elapsed_days_tbl CTE์์ sales_ymd์ elapsed_days๊ฐ ์ ํ๋์ด ์๋ค. ๊ทธ๋ฐ ๋ค์ ์ด ์ฝ๋์์๋ sales_ymd์์ elapsed_days๋ฅผ ๋นผ์ ์๋ก์ด ๋ ์ง๋ฅผ ๊ณ์ฐํ๋ค. ์ด๋ elapsed_days๋ฅผ ์ ์๋ก ๋ณํํ๊ณ sales_ymd์์ ๋นผ๋ ๋ฐฉ์์ผ๋ก ์ด๋ฃจ์ด์ง๋๋ค. ๊ฒฐ๊ณผ ๋ ์ง๋ sales_ymd์ ๋ ์ง์ ํด๋นํ๋ ์ฃผ ์์์ผ์ด ๋๋ค.
์ฝ๋ ๋ง์ง๋ง์ ์๋ LIMIT 10 ๊ตฌ๋ฌธ์ ์ถ๋ ฅ์ ์ฒ์ 10์ค๋ก ์ ํํ๋ค.
S-075: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์์ ๋ฌด์์๋ก 1%์ ๋ฐ์ดํฐ๋ฅผ ์ถ์ถํ์ฌ ๋งจ ์๋ถํฐ 10๊ฐ๋ฅผ ํ์ํ๋ผ.
%%sql
-- ์ฝ๋ ์์ 1 (๋จ์ํ๊ฒ ํ ๊ฒฝ์ฐ. ๋จ, 1.0% ์ ํ๋ก ๊ฑด์ ๋ณ๋์ด ์์)
SELECT * FROM customer WHERE RANDOM() <= 0.01
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | customer_name | gender_cd | gender | birth_day | age | postal_cd | address | application_store_cd | application_date | status_cd |
---|---|---|---|---|---|---|---|---|---|---|
CS019415000117 | ๅฎฎ่ ่ฝไปฅ | 1 | ๅฅณๆง | 1974-07-10 | 44 | 173-0036 | ๆฑไบฌ้ฝๆฟๆฉๅบๅๅ********** | S13019 | 20141114 | C-20100720-D |
CS040513000111 | ๅฏบ่ฅฟ ๅฅๅคฎ | 1 | ๅฅณๆง | 1966-06-03 | 52 | 226-0027 | ็ฅๅฅๅท็ๆจชๆตๅธ็ทๅบ้ทๆดฅ็ฐ********** | S14040 | 20150728 | 6-20090622-6 |
CS029402000041 | ๆต ๅฉ ไฟไบ | 0 | ็ทๆง | 1975-08-15 | 43 | 134-0013 | ๆฑไบฌ้ฝๆฑๆธๅทๅบๆฑๆธๅท********** | S12029 | 20150220 | 0-00000000-0 |
CS019712000008 | ๆข ๆฌ ใใซใซ | 1 | ๅฅณๆง | 1945-04-14 | 73 | 173-0037 | ๆฑไบฌ้ฝๆฟๆฉๅบๅฐ่ๆ น********** | S13019 | 20150613 | 0-00000000-0 |
CS015713000077 | ้ทๆฒข ็ต่กฃ | 1 | ๅฅณๆง | 1947-10-09 | 71 | 136-0075 | ๆฑไบฌ้ฝๆฑๆฑๅบๆฐ็ ********** | S13015 | 20150308 | 0-00000000-0 |
CS008515000014 | ้ๆฒข ใใใฟ | 1 | ๅฅณๆง | 1959-06-09 | 59 | 157-0067 | ๆฑไบฌ้ฝไธ็ฐ่ฐทๅบๅๅค่ฆ********** | S13008 | 20150219 | 9-20091212-B |
CS031514000047 | ๅๅฃ ็คผๅญ | 1 | ๅฅณๆง | 1963-07-26 | 55 | 151-0064 | ๆฑไบฌ้ฝๆธ่ฐทๅบไธๅ********** | S13031 | 20150927 | 8-20090607-6 |
CS004313000412 | ๆฅๆฅ ็ณ | 1 | ๅฅณๆง | 1984-07-26 | 34 | 176-0024 | ๆฑไบฌ้ฝ็ทด้ฆฌๅบไธญๆ********** | S13004 | 20170525 | 0-00000000-0 |
CS035513000155 | ๆฟๅ ๆไปฃ | 1 | ๅฅณๆง | 1960-06-12 | 58 | 157-0075 | ๆฑไบฌ้ฝไธ็ฐ่ฐทๅบ็ งๅ ฌๅ********** | S13035 | 20150625 | 0-00000000-0 |
CS003512000587 | ๅคงๅฑฑ ๆฒ็ฅ็ตต | 1 | ๅฅณๆง | 1959-04-01 | 59 | 214-0014 | ็ฅๅฅๅท็ๅทๅดๅธๅคๆฉๅบ็ปๆธ********** | S13003 | 20170306 | 0-00000000-0 |
์ค๋ช :
์ด SQL ์ฝ๋๋ customer ํ ์ด๋ธ์์ 10๋ช ์ ๊ณ ๊ฐ ์ค ์์์ ํ์ ์งํฉ์ ์ ํํ๋ ๊ฒ์ผ๋ก, RANDOM() ํจ์๋ ํ ์ด๋ธ์ ๊ฐ ํ์ ๋ํด 0์์ 1 ์ฌ์ด์ ์์์ ๊ฐ์ ์์ฑํ๊ณ , WHERE ๊ตฌ๋ฌธ์ ์์ฑ๋ ๊ฐ์ด 0.01 ์ดํ(์ ํ๋ ํ๋ฅ 1%)์ธ์ง ์ฌ๋ถ์ ๋ฐ๋ผ ํ์ ํํฐ๋งํฉ๋๋ค. ์ ๋ฐ๋ผ ํ์ ํํฐ๋งํฉ๋๋ค.
์ด๋ ํ ์ด๋ธ์ ๋ค๋ฅธ ์กฐ๊ฑด์ด๋ ์์์ ๊ด๊ณ์์ด ๊ฐ ํ์ด ์ ํ๋ ํ๋ฅ ์ด ๋์ผํ๋ค๋ ๊ฒ์ ์๋ฏธํ๋ฉฐ, LIMIT ์ ์ ์กฐ๊ฑด์ ์ถฉ์กฑํ๋ ์ฒ์ 10๊ฐ์ ํ์ผ๋ก ์ถ๋ ฅ์ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
%%sql
-- ์ฝ๋ ์์ 2 (์ ์คํ๊ฒ ํ๋ค๋ฉด. ์นด์ดํธ๋ฅผ ๋ง๋ค์ด ์ถ๋ ฅ ํ์๋ฅผ ๊ณ ์ )
WITH customer_tmp AS(
SELECT
*
,ROW_NUMBER() OVER(ORDER BY RANDOM()) AS row
,COUNT(*) OVER() AS cnt
FROM customer
)
SELECT
customer_id
,customer_name
,gender_cd
,gender
,birth_day
,age
,postal_cd
,address
,application_store_cd
,application_date
,status_cd
FROM customer_tmp
WHERE row <= cnt * 0.01
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | customer_name | gender_cd | gender | birth_day | age | postal_cd | address | application_store_cd | application_date | status_cd |
---|---|---|---|---|---|---|---|---|---|---|
CS027514000069 | ๅๅฑ ใใใ | 9 | ไธๆ | 1967-02-10 | 52 | 251-0033 | ็ฅๅฅๅท็่คๆฒขๅธ็็ฌๅฑฑ********** | S14027 | 20141213 | A-20101018-B |
CS031504000012 | ๅไบ ๅๅบ | 0 | ็ทๆง | 1966-09-19 | 52 | 151-0062 | ๆฑไบฌ้ฝๆธ่ฐทๅบๅ ไปฃใ ๆจ็บ********** | S13031 | 20150310 | 0-00000000-0 |
CS028403000022 | ๆฐๅฃ ไธ้ | 0 | ็ทๆง | 1973-09-12 | 45 | 246-0012 | ็ฅๅฅๅท็ๆจชๆตๅธ็ฌ่ฐทๅบๆฑ้********** | S14028 | 20150905 | 0-00000000-0 |
CS001415000103 | ๆฐด่ฐท ๅๅค | 1 | ๅฅณๆง | 1976-12-08 | 42 | 144-0051 | ๆฑไบฌ้ฝๅคง็ฐๅบ่ฅฟ่ฒ็ฐ********** | S13001 | 20150509 | A-20100925-C |
CS019313000145 | ๆฉ็พ ็ฑ็พๅญ | 1 | ๅฅณๆง | 1985-02-20 | 34 | 173-0033 | ๆฑไบฌ้ฝๆฟๆฉๅบๅคงๅฑฑ่ฅฟ็บ********** | S13019 | 20141226 | 0-00000000-0 |
CS002412000346 | ่ๅท ็พๅ | 1 | ๅฅณๆง | 1973-03-22 | 46 | 187-0045 | ๆฑไบฌ้ฝๅฐๅนณๅธๅญฆๅ่ฅฟ็บ********** | S13002 | 20160808 | 0-00000000-0 |
CS003415000271 | ็จฒๅฃ ้ฅ | 1 | ๅฅณๆง | 1975-12-14 | 43 | 201-0001 | ๆฑไบฌ้ฝ็ๆฑๅธ่ฅฟ้ๅท********** | S13003 | 20160630 | A-20090325-8 |
CS029502000052 | ๅฒกๅด ๅชไธ | 0 | ็ทๆง | 1963-03-28 | 56 | 134-0085 | ๆฑไบฌ้ฝๆฑๆธๅทๅบๅ่่ฅฟ********** | S12029 | 20150803 | 0-00000000-0 |
CS002515000290 | ๅคงๅฑฑ ใฟใ | 1 | ๅฅณๆง | 1959-06-05 | 59 | 185-0023 | ๆฑไบฌ้ฝๅฝๅๅฏบๅธ่ฅฟๅ ็บ********** | S13002 | 20160627 | C-20100730-B |
CS009314000030 | ่ฅฟๅท ๅฅใ | 1 | ๅฅณๆง | 1983-05-15 | 35 | 158-0091 | ๆฑไบฌ้ฝไธ็ฐ่ฐทๅบไธญ็บ********** | S13009 | 20150519 | E-20100910-D |
์ค๋ช :
์ด ์ฝ๋๋ SQL ํ๋ก๊ทธ๋๋ฐ ์ธ์ด๋ฅผ ์ฌ์ฉํ์ฌ "customer" ํ ์ด๋ธ์์ ์์์ ๊ณ ๊ฐ ํ์ ์งํฉ์ ์ ํํ๋ ์ฝ๋์ ๋๋ค.
์ด ์ฝ๋์์๋ ๋จผ์ "customer" ํ ์ด๋ธ์์ ๋ชจ๋ ์ด์ ์ ํํ๊ณ ROW_NUMBER ํจ์์ COUNT ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๋ ๊ฐ์ ์ด์ ์ถ๊ฐํ์ฌ "customer_tmp"๋ผ๋ ์์ ํ ์ด๋ธ์ ์์ฑํฉ๋๋ค.
ROW_NUMBER ํจ์๋ ORDER BY RANDOM() ๊ตฌ๋ฌธ์ ๊ฒฐ๊ณผ์ ๋ฐ๋ผ ํ ์ด๋ธ์ ๊ฐ ํ์ ๊ณ ์ ํ ์ ์๋ฅผ ํ ๋นํ๋ค. ์ด๋ ๊ฒ ํ๋ฉด ํ ์ด๋ธ์ ํ ์์๊ฐ ํจ๊ณผ์ ์ผ๋ก ๋ฌด์์ํ๋๋ค.
OVER() ์ ์ด ์๋ COUNT ํจ์๋ ํ ์ด๋ธ์ ์ด ํ ์๋ฅผ ๊ณ์ฐํ๊ณ ๊ฐ ํ์ ์ด ์๋ฅผ ํฌํจํ๋ "cnt"๋ผ๋ ์๋ก์ด ์ด์ ์์ฑํ๋ค.
๊ทธ๋ฐ ๋ค์ ๋ฉ์ธ SELECT ๋ฌธ์ ROW_NUMBER ํจ์๋ก ์์ฑ๋ "row" ์ด๋ก ํํฐ๋งํ์ฌ ์์ ํ ์ด๋ธ์์ ๊ณ ๊ฐ์ ํ์ ์งํฉ์ ์ ํํ๋ค. ๊ตฌ์ฒด์ ์ผ๋ก COUNT ํจ์๋ก ์์ฑ๋ "cnt" ์ด์ ์ํด ๊ฒฐ์ ๋๋ ์ฒ์ 1%์ ํ์ ์ ํํ๋ค.
๋ง์ง๋ง ์ "LIMIT 10"์ ๊ฒฐ๊ณผ๋ฅผ ํ์ ์งํฉ์ ์ฒ์ 10 ๊ฐ ํ์ผ๋ก ์ ํํ์ฌ ์๋ "customer"ํ ์ด๋ธ์์ 10 ๋ช ์ ๊ณ ๊ฐ ๋ฌด์์ ์ํ์ ํจ๊ณผ์ ์ผ๋ก ๋ฐํํฉ๋๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ํ ์คํธ ๋ฐ ํ์์ ๋ถ์ ๋ชฉ์ ์ผ๋ก ํฐ ํ ์ด๋ธ์์ ๋ฌด์์ ๊ณ ๊ฐ ์ํ์ ์ ํํ๋ ๊ฐ๋จํ ๋ฐฉ๋ฒ์ด๋ค. ์ํ๋ง ๋น์จ(์ด ๊ฒฝ์ฐ 1%)์ ์กฐ์ ํ์ฌ ๋ ํฐ ์ํ์ด๋ ๋ ์์ ์ํ์ ๋ง๋ค ์ ์์ต๋๋ค.
S-076: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์์ ์ฑ๋ณ ์ฝ๋(gender_cd)์ ๋น์จ์ ๋ฐ๋ผ ๋ฌด์์๋ก 10%์ ๋ฐ์ดํฐ๋ฅผ ์ธตํ ์ถ์ถํ๊ณ , ์ฑ๋ณ ์ฝ๋๋ณ๋ก ๊ฑด์๋ฅผ ์ง๊ณํ๋ผ.
%%sql
-- ์ฝ๋ ์์ 1
WITH cusotmer_random AS (
SELECT
customer_id,
gender_cd,
cnt
FROM (
SELECT
gender_cd,
ARRAY_AGG(customer_id ORDER BY RANDOM()) AS customer_r,
COUNT(1) AS cnt
FROM
customer
GROUP BY gender_cd
)sample, UNNEST(customer_r) AS customer_id
),
cusotmer_rownum AS(
SELECT
* ,
ROW_NUMBER() OVER(PARTITION BY gender_cd) AS rn
FROM
cusotmer_random
)
SELECT
gender_cd,
COUNT(1) AS customer_num
FROM
cusotmer_rownum
WHERE
rn <= cnt * 0.1
GROUP BY
gender_cd
;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
gender_cd | customer_num |
---|---|
0 | 298 |
1 | 1791 |
9 | 107 |
์ค๋ช :
์ด SQL ์ฝ๋๋ "customer"๋ผ๋ ํ ์ด๋ธ์์ ์์์ ๊ณ ๊ฐ ์ํ์ ์ ํํ๊ณ , ์ํ ๋ด ๊ฐ ์ฑ๋ณ์ ํด๋นํ๋ ๊ณ ๊ฐ ์๋ฅผ ๊ณ์ฐํ๊ณ ์๋ค.
์๋๋ ์ฝ๋์ ๊ฐ ํํธ๊ฐ ์ํํ๋ ์์ ์ ๋ด์ญ์ ๋๋ค.
WITH ๊ตฌ๋ฌธ: ์ด ๊ตฌ๋ฌธ์ ํ์ SELECT ๋ฌธ์์ ์ฌ์ฉํ ๋ ๊ฐ์ ์์ ํ ์ด๋ธ์ธ 'customer_random'๊ณผ 'customer_rownum'์ ์์ฑํ๋ค.
customer_random ํ ์ด๋ธ. ์ด ํ ์ด๋ธ์ 'customer' ํ ์ด๋ธ์์ ๊ณ ๊ฐ์ ๋ฌด์์ ์ํ์ ์ ํํ๊ณ ๊ฐ ๊ณ ๊ฐ์๊ฒ ์ฑ๋ณ์ ๋ฐ๋ผ 'customer_r' ๊ฐ์ ํ ๋นํ๋ฉฐ, 'cnt' ์ด์ ๊ฐ ์ฑ๋ณ ๊ทธ๋ฃน์ ์ด ๊ณ ๊ฐ ์๋ฅผ ๊ณ์ฐํ๋ค.
customer_rownum ํ ์ด๋ธ. ์ด ํ ์ด๋ธ์ 'customer_random' ํ ์ด๋ธ์ ๊ฐ ๊ณ ๊ฐ์๊ฒ ์ฑ๋ณ๋ก ๊ตฌ๋ถ๋ ํ ๋ฒํธ("rn")๋ฅผ ์ถ๊ฐํ๋ค.
SELECT ๋ฌธ. ์ด ๋ฌธ์ ๊ฐ ์ฑ๋ณ ๊ทธ๋ฃน์์ ํ ๋ฒํธ๊ฐ ํด๋น ์ฑ๋ณ ๊ทธ๋ฃน ๋ด ์ด ๊ณ ๊ฐ ์์ 10% ์ดํ์ธ ๊ณ ๊ฐ ์๋ฅผ ๊ณ์ฐํ๋ค(์ฆ, ํ ๋ฒํธ ๊ธฐ์ค ์์ 10%์ ๊ณ ๊ฐ์ด๋ค). ๊ฒฐ๊ณผ๋ 'gender_cd'์ 'customer_num'์ ๋ ์ด์ ๊ฐ์ง ํ๋ก, 'gender_cd'๋ ์ฑ๋ณ ์ฝ๋, 'customer_num'์ ํด๋น ์ฑ๋ณ ๊ทธ๋ฃน์์ ํ ๋ฒํธ๋ก ์์ 10%์ ์ํ๋ ๊ณ ๊ฐ ์์ด๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ๋ ํฐ ๋ฐ์ดํฐ ์ธํธ์์ ๊ณ ๊ฐ์ ๋ฌด์์ ์ํ์ ์ ํํ๊ณ ๊ทธ ์ฑ๋ณ ๋ถํฌ๋ฅผ ๋ถ์ํ๋ ๋ฐ ํธ๋ฆฌํ๋ค.
ย
%%sql
-- ์ฝ๋ ์์ 2
WITH cusotmer_random AS (
SELECT
* ,
ROW_NUMBER() OVER(PARTITION BY gender_cd ORDER BY RANDOM()) AS rn,
COUNT(1) OVER(PARTITION BY gender_cd) cnt
FROM
customer
)
SELECT
gender_cd,
COUNT(1) AS customer_num
FROM
cusotmer_random
WHERE
rn <= cnt * 0.1
GROUP BY
gender_cd
;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
gender_cd | customer_num |
---|---|
9 | 107 |
0 | 298 |
1 | 1791 |
์ค๋ช :
์ด ์ฝ๋๋ SQL ํ๋ก๊ทธ๋๋ฐ ์ธ์ด๋ฅผ ์ฌ์ฉํ์ฌ "customer" ํ ์ด๋ธ์์ ๊ณ ๊ฐ์ ํ์ ์งํฉ์ ๋ฌด์์๋ก ์ ํํ๊ณ ์ฑ๋ณ์ ๋ฐ๋ผ ๊ทธ๋ฃนํํ์ฌ ๊ฐ ์ฑ๋ณ ๊ทธ๋ฃน์ ๊ณ ๊ฐ ์๋ฅผ ๋ฐํํ๋ ์ฝ๋์ ๋๋ค.
์ด ์ฝ๋์์๋ ๋จผ์ 'customer' ํ ์ด๋ธ์์ ๋ชจ๋ ์ด์ ์ ํํ๊ณ ROW_NUMBER ํจ์์ COUNT ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๋ ๊ฐ์ ์ด์ ์ถ๊ฐํ์ฌ 'cusotmer_random'์ด๋ผ๋ ์์ ํ ์ด๋ธ์ ์์ฑํฉ๋๋ค.
ROW_NUMBER ํจ์๋ ORDER BY RANDOM() ๊ตฌ๋ฌธ์ ๊ฒฐ๊ณผ์ ๋ฐ๋ผ ํ ์ด๋ธ์ ๊ฐ ํ์ ๊ณ ์ ํ ์ ์๋ฅผ ํ ๋นํ๋ค. ์ด๋ ํ ์ด๋ธ์ ํ ์์๋ฅผ ํจ๊ณผ์ ์ผ๋ก ๋ฌด์์ํํฉ๋๋ค.
ROW_NUMBER ํจ์์ PARTITION BY gender_cd ๊ตฌ๋ฌธ์ ๊ฐ ์ฑ๋ณ ๊ทธ๋ฃน์ ๋ํด ๊ฐ๋ณ์ ์ผ๋ก ๋ฌด์์ ์์๋ฅผ ๋ณด์ฅํ๋ค.
OVER(PARTITION BY gender_cd) ์ ์ด ์๋ COUNT ํจ์๋ ๊ฐ ์ฑ๋ณ ๊ทธ๋ฃน์ ์ด ํ ์๋ฅผ ๊ณ์ฐํ๊ณ ๊ฐ ํ์ ์ด ์นด์ดํธ๋ฅผ ํฌํจํ๋ 'cnt'๋ผ๋ ์๋ก์ด ์ด์ ์์ฑํ๋ค.
๋ค์์ผ๋ก ๋ฉ์ธ SELECT ๋ฌธ์ ROW_NUMBER ํจ์๋ก ์์ฑ๋ 'rn' ์ด๋ก ํํฐ๋งํ์ฌ ์์ ํ ์ด๋ธ์์ ๊ณ ๊ฐ์ ํ์ ์งํฉ์ ์ ํํ๋ค. ๊ตฌ์ฒด์ ์ผ๋ก COUNT ํจ์๋ก ์์ฑ๋ "cnt" ์ปฌ๋ผ์ ์ํด ๊ฒฐ์ ๋๋ ๊ฐ ์ฑ๋ณ ๊ทธ๋ฃน ํ์ ์ฒ์ 10%๋ฅผ ์ ํํ๋ค.
WHERE ์ ์ 'cusotmer_random' ํ ์ด๋ธ์ ํ์ ํํฐ๋งํ์ฌ 'rn' ๊ฐ์ด ๊ฐ ์ฑ๋ณ ๊ทธ๋ฃน์ 'cnt' ๊ฐ์ 10% ์ดํ์ธ ๊ฒ๋ง ํฌํจํ๋ค.
๋ง์ง๋ง์ผ๋ก, ๋ฉ์ธ SELECT ๋ฌธ์ gender_cd๋ก ํ์ ๊ทธ๋ฃนํํ๊ณ ๊ฐ ๊ทธ๋ฃน์ ํ ์๋ฅผ ๋ฐํํ์ฌ ํจ๊ณผ์ ์ผ๋ก ๋ฌด์์ ์ํ์ ๊ฐ ์ฑ๋ณ ๊ทธ๋ฃน์ ๊ณ ๊ฐ ์๋ฅผ ์ ๊ณตํฉ๋๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ํฐ ํ ์ด๋ธ์์ ๊ณ ๊ฐ์ ํ์ ์งํฉ์ ๋ฌด์์๋ก ์ ํํ๋ฉด์ ์ํ์ด ์๋ ํ ์ด๋ธ๊ณผ ๋ง์ฐฌ๊ฐ์ง๋ก ๊ฐ ์ฑ๋ณ ๊ทธ๋ฃน์ ๋น์จ์ ํฌํจํ๋๋ก ๋ณด์ฅํ๋ ๋ฐฉ๋ฒ์ด๋ค. ์ํ๋ง ๋น์จ(์ด ๊ฒฝ์ฐ 10%)์ ๋ ํฌ๊ฑฐ๋ ์์ ์ํ์ ๋ง๋ค๊ธฐ ์ํด ์กฐ์ ํ ์ ์์ต๋๋ค.
S-077: ์์์ฆ ๋ช ์ธ์ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ ๊ธ์ก์ ๊ณ ๊ฐ ๋จ์๋ก ํฉ์ฐํ๊ณ , ํฉ์ฐํ ๋งค์ถ ๊ธ์ก์ ํธ์ฐจ๋ฅผ ์ถ์ถํ๋ผ. ๋จ, ์ด์๊ฐ์ ๋งค์ถ๊ธ์ก ํฉ๊ณ๋ฅผ ๋ก๊ทธํํ ํ ํ๊ท ๊ณผ ํ์คํธ์ฐจ๋ฅผ ๊ณ์ฐํ์ฌ ๊ทธ ํ๊ท ์์ 3ฯ ์ด์ ๋ฒ์ด๋ ๊ฒ์ผ๋ก ํ๋ค(์์ฐ๋์, ์์ฉ๋์ ๋ชจ๋ ๊ฐ๋ฅ). ๊ฒฐ๊ณผ๋ 10๊ฑด ํ์ํ๋ผ.
%%sql
WITH sales_amount AS(
SELECT
customer_id,
SUM(amount) AS sum_amount,
LN(SUM(amount) + 0.5) AS log_sum_amount
FROM
receipt
GROUP BY
customer_id
)
SELECT
customer_id,
sum_amount,
log_sum_amount
FROM
sales_amount
CROSS JOIN (
SELECT
AVG(log_sum_amount) AS avg_amount,
STDDEV_POP(log_sum_amount) AS std_amount
FROM sales_amount
) stats_amount
WHERE
ABS(log_sum_amount - avg_amount) / std_amount > 3
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
customer_id | sum_amount | log_sum_amount |
---|---|---|
ZZ000000000000 | 12395003 | 16.332804005823312 |
์ค๋ช :
์ด ์ฝ๋๋ SQL ํ๋ก๊ทธ๋๋ฐ ์ธ์ด๋ฅผ ์ฌ์ฉํ์ฌ ๋์ ๋ณํ๋ ๊ตฌ๋งค ๊ธ์ก์ ๊ธฐ์ค์ผ๋ก ๊ตฌ๋งค ๊ธ์ก ํฉ๊ณ๊ฐ ํ์ค์์ ํฌ๊ฒ ๋ฒ์ด๋ ๊ณ ๊ฐ์ ์๋ณํฉ๋๋ค.
์ด ์ฝ๋์์๋ ๋จผ์ "์์์ฆ" ํ ์ด๋ธ์์ customer_id์ ๊ตฌ๋งค ๊ธ์ก์ ํฉ๊ณ๋ฅผ ์ ํํ๊ณ customer_id๋ก ๊ทธ๋ฃนํํ์ฌ "sales_amount"๋ผ๋ ์์ ํ ์ด๋ธ์ ์์ฑํฉ๋๋ค. ๋ํ, ๊ตฌ๋งค ๊ธ์ก ํฉ๊ณ๋ LN ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๋์ ๋ณํํ์ฌ 0์ ๋์๋ฅผ ์ทจํ์ง ์๋๋ก 0.5๋ฅผ ๋ํฉ๋๋ค.
๋ค์์ผ๋ก ๋ฉ์ธ SELECT ๋ฌธ์ ์์ ํ ์ด๋ธ์์ customer_id, sum_amount, log_sum_amount ์ปฌ๋ผ์ ์ ํํ๋ค. ๋ํ CROSS JOIN์ ์ฌ์ฉํ์ฌ "stats_amount"๋ผ๋ ๋ ๋ค๋ฅธ ์์ ํ ์ด๋ธ์ ์ฐธ์ฌํ๋ค." "stats_amount"๋ "sales_amount" ํ ์ด๋ธ์์ log_sum_amount ์ด์ ํ๊ท ๊ณผ ๋ชจํ์คํธ์ฐจ๋ฅผ ์ ํํ์ฌ ์์ฑํ๋ค.
WHERE ์ ์ "sales_amount" ํ ์ด๋ธ์ ํ์ ํํฐ๋งํ์ฌ log_sum_amount์ ํ๊ท log_sum_amount์ ์ฐจ์ด์ ์ ๋๊ฐ์ log_sum_amount์ ํ์คํธ์ฐจ๋ก ๋๋์ด 3๋ณด๋ค ํฐ ๊ฒ๋ค๋ง ํฌํจํ๋ค. ์ด๋ ๊ตฌ๋งค ๊ธ์ก์ด ํ๊ท ๊ฐ์์ 3ํ์คํธ์ฐจ ์ด์ ๋จ์ด์ ธ ์๋ ๊ณ ๊ฐ์ ์๋ณํ๋ ๋ฐฉ๋ฒ์ด๋ฉฐ, ํต๊ณ ๋ถ์์์ ์ด์๊ฐ์ ์๋ณํ๋ ์ผ๋ฐ์ ์ธ ์๊ณ๊ฐ์ด๋ค.
๋ง์ง๋ง์ผ๋ก, ๋ฉ์ธ SELECT ๋ฌธ์ ์ถ๋ ฅ์ 10์ค๋ก ์ ํํ๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ๊ตฌ๋งค ๊ธ์ก์ ๋์ ์ฒ๋๋ก ๋ณํํ๊ณ ํต๊ณ์ ์ด์์น ํ์ง ๊ธฐ์ค์ ์ ์ฉํ์ฌ ๋ฐ์ดํฐ ์ธํธ์ ํ๊ท ๊ตฌ๋งค ๊ธ์ก์ ๋นํด ํ์ ํ๊ฒ ํฐ ๊ตฌ๋งค ๋๋ ์์ ๊ตฌ๋งค๋ฅผ ํ ๊ณ ๊ฐ์ ์๋ณํ๋ ๋ฐฉ๋ฒ์ด๋ค.
S-078: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ ๊ธ์ก(amount)์ ๊ณ ๊ฐ ๋จ์๋ก ํฉ์ฐํ๊ณ , ํฉ์ฐ๋ ๋งค์ถ ๊ธ์ก์ ์ธ๊ณฝ๊ฐ์ ์ถ์ถํ๋ค. ๋จ, ๊ณ ๊ฐ ID๊ฐ โZโ๋ก ์์ํ๋ ๊ฒ์ ๋นํ์์ ์๋ฏธํ๋ฏ๋ก ์ ์ธํ์ฌ ๊ณ์ฐํ๋ค. ์ฌ๊ธฐ์ ์ด์๊ฐ์ 1์ฌ๋ถ์์ 3์ฌ๋ถ์์ ์ฐจ์ด์ธ IQR์ ์ด์ฉํ์ฌ โ1์ฌ๋ถ์์ -1.5รIQRโ ์ดํ ๋๋ โ3์ฌ๋ถ์์+1.5รIQRโ์ ์ด๊ณผํ๋ ๊ฒ์ผ๋ก ํ๋ค. ๊ฒฐ๊ณผ๋ 10๊ฑด ํ์ํ๋ค.
%%sql
WITH sales_amount AS(
SELECT
customer_id,
SUM(amount) AS sum_amount
FROM
receipt
WHERE
customer_id NOT LIKE 'Z%'
GROUP BY
customer_id
)
SELECT
customer_id,
sum_amount
FROM
sales_amount
CROSS JOIN (
SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY sum_amount) AS amount_25per,
PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY sum_amount) AS amount_75per
FROM sales_amount
) stats_amount
WHERE
sum_amount < amount_25per - (amount_75per - amount_25per) * 1.5
OR amount_75per + (amount_75per - amount_25per) * 1.5 < sum_amount
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sum_amount |
---|---|
CS013415000226 | 8362 |
CS011415000041 | 9454 |
CS014514000004 | 8872 |
CS021514000008 | 12839 |
CS014515000007 | 9763 |
CS040415000220 | 10158 |
CS028415000161 | 8465 |
CS034515000173 | 10074 |
CS022515000065 | 12903 |
CS007514000094 | 15735 |
์ค๋ช :
์ด ์ฝ๋๋ SQL ํ๋ก๊ทธ๋๋ฐ ์ธ์ด๋ฅผ ์ฌ์ฉํ์ฌ ๋ฐ์ดํฐ๋ฒ ์ด์ค๋ฅผ ์ฟผ๋ฆฌํ๊ณ ์๋ค. ๊ตฌ์ฒด์ ์ผ๋ก CTE(Common Table Expression)๋ฅผ ์ฌ์ฉํ์ฌ 'sales_amount'๋ผ๋ ์์ ํ ์ด๋ธ์ ์์ฑํ๊ณ 'receive' ํ ์ด๋ธ์ ๊ฐ ๊ณ ๊ฐ์ ๋ํ ์ด ๋งค์ถ์ก์ ๊ณ์ฐํ๋ค.
CTE์ WHERE ๊ตฌ๋ฌธ์ ID๊ฐ "Z"๋ก ์์ํ๋ ๊ณ ๊ฐ์ ํํฐ๋งํ์ฌ ์ ์ธํ๋ฉฐ, GROUP BY ๊ตฌ๋ฌธ์ ๊ณ ๊ฐ ID๋ณ๋ก ๋งค์ถ์ ๊ทธ๋ฃนํํ๋ค.
๋ฉ์ธ ์ฟผ๋ฆฌ์์๋ 'sales_amount' ํ ์ด๋ธ๊ณผ PERCENTILE_CONT ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๋งค์ถ ๊ธ์ก์ 25๋ฒ์งธ ๋ฐฑ๋ถ์์์ 75๋ฒ์งธ ๋ฐฑ๋ถ์์๋ฅผ ๊ณ์ฐํ๋ ์๋ธ ์ฟผ๋ฆฌ ์ฌ์ด์ CROSS JOIN์ด ์ํ๋๋ค. ์ด ๋ฐฑ๋ถ์์๋ "stats_amount"๋ผ๋ ์์ ํ ์ด๋ธ์ ์ ์ฅ๋๋ค.
๋ง์ง๋ง์ผ๋ก ๋ฉ์ธ ์ฟผ๋ฆฌ์ WHERE ๊ตฌ๋ฌธ์์ 25๋ถ์์๋ 75๋ถ์์์์ ์ฌ๋ถ์์ ๋ฒ์(IQR)์ 1.5๋ฐฐ ์ด์ ๋จ์ด์ ธ ์๋ ๋งค์ถ ๊ธ์ก์ ํํฐ๋งํ์ฌ ์ ์ธํ๋ค. ์ด๋ ๋ฐ์ดํฐ ์ธํธ์ ์ด์๊ฐ์ ์๋ณํ๋ ์ผ๋ฐ์ ์ธ ๋ฐฉ๋ฒ์ด๋ค. ๊ฒฐ๊ณผ๋ ์์ 10๊ฐ ๋ผ์ธ์ผ๋ก ์ ํ๋ฉ๋๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค๋ฅผ ์กฐํํ์ฌ ์ฌ๋ถ์์ ๋ฒ์ ๋ด์ ์์น์ ๋ฐ๋ผ ๋์ข ์ ๊ณ์ ๋นํด ๋น์ ์์ ์ผ๋ก ๋์ ๋๋ ๋ฎ์ ๋งค์ถ ๊ธ์ก์ ๊ฐ์ง ๊ณ ๊ฐ์ ์๋ณํ๋ ๊ฒ์ด๋ค.
S-079: ์ํ ๋ฐ์ดํฐ(df_product)์ ๊ฐ ํญ๋ชฉ์ ๋ํด ๊ฒฐ์ ์๋ฅผ ํ์ธํ๋ผ.
%%sql
SELECT
SUM(
CASE WHEN product_cd IS NULL THEN 1 ELSE 0 END
) AS product_cd,
SUM(
CASE WHEN category_major_cd IS NULL THEN 1 ELSE 0 END
) AS category_major_cd,
SUM(
CASE WHEN category_medium_cd IS NULL THEN 1 ELSE 0 END
) AS category_medium_cd,
SUM(
CASE WHEN category_small_cd IS NULL THEN 1 ELSE 0 END
) AS category_small_cd,
SUM(
CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END
) AS unit_price,
SUM(
CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END
) AS unit_cost
FROM product LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
product_cd | category_major_cd | category_medium_cd | category_small_cd | unit_price | unit_cost |
---|---|---|---|---|---|
0 | 0 | 0 | 0 | 7 | 7 |
์ค๋ช :
์ด ์ฝ๋๋ SQL ํ๋ก๊ทธ๋๋ฐ ์ธ์ด๋ฅผ ์ฌ์ฉํ์ฌ ๋ฐ์ดํฐ๋ฒ ์ด์ค๋ฅผ ์ฟผ๋ฆฌํ๊ณ ์๋ค. ๊ตฌ์ฒด์ ์ผ๋ก "product" ํ ์ด๋ธ์ ์กฐํํ๊ณ SUM ๋ฌธ๊ณผ CASE ๋ฌธ์ ์กฐํฉํ์ฌ ํ ์ด๋ธ์ ๊ฐ ์ด์์ ๋๋ฝ๋ ๊ฐ(NULL)์ ๊ฐ์๋ฅผ ๊ณ์ฐํ๊ณ ์์ต๋๋ค.
์ด ์ฟผ๋ฆฌ์์๋ 6๊ฐ์ ์ปฌ๋ผ์ ์ ํํ๊ณ ํ ์ด๋ธ์ ์ปฌ๋ผ ์ด๋ฆ๊ณผ ์ผ์นํ๋ ๋ณ์นญ์ ๋ถ์ฌํ๊ณ ์๋ค. 'product_cd', 'category_major_cd', 'category_medium_cd', 'category_small_cd', 'unit_price', 'unit_cost'.
๊ฐ ์ปฌ๋ผ์ ๋ํด CASE ๋ฌธ์ ๊ฐ์ด NULL์ธ์ง ์ฌ๋ถ๋ฅผ ํ์ธํ๊ณ , NULL์ด๋ฉด 1์, ๊ทธ๋ ์ง ์์ผ๋ฉด 0์ ๋ฐํํ๋ค. ๊ทธ๋ฆฌ๊ณ SUM ํจ์๋ก 1๊ณผ 0์ ํฉํ์ฌ ๊ฐ ์ด์ NULL ๊ฐ์ ์ด ๊ฐ์๋ฅผ ๊ตฌํ๋ค.
LIMIT 10 ๊ตฌ๋ฌธ์ ๊ฒฐ๊ณผ๋ฅผ ํ ์ด๋ธ์ ์ฒ์ 10๊ฐ์ ํ์ผ๋ก ์ ํํ์ง๋ง, ์ด ๊ฒฝ์ฐ ์์ฝ ํต๊ณ์๋ง ๊ด์ฌ์ด ์๊ธฐ ๋๋ฌธ์ ๋ณ ์๋ฏธ๊ฐ ์์ ๊ฒ์ ๋๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ "product" ํ ์ด๋ธ์ ๋๋ฝ๋ ๋ฐ์ดํฐ๋ฅผ ํ์ธํ๊ณ ๊ฐ ์ด์ ๋๋ฝ๋ ๊ฐ์ ์์ ๋ํ ๊ฐ๋จํ ์์ฝ์ ์ป์ ์ ์๋ ๊ฐ๋จํ ๋ฐฉ๋ฒ์ด๋ค.
ย
S-080: ์ํ ๋ฐ์ดํฐ(df_product) ์ค ์ด๋ ํ ํญ๋ชฉ์ ๊ฒฐ์์ด ๋ฐ์ํ ๋ ์ฝ๋๋ฅผ ๋ชจ๋ ์ญ์ ํ ์๋ก์ด ์ํ ๋ฐ์ดํฐ๋ฅผ ์์ฑํ๋ค. ๋ํ, ์ญ์ ์ ํ์ ๊ฑด์๋ฅผ ํ์ํ๊ณ , 079์์ ํ์ธํ ๊ฑด์๋งํผ ๊ฐ์ํ ๊ฒ๋ ํ์ธํด์ผ ํ๋ค.
%%sql
DROP TABLE IF EXISTS product_1;
CREATE TABLE product_1 AS (
SELECT * FROM product
WHERE unit_price IS NOT NULL AND unit_cost IS NOT NULL
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 10023 rows affected.
[]
%%sql
SELECT '์ญ์ ์ ', COUNT(1) FROM product;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
?column? | count |
---|---|
์ญ์ ์ | 10030 |
%%sql
SELECT '์ญ์ ํ', COUNT(1) FROM product_1;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
?column? | count |
---|---|
์ญ์ ํ | 10023 |
์ค๋ช :
์ด ์ฝ๋๋ SQL ํ๋ก๊ทธ๋๋ฐ ์ธ์ด๋ฅผ ์ฌ์ฉํ์ฌ 'product' ํ ์ด๋ธ์์ 'unit_price' ์ด๊ณผ 'unit_cost' ์ด์ด ๋ชจ๋ NULL์ด ์๋ ํ๋ง ์ ํํ์ฌ 'product_1'์ด๋ผ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๊ณ ์๋ค.
์ฒซ ๋ฒ์งธ ํ์ 'DROP TABLE IF EXISTS product_1;'์ 'product_1' ํ ์ด๋ธ์ด ์ด๋ฏธ ์กด์ฌํ๋์ง ํ์ธํ๊ณ , ์กด์ฌํ๋ ๊ฒฝ์ฐ ์ญ์ ํ์ฌ ์๋ก ์์ฑํ๋ ํ ์ด๋ธ๊ณผ ์ถฉ๋์ ํผํ๊ธฐ ์ํ ์์ ์ฅ์น์ด๋ค.
๋ ๋ฒ์งธ ํ์ 'CREATE TABLE product_1 AS (...) ;'๋ 'product_1'์ด๋ผ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๊ณ , ๊ทธ ๋ด์ฉ์ SELECT ๋ฌธ ๊ฒฐ๊ณผ๋ฅผ ๊ธฐ๋ฐ์ผ๋ก ํ๋ค๋ ๊ฒ์ ์ง์ ํ๋ค.
SELECT ๋ฌธ์ product ํ ์ด๋ธ์์ ๋ชจ๋ ์ด์ ์ ํํ์ง๋ง, WHERE ๊ตฌ๋ฌธ์ด ํฌํจ๋์ด unit_price ๋๋ unit_cost ์ด์ด NULL์ธ ํ์ ํํฐ๋งํ๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ์๋์ "product" ํ ์ด๋ธ์์ "unit_price"์ "unit_cost" ๋ ์ด์ ๋ชจ๋ ์ ํจํ ๋ฐ์ดํฐ๊ฐ ์๋ ํ๋ง ํฌํจํ๋ ์ ํ ์ด๋ธ์ ์์ฑํฉ๋๋ค. ์ด๋ ๋ฐ์ดํฐ๋ฅผ ์ ๋ฆฌํ๊ณ ๋ถ์ ๋ฐ ๋ชจ๋ธ๋ง์ ๋๋นํ๊ธฐ ์ํ ์ผ๋ฐ์ ์ธ ๋ฐฉ๋ฒ์ด๋ค. ๋๋ฝ๋ ๋ฐ์ดํฐ๋ ์ผ๋ถ ๋ถ์ ๊ธฐ๋ฒ์์ ๋ฌธ์ ๋ฅผ ์ผ์ผํฌ ์ ์๊ธฐ ๋๋ฌธ์ด๋ค.
Comment