๋ฐ์ดํฐ ์ฌ์ด์ธ์ค 100๋ฒ์ ๋ ธํฌ(๊ตฌ์กฐํ ๋ฐ์ดํฐ ์ฒ๋ฆฌํธ)- SQL Part 3 (Q41 to Q60)์ ํด์ค์ ๋๋ค.
ย
์ฐธ๊ณ (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-041: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ ๊ธ์ก(amount)์ ๋ ์ง(sales_ymd)๋ณ๋ก ์ง๊ณํ์ฌ, ์ง๋๋ฒ ๋งค์ถ์ด ์์๋ ๋ ๋ก๋ถํฐ์ ๋งค์ถ ๊ธ์ก ์ฆ๊ฐ์ ๊ณ์ฐํ๋ผ. ๊ทธ๋ฆฌ๊ณ ๊ฒฐ๊ณผ๋ฅผ 10๊ฑด ํ์ํ๋ผ.
%%sql
WITH sales_amount_by_date AS (
SELECT
sales_ymd,
SUM(amount) AS amount
FROM receipt
GROUP BY
sales_ymd
),
sales_amount_by_date_with_lag as (
SELECT
sales_ymd,
LAG(sales_ymd, 1) OVER(ORDER BY sales_ymd) lag_ymd,
amount,
LAG(amount, 1) OVER(ORDER BY sales_ymd) AS lag_amount
FROM sales_amount_by_date
)
SELECT
sales_ymd,
amount,
lag_ymd,
lag_amount,
amount - lag_amount AS diff_amount
FROM sales_amount_by_date_with_lag
ORDER BY
sales_ymd
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
sales_ymd | amount | lag_ymd | lag_amount | diff_amount |
---|---|---|---|---|
20170101 | 33723 | None | None | None |
20170102 | 24165 | 20170101 | 33723 | -9558 |
20170103 | 27503 | 20170102 | 24165 | 3338 |
20170104 | 36165 | 20170103 | 27503 | 8662 |
20170105 | 37830 | 20170104 | 36165 | 1665 |
20170106 | 32387 | 20170105 | 37830 | -5443 |
20170107 | 23415 | 20170106 | 32387 | -8972 |
20170108 | 24737 | 20170107 | 23415 | 1322 |
20170109 | 26718 | 20170108 | 24737 | 1981 |
20170110 | 20143 | 20170109 | 26718 | -6575 |
์ค๋ช :
์ด ์ฝ๋๋ SQL ์ธ์ด๋ก ์์ฑ๋์์ผ๋ฉฐ, 'WITH' ์ ์ ์ฌ์ฉํ์ฌ 'sales_amount_by_date'์ 'sales_amount_by_date_with_lag'๋ผ๋ ๋ ๊ฐ์ ์์ ํ ์ด๋ธ์ ์์ฑํ๊ณ ์๋ค. ๊ทธ๋ฆฌ๊ณ 'sales_amount_by_date_with_lag' ํ ์ด๋ธ์์ ํน์ ์ด์ ์ ํํ๊ณ 'LAG' ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ์ฐ์๋ ๋ ์ง ๊ฐ์ ๋งค์ถ ๊ธ์ก ์ฐจ์ด๋ฅผ ๊ณ์ฐํ๊ณ ์์ต๋๋ค.
๊ตฌ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ๋ค์๊ณผ ๊ฐ์ ์ฒ๋ฆฌ๋ฅผ ์ํํ๋ค.
์ฒซ ๋ฒ์งธ 'WITH' ์ ์ 'sales_amount_by_date'๋ผ๋ ์์ ํ ์ด๋ธ์ ์์ฑํ๊ณ 'RECIPATE' ํ ์ด๋ธ์ ๊ฐ ๋ ์ง๋ณ ๋งค์ถ ๊ธ์ก์ ํฉ๊ณ๋ฅผ ๊ณ์ฐํ๋ค.
๋ ๋ฒ์งธ 'WITH' ์ ์ 'sales_amount_by_date_with_lag'๋ผ๋ ๋ ๋ค๋ฅธ ์์ ํ ์ด๋ธ์ ์์ฑํ๊ณ , 'LAG' ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ์ ๋ ์ ๋งค์ถ ๊ธ์ก๊ณผ ์ฐ์๋ ๋ ์ง ์ฌ์ด์ ๋งค์ถ ๊ธ์ก์ ์ฐจ์ด๋ฅผ ๊ณ์ฐํ๋ค.
๋ง์ง๋ง 'SELECT' ๋ฌธ์์๋ 'sales_amount_by_date_with_lag' ํ ์ด๋ธ์์ ํ๋งค์ผ, ํ๋งค๊ธ์ก, ์ ๋ , ์ด์ ํ๋งค๊ธ์ก, ์ฐ์๋ ๋ ์ง์ ํ๋งค๊ธ์ก ์ฐจ์ด ๋ฑ์ ์ด์ ์ ํํ๋ค.
๊ทธ๋ฆฌ๊ณ ๊ฒฐ๊ณผ๋ ํ๋งค ๋ ์ง์ ์ค๋ฆ์ฐจ์์ผ๋ก ์ ๋ ฌ๋์ด ์ฒ์ 10๊ฐ์ ํ์ผ๋ก ์ ํ๋๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋๋ "LAG" ํจ์๋ฅผ ์ฌ์ฉํ์ฌ "์์์ฆ" ํ ์ด๋ธ์ ์ฐ์๋ ๋ ์ง ์ฌ์ด์ ํ๋งค ๊ธ์ก ์ฐจ์ด๋ฅผ ๊ณ์ฐํ๊ณ ์ฒ์ 10๊ฐ์ ํ์ ๊ฒฐ๊ณผ๋ฅผ ํ์ํ๋ค.
S-042: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ ๊ธ์ก(amount)์ ๋ ์ง(sales_ymd)๋ณ๋ก ์ง๊ณํ๊ณ , ๊ฐ ๋ ์ง์ ๋ฐ์ดํฐ์ ๋ํด ์ด์ , ์ ์ , 3ํ ์ ์ ๋งค์ถ์ด ์์๋ ๋ ์ ๋ฐ์ดํฐ๋ฅผ ๊ฒฐํฉํ๋ผ. ๊ทธ๋ฆฌ๊ณ ๊ฒฐ๊ณผ๋ฅผ 10๊ฑด ํ์ํ๋ผ.
%%sql
# ์ฝ๋ ์์ 1: ์ธ๋กํ ์ผ์ด์ค
WITH sales_amount_by_date AS (
SELECT
sales_ymd,
SUM(amount) AS amount
FROM receipt
GROUP BY
sales_ymd
),
sales_amount_lag_date AS (
SELECT
sales_ymd,
LAG(sales_ymd, 3) OVER (ORDER BY sales_ymd) AS lag_date_3,
amount
FROM sales_amount_by_date
)
SELECT
a.sales_ymd,
a.amount,
b.sales_ymd AS lag_ymd,
b.amount AS lag_amount
FROM sales_amount_lag_date a
JOIN sales_amount_lag_date b
ON
(
a.lag_date_3 IS NULL
OR a.lag_date_3 <= b.sales_ymd
)
AND b.sales_ymd < a.sales_ymd
ORDER BY
sales_ymd,
lag_ymd
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
sales_ymd | amount | lag_ymd | lag_amount |
---|---|---|---|
20170102 | 24165 | 20170101 | 33723 |
20170103 | 27503 | 20170101 | 33723 |
20170103 | 27503 | 20170102 | 24165 |
20170104 | 36165 | 20170101 | 33723 |
20170104 | 36165 | 20170102 | 24165 |
20170104 | 36165 | 20170103 | 27503 |
20170105 | 37830 | 20170102 | 24165 |
20170105 | 37830 | 20170103 | 27503 |
20170105 | 37830 | 20170104 | 36165 |
20170106 | 32387 | 20170103 | 27503 |
์ค๋ช :
์ด ์ฝ๋๋ SQL ์ธ์ด๋ก ์์ฑ๋์์ผ๋ฉฐ, 'WITH' ์ ์ ์ฌ์ฉํ์ฌ 'sales_amount_by_date'์ 'sales_amount_lag_date'๋ผ๋ ๋ ๊ฐ์ ์์ ํ ์ด๋ธ์ ์์ฑํ๊ณ ์๋ค. ๊ทธ๋ฆฌ๊ณ 'sales_amount_lag_date' ํ ์ด๋ธ์ ๋ํด ์์ฒด ๊ฒฐํฉ์ ์ํํ์ฌ ํน์ ์กฐ๊ฑด์ ๋ฐ๋ผ ๊ฒฐ๊ณผ๋ฅผ ํํฐ๋งํ๊ณ ์์ต๋๋ค.
๊ตฌ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ๋ค์๊ณผ ๊ฐ์ ์ฒ๋ฆฌ๋ฅผ ์ํํ๊ณ ์์ต๋๋ค.
์ฒซ ๋ฒ์งธ 'WITH' ์ ์ 'sales_amount_by_date'๋ผ๋ ์์ ํ ์ด๋ธ์ ์์ฑํ๊ณ , 'receive' ํ ์ด๋ธ์ ๊ฐ ๋ ์ง๋ณ ๋งค์ถ ๊ธ์ก์ ํฉ๊ณ๋ฅผ ๊ณ์ฐํ๋ค.
๋ ๋ฒ์งธ 'WITH' ์ ์ 'sales_amount_lag_date'๋ผ๋ ๋ ๋ค๋ฅธ ์์ ํ ์ด๋ธ์ ์์ฑํ๊ณ 'LAG' ํจ์๋ฅผ ์ฌ์ฉํ์ฌ 'sales_amount_by_date' ํ ์ด๋ธ์ 3์ผ ์ ํ๋งค์ผ๊ณผ ๊ฐ ๋ ์ง์ ํ๋งค๊ธ์ก์ ๊ณ์ฐํ๋ค.
๋ง์ง๋ง 'SELECT' ๋ฌธ์ 'sales_amount_lag_date' ํ ์ด๋ธ์์ ํน์ ์ด์ ์ ํํ์ฌ ์์ ๊ณผ ์์ฒด ๊ฒฐํฉ์ ์ํํ๋ค. ๊ฒฐํฉ ์กฐ๊ฑด์ ํ ํ์ ํ๋งค์ผ์ด ๋ค๋ฅธ ํ์ ํ๋งค์ผ๋ณด๋ค ์๊ณ , ๋ ๋ฒ์งธ ํ์ ํ๋งค์ผ์ด 3์ผ ์ ์ ์ฒซ ๋ฒ์งธ ํ์ ํ๋งค์ผ ์ดํ์ธ ํ๋ง ํฌํจํ๋๋ก ๊ฒฐ๊ณผ๋ฅผ ํํฐ๋งํ๋ค.
๊ทธ๋ฆฌ๊ณ ๊ฒฐ๊ณผ๋ ํ๋งค์ผ๊ณผ ์ด์ ํ๋งค์ผ์ ์ค๋ฆ์ฐจ์์ผ๋ก ์ ๋ ฌ๋์ด ์ฒ์ 10๊ฐ์ ํ์ผ๋ก ์ ํ๋ฉ๋๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋๋ 'receipt' ํ ์ด๋ธ์ ๊ฐ ๋ ์ง๋ณ ๋งค์ถ ๋ ์ง์ ๋งค์ถ ๊ธ์ก์ด ํฌํจ๋ ํ ์ด๋ธ์ ๋ํด ์์ฒด ๊ฒฐํฉ์ ์คํํ๊ณ ์์ต๋๋ค. ๊ฒฐํฉ ์กฐ๊ฑด์ ํ ํ์ ๋งค์ถ ๋ ์ง๊ฐ ๋ค๋ฅธ ํ์ ๋งค์ถ ๋ ์ง๋ณด๋ค ์๊ณ , ๋ ๋ฒ์งธ ํ์ ๋งค์ถ ๋ ์ง๊ฐ 3์ผ ์ ์ ์ฒซ ๋ฒ์งธ ํ์ ๋งค์ถ ๋ ์ง ์ดํ์ธ ํ๋ง ํฌํจํ๋๋ก ๊ฒฐ๊ณผ๋ฅผ ํํฐ๋งํฉ๋๋ค. ๊ฒฐ๊ณผ๋ ์ฒ์ 10๊ฐ ํ์ ํ๋งค์ผ, ํ๋งค๊ธ์ก, ์ด์ ํ๋งค์ผ, ์ด์ ํ๋งค๊ธ์ก์ด ํ์๋ฉ๋๋ค.
%%sql
# ์ฝ๋ ์์ 2: ๊ฐ๋กํ ์ผ์ด์ค
WITH sales_amount_by_date AS (
SELECT
sales_ymd,
SUM(amount) AS amount
FROM receipt
GROUP BY
sales_ymd
),
sales_amount_with_lag AS (
SELECT
sales_ymd,
amount,
LAG(sales_ymd, 1) OVER (ORDER BY sales_ymd) AS lag_ymd_1,
LAG(amount, 1) OVER (ORDER BY sales_ymd) AS lag_amount_1,
LAG(sales_ymd, 2) OVER (ORDER BY sales_ymd) AS lag_ymd_2,
LAG(amount, 2) OVER (ORDER BY sales_ymd) AS lag_amount_2,
LAG(sales_ymd, 3) OVER (ORDER BY sales_ymd) AS lag_ymd_3,
LAG(amount, 3) OVER (ORDER BY sales_ymd) AS lag_amount_3
FROM sales_amount_by_date
)
SELECT
sales_ymd,
amount,
lag_ymd_1,
lag_amount_1,
lag_ymd_2,
lag_amount_2,
lag_ymd_3,
lag_amount_3
FROM sales_amount_with_lag
WHERE
lag_ymd_3 IS NOT NULL
ORDER BY
sales_ymd
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
sales_ymd | amount | lag_ymd_1 | lag_amount_1 | lag_ymd_2 | lag_amount_2 | lag_ymd_3 | lag_amount_3 |
---|---|---|---|---|---|---|---|
20170104 | 36165 | 20170103 | 27503 | 20170102 | 24165 | 20170101 | 33723 |
20170105 | 37830 | 20170104 | 36165 | 20170103 | 27503 | 20170102 | 24165 |
20170106 | 32387 | 20170105 | 37830 | 20170104 | 36165 | 20170103 | 27503 |
20170107 | 23415 | 20170106 | 32387 | 20170105 | 37830 | 20170104 | 36165 |
20170108 | 24737 | 20170107 | 23415 | 20170106 | 32387 | 20170105 | 37830 |
20170109 | 26718 | 20170108 | 24737 | 20170107 | 23415 | 20170106 | 32387 |
20170110 | 20143 | 20170109 | 26718 | 20170108 | 24737 | 20170107 | 23415 |
20170111 | 24287 | 20170110 | 20143 | 20170109 | 26718 | 20170108 | 24737 |
20170112 | 23526 | 20170111 | 24287 | 20170110 | 20143 | 20170109 | 26718 |
20170113 | 28004 | 20170112 | 23526 | 20170111 | 24287 | 20170110 | 20143 |
์ค๋ช :
์ด ์ฝ๋๋ SQL ์ธ์ด๋ก ์์ฑ๋์์ผ๋ฉฐ, 'WITH' ์ ์ ์ฌ์ฉํ์ฌ 'sales_amount_by_date'๋ผ๋ ์ด๋ฆ์ ์์ ํ ์ด๋ธ์ ์์ฑํ๊ณ ์๋ค. ๋ค์์ผ๋ก 'LAG' ํจ์๋ฅผ ์ฌ์ฉํ์ฌ 'sales_amount_with_lag'๋ผ๋ ์ด๋ฆ์ ๋ ๋ค๋ฅธ ์์ ํ ์ด๋ธ์ ์ถ๊ฐ ์ด์ ์์ฑํ๊ณ ์๋ค. ๋ง์ง๋ง์ผ๋ก 'sales_amount_with_lag' ํ ์ด๋ธ์์ ํน์ ์ปฌ๋ผ์ ์ ํํ์ฌ ํน์ ์กฐ๊ฑด์ ๋ฐ๋ผ ๊ฒฐ๊ณผ๋ฅผ ํํฐ๋งํ๊ณ ์์ต๋๋ค.
๊ตฌ์ฒด์ ์ผ๋ก ์ด ์ฝ๋์์๋ ๋ค์๊ณผ ๊ฐ์ ์ฒ๋ฆฌ๋ฅผ ์ํํ๊ณ ์์ต๋๋ค.
์ฒซ ๋ฒ์งธ "WITH" ์ ์ "sales_amount_by_date"๋ผ๋ ์์ ํ ์ด๋ธ์ ์์ฑํ๊ณ "receive" ํ ์ด๋ธ์ ๊ฐ ๋ ์ง๋ณ ๋งค์ถ ๊ธ์ก์ ํฉ๊ณ๋ฅผ ๊ณ์ฐํ๋ค.
๋ ๋ฒ์งธ "WITH" ์ ์ "sales_amount_with_lag"๋ผ๋ ๋ ๋ค๋ฅธ ์์ ํ ์ด๋ธ์ ์์ฑํ๊ณ "sales_amount_by_date" ํ ์ด๋ธ์ ๊ฐ ๋ ์ง์ ํ๋งค ๊ธ์ก๊ณผ ์ด์ 3 ์ผ๊ฐ์ ํ๋งค ๊ธ์ก์ ๊ณ์ฐํ๊ธฐ ์ํด "LAG" ํจ์๋ฅผ ์ฌ์ฉํฉ๋๋ค.
๋ง์ง๋ง "SELECT" ๋ฌธ์ "sales_amount_with_lag" ํ ์ด๋ธ์์ ํน์ ์ด์ ์ ํํ๊ณ , 3 ์ผ ์ ๋ ์ง๊ฐ NULL์ด ์๋ ํ๋ง ํฌํจํ๋๋ก ๊ฒฐ๊ณผ๋ฅผ ํํฐ๋งํ๋ค. ์ฆ, ํ ์ด๋ธ์ ์ฒซ ๋ ํ์ ์ธ ๋ฒ์งธ ์ด์ ๋ ์ง๊ฐ ์๊ธฐ ๋๋ฌธ์ ์ ์ธ๋๋ค.
๊ทธ๋ฐ ๋ค์ ๊ฒฐ๊ณผ๋ฅผ ํ๋งค ๋ ์ง์ ์ค๋ฆ์ฐจ์์ผ๋ก ์ ๋ ฌํ์ฌ ์ฒ์ 10 ๊ฐ ํ์ผ๋ก ์ ํํฉ๋๋ค.
์์ฝํ๋ฉด, ์ด ์ฝ๋๋ "์์์ฆ" ํ ์ด๋ธ์ ๊ฐ ๋ ์ง์ ํ๋งค ๊ธ์ก์ ํฉ๊ณ๋ฅผ ๊ณ์ฐํ๊ณ "LAG" ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ฐ ๋ ์ง์ ์ด์ 3 ์ผ์ ํ๋งค ๋ ์ง์ ํ๋งค ๊ธ์ก์ ๊ณ์ฐํ๋ค. ๊ฒฐ๊ณผ๋ ๊ฐ ๋ ์ง์ ํ๋งค์ผ๊ณผ ํ๋งค๊ธ์ก, ์ด์ 3์ผ๊ฐ์ ํ๋งค์ผ๊ณผ ํ๋งค๊ธ์ก์ ํ์ํ๋ค. ๊ฒฐ๊ณผ๋ ์ฒ์ ๋ ํ์ ์ธ ๋ฒ์งธ ์ด์ ๋ ์ง๊ฐ ์๊ธฐ ๋๋ฌธ์ ์ ์ธํ๋๋ก ํํฐ๋ง๋๋ค. ๊ฒฐ๊ณผ์ ์ฒ์ 10๊ฐ์ ํ์ ๋งค์ถ ๋ ์ง์ ์ค๋ฆ์ฐจ์์ผ๋ก ํ์๋ฉ๋๋ค.
S-043: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)๋ฅผ ๊ฒฐํฉํ์ฌ ์ฑ๋ณ ์ฝ๋(gender_cd)์ ์ฐ๋ น(age์์ ๊ณ์ฐ)๋ณ ๋งค์ถ ๊ธ์ก(amount)์ ํฉ์ฐํ ๋งค์ถ ์์ฝ ๋ฐ์ดํฐ๋ฅผ ์์ฑํ๋ค. ์ฑ๋ณ ์ฝ๋๋ 0์ ๋จ์ฑ, 1์ ์ฌ์ฑ, 9๋ ์ ์ ์์์ ๋ํ๋ธ๋ค.
๋จ, ํญ๋ชฉ ๊ตฌ์ฑ์ ์ฐ๋ น, ์ฌ์ฑ ๋งค์ถ๊ธ์ก, ๋จ์ฑ ๋งค์ถ๊ธ์ก, ๋จ์ฑ ๋งค์ถ๊ธ์ก, ์ฑ๋ณ ๋ฏธ์ ๋งค์ถ๊ธ์ก์ 4๊ฐ ํญ๋ชฉ์ผ๋ก ๊ตฌ์ฑํ๋ค(์ธ๋ก๋ก ์ฐ๋ น, ๊ฐ๋ก๋ก ์ฑ๋ณ ๊ต์ฐจ ์ง๊ณ). ๋ํ ์ฐ๋ น์ 10์ธ ๋จ์์ ๊ณ๊ธ์ผ๋ก ํ๋ค.
%%sql
DROP TABLE IF EXISTS sales_summary;
CREATE TABLE sales_summary AS
WITH gender_era_amount AS (
SELECT
TRUNC(age / 10) * 10 AS era,
c.gender_cd,
SUM(r.amount) AS amount
FROM customer c
JOIN receipt r
ON
c.customer_id = r.customer_id
GROUP BY
era,
c.gender_cd
)
SELECT
era,
SUM(CASE WHEN gender_cd = '0' THEN amount END) AS male,
SUM(CASE WHEN gender_cd = '1' THEN amount END) AS female,
SUM(CASE WHEN gender_cd = '9' THEN amount END) AS unknown
FROM gender_era_amount
GROUP BY
era
ORDER BY
era
;
SELECT
*
FROM sales_summary
;
* postgresql://padawan:***@db:5432/dsdojo_db Done. 9 rows affected. 9 rows affected.
era | male | female | unknown |
---|---|---|---|
10.0 | 1591 | 149836 | 4317 |
20.0 | 72940 | 1363724 | 44328 |
30.0 | 177322 | 693047 | 50441 |
40.0 | 19355 | 9320791 | 483512 |
50.0 | 54320 | 6685192 | 342923 |
60.0 | 272469 | 987741 | 71418 |
70.0 | 13435 | 29764 | 2427 |
80.0 | 46360 | 262923 | 5111 |
90.0 | None | 6260 | None |
์ค๋ช :
์ด SQL ์ฝ๋์์๋ ์ฐ๋ น๋(์๋)์ ์ฑ๋ณ์ ๋ฐ๋ฅธ ๋งค์ถ ๊ธ์ก์ ํฉ๊ณ๋ฅผ ์ ๋ฆฌํ sales_summary๋ผ๋ ํ ์ด๋ธ์ ์์ฑํฉ๋๋ค.
๋จผ์ ๊ณ ๊ฐ ํ ์ด๋ธ๊ณผ ์์์ฆ ํ ์ด๋ธ์ ๊ฒฐํฉํ์ฌ gender_era_amount๋ผ๋ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ ์์ฑํฉ๋๋ค. ์ด CTE๋ ์ฐ๋ น๋(์๋)์ ์ฑ๋ณ์ ๋ฐ๋ผ ๋ ์ฝ๋๋ฅผ ๊ทธ๋ฃนํํ๊ณ ์ด ๋งค์ถ์ก์ ๊ณ์ฐํ๋ค.
๊ทธ๋ฐ ๋ค์ gender_era_amount CTE์ ๋ฐ์ดํฐ๋ฅผ ์ง๊ณํ์ฌ sales_summary ํ ์ด๋ธ์ด ์์ฑ๋๋ค. ์ด ํ ์ด๋ธ์๋ ์๋, ๋จ์ฑ, ์ฌ์ฑ, ์ฌ์ฑ, ์ ์ ์์์ 4๊ฐ ์ปฌ๋ผ์ด ์์ผ๋ฉฐ, SELECT ๊ตฌ๋ฌธ ๋ด์ SUM(CASE...) ๋ฌธ์ ์ฑ๋ณ๋ณ ๋งค์ถ ๊ธ์ก์ ๋ณ๋์ ์ด๋ก ํผ๋ฒํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค.
๋ง์ง๋ง์ผ๋ก SELECT ๋ฌธ์ ์ฌ์ฉํ์ฌ ์๋ก ์์ฑ๋ sales_summary ํ ์ด๋ธ์์ ๋ชจ๋ ํ์ ๊ฐ์ ธ์ต๋๋ค.
S-044: 043์์ ๋ง๋ ๋งค์ถ ์์ฝ ๋ฐ์ดํฐ(df_sales_summary)๋ ์ฑ๋ณ ๋งค์ถ์ ๊ฐ๋ก๋ก ๋์ดํ ๋ฐ์ดํฐ์๋ค. ์ด ๋ฐ์ดํฐ์์ ์ฑ๋ณ์ ์ธ๋ก๋ก ๊ฐ์ ธ์์ ์ฐ๋ น, ์ฑ๋ณ ์ฝ๋, ๋งค์ถ ๊ธ์ก์ ์ธ ๊ฐ์ง ํญ๋ชฉ์ผ๋ก ๋ณํํ๋ผ. ๋จ, ์ฑ๋ณ ์ฝ๋๋ ๋จ์ฑ์ โ00โ, ์ฌ์ฑ์ โ01โ, ์ ์ ์์์ โ99โ๋ก ํ๋ค.
%%sql
-- SQL์ ์ ํฉํ์ง ์๊ธฐ ๋๋ฌธ์ ๋ค์ ๊ฐ์์ ์ผ๋ก ๊ธฐ์ ํ๋ค(์นดํ
๊ณ ๋ฆฌ ์๊ฐ ๋ง์ ๋๋ ๋งค์ฐ ๊ธด SQL์ด ๋ ์ ์๋ค๋ ์ ์ ์ ์)
SELECT era, '00' AS gender_cd , male AS amount FROM sales_summary
UNION ALL
SELECT era, '01' AS gender_cd, female AS amount FROM sales_summary
UNION ALL
SELECT era, '99' AS gender_cd, unknown AS amount FROM sales_summary
;
* postgresql://padawan:***@db:5432/dsdojo_db 27 rows affected.
era | gender_cd | amount |
---|---|---|
10.0 | 00 | 1591 |
20.0 | 00 | 72940 |
30.0 | 00 | 177322 |
40.0 | 00 | 19355 |
50.0 | 00 | 54320 |
60.0 | 00 | 272469 |
70.0 | 00 | 13435 |
80.0 | 00 | 46360 |
90.0 | 00 | None |
10.0 | 01 | 149836 |
20.0 | 01 | 1363724 |
30.0 | 01 | 693047 |
40.0 | 01 | 9320791 |
50.0 | 01 | 6685192 |
60.0 | 01 | 987741 |
70.0 | 01 | 29764 |
80.0 | 01 | 262923 |
90.0 | 01 | 6260 |
10.0 | 99 | 4317 |
20.0 | 99 | 44328 |
30.0 | 99 | 50441 |
40.0 | 99 | 483512 |
50.0 | 99 | 342923 |
60.0 | 99 | 71418 |
70.0 | 99 | 2427 |
80.0 | 99 | 5111 |
90.0 | 99 | None |
ย
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, sales_summary๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค. ์ด ์ฝ๋์ ๋ชฉ์ ์ sales_summary ํ ์ด๋ธ์ ๋ฐ์ดํฐ๋ฅผ ๋ณด๋ค ์ฝ๊ฒ ๋ถ์ํ ์ ์๋ ํ์์ผ๋ก ๋ณํํ๋ ๊ฒ์ด๋ค.
SELECT ๋ฌธ์ ์ฌ์ฉํ์ฌ sales_summary ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๊ณ , UNION ALL ์ฐ์ฐ์๋ฅผ ์ฌ์ฉํ์ฌ ์ฌ๋ฌ SELECT ๋ฌธ ๊ฒฐ๊ณผ๋ฅผ ํ๋์ ๊ฒฐ๊ณผ ์งํฉ์ผ๋ก ๊ฒฐํฉํ๋ค.
์ฒซ ๋ฒ์งธ SELECT ๋ฌธ์ sales_summary ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ era์ male ์ปฌ๋ผ์ ์ ํํ๋ค. ๋ํ gender_cd๋ผ๋ ์๋ก์ด ์ปฌ๋ผ๋ ํฌํจ๋๋ฉฐ, ์ด ์ปฌ๋ผ์ ๊ฐ์ 00์ด๋ค. ์ด ์ปฌ๋ผ์ ๋ฌธ์์ด ๋ฆฌํฐ๋ด '00' AS gender_cd๋ฅผ ์ฌ์ฉํ์ฌ ์ถ๊ฐ๋์์ผ๋ฉฐ, AS ํค์๋๋ ์ปฌ๋ผ์ ์ด๋ฆ์ gender_cd๋ก ๋ณ๊ฒฝํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด SELECT ๋ฌธ ๊ฒฐ๊ณผ ์งํฉ์๋ era, gender_cd, amount์ ์ธ ๊ฐ์ ์ปฌ๋ผ์ด ํฌํจ๋์ด ์๋ค.
๋ ๋ฒ์งธ SELECT ๋ฌธ์ ์ฒซ ๋ฒ์งธ ๋ฌธ๊ณผ ๋น์ทํ์ง๋ง, era์ female ์ด์ ์ ํํ๊ณ gender_cd ์ด์ 01๋ก ์ค์ ํ๊ณ ์๋ค. ์ด SELECT ๋ฌธ์ ๊ฒฐ๊ณผ ์งํฉ์๋ era, gender_cd, amount์ ์ธ ๊ฐ์ ์ปฌ๋ผ์ด ํฌํจ๋์ด ์๋ค.
์ด SELECT ๋ฌธ ๊ฒฐ๊ณผ ์งํฉ์๋ era, gender_cd, amount ์ธ ๊ฐ์ ์ปฌ๋ผ์ด ํฌํจ๋์ด ์๋ค.
๋ง์ง๋ง์ผ๋ก UNION ALL ์ฐ์ฐ์๋ฅผ ์ฌ์ฉํ์ฌ 3๊ฐ์ SELECT ๋ฌธ ๊ฒฐ๊ณผ ์งํฉ์ ํ๋์ ๊ฒฐ๊ณผ ์งํฉ์ผ๋ก ํฉ์น๋ค. ๊ฒฐ๊ณผ ํ ์ด๋ธ์ era์ gender_cd์ ๊ฐ ์กฐํฉ์ ๋ํด ํ๋์ ํ์ ๊ฐ์ง๋ฉฐ, amount ์ด์ gender_cd์ ๊ฐ์ ๋ฐ๋ผ ๋จ์ฑ, ์ฌ์ฑ ๋๋ ์ ์ ์์ ์ค ํ๋์ ๊ฐ์ ํฌํจํ๊ฒ ๋๋ค.
S-045: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ์๋ ์์ผ(birth_day)์ ๋ ์งํ์ผ๋ก ๋ฐ์ดํฐ๋ฅผ ๋ณด์ ํ๊ณ ์๋ค. ์ด๋ฅผ YYYYMMDD ํ์์ ๋ฌธ์์ด๋ก ๋ณํํ์ฌ ๊ณ ๊ฐ ID(customer_id)์ ํจ๊ป 10๊ฑด ํ์ํ๋ผ.
%%sql
SELECT
customer_id,
TO_CHAR(birth_day, 'YYYYMMDD') AS birth_day
FROM customer
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | birth_day |
---|---|
CS021313000114 | 19810429 |
CS037613000071 | 19520401 |
CS031415000172 | 19761004 |
CS028811000001 | 19330327 |
CS001215000145 | 19950329 |
CS020401000016 | 19740915 |
CS015414000103 | 19770809 |
CS029403000008 | 19730817 |
CS015804000004 | 19310502 |
CS033513000180 | 19620711 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, customer๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค. ์ด ์ฝ๋์ ๋ชฉ์ ์ customer ํ ์ด๋ธ์์ customer_id์ birth_day ์ปฌ๋ผ์ ๊ฐ์ ธ์ค๊ณ , birth_day ์ปฌ๋ผ์ ํน์ ๋ฐฉ์์ผ๋ก ์์์ ์ง์ ํ๋ ๊ฒ์ด๋ค.
SELECT ๋ฌธ์ ์ฌ์ฉํ์ฌ customer ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๊ณ LIMIT ์ ์ ์ฌ์ฉํ์ฌ ๋ฐํ๋๋ ํ ์๋ฅผ 10ํ์ผ๋ก ์ ํํ๊ณ ์๋ค.
TO_CHAR ํจ์๋ birth_day ์ปฌ๋ผ์ ์ง์ ๋ ํ์์ ๋ฌธ์์ด๋ก ๋ณํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด ๊ฒฝ์ฐ ํ์์ 'YYYYMMDD'์ด๋ฉฐ, ์์ผ์ ๋ , ์, ์ผ์ ๋ํ๋ ๋๋ค. ๊ฒฐ๊ณผ ๋ฌธ์์ด์ ๊ธธ์ด๋ 8์์ด๋ฉฐ, ์ฐ๋๋ 4์, ์์ 2์, ์ผ์ 2์๋ก ํํ๋๋ค.
AS ํค์๋๋ ๊ฒฐ๊ณผ ์ปฌ๋ผ์ ์ด๋ฆ์ birth_day๋ก ๋ณ๊ฒฝํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด SELECT ๋ฌธ ๊ฒฐ๊ณผ ์งํฉ์๋ customer_id์ birth_day๋ผ๋ ๋ ๊ฐ์ ์ด์ด ํฌํจ๋์ด ์์ผ๋ฉฐ, birth_day ์ด์๋ ๊ฐ ๊ณ ๊ฐ์ ์๋ ์์ผ์ด ์ง์ ๋ ํ์์ผ๋ก ํฌํจ๋์ด ์์ต๋๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ customer ํ ์ด๋ธ์์ customer_id์ birth_day ์ปฌ๋ผ์ ๊ฐ์ ธ์ค๊ณ birth_day ์ปฌ๋ผ์ ํน์ ๋ฐฉ์์ผ๋ก ์์์ ์ง์ ํ๊ณ ์ถ์ ๋ ์ ์ฉํ๋ค. ์ด ํ์ํ๋ ๋ฐ์ดํฐ๋ ๋ถ์์ด๋ ๋ณด๊ณ ์ ์์ฑ์ ์ ์ฉํฉ๋๋ค.
ย
S-046: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ์ ์ฒญ์ผ(application_date)์ YYYYMMDD ํ์์ ๋ฌธ์์ด ํํ๋ก ๋ฐ์ดํฐ๋ฅผ ๋ณด์ ํ๊ณ ์๋ค. ์ด๋ฅผ ๋ ์งํ์ผ๋ก ๋ณํํ์ฌ ๊ณ ๊ฐ ID(customer_id)์ ํจ๊ป 10๊ฑด์ฉ ํ์ํ๋ผ.
%%sql
SELECT
customer_id,
TO_DATE(application_date, 'YYYYMMDD') AS application_date
FROM customer
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | application_date |
---|---|
CS021313000114 | 2015-09-05 |
CS037613000071 | 2015-04-14 |
CS031415000172 | 2015-05-29 |
CS028811000001 | 2016-01-15 |
CS001215000145 | 2017-06-05 |
CS020401000016 | 2015-02-25 |
CS015414000103 | 2015-07-22 |
CS029403000008 | 2015-05-15 |
CS015804000004 | 2015-06-07 |
CS033513000180 | 2015-07-28 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, customer๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค. ์ด ์ฝ๋์ ๋ชฉ์ ์ customer ํ ์ด๋ธ์์ customer_id ์ด๊ณผ application_date ์ด์ ๊ฐ์ ธ์ application_date ์ด์ ๋ ์ง ํ์์ผ๋ก ๋ณํํ๋ ๊ฒ์ด๋ค.
SELECT ๋ฌธ์ ์ฌ์ฉํ์ฌ customer ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๊ณ LIMIT ์ ์ ์ฌ์ฉํ์ฌ ๋ฐํ๋๋ ํ ์๋ฅผ 10ํ์ผ๋ก ์ ํํ๊ณ ์๋ค.
TO_DATE ํจ์๋ application_date ์ปฌ๋ผ์ ๋ฌธ์์ด์์ ๋ ์ง ํ์์ผ๋ก ๋ณํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด ๊ฒฝ์ฐ ํ์์ 'YYYYMMDD'์ด๋ฉฐ, ์ ์ฒญ์ผ์ ๋ , ์, ์ผ์ ๋ํ๋ธ๋ค. ๊ฒฐ๊ณผ์ ์ผ๋ก ๋ ์ง ๊ฐ์ yyyy-mm-dd ํ์์ด ๋๋ค.
AS ํค์๋๋ ๊ฒฐ๊ณผ ์ปฌ๋ผ์ ์ด๋ฆ์ application_date๋ก ๋ฐ๊พธ๊ธฐ ์ํด ์ฌ์ฉ๋๋ค. ์ด SELECT ๋ฌธ ๊ฒฐ๊ณผ ์งํฉ์๋ customer_id์ application_date๋ผ๋ ๋ ๊ฐ์ ์ปฌ๋ผ์ด ํฌํจ๋๋ฉฐ, application_date ์ปฌ๋ผ์๋ ๊ฐ ๊ณ ๊ฐ์ ์ ์ฒญ ๋ ์ง๊ฐ ๋ ์ง ํ์์ผ๋ก ํฌํจ๋์ด ์๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ๊ณ ๊ฐ ํ ์ด๋ธ์์ customer_id์ application_date ์ปฌ๋ผ์ ๊ฐ์ ธ์ application_date ์ปฌ๋ผ์ ๋ ์ง ํ์์ผ๋ก ๋ณํํ๊ณ ์ถ์ ๋ ์ ์ฉํ๋ค. ์ด ํ์ํ๋ ๋ฐ์ดํฐ๋ ํนํ ๋ ์ง ๊ณ์ฐ์ด๋ ๋น๊ต๊ฐ ํฌํจ๋๋ ๊ฒฝ์ฐ ๋ถ์์ด๋ ๋ณด๊ณ ์ ์์ฑ์ ์ ์ฉํฉ๋๋ค.
S-047: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ์ผ(sales_ymd)์ YYYYMMDD ํ์์ ์ซ์ํ์ผ๋ก ๋ฐ์ดํฐ๋ฅผ ๋ณด์ ํ๊ณ ์๋ค. ์ด๋ฅผ ๋ ์งํ์ผ๋ก ๋ณํํ์ฌ ์์์ฆ ๋ฒํธ(receipt_no), ์์์ฆ ํ์๋ฒํธ(receipt_sub_no)์ ํจ๊ป 10๊ฑด์ฉ ํ์ํ๋ผ.
%%sql
SELECT
receipt_no,
receipt_sub_no,
TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') AS sales_ymd
FROM receipt
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
receipt_no | receipt_sub_no | sales_ymd |
---|---|---|
112 | 1 | 2018-11-03 |
1132 | 2 | 2018-11-18 |
1102 | 1 | 2017-07-12 |
1132 | 1 | 2019-02-05 |
1102 | 2 | 2018-08-21 |
1112 | 1 | 2019-06-05 |
1102 | 2 | 2018-12-05 |
1102 | 1 | 2019-09-22 |
1112 | 2 | 2017-05-04 |
1102 | 1 | 2019-10-10 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, receipt๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค. ์ด ์ฝ๋์ ๋ชฉ์ ์ reciport ํ ์ด๋ธ์์ reciport_no, reciport_sub_no, sales_ymd ์ด์ ๊ฐ์ ธ์ sales_ymd ์ด์ ๋ ์ง ํ์์ผ๋ก ๋ณํํ๋ ๊ฒ์ด๋ค.
SELECT ๋ฌธ์ RECIPATE ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, LIMIT ๊ตฌ๋ฌธ์ ๋ฐํ๋๋ ํ์ ์๋ฅผ 10์ผ๋ก ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
CAST ํจ์๋ sales_ymd ์ปฌ๋ผ์ ์ ์์์ ๋ฌธ์์ด๋ก ๋ณํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ๊ฒฐ๊ณผ ๋ฌธ์์ด์๋ 'YYYYMMDD' ํ์์ผ๋ก ํ๋งค ๋ ์ง์ ๋ , ์, ์ผ์ด ํฌํจ๋ ๊ฒ์ด๋ค.
TO_DATE ํจ์๋ sales_ymd์ ๋ฌธ์์ด์ ๋ ์ง ํ์์ผ๋ก ๋ณํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด ๊ฒฝ์ฐ ํ์์ 'YYYYMMDD'์ด๋ฉฐ, ์ด๋ ํ๋งค ๋ ์ง์ ๋ , ์, ์ผ์ ๋ํ๋ธ๋ค. ๊ฒฐ๊ณผ ๋ ์ง ๊ฐ์ yyyy-mm-dd ํ์์ด ๋๋ค.
AS ํค์๋๋ ๊ฒฐ๊ณผ ์ปฌ๋ผ์ ์ด๋ฆ์ sales_ymd๋ก ๋ณ๊ฒฝํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด SELECT ๋ฌธ ๊ฒฐ๊ณผ ์งํฉ์๋ receive_no, receive_sub_no, sales_ymd์ ์ธ ๊ฐ์ ์ด์ด ํฌํจ๋์ด ์์ผ๋ฉฐ, sales_ymd ์ด์๋ ๊ฐ ์์์ฆ์ ํ๋งค ๋ ์ง๊ฐ ๋ ์ง ํ์์ผ๋ก ํฌํจ๋์ด ์์ต๋๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ receipt ํ ์ด๋ธ์์ receive_no, receive_sub_no, sales_ymd ์ปฌ๋ผ์ ๊ฐ์ ธ์ค๊ณ sales_ymd ์ปฌ๋ผ์ ๋ ์ง ํ์์ผ๋ก ๋ณํํ๊ณ ์ถ์ ๋ ์ ์ฉํ๋ค. ์ด ํ์ํ๋ ๋ฐ์ดํฐ๋ ํนํ ๋ ์ง ๊ณ์ฐ์ด๋ ๋น๊ต๊ฐ ํฌํจ๋๋ ๊ฒฝ์ฐ ๋ถ์์ด๋ ๋ณด๊ณ ์ ์์ฑ์ ์ ์ฉํ๋ค.
S-048: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ ์ํฌํฌ ์ด(sales_epoch)๋ ์ซ์ํ UNIX ์ด๋ก ๋ฐ์ดํฐ๋ฅผ ๋ณด์ ํ๊ณ ์๋ค. ์ด๋ฅผ ๋ ์งํ์ผ๋ก ๋ณํํ์ฌ ์์์ฆ ๋ฒํธ(receipt_no), ์์์ฆ ์๋ธ๋ฒํธ(receipt_sub_no)์ ํจ๊ป 10๊ฑด์ ํ์ํ๋ผ.
%%sql
SELECT
receipt_no,
receipt_sub_no,
CAST(TO_TIMESTAMP(sales_epoch) AS DATE) AS sales_ymd
FROM receipt
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
receipt_no | receipt_sub_no | sales_ymd |
---|---|---|
112 | 1 | 2018-11-03 |
1132 | 2 | 2018-11-18 |
1102 | 1 | 2017-07-12 |
1132 | 1 | 2019-02-05 |
1102 | 2 | 2018-08-21 |
1112 | 1 | 2019-06-05 |
1102 | 2 | 2018-12-05 |
1102 | 1 | 2019-09-22 |
1112 | 2 | 2017-05-04 |
1102 | 1 | 2019-10-10 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, receiport๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค. ์ด ์ฝ๋์ ๋ชฉ์ ์ receive ํ ์ด๋ธ์์ receive_no, receive_sub_no, sales_epoch ์ด์ ๊ฐ์ ธ์ sales_epoch ์ด์ ๋ ์ง ํ์์ผ๋ก ๋ณํํ๋ ๊ฒ์ด๋ค.
SELECT ๋ฌธ์ ์ฌ์ฉํ์ฌ ์์์ฆ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๊ณ LIMIT ์ ์ ์ฌ์ฉํ์ฌ ๋ฐํ๋๋ ํ ์๋ฅผ 10ํ์ผ๋ก ์ ํํ๊ณ ์๋ค.
TO_TIMESTAMP ํจ์๋ฅผ ์ฌ์ฉํ์ฌ sales_epoch ์ปฌ๋ผ์ ์ ์ ๊ฐ์์ ํ์์คํฌํ ํ์์ผ๋ก ๋ณํํ๊ณ ์๋ค. ๊ฒฐ๊ณผ ํ์์คํฌํ๋ 1970๋ 1์ 1์ผ 00:00:00:00 UTC๋ก๋ถํฐ ๋ช ์ด๊ฐ ์ง๋ฌ๋์ง๋ฅผ ๋ํ๋ ๋๋ค.
CAST ํจ์๋ ํ์์คํฌํ๋ฅผ ๋ ์ง ํ์์ผ๋ก ๋ณํํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ์ด ๊ฒฝ์ฐ ๊ฒฐ๊ณผ ๋ ์ง ๊ฐ์ yyyy-mm-dd ํ์์ด ๋๋ค.
AS ํค์๋๋ ๊ฒฐ๊ณผ ์ปฌ๋ผ์ ์ด๋ฆ์ sales_ymd๋ก ๋ณ๊ฒฝํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด SELECT ๋ฌธ ๊ฒฐ๊ณผ ์งํฉ์๋ receive_no, receive_sub_no, sales_ymd ์ธ ๊ฐ์ ์ปฌ๋ผ์ด ํฌํจ๋์ด ์์ผ๋ฉฐ, sales_ymd ์ปฌ๋ผ์๋ ๊ฐ ์์์ฆ์ ํ๋งค ๋ ์ง๊ฐ ๋ ์ง ํ์์ผ๋ก ํฌํจ๋์ด ์๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ receipt ํ ์ด๋ธ์์ receipt_no, receive_sub_no ๋ฐ sales_epoch ์ด์ ๊ฐ์ ธ์ค๊ณ sales_epoch ์ด์ ๋ ์ง ํ์์ผ๋ก ๋ณํํ๊ณ ์ถ์ ๋ ์ ์ฉํ๋ค. ์ด ํ์ํ๋ ๋ฐ์ดํฐ๋ ํนํ ๋ ์ง ๊ณ์ฐ์ด๋ ๋น๊ต๊ฐ ํฌํจ๋ ๊ฒฝ์ฐ ๋ถ์์ด๋ ๋ณด๊ณ ์ ์์ฑ์ ์ ์ฉํ๋ค.
S-049: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ ์ํฌํฌ ์ด(sales_epoch)๋ฅผ ๋ ์งํ์ผ๋ก ๋ณํํ์ฌ โ์ฐ๋โ๋ง ์ถ์ถํ์ฌ ์์์ฆ ๋ฒํธ(receipt_no), ์์์ฆ ํ์ ๋ฒํธ(receipt_sub_no)์ ํจ๊ป 10๊ฑด ํ์ํ๋ผ.
%%sql
SELECT
receipt_no,
receipt_sub_no,
EXTRACT(YEAR FROM TO_TIMESTAMP(sales_epoch)) AS sales_year
FROM receipt
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
receipt_no | receipt_sub_no | sales_year |
---|---|---|
112 | 1 | 2018 |
1132 | 2 | 2018 |
1102 | 1 | 2017 |
1132 | 1 | 2019 |
1102 | 2 | 2018 |
1112 | 1 | 2019 |
1102 | 2 | 2018 |
1102 | 1 | 2019 |
1112 | 2 | 2017 |
1102 | 1 | 2019 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, receiport๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค. ์ด ์ฝ๋์ ๋ชฉ์ ์ receive ํ ์ด๋ธ์์ receive_no, receive_sub_no, sales_epoch ์ด์ ๊ฐ์ ธ์ค๊ณ , sales_epoch์ ํ์์คํฌํ ์ด์์ ์ฐ๋๋ฅผ ์ถ์ถํ๋ ๊ฒ์ด๋ค.
์์์ฆ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๊ธฐ ์ํด SELECT ๋ฌธ์ ์ฌ์ฉํ๊ณ , LIMIT ์ ์ ์ฌ์ฉํ์ฌ ๋ฐํ๋๋ ํ ์๋ฅผ 10ํ์ผ๋ก ์ ํํ๊ณ ์๋ค.
TO_TIMESTAMP ํจ์๋ฅผ ์ฌ์ฉํ์ฌ sales_epoch ์ด์ ์ ์ ๊ฐ์์ ํ์์คํฌํ ํ์์ผ๋ก ๋ณํํ๋ค. ๊ฒฐ๊ณผ ํ์์คํฌํ๋ 1970๋ 1์ 1์ผ 00:00:00:00 UTC๋ก๋ถํฐ์ ์ด ๋จ์๊ฐ ๋๋ค.
EXTRACT ํจ์๋ sales_epoch์ ํ์์คํฌํ์์ ์ฐ๋๋ฅผ ์ถ์ถํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ๊ฒฐ๊ณผ ๊ฐ์ ์ฐ๋๋ฅผ ๋ํ๋ด๋ ์ ์๊ฐ ๋ฉ๋๋ค.
AS ํค์๋๋ ๊ฒฐ๊ณผ ์ปฌ๋ผ์ ์ด๋ฆ์ sales_year๋ก ๋ณ๊ฒฝํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ์ด SELECT ๋ฌธ ๊ฒฐ๊ณผ ์งํฉ์๋ receive_no, receive_sub_no, sales_year์ ์ธ ๊ฐ์ ์ด์ด ํฌํจ๋์ด ์์ผ๋ฉฐ, sales_year ์ด์๋ ๊ฐ ์์์ฆ์ ํ๋งค์ผ ์ฐ๋๊ฐ ํฌํจ๋์ด ์๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ receipt ํ ์ด๋ธ์์ receipt_no, receive_sub_no ๋ฐ sales_epoch ์ด์ ๊ฐ์ ธ์ค๊ณ sales_epoch ํ์์คํฌํ ์ด์์ ์ฐ๋๋ฅผ ์ถ์ถํ๊ณ ์ถ์ ๋ ์ ์ฉํ๋ค. ์ด ์ถ์ถ๋ ๋ฐ์ดํฐ๋ ํ๋งค์ผ ์ฐ๋์ ๋ฐ๋ผ ๋ฐ์ดํฐ๋ฅผ ๊ทธ๋ฃนํํ๊ฑฐ๋ ํํฐ๋งํ๋ ๋ฐ ์ ์ฉํ๋ค.
S-050: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ ์ํฌํฌ ์ด(sales_epoch)๋ฅผ ๋ ์งํ์ผ๋ก ๋ณํํ์ฌ โ์โ๋ง ์ถ์ถํ์ฌ ์์์ฆ ๋ฒํธ(receipt_no), ์์์ฆ ํ์ ๋ฒํธ(receipt_sub_no)์ ํจ๊ป 10๊ฑด์ ํ์ํ๋ค. ๋จ, โ์โ์ 0์ผ๋ก ์ฑ์์ง 2์๋ฆฌ๋ก ์ถ์ถํ๋ค.
%%sql
SELECT
receipt_no,
receipt_sub_no,
TO_CHAR(
EXTRACT(MONTH FROM TO_TIMESTAMP(sales_epoch)),
'FM00'
) AS sales_month
FROM receipt
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
receipt_no | receipt_sub_no | sales_month |
---|---|---|
112 | 1 | 11 |
1132 | 2 | 11 |
1102 | 1 | 07 |
1132 | 1 | 02 |
1102 | 2 | 08 |
1112 | 1 | 06 |
1102 | 2 | 12 |
1102 | 1 | 09 |
1112 | 2 | 05 |
1102 | 1 | 10 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, receiport๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค. ์ด ์ฝ๋์ ๋ชฉ์ ์ receiport ํ ์ด๋ธ์์ receiport_no, receiport_sub_no, sales_epoch ์ด์ ๊ฐ์ ธ์ค๊ณ , sales_epoch ํ์์คํฌํ ์ด์์ ์์ ์ถ์ถํ๊ณ , ๊ฒฐ๊ณผ ์์ ์์ 0์ด ๋ ์๋ฆฌ ์ซ์๋ก ๋ ๋ฌธ์์ด๋ก ํฌ๋งทํ๋ ๊ฒ์ด๋ค.
SELECT ๋ฌธ์ ์์์ฆ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, LIMIT ๊ตฌ๋ฌธ์ ๋ฐํ๋๋ ํ ์๋ฅผ 10์ผ๋ก ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
TO_TIMESTAMP ํจ์๋ฅผ ์ฌ์ฉํ์ฌ sales_epoch ์ปฌ๋ผ์ ์ ์ ๊ฐ์์ ํ์์คํฌํ ํ์์ผ๋ก ๋ณํํ๊ณ ์๋ค. ๊ฒฐ๊ณผ ํ์์คํฌํ๋ 1970๋ 1์ 1์ผ 00:00:00:00 UTC๋ก๋ถํฐ์ ์ด ๋จ์๊ฐ ๋๋ค.
EXTRACT ํจ์๋ sales_epoch์ ํ์์คํฌํ์์ ์์ ์ถ์ถํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ๊ฒฐ๊ณผ ๊ฐ์ ์์ ๋ํ๋ด๋ ์ ์๊ฐ ๋ฉ๋๋ค.
TO_CHAR ํจ์๋ ์ถ์ถ๋ ์์ ํ์์ ๋ฐ๋ผ ์์ 0์ด ํฌํจ๋ ๋ฌธ์์ด๋ก ๋ณํํ๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, 'FM00' ํ์ ๋ฌธ์์ด์ ๊ฒฐ๊ณผ ๋ฌธ์์ด์ด ๊ณ ์ ๋ ํญ์ 2์์ด๋ฉฐ ์์ 0์ ์ ๊ฑฐ๋๋ค๋ ๊ฒ์ ์ง์ ํ๋ค.
AS ํค์๋๋ ๊ฒฐ๊ณผ ์ปฌ๋ผ์ ์ด๋ฆ์ sales_month๋ก ๋ณ๊ฒฝํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด SELECT ๋ฌธ ๊ฒฐ๊ณผ ์งํฉ์๋ receive_no, receive_sub_no ๋ฐ sales_month์ ์ธ ๊ฐ์ ์ด์ด ํฌํจ๋์ด ์์ผ๋ฉฐ, sales_month ์ด์๋ ๊ฐ ์์์ฆ์ ํ๋งค ๋ ์ง์ ์์ด ๋งจ ์์ 0์ ํฌํจํ 2์๋ฆฌ ๋ฌธ์์ด๋ก ํฌํจ๋์ด ์๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ reciport ํ ์ด๋ธ์์ reciport_no, reciport_sub_no ๋ฐ sales_epoch ์ด์ ๊ฐ์ ธ์ sales_epoch ํ์์คํฌํ ์ด์์ ์์ ์ถ์ถํ๊ณ ๊ฒฐ๊ณผ ์์ ์์ 0์ด ํฌํจ๋ 2์๋ฆฌ ๋ฌธ์์ด๋ก ์์์ ์ง์ ํ๋ค. ์ํ๋ ๊ฒฝ์ฐ์ ์ ์ฉํ๋ค. ์ด ํ์ํ๋ ๋ฐ์ดํฐ๋ ํ๋งค์ผ ์์ ๊ธฐ์ค์ผ๋ก ๋ฐ์ดํฐ๋ฅผ ๊ทธ๋ฃนํํ๊ฑฐ๋ ํํฐ๋งํ๋ ๋ฐ ์ ์ฉํ๋ค.
ย
S-051: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ ์ํฌํฌ ์ด๋ฅผ ๋ ์งํ์ผ๋ก ๋ณํํ์ฌ โ์ผโ๋ง ์ถ์ถํ์ฌ ์์์ฆ ๋ฒํธ(receipt_no), ์์์ฆ ํ์ ๋ฒํธ(receipt_sub_no)์ ํจ๊ป 10๊ฑด ํ์ํ๋ค. ๋จ, โ์ผโ์ 0์ผ๋ก ์ฑ์์ง 2์๋ฆฌ๋ก ์ถ์ถํ๋ค.
%%sql
SELECT
receipt_no, receipt_sub_no,
TO_CHAR(EXTRACT(DAY FROM TO_TIMESTAMP(sales_epoch)), 'FM00') AS sales_day
FROM receipt LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
receipt_no | receipt_sub_no | sales_day |
---|---|---|
112 | 1 | 03 |
1132 | 2 | 18 |
1102 | 1 | 12 |
1132 | 1 | 05 |
1102 | 2 | 21 |
1112 | 1 | 05 |
1102 | 2 | 05 |
1102 | 1 | 22 |
1112 | 2 | 04 |
1102 | 1 | 10 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ, receiport๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค. ์ด ์ฝ๋์ ๋ชฉ์ ์ receiport ํ ์ด๋ธ์์ receiport_no, receiport_sub_no, sales_epoch ์ด์ ๊ฐ์ ธ์ค๊ณ , sales_epoch ํ์์คํฌํ ์ด์์ ๋ ์ง๋ฅผ ์ถ์ถํ๊ณ , ๊ฒฐ๊ณผ ๋ ์ง๋ฅผ ์์ 0์ด ๋ ์๋ฆฌ ์ซ์๋ก ๋ ๋ฌธ์์ด๋ก ํฌ๋งทํ๋ ๊ฒ์ด๋ค.
SELECT ๋ฌธ์ ์์์ฆ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, LIMIT ๊ตฌ๋ฌธ์ ๋ฐํ๋๋ ํ ์๋ฅผ 10์ผ๋ก ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
TO_TIMESTAMP ํจ์๋ฅผ ์ฌ์ฉํ์ฌ sales_epoch ์ปฌ๋ผ์ ์ ์ ๊ฐ์์ ํ์์คํฌํ ํ์์ผ๋ก ๋ณํํ๊ณ ์๋ค. ๊ฒฐ๊ณผ ํ์์คํฌํ๋ 1970๋ 1์ 1์ผ 00:00:00:00 UTC๋ก๋ถํฐ์ ์ด ๋จ์๊ฐ ๋๋ค.
EXTRACT ํจ์๋ sales_epoch์ ํ์์คํฌํ์์ ๋ ์ง๋ฅผ ์ถ์ถํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ๊ฒฐ๊ณผ ๊ฐ์ ๋ ์ง๋ฅผ ๋ํ๋ด๋ ์ ์๊ฐ ๋ฉ๋๋ค.
TO_CHAR ํจ์๋ ์ถ์ถ๋ ๋ ์ง๋ฅผ ํ์์ ๋ฐ๋ผ ์์ 0์ด ํฌํจ๋ ๋ฌธ์์ด๋ก ๋ณํํ๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, 'FM00' ํ์ ๋ฌธ์์ด์ ๊ฒฐ๊ณผ ๋ฌธ์์ด์ด ๊ณ ์ ๋ ํญ์ 2์์ด๋ฉฐ ์์ 0์ด ์ ๊ฑฐ๋๋ค๋ ๊ฒ์ ์ง์ ํ๋ค.
AS ํค์๋๋ ๊ฒฐ๊ณผ ์ปฌ๋ผ์ ์ด๋ฆ์ sales_day๋ก ๋ณ๊ฒฝํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด SELECT ๋ฌธ ๊ฒฐ๊ณผ ์งํฉ์๋ receive_no, receive_sub_no, sales_day์ ์ธ ๊ฐ์ ์ด์ด ํฌํจ๋์ด ์์ผ๋ฉฐ, sales_day ์ด์๋ ๊ฐ ์์์ฆ์ ํ๋งค์ผ ๋ ์ง๊ฐ ๋งจ ์์ 0์ ํฌํจํ 2์๋ฆฌ ๋ฌธ์์ด๋ก ํฌํจ๋์ด ์๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ reciport ํ ์ด๋ธ์์ reciport_no, reciport_sub_no, sales_epoch ์ปฌ๋ผ์ ๊ฐ์ ธ์ค๊ณ , sales_epoch ํ์์คํฌํ ์ปฌ๋ผ์์ ๋ ์ง๋ฅผ ์ถ์ถํ๊ณ , ๊ฒฐ๊ณผ ๋ ์ง๋ฅผ ์์ 0์ด ํฌํจ๋ ๋ ์๋ฆฌ ๋ฌธ์์ด๋ก ํฌ๋งทํ๊ณ ์ถ์ ๋ ์ ์ฉํ๋ค. ์ด ํ์ํ๋ ๋ฐ์ดํฐ๋ ํ๋งค์ผ ๋ ์ง๋ฅผ ๊ธฐ์ค์ผ๋ก ๋ฐ์ดํฐ๋ฅผ ๊ทธ๋ฃนํํ๊ฑฐ๋ ํํฐ๋งํ๋ ๋ฐ ์ ์ฉํ๋ค.
ย
S-052: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ ๊ธ์ก(amount)์ ๊ณ ๊ฐ ID(customer_id)๋ณ๋ก ํฉ์ฐํ ํ, ๋งค์ถ ๊ธ์ก ์ดํฉ์ ๋ํด 2,000์ ์ดํ๋ฅผ 0, 2,000์๋ณด๋ค ํฐ ๊ธ์ก์ 1๋ก ์ด๋ถํํ์ฌ ๊ณ ๊ฐ ID, ๋งค์ถ ๊ธ์ก ์ดํฉ๊ณผ ํจ๊ป 10๊ฑด์ฉ ํ์ํ๋ค. ๋จ, ๊ณ ๊ฐ ID๊ฐ โZโ๋ก ์์ํ๋ ๊ฒ์ ๋นํ์์ ์๋ฏธํ๋ฏ๋ก ์ ์ธํ์ฌ ๊ณ์ฐํ๋ค.
%%sql
SELECT
customer_id,
SUM(amount) AS sum_amount,
CASE
WHEN SUM(amount) > 2000 THEN 1
ELSE 0
END AS sales_flg
FROM receipt
WHERE customer_id NOT LIKE 'Z%'
GROUP BY customer_id
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sum_amount | sales_flg |
---|---|---|
CS001311000059 | 2302 | 1 |
CS004614000122 | 248 | 0 |
CS003512000043 | 298 | 0 |
CS011615000061 | 246 | 0 |
CS029212000033 | 3604 | 1 |
CS007515000119 | 7157 | 1 |
CS034515000123 | 3699 | 1 |
CS004315000058 | 490 | 0 |
CS026414000014 | 6671 | 1 |
CS001615000099 | 768 | 0 |
์ค๋ช :
์ด SQL ์ฝ๋๋ receive๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํ๊ณ ์๋ค. ์ด ์ฝ๋์ ๋ชฉ์ ์ RECIPATE ํ ์ด๋ธ์ ๊ฐ ๊ณ ๊ฐ์ ๋งค์ถ ์ด์ก์ ๊ณ์ฐํ๋ ๊ฒ๊ณผ ๊ฐ ๊ณ ๊ฐ์ ๋งค์ถ ์ด์ก์ด 2000๋ณด๋ค ํฐ์ง ์ฌ๋ถ๋ฅผ ๋ํ๋ด๋ ํ๋๊ทธ๋ฅผ ์์ฑํ๋ ๊ฒ์ ๋๋ค.
SELECT ๋ฌธ์ customer_id ์ด, SUM ํจ์๋ก ๊ณ์ฐํ ๊ฐ ๊ณ ๊ฐ์ ๋งค์ถ ์ด์ก, ๋งค์ถ ์ด์ก์ด 2000๋ณด๋ค ํฌ๋ฉด 1, ๊ทธ๋ ์ง ์์ผ๋ฉด 0์ ๋ฐํํ๋ CASE ์์ ๊ฐ์ ธ์จ๋ค. ํ๋๊ทธ๊ฐ ํฌํจ๋ ๊ฒฐ๊ณผ ์ปฌ๋ผ์ sales_flg๋ก ๋ช ๋ช ํ๋ค.
WHERE ์ ์ ๊ณ ๊ฐ ID๊ฐ 'Z'๋ก ์์ํ๋ ๋ ์ฝ๋๋ฅผ ํํฐ๋งํ์ฌ ์ ์ธํ๋ค. ์ด๋ ํน์ ๊ณ ๊ฐ์ ๊ณ์ฐ์ด๋ ํ๋๊ทธ ์ค์ ์์ ์ ์ธํ๋ ๋ฐ ์ฌ์ฉํ ์ ์์ต๋๋ค.
GROUP BY ๊ตฌ๋ฌธ์ ๊ณ ๊ฐ๋ณ ์ด ๋งค์ถ ๊ธ์ก๊ณผ ํ๋๊ทธ๊ฐ ๊ณ์ฐ๋๋๋ก ๊ฒฐ๊ณผ๋ฅผ ๊ณ ๊ฐ ID๋ณ๋ก ๊ทธ๋ฃนํํฉ๋๋ค.
LIMIT ์ ์ ์ถ๋ ฅ์ ์ฒ์ 10๊ฐ์ ๊ฒฐ๊ณผ๋ก ์ ํํฉ๋๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ์์์ฆ ํ ์ด๋ธ์ ๊ฐ ๊ณ ๊ฐ์ ๋ํ ์ด ํ๋งค์ก์ ๊ณ์ฐํ๊ณ ์ด ํ๋งค์ก์ด ํน์ ์๊ณ๊ฐ(์ด ๊ฒฝ์ฐ 2000)๋ณด๋ค ํฐ์ง ์ฌ๋ถ๋ฅผ ๋ํ๋ด๋ ํ๋๊ทธ๋ฅผ ์์ฑํ๊ณ ์ ํ ๋ ์ ์ฉํฉ๋๋ค. ์ด๋ ๊ณ ์ก ์ผํ์ ํ๋ ๊ณ ๊ฐ์ ์๋ณํ๊ฑฐ๋ ๊ณ ๊ฐ์ ๋งค์ถ ๊ธ์ก์ ์ง๊ณํ์ฌ ๋ถ์ํด์ผ ํ๋ ๋ค๋ฅธ ์ ํ์ ๋ถ์์ ์ ์ฉํ ์ ์์ต๋๋ค.
ย
S-053: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ์ฐํธ๋ฒํธ(postal_cd)์ ๋ํด ๋์ฟ(์ 3์๋ฆฌ๊ฐ 100~209์ธ ๊ฒ)๋ฅผ 1, ๊ทธ ์ธ์ ๊ฒ์ 0์ผ๋ก ์ด์งํํ๋ผ. ๋ํ ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๊ฒฐํฉํ์ฌ ์ ์ฒด ๊ธฐ๊ฐ ๋์ ๋งค์ถ ์ค์ ์ด ์๋ ๊ณ ๊ฐ ์๋ฅผ ์์ฑํ ์ดํญ๋์๋ณ๋ก ๊ณ์ฐํ๋ผ.
%%sql
WITH cust AS (
SELECT
customer_id,
postal_cd,
CASE
WHEN CAST(SUBSTR(postal_cd, 1, 3) AS INTEGER) BETWEEN 100 AND 209 THEN 1
ELSE 0
END AS postal_flg
FROM
customer
),
rect AS(
SELECT DISTINCT
customer_id
FROM
receipt
)
SELECT
c.postal_flg,
COUNT(DISTINCT c.customer_id) AS customer_cnt
FROM
cust c
JOIN
rect r
USING (customer_id)
GROUP BY
c.postal_flg
;
* postgresql://padawan:***@db:5432/dsdojo_db 2 rows affected.
postal_flg | customer_cnt |
---|---|
0 | 3906 |
1 | 4400 |
์ค๋ช :
์ด SQL ์ฝ๋์๋ cust๋ผ๋ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ด ํฌํจ๋์ด ์์ผ๋ฉฐ, customer ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํ์ฌ postal_cd๋ฅผ ๊ธฐ๋ฐ์ผ๋ก ๊ฐ ๊ณ ๊ฐ์ postal_flg ํ๋๊ทธ๋ฅผ ๊ณ์ฐํ๊ณ ์์ต๋๋ค.
postal_flg ํ๋๊ทธ๋ CASE ์์์ ์ฌ์ฉํ์ฌ ๊ณ์ฐ๋๋ฉฐ, postal_cd์ ์ฒซ ์ธ ๊ธ์๊ฐ 100์์ 209(ํฌํจ)๊น์ง์ธ์ง ํ์ธํ๊ณ , ์ฐธ์ด๋ฉด 1, ๊ฑฐ์ง์ด๋ฉด 0์ด๋ผ๋ ๊ฐ์ ํ ๋นํ๋ค. ์ด ํ๋๊ทธ๋ ์๋ฅผ ๋ค์ด ์ฐํธ๋ฒํธ ๋ฒ์์ ๋ฐ๋ผ ๊ณ ๊ฐ์ ๊ทธ๋ฃนํํ๋ ๋ฐ ์ฌ์ฉํ ์ ์๋ค.
๋ ๋ฒ์งธ CTE์ธ rect๋ recipate ํ ์ด๋ธ์์ ๊ฐ๋ณ customer_id๋ฅผ ์ ํํ๋ค.
๋ง์ง๋ง SELECT ๋ฌธ์ customer_id ์ด์์ cust์ rect CTE๋ฅผ ๊ฒฐํฉํ๊ณ , postal_flg ์ด์์ ๊ฒฐ๊ณผ๋ฅผ ๊ทธ๋ฃนํํ๋ฉฐ, COUNT(DISTINCT c.customer_id) ํจ์๋ ๊ฐ postal_flg ๊ฐ์ ๋ํด ์๋ก ๋ค๋ฅธ ๊ณ ๊ฐ ์๋ฅผ ๊ณ์ฐํ๋ค. ๋ฅผ ๊ณ์ฐํฉ๋๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ๊ณ ๊ฐ ํ ์ด๋ธ๊ณผ ์์์ฆ ํ ์ด๋ธ์ ์ ์ฅ๋ ๋ฐ์ดํฐ๋ฅผ ๊ธฐ๋ฐ์ผ๋ก ์ฐํธ๋ฒํธ ๋ฒ์์ ๋ฐ๋ผ ๊ณ ๊ฐ์ ๊ทธ๋ฃนํํ๊ณ ๊ฐ ๋ฒ์์ ๋ค๋ฅธ ๊ณ ๊ฐ ์๋ฅผ ๊ณ์ฐํ๊ณ ์ถ์ ๋ ์ ์ฉํ๋ค.
ย
S-054: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ์ฃผ์(address)๋ ์ฌ์ดํ๋งํ, ์ง๋ฐํ, ๋์ฟ๋, ๊ฐ๋๊ฐ์ํ ์ค ํ๋์ด๋ค. ๋๋๋ถํ๋ณ๋ก ์ฝ๋ ๊ฐ์ ์์ฑํ์ฌ ๊ณ ๊ฐ ID, ์ฃผ์์ ํจ๊ป 10๊ฑด์ฉ ํ์ํ๋ผ. ๊ฐ์ ์ฌ์ดํ๋งํ์ 11, ์ง๋ฐํ์ 12, ๋์ฟ๋๋ฅผ 13, ๊ฐ๋๊ฐ์ํ์ 14๋ก ํ๋ค.
%%sql
-- SQL์ ์ ํฉํ์ง ์๊ธฐ ๋๋ฌธ์ ๋ค์ ๊ฐ์์ ์ผ๋ก ๊ธฐ์ ํ๋ค(์นดํ
๊ณ ๋ฆฌ ์๊ฐ ๋ง์ผ๋ฉด ๋งค์ฐ ๊ธด SQL์ด ๋๋ค๋ ์ ์ ์ฃผ์)
-- ์ฝ๋ ์์ 1(๊ณ ์ ์ผ๋ก ์๋ผ๋ด๊ธฐ)
SELECT
customer_id,
address,
CASE SUBSTR(address,1, 3)
WHEN 'ๅผ็็' THEN '11'
WHEN 'ๅ่็' THEN '12'
WHEN 'ๆฑไบฌ้ฝ' THEN '13'
WHEN '็ฅๅฅๅท' THEN '14'
END AS prefecture_cd
FROM
customer
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | address | prefecture_cd |
---|---|---|
CS021313000114 | ็ฅๅฅๅท็ไผๅขๅๅธ็ฒ็ชช********** | 14 |
CS037613000071 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | 13 |
CS031415000172 | ๆฑไบฌ้ฝๆธ่ฐทๅบไปฃใ ๆจ********** | 13 |
CS028811000001 | ็ฅๅฅๅท็ๆจชๆตๅธๆณๅบๅๆณ็บ********** | 14 |
CS001215000145 | ๆฑไบฌ้ฝๅคง็ฐๅบไปฒๅ ญ้ท********** | 13 |
CS020401000016 | ๆฑไบฌ้ฝๆฟๆฉๅบ่ฅๆจ********** | 13 |
CS015414000103 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | 13 |
CS029403000008 | ๅ่็ๆตฆๅฎๅธๆตทๆฅฝ********** | 12 |
CS015804000004 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | 13 |
CS033513000180 | ็ฅๅฅๅท็ๆจชๆตๅธๆญๅบๅ้จ็บ********** | 14 |
์ค๋ช :
์ด SQL ์ฝ๋๋ customer ํ ์ด๋ธ์์ ๊ฐ ๊ณ ๊ฐ์ customer_id, address, prefecture_cd code๋ฅผ ์ ํํ๋ค.
์ด ์ฝ๋์ CASE ํํ์์ address ์ด์ ์ฒซ 3๊ธ์๋ฅผ ์ฒดํฌํ๊ณ ๋ค์ ์กฐ๊ฑด์ ๋ฐ๋ผ ํด๋น prefecture_cd์ ๊ฐ์ ํ ๋นํ๋ค.
์ฃผ์๋์ ์ฒซ 3๊ธ์๊ฐ '์ฌ์ดํ๋งํ'์ด๋ฉด prefecture_cd ๊ฐ์ '11'๋ก ์ค์ ๋๋ค.
์ฃผ์๋์ ์ฒซ 3๊ธ์๊ฐ '์น๋ฐํ'์ธ ๊ฒฝ์ฐ, prefecture_cd์ ๊ฐ์ '12'๋ก ์ค์ ๋๋ค.
์ฃผ์๋์ ์ฒซ 3๊ธ์๊ฐ '๋์ฟ๋'์ธ ๊ฒฝ์ฐ, prefecture_cd์ ๊ฐ์ '13'์ผ๋ก ์ค์ ๋๋ค.
์ฃผ์๋์ ์ฒซ 3๊ธ์๊ฐ '๊ฐ๋๊ฐ์'์ธ ๊ฒฝ์ฐ, prefecture_cd์ ๊ฐ์ '14'๋ก ์ค์ ๋๋ค.
์ด ์ฝ๋๋ ๊ณ ๊ฐ ํ ์ด๋ธ์ ์ ์ฅ๋ ์ฃผ์ ์ ๋ณด๋ฅผ ๋ฐํ์ผ๋ก ๊ณ ๊ฐ์ ๋๋๋ถํ๋ณ๋ก ๋ถ๋ฅํ๊ณ ์ถ์ ๋ ์ ์ฉํ๋ค. ๊ฒฐ๊ณผ๊ฐ์ธ prefecture_cd ์ปฌ๋ผ์ ์ถ๊ฐ ๋ถ์์ด๋ ๊ทธ๋ฃนํ์ ํ์ฉํ ์ ์๋ค.
ย
%%sql
-- ์ฝ๋ ์์ 2 (์ ๊ท ํํ์ ์ฌ์ฉ)
SELECT
customer_id,
address,
CASE SUBSTRING(address, '^.*?[้ฝ้ๅบ็]')
WHEN 'ๅผ็็' THEN '11'
WHEN 'ๅ่็' THEN '12'
WHEN 'ๆฑไบฌ้ฝ' THEN '13'
WHEN '็ฅๅฅๅท็' THEN '14'
END AS prefecture_cd
FROM
customer
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | address | prefecture_cd |
---|---|---|
CS021313000114 | ็ฅๅฅๅท็ไผๅขๅๅธ็ฒ็ชช********** | 14 |
CS037613000071 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | 13 |
CS031415000172 | ๆฑไบฌ้ฝๆธ่ฐทๅบไปฃใ ๆจ********** | 13 |
CS028811000001 | ็ฅๅฅๅท็ๆจชๆตๅธๆณๅบๅๆณ็บ********** | 14 |
CS001215000145 | ๆฑไบฌ้ฝๅคง็ฐๅบไปฒๅ ญ้ท********** | 13 |
CS020401000016 | ๆฑไบฌ้ฝๆฟๆฉๅบ่ฅๆจ********** | 13 |
CS015414000103 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | 13 |
CS029403000008 | ๅ่็ๆตฆๅฎๅธๆตทๆฅฝ********** | 12 |
CS015804000004 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | 13 |
CS033513000180 | ็ฅๅฅๅท็ๆจชๆตๅธๆญๅบๅ้จ็บ********** | 14 |
์ค๋ช :
์ด SQL ์ฝ๋๋ customer ํ ์ด๋ธ์์ ๊ฐ ๊ณ ๊ฐ์ customer_id, address, prefecture_cd code๋ฅผ ์ ํํ๋ค.
์ด ์ฝ๋์ CASE ํํ์์ ์ ๊ท์ ํจํด '^. *? [๋๋๋ถํ]'์ ์ฌ์ฉํ์ฌ ์ฃผ์์ด์์ ๋๋๋ถํ ์ด๋ฆ์ ์ถ์ถํ๊ณ , ์๋ ์กฐ๊ฑด์ ๋ฐ๋ผ ํด๋น ๋๋๋ถํ_cd ๊ฐ์ ํ ๋นํ๊ณ ์๋ค.
์ถ์ถ๋ ๋๋๋ถํ ์ด๋ฆ์ด '์ฌ์ดํ๋งํ'์ธ ๊ฒฝ์ฐ, prefecture_cd ๊ฐ์ '11'๋ก ์ค์ ๋๋ค.
์ถ์ถ๋ ๋๋๋ถํ ์ด๋ฆ์ด '์ง๋ฐํ'์ธ ๊ฒฝ์ฐ, prefecture_cd ๊ฐ์ '12'๋ก ์ค์ ๋๋ค.
์ถ์ถ๋ ๋๋๋ถํ ์ด๋ฆ์ด '๋์ฟ๋'์ธ ๊ฒฝ์ฐ, prefecture_cd์ ๊ฐ์ '13'์ผ๋ก ์ค์ ๋๋ค.
์ถ์ถ๋ ๋๋๋ถํ์ด '๊ฐ๋๊ฐ์ํ'์ด๋ผ๋ฉด prefecture_cd์ ๊ฐ์ '14'๋ก ์ค์ ๋๋ค.
์ด ์ฝ๋๋ ์์ ์์ ์ ๋น์ทํ์ง๋ง, SUBSTR ํจ์ ๋์ SUBSTRING ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ์ฃผ์๋์์ ๋๋๋ถํ ์ด๋ฆ์ ์ถ์ถํ๊ณ ์๋ค. ์ ๊ท ํํ์ ํจํด '^. *? [้ฝ้ๅบ็]ใ๋ ์์์ ๋ฌธ์(^. *?) ๋ก ์์ํด์ ๋, ์, ๋, ๋ถ, ํ ์ค ํ๋๋ก ๋๋๋ ๋ฌธ์์ด๊ณผ ์ผ์นํฉ๋๋ค. ์ด ์ฝ๋๋ ๊ณ ๊ฐ ํ ์ด๋ธ์ ์ฃผ์ ์ ๋ณด๊ฐ ํ์คํ๋์ง ์์ ๋๋๋ถํ ๋ช ์นญ์ ํ์์ด ์ ๊ฐ๊ฐ์ธ ๊ฒฝ์ฐ์ ์ ํจํ๋ค. ์์ฑ๋ prefecture_cd ์ปฌ๋ผ์ ์ถ๊ฐ ๋ถ์ ๋ฐ ๊ทธ๋ฃนํ์ ์ฌ์ฉํ ์ ์๋ค.
%%sql
-- ์ฝ๋ ์์ 3 (LIKE ์ฌ์ฉ)
SELECT
customer_id,
address,
CASE
WHEN address LIKE 'ๅผ็็%' THEN '11'
WHEN address LIKE 'ๅ่็%' THEN '12'
WHEN address LIKE 'ๆฑไบฌ้ฝ%' THEN '13'
WHEN address LIKE '็ฅๅฅๅท็%' THEN '14'
END AS prefecture_cd
FROM
customer
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | address | prefecture_cd |
---|---|---|
CS021313000114 | ็ฅๅฅๅท็ไผๅขๅๅธ็ฒ็ชช********** | 14 |
CS037613000071 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | 13 |
CS031415000172 | ๆฑไบฌ้ฝๆธ่ฐทๅบไปฃใ ๆจ********** | 13 |
CS028811000001 | ็ฅๅฅๅท็ๆจชๆตๅธๆณๅบๅๆณ็บ********** | 14 |
CS001215000145 | ๆฑไบฌ้ฝๅคง็ฐๅบไปฒๅ ญ้ท********** | 13 |
CS020401000016 | ๆฑไบฌ้ฝๆฟๆฉๅบ่ฅๆจ********** | 13 |
CS015414000103 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | 13 |
CS029403000008 | ๅ่็ๆตฆๅฎๅธๆตทๆฅฝ********** | 12 |
CS015804000004 | ๆฑไบฌ้ฝๆฑๆฑๅบๅ็ ********** | 13 |
CS033513000180 | ็ฅๅฅๅท็ๆจชๆตๅธๆญๅบๅ้จ็บ********** | 14 |
์ค๋ช :
์ด SQL ์ฝ๋๋ customer๋ผ๋ ํ ์ด๋ธ์ ๋ํ ์ฟผ๋ฆฌ๋ฅผ ์ํํ์ฌ ์ฒ์ 10ํ์ customer_id, address, prefecture_cd ์ปฌ๋ผ์ ์ ํํ๊ณ ์์ต๋๋ค. ์ผ์นํ๋๋ก CASE ๋ฌธ์ฅ์ ์ฌ์ฉํ์ฌ ์์ฑ๋์์ต๋๋ค.
๊ตฌ์ฒด์ ์ผ๋ก CASE ๋ฌธ์ฅ์ ์ผ๋ณธ์ ๋๋๋ถํ(์ฌ์ดํ๋งํ%, ์ง๋ฐํ%, ๋์ฟ๋%, ๊ฐ๋๊ฐ์ํ%) ๋ค์ ์์์ ๋ฌธ์์ด์ด ์ด์ด์ง๋ ๋ฌธ์์ด ํจํด๊ณผ LIKE ์ฐ์ฐ์๋ฅผ ์ฌ์ฉํ์ฌ ์ฃผ์ ์ปฌ๋ผ์ ํ์ธํ๋ค. ์ฃผ์ ์ปฌ๋ผ์ด ์ด๋ ํ ํจํด๊ณผ ์ผ์นํ๋ฉด ํด๋น ๋๋๋ถํ ์ฝ๋(11, 12, 13, 14)๊ฐ prefecture_cd ์ปฌ๋ผ์ ๋ถ์ฌ๋๋ค.
์ด ์ฝ๋๋ ์์ ์ ์ํ ๋ค๋ฅธ ๋ ๊ฐ์ง ์์ ์ ๋น๊ตํ์ฌ ์ฃผ์ ์ปฌ๋ผ์์ ๋๋๋ถํ ์ฝ๋๋ฅผ ์ถ์ถํ๋ ๋์ฒด์ ์ธ ๋ฐฉ๋ฒ์ด๋ค.
S-055: ์์์ฆ ๋ช ์ธ์(df_receipt) ๋ฐ์ดํฐ์ ๋งค์ถ ๊ธ์ก(amount)์ ๊ณ ๊ฐ ID(customer_id)๋ณ๋ก ํฉ์ฐํ๊ณ , ๊ทธ ํฉ์ฐ ๊ธ์ก์ ์ฌ๋ถ์์๋ฅผ ๊ตฌํ์์ค. ๊ทธ ํ, ๊ณ ๊ฐ๋ณ ๋งค์ถ๊ธ์ก ํฉ๊ณ์ ๋ํด ์๋ ๊ธฐ์ค์ผ๋ก ์นดํ ๊ณ ๋ฆฌ ๊ฐ์ ์์ฑํ์ฌ ๊ณ ๊ฐ ID, ๋งค์ถ๊ธ์ก ํฉ๊ณ์ ํจ๊ป 10๊ฑด์ฉ ํ์ํ๋ผ. ์นดํ ๊ณ ๋ฆฌ ๊ฐ์ ์์๋๋ก 1~4๋ก ํ๋ค.
- ์ต์๊ฐ ์ด์ 1์ฌ๋ถ์์ ๋ฏธ๋ง ใปใปใป 1์ ๋ถ์ฌ
- 1์ฌ๋ถ์ ์ด์ 2์ฌ๋ถ์ ๋ฏธ๋ง ใปใปใป 2๋ฅผ ๋ถ์ฌ
- 2์ฌ๋ถ์ ์ด์ 3์ฌ๋ถ์ ๋ฏธ๋ง ใปใปใป 3์ ๋ถ์ฌ
- 3์ฌ๋ถ์ ์ด์ ใปใปใป 4์ ๋ถ์ฌ
%%sql
WITH sales_amount AS(
SELECT
customer_id,
SUM(amount) AS sum_amount
FROM
receipt
GROUP BY
customer_id
),
sales_pct AS (
SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY sum_amount) AS pct25,
PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY sum_amount) AS pct50,
PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY sum_amount) AS pct75
FROM
sales_amount
)
SELECT
a.customer_id,
a.sum_amount,
CASE
WHEN a.sum_amount < pct25 THEN 1
WHEN pct25 <= a.sum_amount AND a.sum_amount < pct50 THEN 2
WHEN pct50 <= a.sum_amount AND a.sum_amount < pct75 THEN 3
WHEN pct75 <= a.sum_amount THEN 4
END AS pct_group
FROM sales_amount a
CROSS JOIN sales_pct p
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sum_amount | pct_group |
---|---|---|
CS001311000059 | 2302 | 3 |
CS004614000122 | 248 | 1 |
CS003512000043 | 298 | 1 |
CS011615000061 | 246 | 1 |
CS029212000033 | 3604 | 3 |
CS007515000119 | 7157 | 4 |
CS034515000123 | 3699 | 4 |
CS004315000058 | 490 | 1 |
CS026414000014 | 6671 | 4 |
CS001615000099 | 768 | 2 |
์ค๋ช :
์ด SQL ์ฝ๋๋ ๋ค์ ์์ ์ ์ํํฉ๋๋ค.
SUM ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ์์์ฆ ํ ์ด๋ธ์ ๊ธ์ก ์ด์ ๊ณ ๊ฐ ID๋ณ๋ก ํฉ์ฐํ๊ณ ๊ณ ๊ฐ ID๋ณ๋ก ๊ทธ๋ฃนํํ์ฌ ๊ฐ ๊ณ ๊ฐ์ ํ๋งค ๊ธ์ก ํฉ๊ณ๋ฅผ ๊ณ์ฐํฉ๋๋ค. ๊ฒฐ๊ณผ ํ ์ด๋ธ์ sales_amount๋ผ๋ ์ด๋ฆ์ ํ์ ์ฟผ๋ฆฌ๋ก ์ ์ฅ๋ฉ๋๋ค.
์ฐฝ ํจ์์ธ PERCENTILE_CONT ํจ์๋ฅผ ์ฌ์ฉํ์ฌ sales_amount ํ์ ์ฟผ๋ฆฌ์์ sum_amount ์ด์ ์ฌ๋ถ์์(25์, 50์, 75์)๋ฅผ ๊ณ์ฐํ๋ค.
CROSS JOIN ์ฐ์ฐ์ผ๋ก sales_amount ํ์ ์ฟผ๋ฆฌ์ sales_pct ํ์ ์ฟผ๋ฆฌ๋ฅผ ๊ฒฐํฉํ์ฌ sales_amount ํ ์ด๋ธ์ ๊ฐ ํ์ ๋ํ ์ฌ๋ถ์์ ๊ฐ์ ์ป๋๋ค.
๋ง์ง๋ง์ผ๋ก ์๋ก์ด ์ด pct_group์ ๊ณ์ฐํ์ฌ ์ด ๋งค์ถ์ก์ ๊ธฐ์ค์ผ๋ก ๊ฐ ๊ณ ๊ฐ์๊ฒ ๋ค์๊ณผ ๊ฐ์ด ๋ฐฑ๋ถ์์ ๊ทธ๋ฃน์ ํ ๋นํ๋ค.
1:sum_amount๊ฐ 25๋ฒ์งธ ๋ฐฑ๋ถ์์๋ณด๋ค ์์ ๊ฒฝ์ฐ(pct25)
2:sum_amount๊ฐ 25๋ฐฑ๋ถ์์ ์ด์ 50๋ฐฑ๋ถ์์ ๋ฏธ๋ง์ธ ๊ฒฝ์ฐ(pct50).
3:sum_amount๊ฐ 50๋ฒ์งธ ๋ฐฑ๋ถ์์ ์ด์ 75๋ฒ์งธ ๋ฐฑ๋ถ์์ ๋ฏธ๋ง์ธ ๊ฒฝ์ฐ(pct75).
sum_amount๊ฐ 75 ๋ฐฑ๋ถ์์ ์ด์์ด๋ฉด 4.
์ต์ข ๊ฒฐ๊ณผ๋ ์ฒ์ 10๊ฐ์ ํ์ผ๋ก ์ ํ๋๋ค.
S-056: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ๋์ด(age)๋ฅผ ๊ธฐ์ค์ผ๋ก 10์ธ ๋จ์๋ก ์ฐ๋ น์ ๊ณ์ฐํ์ฌ ๊ณ ๊ฐ ID(customer_id), ์๋ ์์ผ(birth_day)๊ณผ ํจ๊ป 10๊ฑด์ฉ ํ์ํ๋ค. ๋จ, 60์ธ ์ด์์ ๋ชจ๋ 60๋ ์ด์์ผ๋ก ํ๋ค. ์ฐ๋ น์ ๋ํ๋ด๋ ์นดํ ๊ณ ๋ฆฌ ๋ช ์นญ์ ์์๋๋ก ํ๋ค.
%%sql
SELECT
customer_id,
birth_day,
-- ํ์ธ์ฉ ํญ๋ชฉ
-- age,
LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60) AS era
FROM
customer
GROUP BY
customer_id,
birth_day
-- ํ์ธ ์กฐ๊ฑด
-- HAVING LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60) >= 60
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | birth_day | era |
---|---|---|
CS001105000001 | 2000-01-14 | 10 |
CS001112000009 | 2006-08-24 | 10 |
CS001112000019 | 2001-01-31 | 10 |
CS001112000021 | 2001-12-15 | 10 |
CS001112000023 | 2004-01-26 | 10 |
CS001112000024 | 2001-01-16 | 10 |
CS001112000029 | 2005-01-24 | 10 |
CS001112000030 | 2003-03-02 | 10 |
CS001113000004 | 2003-02-22 | 10 |
CS001113000010 | 2005-05-09 | 10 |
์ค๋ช :
์ด ์ฝ๋๋ customer ํ ์ด๋ธ์์ customer_id, birth_day, era(๊ณ์ฐ ํ๋)๋ฅผ ์ ํํ๊ณ , era ํ๋๋ ๊ณ ๊ฐ์ ๋์ด(๋ช ์์ ์ผ๋ก ์ ํ๋์ง ์์์ง๋ง ๊ณ์ฐ์ ์ฌ์ฉ๋จ)๋ฅผ ๊ฒฐ์ ํ๊ณ , 10์ธ ๋ฏธ๋ง์ผ๋ก ์๋ฆฐ ๋์ด์ 60 ์ฌ์ด์ ๋ฎ์ ๊ฐ์ ๊ฐ์ ธ์์ ๊ณ์ฐํ๋ค. ๋ฎ์ ๊ฐ์ ์ทจํ์ฌ ๊ณ์ฐ๋๋ค.
๊ฒฐ๊ณผ๋ customer_id์ birth_day๋ก ๊ทธ๋ฃนํ๋๋ค. ๊ทธ๋ฐ ๋ค์ HAVING ์ ์ ์ฌ์ฉํ์ฌ ์ฐ๋ น์ด 60์ธ ๋ฏธ๋ง์ธ ๊ณ ๊ฐ์ ํํฐ๋งํ๋ค.
์์ปจ๋, ์ด ์ฝ๋๋ ๊ณ ๊ฐ ํ ์ด๋ธ์์ ๊ณ ๊ฐ ์ ๋ณด๋ฅผ ์ ํํ๊ณ , ๋์ด์ ์๋๋ฅผ ๊ณ์ฐํ๊ณ , customer_id์ birth_day๋ก ๊ฒฐ๊ณผ๋ฅผ ๊ทธ๋ฃนํํ์ฌ ์ฐ๋ น์ด 60์ธ ๋ฏธ๋ง์ธ ๊ณ ๊ฐ์ ํํฐ๋งํ๊ณ ์๋ค.
S-057: 056์ ์ถ์ถ ๊ฒฐ๊ณผ์ ์ฑ๋ณ ์ฝ๋(gender_cd)์ ๋ฐ๋ผ ์ฑ๋ณร์ฐ๋ น์ ์กฐํฉ์ ๋ํ๋ด๋ ์นดํ ๊ณ ๋ฆฌ ๋ฐ์ดํฐ๋ฅผ ์๋ก ๋ง๋ค์ด 10๊ฐ๋ฅผ ํ์ํ๋ผ. ์กฐํฉ์ ๋ํ๋ด๋ ์นดํ ๊ณ ๋ฆฌ์ ๊ฐ์ ์์๋ก ์ ํ๋ค.
%%sql
-- ์ฑ๋ณ ์ฝ๋ 1์๋ฆฌ์ ์ฐ๋ ์ฝ๋ 2์๋ฆฌ๋ฅผ ์ฐ๊ฒฐํ ์ฑ์ฐ๋ น ์ฝ๋๋ฅผ ์์ฑํ๋ค.
SELECT
customer_id,
birth_day,
gender_cd || TO_CHAR(LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60), 'FM00') AS gender_era
FROM
customer
GROUP BY
customer_id,
birth_day
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | birth_day | gender_era |
---|---|---|
CS001105000001 | 2000-01-14 | 010 |
CS001112000009 | 2006-08-24 | 110 |
CS001112000019 | 2001-01-31 | 110 |
CS001112000021 | 2001-12-15 | 110 |
CS001112000023 | 2004-01-26 | 110 |
CS001112000024 | 2001-01-16 | 110 |
CS001112000029 | 2005-01-24 | 110 |
CS001112000030 | 2003-03-02 | 110 |
CS001113000004 | 2003-02-22 | 110 |
CS001113000010 | 2005-05-09 | 110 |
์ค๋ช :
์ด SQL ์ฝ๋๋ customer_id, birth_day, ๊ทธ๋ฆฌ๊ณ gender_cd์ era๋ก ๊ตฌ์ฑ๋ ์ฐ๊ฒฐ ๋ฌธ์์ด์ ์ ํํ๋ค. era๋ ๊ณ ๊ฐ์ ๋์ด๋ฅผ 10๋ ๋จ์๋ก ๋ฐ์ฌ๋ฆผํ ๊ฒ์ผ๋ก ์ต๋ era๋ 60์ธ๊น์ง์ด๋ฉฐ, gender_era์ ๋ฌธ์์ด์ gender_cd์ era๋ฅผ ์ฐ๊ฒฐํ์ฌ ์์ฑ๋ฉ๋๋ค.
SQL ์ฝ๋๋ customer_id์ birth_day๋ก ๊ฒฐ๊ณผ๋ฅผ ๊ทธ๋ฃนํํ๊ณ ๊ฒฐ๊ณผ์ ์ฒ์ 10์ค๋ง ๋ฐํํ๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ์ฐ๋ น๊ณผ ์ฑ๋ณ์ ํฌํจํ ๊ณ ๊ฐ ๋ฐ์ดํฐ์ ์์ฝ์ ์์ฑํ๊ณ ์๋ ์ฐจ์์ ์ถ๊ฐํ๋ค. ์์ฑ๋ ํ ์ด๋ธ์ ์ฐ๋ น๊ณผ ์ฑ๋ณ์ ๋ฐ๋ผ ๊ณ ๊ฐ์ ์ถ๊ฐ๋ก ๋ถ์ํ๊ฑฐ๋ ์ธ๋ถํํ๋ ๋ฐ ์ฌ์ฉํ ์ ์๋ค.
ย
S-058: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ์ฑ๋ณ ์ฝ๋(gender_cd)๋ฅผ ๋๋ฏธ ๋ณ์๋ก ๋ง๋ค์ด ๊ณ ๊ฐ ID(customer_id)์ ํจ๊ป 10๊ฑด ํ์ํ๋ผ.
%%sql
-- ์นดํ
๊ณ ๋ฆฌ ์๊ฐ ๋ง์ ๊ฒฝ์ฐ ๋งค์ฐ ๊ธด SQL์ด ๋ ์ ์๋ค๋ ์ ์ ์ฃผ์
-- ์นดํ
๊ณ ๋ฆฌ๋ฅผ ํ๋ ์ค์ด๊ณ ์ถ๋ค๋ฉด CASE ๋ฌธ์ฅ์ ํ๋๋ง ๋นผ๋ฉด OK
SELECT
customer_id,
CASE WHEN gender_cd = '0' THEN '1' ELSE '0' END AS gender_cd_0,
CASE WHEN gender_cd = '1' THEN '1' ELSE '0' END AS gender_cd_1,
CASE WHEN gender_cd = '9' THEN '1' ELSE '0' END AS gender_cd_9
FROM
customer
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | gender_cd_0 | gender_cd_1 | gender_cd_9 |
---|---|---|---|
CS021313000114 | 0 | 1 | 0 |
CS037613000071 | 0 | 0 | 1 |
CS031415000172 | 0 | 1 | 0 |
CS028811000001 | 0 | 1 | 0 |
CS001215000145 | 0 | 1 | 0 |
CS020401000016 | 1 | 0 | 0 |
CS015414000103 | 0 | 1 | 0 |
CS029403000008 | 1 | 0 | 0 |
CS015804000004 | 1 | 0 | 0 |
CS033513000180 | 0 | 1 | 0 |
์ค๋ช :
์ด SQL ์ฝ๋๋ customer_id๋ฅผ ์ ํํ๊ณ gender_cd_0, gender_cd_1, gender_cd_9๋ผ๋ 3๊ฐ์ ์๋ก์ด ์ด์ ์์ฑํ๋ค. ์ด ์๋ก์ด ์ปฌ๋ผ์ CASE ๋ฌธ์ฅ์ ์ฌ์ฉํ์ฌ ๋ง๋ค์ด์ก์ต๋๋ค.
CASE ๋ฌธ์ ์กฐ๊ฑด๋ฌธ์ผ๋ก, ์ผ๋ จ์ ์กฐ๊ฑด์ ํ๊ฐํ๊ณ ํด๋น ๊ฒฐ๊ณผ๋ฅผ ๋ฐํํ๋ค. ์ด ๊ฒฝ์ฐ ๊ฐ ๊ณ ๊ฐ์ ๋ํด ์ฝ๋๋ gender_cd ์ด์ ๊ฐ์ ํ์ธํ๊ณ , gender_cd๊ฐ '0'์ธ ๊ฒฝ์ฐ ์ฝ๋๋ gender_cd_0 ์ด์ '1'์ ๋ฐํํ๊ณ ๋ค๋ฅธ ์ด์ '0'์ ๋ฐํํ๊ณ , gender_cd๊ฐ '1'์ธ ๊ฒฝ์ฐ ์ฝ๋๋ gender_cd_1 ์ด์ '1'์ ๋ฐํํ๊ณ ๋ค๋ฅธ ์ด์๋ '0'์ ๋ฐํํ๋ฉฐ, gender_cd๊ฐ '9'์ธ ๊ฒฝ์ฐ ์ฝ๋๋ gender_cd_9 ์ด์ '1'์ ๋ฐํํ๊ณ ๋ค๋ฅธ ์ด์๋ '0'์ ๋ฐํํ๋ค.
๊ฒฐ๊ณผ ์ถ๋ ฅ์ customer_id, gender_cd_0, gender_cd_1, gender_cd_9 ์ปฌ๋ผ์ ๊ฐ์ง๋ฉฐ, ๊ฐ ๊ณ ๊ฐ์ ๋ํด ์๋ก์ด ์ปฌ๋ผ ์ค ํ๋๋ง ๊ณ ๊ฐ์ ์ฑ๋ณ์ ๋ํ๋ด๋ '1'์ด๋ผ๋ ๊ฐ์ ๊ฐ์ง๊ฒ ๋๋ค.
S-059: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ ๊ธ์ก(amount)์ ๊ณ ๊ฐ ID(customer_id)๋ณ๋ก ํฉ์ฐํ๊ณ , ๋งค์ถ ๊ธ์ก ํฉ๊ณ๋ฅผ ํ๊ท 0, ํ์คํธ์ฐจ 1๋ก ํ์คํํ์ฌ ๊ณ ๊ฐ ID, ๋งค์ถ ๊ธ์ก ํฉ๊ณ์ ํจ๊ป 10๊ฑด์ฉ ํ์ํ๋ผ. ํ์คํ์ ์ฌ์ฉํ๋ ํ์คํธ์ฐจ๋ ๋ถ์ฐ ์ ๊ณฑ๊ทผ ๋๋ ๋ถ๊ท ํ ๋ถ์ฐ ์ ๊ณฑ๊ทผ ์ค ์ด๋ ๊ฒ์ด๋ ์๊ด์๋ค. ๋จ, ๊ณ ๊ฐ ID๊ฐ โZโ๋ก ์์ํ๋ ๊ฒ์ ๋นํ์์ ์๋ฏธํ๋ฏ๋ก ์ ์ธํ์ฌ ๊ณ์ฐํ๋ค.
%%sql
-- ์ฝ๋ ์์ 1 (STDDEV_POP๋ก ํ์คํ)
WITH sales_amount AS(
SELECT
customer_id,
SUM(amount) AS sum_amount
FROM
receipt
WHERE
customer_id NOT LIKE 'Z%'
GROUP BY
customer_id
),
stats_amount AS (
SELECT
AVG(sum_amount) AS avg_amount,
STDDEV_POP(sum_amount) AS stddev_amount
FROM
sales_amount
)
SELECT
customer_id,
sum_amount,
(sum_amount - avg_amount) / stddev_amount AS std_amount
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sum_amount | std_amount |
---|---|---|
CS001311000059 | 2302 | -0.09032946448390523062 |
CS004614000122 | 248 | -0.84533488878695858131 |
CS003512000043 | 298 | -0.82695598361891930792 |
CS011615000061 | 246 | -0.84607004499368015224 |
CS029212000033 | 3604 | 0.38825722609183744835 |
CS007515000119 | 7157 | 1.6942622273327082 |
CS034515000123 | 3699 | 0.42317714591111206778 |
CS004315000058 | 490 | -0.75638098777364849812 |
CS026414000014 | 6671 | 1.5156192690993665 |
CS001615000099 | 768 | -0.65419427503935013810 |
์ค๋ช :
์ด ์ฝ๋๋ SQL ์ธ์ด๋ก ์์ฑ๋์์ผ๋ฉฐ, SQL์ ์ง์ํ๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค ๊ด๋ฆฌ ์์คํ ์ ์ฌ์ฉํฉ๋๋ค.
์ด ์ฝ๋๋ recipate๋ผ๋ ํ ์ด๋ธ์ ๋ํด ๋ฐ์ดํฐ ๋ถ์ ์์ ์ ์คํํ๊ณ ์๋ค. ์ด ํ ์ด๋ธ์๋ customer_id, ๊ธ์ก ๋ฑ์ ์ด์ ๊ฐ์ง ํธ๋์ญ์ ๋ฐ์ดํฐ๊ฐ ํฌํจ๋์ด ์์ต๋๋ค.
์ด ์ฝ๋๋ CTE(Common Table Expression)๋ฅผ ์ฌ์ฉํ์ฌ ๋ ๊ฐ์ ์์ ํ ์ด๋ธ sales_amount์ stats_amount๋ฅผ ์์ฑํ๊ณ ์๋ค.
sales_amount๋ผ๋ ์ด๋ฆ์ ์ฒซ ๋ฒ์งธ CTE๋ ๋ฌธ์ 'Z'๋ก ์์ํ์ง ์๋ customer_id๋ฅผ ๊ฐ์ง ๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ๊ธ์ก์ ํฉ๊ณ๋ฅผ ๊ณ์ฐํ๊ณ ์๋ค. ๊ฑฐ๋ ๋ฐ์ดํฐ๋ฅผ customer_id๋ก ๊ทธ๋ฃนํํ์ฌ ๊ฐ customer_id์ ๊ธ์ก ํฉ๊ณ๋ฅผ ๊ณ์ฐํ๊ณ ์์ต๋๋ค.
stats_amount๋ผ๋ ์ด๋ฆ์ ๋ ๋ฒ์งธ CTE๋ ๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ์ด ๊ธ์ก์ ํ๊ท ๊ณผ ํ์คํธ์ฐจ๋ฅผ ๊ณ์ฐํ๊ณ ์์ต๋๋ค.
๋ง์ง๋ง SELECT ๋ฌธ์ sales_amount ํ ์ด๋ธ์์ ์ปฌ๋ผ์ ์ ํํ๊ณ ํฌ๋ก์ค ์กฐ์ธ์ ์ฌ์ฉํ์ฌ stats_amount ํ ์ด๋ธ๊ณผ ๊ฒฐํฉํฉ๋๋ค. ๋ํ ์ด ๊ธ์ก์์ ํ๊ท ๊ธ์ก์ ๋นผ๊ณ ์ด๋ฅผ ํ์คํธ์ฐจ๋ก ๋๋์ด ๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ํ์คํ๋ ๊ธ์ก์ ๊ณ์ฐํ๊ณ ์์ต๋๋ค.
๋ง์ง๋ง LIMIT 10์ ์ถ๋ ฅ์ ์ฒ์ 10ํ์ผ๋ก ์ ํํ๊ณ ์๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ๊ฑฐ๋ ๋ฐ์ดํฐ์ ๋ํ ํต๊ณ ๋ถ์์ ์ํํ์ฌ 'Z'๋ก ์์ํ๋ customer_id๊ฐ ์๋ ๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ํ์คํ ๋ ๊ธ์ก์ ๊ณ์ฐํ๊ณ ์์ต๋๋ค.
ย
%%sql
-- ์ฝ๋ ์์ 2 (STDDEV_SAMP๋ก ํ์คํ, ์ฝ๋ ์์ 2์ ์ฝ๊ฐ ๊ฐ์ด ๋ฌ๋ผ์ง)
WITH sales_amount AS(
SELECT
customer_id,
SUM(amount) AS sum_amount
FROM
receipt
WHERE
customer_id NOT LIKE 'Z%'
GROUP BY
customer_id
),
stats_amount AS (
SELECT
AVG(sum_amount) AS avg_amount,
STDDEV_SAMP(sum_amount) AS stddev_amount
FROM
sales_amount
)
SELECT
customer_id,
sum_amount,
(sum_amount - avg_amount) / stddev_amount AS std_amount
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sum_amount | std_amount |
---|---|---|
CS001311000059 | 2302 | -0.09032402671702291270 |
CS004614000122 | 248 | -0.84528400025253654164 |
CS003512000043 | 298 | -0.82690620148098070841 |
CS011615000061 | 246 | -0.84601911220339877497 |
CS029212000033 | 3604 | 0.38823385329429098451 |
CS007515000119 | 7157 | 1.6941602340010485 |
CS034515000123 | 3699 | 0.42315167096024706764 |
CS004315000058 | 490 | -0.75633545419820630882 |
CS026414000014 | 6671 | 1.5155280299415258 |
CS001615000099 | 768 | -0.65415489302835587608 |
์ค๋ช :
์ด ์ฝ๋๋ ์ด์ ์ฝ๋์ ๋งค์ฐ ์ ์ฌํ์ง๋ง, stats_amount CTE์์ ํ ๊ฐ์ง ์ค์ํ ์ฐจ์ด์ ์ด ์๋ค.
ํ์คํธ์ฐจ ๊ณ์ฐ์ STDDEV_POP ํจ์๋ฅผ ์ฌ์ฉํ๋ ๋์ STDDEV_SAMP ํจ์๋ฅผ ์ฌ์ฉํฉ๋๋ค.
STDDEV_SAMP๋ ๋ฐ์ดํฐ ์ํ์ ๊ธฐ๋ฐ์ผ๋ก ๋ชจ์ง๋จ์ ํ์คํธ์ฐจ ์ถ์ ์น์ธ ์ํ ํ์คํธ์ฐจ๋ฅผ ๊ณ์ฐํฉ๋๋ค.
๋ฐ๋ฉด STDDEV_POP์ ์ ์ฒด ๋ชจ์ง๋จ์ ๋ถ์ํ ์ ์๋ค๊ณ ๊ฐ์ ํ๊ณ ์ ํํ ๋ชจ์ง๋จ ํ์คํธ์ฐจ๋ฅผ ๊ณ์ฐํ๋ค.
์ฆ, ์ด ์ฝ๋์ ์ถ๋ ฅ์ ์ด์ ์ฝ๋์ ๋น์ทํ์ง๋ง ํ์คํธ์ฐจ ๊ณ์ฐ์ ์ ์ฒด ๋ชจ์ง๋จ์ด ์๋ ๋ฐ์ดํฐ ์ํ์ ๊ธฐ๋ฐ์ผ๋ก ํ๋ค.
์ผ๋ฐ์ ์ผ๋ก ๋ฐ์ดํฐ ์ธํธ๊ฐ ๋งค์ฐ ํฐ ๊ฒฝ์ฐ ์ํ๊ณผ ๋ชจ์ง๋จ์ ํ์ค ํธ์ฐจ์ ์ฐจ์ด๊ฐ ์์ต๋๋ค. ๊ทธ๋ฌ๋ ๋ฐ์ดํฐ ์ธํธ๊ฐ ์์ผ๋ฉด ๊ทธ ์ฐจ์ด๊ฐ ์ปค์ง ์ ์์ต๋๋ค.
ย
S-060: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ ๊ธ์ก(amount)์ ๊ณ ๊ฐ ID(customer_id)๋ณ๋ก ํฉ์ฐํ์ฌ ๋งค์ถ ๊ธ์ก ํฉ๊ณ๋ฅผ ์ต์๊ฐ 0, ์ต๋๊ฐ 1๋ก ์ ๊ทํํ์ฌ ๊ณ ๊ฐ ID, ๋งค์ถ ๊ธ์ก ํฉ๊ณ์ ํจ๊ป 10๊ฑด์ฉ ํ์ํ๋ค. ๋จ, ๊ณ ๊ฐ ID๊ฐ โZโ๋ก ์์ํ๋ ๊ฒ์ ๋นํ์์ ์๋ฏธํ๋ฏ๋ก ์ ์ธํ์ฌ ๊ณ์ฐํ๋ค.
%%sql
WITH sales_amount AS(
SELECT
customer_id,
SUM(amount) AS sum_amount
FROM
receipt
WHERE
customer_id NOT LIKE 'Z%'
GROUP BY
customer_id
),
stats_amount AS (
SELECT
MAX(sum_amount) AS max_amount,
MIN(sum_amount) AS min_amount
FROM
sales_amount
)
SELECT
customer_id,
sum_amount,
1.0 * (sum_amount - min_amount)
/ (max_amount - min_amount) AS scale_amount
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sum_amount | scale_amount |
---|---|---|
CS001311000059 | 2302 | 0.09697601668404588113 |
CS004614000122 | 248 | 0.00773375043448036149 |
CS003512000043 | 298 | 0.00990615224191866528 |
CS011615000061 | 246 | 0.00764685436218282934 |
CS029212000033 | 3604 | 0.15354535974973931178 |
CS007515000119 | 7157 | 0.30791623218630517901 |
CS034515000123 | 3699 | 0.15767292318387208898 |
CS004315000058 | 490 | 0.01824817518248175182 |
CS026414000014 | 6671 | 0.28680048661800486618 |
CS001615000099 | 768 | 0.03032672923183872089 |
์ค๋ช :
์ด ์ฝ๋๋ ์์์ฆ ํ ์ด๋ธ์ ๋ถ์ํ์ง๋ง, ํ์คํ ๋ ๊ธ์ก์ ๊ณ์ฐํ๋ ๋์ ๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ๊ณ๋๋ ๊ธ์ก์ ๊ณ์ฐํฉ๋๋ค.
์ด ์ฝ๋์์๋ sales_amount์ stats_amount๋ผ๋ ๋ ๊ฐ์ CTE๋ฅผ ์ฌ์ฉํ๋๋ฐ, ์ด๋ ์ด์ ์ฝ๋์ ์ ์ฌํ๋ค.
์ฒซ ๋ฒ์งธ CTE๋ ๋ฌธ์ 'Z'๋ก ์์ํ์ง ์๋ customer_id๋ฅผ ๊ฐ์ง ๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ์ด ๊ธ์ก์ ๊ณ์ฐํ๊ณ , ๋ ๋ฒ์งธ CTE๋ ์์์ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ์ด ๊ธ์ก์ ์ต๋๊ฐ๊ณผ ์ต์๊ฐ์ ๊ณ์ฐํ๊ณ ์์ต๋๋ค.
๋ง์ง๋ง SELECT ๋ฌธ์ sales_amount ํ ์ด๋ธ์์ ์ปฌ๋ผ์ ์ ํํ๊ณ ํฌ๋ก์ค ์กฐ์ธ์ ์ฌ์ฉํ์ฌ stats_amount ํ ์ด๋ธ๊ณผ ๊ฒฐํฉํ๊ณ ์๋ค.
๋ํ ๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ์ค์ผ์ผ๋ง๋ ๊ธ์ก์ ๊ณ์ฐํฉ๋๋ค. ์ด๋ ์ฌ์ฉํ ์ด ๊ธ์ก์์ ์ฌ์ฉํ ์ต์ ๊ธ์ก์ ๋บ ํ ์ฌ์ฉํ ์ด ๊ธ์ก ๋ฒ์๋ก ๋๋ ๊ฐ์ผ๋ก, ์ต๋ ๊ธ์ก๊ณผ ์ต์ ๊ธ์ก์ ์ฐจ์ด์ ๋๋ค.
์์ ์์ 1.0 *๋ ๊ฒฐ๊ณผ๋ฅผ ๋ถ๋ ์์์ ์ซ์๋ก ๋ณํํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ์์์ ์ฌ์ฉ๋ ๊ฐ ์ค ํ๋๊ฐ ๋ถ๋ ์์์ ์ซ์์ผ ๋ ํ์ํฉ๋๋ค.
๋ง์ง๋ง LIMIT 10์ ์ถ๋ ฅ์ ์ฒ์ 10ํ์ผ๋ก ์ ํํฉ๋๋ค.
์ ์ฒด์ ์ผ๋ก ์ด ์ฝ๋๋ ๋ฌธ์ 'Z'๋ก ์์ํ๋ customer_id๊ฐ ์๋ ๊ฐ ๊ณ ๊ฐ์ด ์ง์ถํ ๊ธ์ก(scaled amount)์ ๊ตฌํ๋ ๋ฐ์ดํฐ ๋ถ์ ์์ ์ ์ํํ๊ณ ์๋ค. ์ค์ผ์ผ๋ง๋ ๊ธ์ก์ 0์์ 1 ์ฌ์ด์ ๊ฐ์ผ๋ก, ๋ชจ๋ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ์ด์ก ๋ฒ์์ ๋ํ ๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ์ด์ก์ ๋น์จ์ ๋ํ๋ธ๋ค.
ย
Comment