๋ฐ์ดํฐ ์ฌ์ด์ธ์ค 100๋ฒ์ ๋ ธํฌ(๊ตฌ์กฐํ ๋ฐ์ดํฐ ์ฒ๋ฆฌํธ)- SQL Part 2 (Q21 to Q40)์ ํด์ค์ ๋๋ค.
ย
์ฐธ๊ณ (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-021:ย ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋ํด ๊ฑด์๋ฅผ ์ธ์ด๋ผ.
%%sql
-- ์ฝ๋ ์์ 1
SELECT COUNT(1) FROM receipt;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
count |
---|
104681 |
์ค๋ช :
์ด ์ฝ๋๋ "receipt"๋ผ๋ ํ ์ด๋ธ์์ ํ ์๋ฅผ ์ ํํ๋ SQL ์ฟผ๋ฆฌ์ ๋๋ค.
SELECT ํค์๋๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค๋ ๊ฒ์ ๋ํ๋ด๊ธฐ ์ํด ์ฌ์ฉ๋๋ค.
COUNT ํจ์๋ 'receipt' ํ ์ด๋ธ์ ํ ์๋ฅผ ๊ณ์ฐํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ์ด ๊ฒฝ์ฐ 'COUNT(1)' ๋ฌธ์ ๊ฐ ํ์ ์ฒซ ๋ฒ์งธ ์ด์ ๊ฐ์ด NULL์ด ์๋ ํ ์ด๋ธ์ ํ ์๋ฅผ ๊ณ์ฐํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด๋ ์ด๋ค ์ด์ด NULL์ธ์ง์ ๊ด๊ณ์์ด ํ ์ด๋ธ์ ๋ชจ๋ ํ์ ๊ณ์ฐํ๋ "COUNT(*)"๋ฅผ ์ฌ์ฉํ๋ ๊ฒ๊ณผ ๋์ผํ๋ค.
FROM ํค์๋๋ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ ์ค๋ ค๋ ํ ์ด๋ธ์ ๋ํ๋ด๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, ์ด ๊ฒฝ์ฐ "receipt" ํ ์ด๋ธ์ ๋ํ๋ ๋๋ค.
์ฆ, ์์ฝํ๋ฉด ์ด ์ฟผ๋ฆฌ๋ "receive" ํ ์ด๋ธ์ ํ ์๋ฅผ ์ ํํ๊ณ ๋ฐํํ๋ ๊ฒ์ด๋ค.
%%sql
-- ์ฝ๋ ์์ 2๏ผ*์ด๋ผ๋ OK๏ผ
SELECT COUNT(*) FROM receipt;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
count |
---|
104681 |
์ค๋ช :
์ด ์ฝ๋๋ "receipt"๋ผ๋ ํ ์ด๋ธ์์ ํ ์๋ฅผ ์ ํํ๋ SQL ์ฟผ๋ฆฌ์ ๋๋ค.
SELECT ํค์๋๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค๋ ๊ฒ์ ๋ํ๋ด๊ธฐ ์ํด ์ฌ์ฉ๋๋ค.
COUNT ํจ์๋ "receipt" ํ ์ด๋ธ์ ํ ์๋ฅผ ๊ณ์ฐํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ์ด ๊ฒฝ์ฐ "COUNT(*)"๋ฌธ์ ์ด๋ค ์ด์ด NULL์ธ์ง ์ฌ๋ถ์ ๊ด๊ณ์์ด ํ ์ด๋ธ์ ๋ชจ๋ ํ์ ๊ณ์ฐํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค.
FROM ํค์๋๋ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ ์ค๋ ค๋ ํ ์ด๋ธ, ์ด ๊ฒฝ์ฐ "RECIPATE" ํ ์ด๋ธ์ ๋ํ๋ด๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค.
์ฆ, ์์ฝํ๋ฉด, ์ด ์ฟผ๋ฆฌ๋ "RECIPATE" ํ ์ด๋ธ์ ๋ชจ๋ ํ์ ์ ํํ๊ณ ๊ทธ ๊ฐ์๋ฅผ ๋ฐํํ๋ค.
S-022: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๊ณ ๊ฐ ID(customer_id)์ ๋ํด ๊ณ ์ ๊ฑด์๋ฅผ ์ธ์ด๋ผ.
%%sql
SELECT
COUNT(DISTINCT customer_id)
FROM receipt
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
count |
---|
8307 |
์ค๋ช :
์ด ์ฝ๋๋ "receipt"๋ผ๋ ํ ์ด๋ธ์์ ๋ค๋ฅธ ๊ณ ๊ฐ ID์ ๊ฐ์๋ฅผ ์ ํํ๋ SQL ์ฟผ๋ฆฌ์ด๋ค.
sql์ ์ด ์ฝ๋๊ฐ SQL ์ฝ๋์์ ๋ํ๋ด๋ Jupyter Notebook์ ์ ๋งค์ง ๋ช ๋ น์ด๋ค.
SELECT ํค์๋๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค๋ ๊ฒ์ ๋ํ๋ด๊ธฐ ์ํด ์ฌ์ฉ๋๋ค.
COUNT ํจ์๋ 'RECIPATE' ํ ์ด๋ธ์์ ๋ค๋ฅธ ๊ณ ๊ฐ ID์ ์๋ฅผ ๊ณ์ฐํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด ๊ฒฝ์ฐ 'COUNT(DISTINCT customer_id)' ๋ฌธ์ ํ ์ด๋ธ ๋ด ๊ณ ์ ํ ๊ณ ๊ฐ ID์ ์๋ฅผ ๊ณ์ฐํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
DISTINCT ํค์๋๋ 'customer_id' ์ด์ ๊ณ ์ ํ ๊ฐ๋ง ๊ณ์ฐํ๋๋ก ์ง์ ํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
FROM ํค์๋๋ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ ์ค๋ ค๋ ํ ์ด๋ธ์ ๋ํ๋ด๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, ์ด ๊ฒฝ์ฐ "receipt" ํ ์ด๋ธ์ ๋ํ๋ ๋๋ค.
์ฆ, ์์ฝํ๋ฉด ์ด ์ฟผ๋ฆฌ๋ 'receive' ํ ์ด๋ธ์ ๊ณ ์ ํ ๊ณ ๊ฐ ID ๊ฐ์๋ฅผ ์ ํํ์ฌ ๋ฐํํ๋ค.
ย
S-023: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋ํด ๋งค์ฅ ์ฝ๋(store_cd)๋ณ ํ๋งค ๊ธ์ก(amount)๊ณผ ํ๋งค ์๋(quantity)์ ํฉ์ฐํ๋ผ.
%%sql
SELECT store_cd
, SUM(amount) AS amount
, SUM(quantity) AS quantity
FROM receipt
group by store_cd
;
* postgresql://padawan:***@db:5432/dsdojo_db 52 rows affected.
store_cd | amount | quantity |
---|---|---|
S12007 | 638761 | 2099 |
S13017 | 748221 | 2376 |
S13043 | 587895 | 1881 |
S13052 | 100314 | 250 |
S13016 | 793773 | 2432 |
S14027 | 714550 | 2303 |
S13009 | 808870 | 2486 |
S14022 | 651328 | 2047 |
S13019 | 827833 | 2541 |
S13039 | 611888 | 1981 |
S14046 | 412646 | 1354 |
S13003 | 764294 | 2197 |
S14028 | 786145 | 2458 |
S14045 | 458484 | 1398 |
S13002 | 727821 | 2340 |
S14042 | 534689 | 1935 |
S13004 | 779373 | 2390 |
S13038 | 708884 | 2337 |
S12014 | 725167 | 2358 |
S14021 | 699511 | 2231 |
S14006 | 712839 | 2284 |
S13001 | 811936 | 2347 |
S14023 | 727630 | 2258 |
S14025 | 755581 | 2394 |
S13035 | 715869 | 2219 |
S14048 | 234276 | 769 |
S14012 | 720600 | 2412 |
S14024 | 736323 | 2417 |
S13041 | 728266 | 2233 |
S14026 | 824537 | 2503 |
S14010 | 790361 | 2290 |
S13015 | 780873 | 2248 |
S13008 | 809288 | 2491 |
S14049 | 230808 | 788 |
S14050 | 167090 | 580 |
S13031 | 705968 | 2336 |
S12013 | 787513 | 2425 |
S13044 | 520764 | 1729 |
S14033 | 725318 | 2282 |
S12029 | 794741 | 2555 |
S13037 | 693087 | 2344 |
S12030 | 684402 | 2403 |
S13032 | 790501 | 2491 |
S14034 | 653681 | 2024 |
S14040 | 701858 | 2233 |
S13005 | 629876 | 2004 |
S14047 | 338329 | 1041 |
S14011 | 805724 | 2434 |
S13018 | 790535 | 2562 |
S13020 | 796383 | 2383 |
S13051 | 107452 | 354 |
S14036 | 203694 | 635 |
์ค๋ช :
SQL ์ฝ๋๊ฐ ๋ ๋ฒ ๋ฐ๋ณต๋๋ค.
์ด ์ฝ๋๋ "receipt"๋ผ๋ ํ ์ด๋ธ์์ "store_cd", "amount", "quantity"๋ผ๋ ์ธ ๊ฐ์ ์ด์ ์ ํํ๋ค. ์ด ํ ์ด๋ธ์๋ ํน์ ๋งค์ฅ์ ๋งค์ถ ๋ฐ์ดํฐ๊ฐ ํฌํจ๋์ด ์๋ค.
SUM ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ฐ ๋งค์ฅ์ "๊ธ์ก" ์ด๊ณผ "์๋" ์ด์ ํฉ๊ณ๋ฅผ ๊ณ์ฐํ๋ค.
GROUP BY ๊ตฌ๋ฌธ์ ๋งค์ถ ๋ฐ์ดํฐ๋ฅผ ๋งค์ฅ๋ณ๋ก ๊ทธ๋ฃนํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ฆ, ์ด ์ฟผ๋ฆฌ๋ 'store_cd' ์ปฌ๋ผ์ ๊ณ ์ ํ ๋งค์ฅ ์ฝ๋๋ณ๋ก '๊ธ์ก'๊ณผ '์๋'์ ํฉ๊ณ๋ฅผ ๋ฐํํ๋ค.
์ฒซ ๋ฒ์งธ ์ฝ๋๊ฐ ๋ ๋ฒ์งธ ์ฝ๋์ ์์ ํ ๋์ผํ๊ธฐ ๋๋ฌธ์ ์ค๋ณต ๋๋ ์ค์์ผ ์ ์์ต๋๋ค. ๊ทธ๋ผ์๋ ๋ถ๊ตฌํ๊ณ ์ด ์ฟผ๋ฆฌ๋ 'receipt' ํ ์ด๋ธ์์ ๊ฐ ๋งค์ฅ์ ํ๋งค ์ํ์ ์ด ๊ธ์ก๊ณผ ์๋์ ์ ํํ์ฌ ๋ฐํํฉ๋๋ค.
S-024: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋ํด ๊ณ ๊ฐ ID(customer_id)๋ณ๋ก ๊ฐ์ฅ ์ต๊ทผ ๋งค์ถ ๋ ์ง(sales_ymd)๋ฅผ ๊ตฌํ์ฌ 10๊ฑด ํ์ํ๋ผ.
%%sql
SELECT
customer_id,
MAX(sales_ymd)
FROM receipt
GROUP BY customer_id
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | max |
---|---|
CS001311000059 | 20180211 |
CS004614000122 | 20181228 |
CS003512000043 | 20180106 |
CS011615000061 | 20190503 |
CS029212000033 | 20180621 |
CS007515000119 | 20190511 |
CS034515000123 | 20190708 |
CS004315000058 | 20170517 |
CS026414000014 | 20190720 |
CS001615000099 | 20170729 |
์ค๋ช :
์ด ์ฝ๋๋ "receipt"๋ผ๋ ํ ์ด๋ธ์์ ๊ฐ ๊ณ ๊ฐ์ ์ต๋ ๋งค์ถ์ผ(sales_ymd)์ ์ ํํ๋ SQL ์ฟผ๋ฆฌ์ด๋ค.
sql์ ์ด ์ฝ๋๊ฐ SQL ์ฝ๋์์ ๋ํ๋ด๋ Jupyter Notebook์ ์ ๋งค์ง ๋ช ๋ น์ด์ด๋ค.
SELECT ํค์๋๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค๋ ๊ฒ์ ๋ํ๋ด๊ธฐ ์ํด ์ฌ์ฉ๋๋ค.
customer_id์ MAX(sales_ymd)๋ 'RECIPATE' ํ ์ด๋ธ์์ ์ ํํ๊ณ ์ ํ๋ ์ปฌ๋ผ์ด๋ฉฐ, MAX ํจ์๋ 'customer_id' ์ปฌ๋ผ์ ๊ณ ์ ํ ๊ณ ๊ฐ ID๋ง๋ค 'sales_ymd' ์ปฌ๋ผ์ ์ต๋๊ฐ์ ์ ํํ๊ธฐ ์ํด ์ฌ์ฉ๋๋ค.
FROM ํค์๋๋ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ฌ ํ ์ด๋ธ์ ๋ํ๋ด๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, ์ด ๊ฒฝ์ฐ 'receive' ํ ์ด๋ธ์ด๋ค.
GROUP BY ๊ตฌ๋ฌธ์ ๊ณ ๊ฐ ID๋ณ๋ก ๋งค์ถ ๋ฐ์ดํฐ๋ฅผ ๊ทธ๋ฃนํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ฆ, ์ด ์ฟผ๋ฆฌ๋ 'customer_id' ์ด์ ๊ณ ์ ํ ๊ณ ๊ฐ ID๋ณ๋ก ์ต๋ ๋งค์ถ ๋ ์ง๋ฅผ ๋ฐํํ๋ค.
LIMIT ํค์๋๋ ์ฟผ๋ฆฌ๊ฐ ๋ฐํํ๋ ๊ฒฐ๊ณผ์ ์๋ฅผ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด ๊ฒฝ์ฐ 'LIMIT 10' ๋ฌธ์ ๊ฒฐ๊ณผ๋ฅผ ์์ 10๊ฐ์ ํ์ผ๋ก ์ ํํ๋ค.
์ฆ, ์์ฝํ๋ฉด, ์ด ์ฟผ๋ฆฌ๋ 'receipt' ํ ์ด๋ธ์์ ๊ฐ ๊ณ ๊ฐ์ ์ต๋ ํ๋งค์ผ์ ์ ํํ์ฌ ๋ฐํํ๊ณ , ๊ฒฐ๊ณผ๋ฅผ ์์ 10๋ช ์ ๊ณ ๊ฐ์ผ๋ก ์ ํํ๋ค.
S-025: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋ํด ๊ณ ๊ฐ ID(customer_id)๋ณ๋ก ๊ฐ์ฅ ์ค๋๋ ๋งค์ถ ๋ ์ง(sales_ymd)๋ฅผ ๊ตฌํ์ฌ 10๊ฑด ํ์ํ๋ผ.
%%sql
SELECT
customer_id,
MIN(sales_ymd)
FROM receipt
GROUP BY customer_id
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | min |
---|---|
CS001311000059 | 20180211 |
CS004614000122 | 20181228 |
CS003512000043 | 20180106 |
CS011615000061 | 20190503 |
CS029212000033 | 20170318 |
CS007515000119 | 20170201 |
CS034515000123 | 20170527 |
CS004315000058 | 20170517 |
CS026414000014 | 20170718 |
CS001615000099 | 20170729 |
์ค๋ช :
์ด ์ฝ๋๋ "receipt"๋ผ๋ ํ ์ด๋ธ์์ ๊ฐ ๊ณ ๊ฐ์ ์ต์ ๋งค์ถ์ผ(sales_ymd)์ ์ ํํ๋ SQL ์ฟผ๋ฆฌ์ด๋ค.
sql์ ์ด ์ฝ๋๊ฐ SQL ์ฝ๋์์ ๋ํ๋ด๋ Jupyter Notebook์ ์ ๋งค์ง ๋ช ๋ น์ด์ด๋ค.
SELECT ํค์๋๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค๋ ๊ฒ์ ๋ํ๋ด๊ธฐ ์ํด ์ฌ์ฉ๋๋ค.
customer_id์ MIN(sales_ymd)์ 'RECIPATE' ํ ์ด๋ธ์์ ์ ํํ๊ณ ์ ํ๋ ์ปฌ๋ผ์ด๋ฉฐ, MIN ํจ์๋ 'customer_id' ์ปฌ๋ผ์ ๊ณ ์ ํ ๊ณ ๊ฐ ID๋ง๋ค 'sales_ymd' ์ปฌ๋ผ์ ์ต์๊ฐ์ ์ ํํ๊ธฐ ์ํด ์ฌ์ฉ๋๋ค.
FROM ํค์๋๋ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ฌ ํ ์ด๋ธ์ ์ง์ ํ๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, ์ด ๊ฒฝ์ฐ 'receive' ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค.
GROUP BY ๊ตฌ๋ฌธ์ ๊ณ ๊ฐ ID๋ณ๋ก ๋งค์ถ ๋ฐ์ดํฐ๋ฅผ ๊ทธ๋ฃนํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ฆ, ์ด ์ฟผ๋ฆฌ๋ 'customer_id' ์ด์ ๊ณ ์ ํ ๊ณ ๊ฐ ID๋ณ๋ก ์ต์ ๋งค์ถ ๋ ์ง ๊ฐ์ ๋ฐํํ๋ค.
LIMIT ํค์๋๋ ์ฟผ๋ฆฌ๊ฐ ๋ฐํํ๋ ๊ฒฐ๊ณผ์ ์๋ฅผ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด ๊ฒฝ์ฐ 'LIMIT 10' ๋ฌธ์ ๊ฒฐ๊ณผ๋ฅผ ์์ 10๊ฐ์ ํ์ผ๋ก ์ ํํฉ๋๋ค.
์ฆ, ์์ฝํ๋ฉด ์ด ์ฟผ๋ฆฌ๋ 'receipt' ํ ์ด๋ธ์์ ๊ฐ ๊ณ ๊ฐ์ ์ต์ ๋งค์ถ์ผ์ ์ ํํ์ฌ ๋ฐํํ๊ณ ๊ฒฐ๊ณผ๋ฅผ ์์ 10๋ช ์ ๊ณ ๊ฐ์ผ๋ก ์ ํํ๊ณ ์๋ค.
S-026: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋ํด ๊ณ ๊ฐ ID(customer_id)๋ณ๋ก ๊ฐ์ฅ ์ต๊ทผ ๋งค์ถ ๋ ์ง(sales_ymd)์ ๊ฐ์ฅ ์ค๋๋ ๋งค์ถ ๋ ์ง(sales_ymd)๋ฅผ ๊ตฌํ๊ณ , ์์๊ฐ ์๋ก ๋ค๋ฅธ ๋ฐ์ดํฐ 10๊ฑด์ ํ์ํ๋ผ.
%%sql
SELECT
customer_id,
MAX(sales_ymd),
MIN(sales_ymd)
FROM receipt
GROUP BY customer_id
HAVING MAX(sales_ymd) != MIN(sales_ymd)
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | max | min |
---|---|---|
CS029212000033 | 20180621 | 20170318 |
CS007515000119 | 20190511 | 20170201 |
CS034515000123 | 20190708 | 20170527 |
CS026414000014 | 20190720 | 20170718 |
CS010515000082 | 20181204 | 20180518 |
CS019315000045 | 20170920 | 20170423 |
CS008513000099 | 20190308 | 20170722 |
CS007615000070 | 20191025 | 20170929 |
CS025415000155 | 20191026 | 20170314 |
CS016414000063 | 20190617 | 20170109 |
์ค๋ช :
์ด ์ฝ๋๋ "receipt"๋ผ๋ ํ ์ด๋ธ์์ ๊ณ ๊ฐ ID, ์ต๋ ๋งค์ถ์ผ, ์ต์ ๋งค์ถ์ผ์ ์ ํํ๋ SQL ์ฟผ๋ฆฌ์ด๋ค.
sql์ ์ด ์ฝ๋๊ฐ SQL ์ฝ๋์์ ๋ํ๋ด๋ Jupyter Notebook์ ์ ๋งค์ง ๋ช ๋ น์ด์ด๋ค.
SELECT ํค์๋๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค๋ ๊ฒ์ ๋ํ๋ด๊ธฐ ์ํด ์ฌ์ฉ๋๋ค.
customer_id, MAX(sales_ymd), MIN(sales_ymd)์ 'recipe' ํ ์ด๋ธ์์ ์ ํํ๊ณ ์ ํ๋ ์ปฌ๋ผ์ด๋ฉฐ, MAX๋ 'customer_id' ์ปฌ๋ผ์ ๊ณ ์ ํ ๊ณ ๊ฐ ID๋ณ๋ก 'sales_ymd' ์ปฌ๋ผ์ ์ต๋๊ฐ์ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, 'MIN'์ 'recipe' ์ปฌ๋ผ์ ์ต๋๊ฐ์ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. 'MIN'์ 'customer_id' ์ด์ ๊ณ ์ ํ ๊ณ ๊ฐ ID๋ณ๋ก 'sales_ymd' ์ด์ ์ต์๊ฐ์ ์ ํํ๊ธฐ ์ํด ์ฌ์ฉ๋๋ค.
FROM ํค์๋๋ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๊ณ ์ ํ๋ ํ ์ด๋ธ์ ๋ํ๋ด๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, ์ด ๊ฒฝ์ฐ 'receive' ํ ์ด๋ธ์ด๋ค.
GROUP BY ๊ตฌ๋ฌธ์ ๊ณ ๊ฐ ID๋ณ๋ก ๋งค์ถ ๋ฐ์ดํฐ๋ฅผ ๊ทธ๋ฃนํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ฆ, ์ด ์ฟผ๋ฆฌ๋ "customer_id" ์ด์ ๊ณ ์ ํ ๊ณ ๊ฐ ID๋ณ๋ก ๋งค์ถ ๋ ์ง์ ์ต๋๊ฐ๊ณผ ์ต์๊ฐ์ ๋ฐํํ๋ค.
HAVING ์ ์ ํน์ ์กฐ๊ฑด์ ๋ฐ๋ผ ๊ฒฐ๊ณผ๋ฅผ ํํฐ๋งํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด ๊ฒฝ์ฐ, ์ฃผ์ด์ง ๊ณ ๊ฐ ID์ ๋ํด ์ต๋ ํ๋งค์ผ๊ณผ ์ต์ ํ๋งค์ผ์ด ๊ฐ์ง ์์์ผ ํ๋ค๋ ์กฐ๊ฑด์ด ์๋ค. ์ฆ, ์ด ์ฟผ๋ฆฌ๋ ์๋ก ๋ค๋ฅธ ๋ ์ง์ ๊ตฌ๋งคํ ๊ณ ๊ฐ๋ง ๋ฐํํ๊ฒ ๋ฉ๋๋ค.
LIMIT ํค์๋๋ ์ฟผ๋ฆฌ๊ฐ ๋ฐํํ๋ ๊ฒฐ๊ณผ์ ์๋ฅผ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด ๊ฒฝ์ฐ 'LIMIT 10' ๋ฌธ์ ๊ฒฐ๊ณผ๋ฅผ ์์ 10๊ฐ์ ํ์ผ๋ก ์ ํํ๋ค.
์ฆ, ์ด ์ฟผ๋ฆฌ๋ '์์์ฆ' ํ ์ด๋ธ์์ ์๋ก ๋ค๋ฅธ ๋ ์ง์ ๊ตฌ๋งคํ ๊ณ ๊ฐ์ ๊ณ ๊ฐ ID, ์ต๋ ํ๋งค์ผ, ์ต์ ํ๋งค์ผ์ ์ ํํ์ฌ ๋ฐํํ๊ณ , ๊ฒฐ๊ณผ๋ฅผ ์์ 10๋ช ์ ๊ณ ๊ฐ์ผ๋ก ์ ํํ๊ณ ์๋ค.
ย
S-027: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋ํด ๋งค์ฅ ์ฝ๋(store_cd)๋ณ ๋งค์ถ ๊ธ์ก(amount)์ ํ๊ท ์ ๊ณ์ฐํ์ฌ ๋ด๋ฆผ์ฐจ์์ผ๋ก TOP5๋ฅผ ํ์ํ๋ผ.
%%sql
SELECT
store_cd,
AVG(amount) AS avg_amount
FROM receipt
GROUP BY store_cd
ORDER BY avg_amount DESC
LIMIT 5
;
* postgresql://padawan:***@db:5432/dsdojo_db 5 rows affected.
store_cd | avg_amount |
---|---|
S13052 | 402.8674698795180723 |
S13015 | 351.1119604316546763 |
S13003 | 350.9155188246097337 |
S14010 | 348.7912621359223301 |
S13001 | 348.4703862660944206 |
์ค๋ช :
์ด ์ฝ๋๋ "receipt"๋ผ๋ ํ ์ด๋ธ์์ ๋งค์ฅ ์ฝ๋์ ๊ทธ ํ๊ท ๋งค์ถ์ก์ ์ ํํ๋ SQL ์ฟผ๋ฆฌ์ด๋ค.
sql์ ์ด ์ฝ๋๊ฐ SQL ์ฝ๋์์ ๋ํ๋ด๋ Jupyter Notebook์ ์ ๋งค์ง ๋ช ๋ น์ด์ด๋ค.
SELECT ํค์๋๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์จ๋ค๋ ๊ฒ์ ๋ํ๋ด๊ธฐ ์ํด ์ฌ์ฉ๋๋ค.
"store_cd"์ "AVG(amount) AS avg_amount"๋ "receipt" ํ ์ด๋ธ์์ ์ ํํ๊ณ ์ ํ๋ ์ปฌ๋ผ์ด๋ฉฐ, AVG ํจ์๋ "store_cd" ์ปฌ๋ผ์ ๊ณ ์ ํ ๋งค์ฅ ์ฝ๋๋ณ ํ๊ท ํ๋งค ๊ธ์ก์ ๊ณ์ฐํ๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, AS ํค์๋๋ ์ด ์ปฌ๋ผ์ "AVG(amount) AS avg_amount"๋ฅผ ์ถ๊ฐํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. avg_amount'๋ผ๋ ๋ณ์นญ์ ๋ถ์ฌํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ์ด ์ด๋ฆ์ ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ์ ํ์๋๋ ์ด๋ฆ์ ๋๋ค.
FROM ํค์๋๋ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ฌ ํ ์ด๋ธ์ ๋ํ๋ด๋ ๋ฐ ์ฌ์ฉ๋๋ฉฐ, ์ด ๊ฒฝ์ฐ "receipt" ํ ์ด๋ธ์ด๋ค.
GROUP BY ๊ตฌ๋ฌธ์ ํ๋งค ๋ฐ์ดํฐ๋ฅผ ๋งค์ฅ ์ฝ๋๋ณ๋ก ๊ทธ๋ฃนํํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ์ฆ, ์ด ์ฟผ๋ฆฌ๋ 'store_cd' ์ด์ ๊ณ ์ ํ ๋งค์ฅ ์ฝ๋๋ณ๋ก ํ๊ท ๋งค์ถ ๊ธ์ก์ ๊ณ์ฐํ๋ ๊ฒ์ด๋ค.
ORDER BY ๊ตฌ๋ฌธ์ ํ๊ท ๋งค์ถ์ก์ ๊ธฐ์ค์ผ๋ก ๊ฒฐ๊ณผ๋ฅผ ๋ด๋ฆผ์ฐจ์์ผ๋ก ์ ๋ ฌํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ์ฆ, ํ๊ท ๋งค์ถ ๊ธ์ก์ด ๊ฐ์ฅ ๋์ ๋งค์ฅ์ด ๊ฒฐ๊ณผ์ ์๋จ์ ํ์๋๋ ๊ฒ์ ๋๋ค.
LIMIT ํค์๋๋ ์ฟผ๋ฆฌ๊ฐ ๋ฐํํ๋ ๊ฒฐ๊ณผ์ ์๋ฅผ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋ฉ๋๋ค. ์ด ๊ฒฝ์ฐ 'LIMIT 5' ๋ฌธ์ฅ์ ๊ฒฐ๊ณผ๋ฅผ ์์ 5๊ฐ ๋งค์ฅ์ผ๋ก ์ ํํฉ๋๋ค.
์ฆ, ์ด ์ฟผ๋ฆฌ๋ '์์์ฆ' ํ ์ด๋ธ์์ ๋งค์ฅ ์ฝ๋์ ํ๊ท ๋งค์ถ์ก์ ์ ํํ๊ณ , ํ๊ท ๋งค์ถ์ก์ ๊ธฐ์ค์ผ๋ก ๋ด๋ฆผ์ฐจ์์ผ๋ก ์ ๋ ฌํ์ฌ ์์ 5๊ฐ ๋งค์ฅ์ผ๋ก๋ง ๊ฒฐ๊ณผ๋ฅผ ์ ํํ์ฌ ๋ฐํํฉ๋๋ค.
S-028: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋ํด ๋งค์ฅ ์ฝ๋(store_cd)๋ณ ๋งค์ถ ๊ธ์ก(amount)์ ์ค์๊ฐ์ ๊ณ์ฐํ์ฌ ๋ด๋ฆผ์ฐจ์์ผ๋ก TOP5๋ฅผ ํ์ํ๋ผ.
%%sql
SELECT
store_cd,
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY amount) AS amount_50per
FROM receipt
GROUP BY store_cd
ORDER BY amount_50per DESC
LIMIT 5
;
* postgresql://padawan:***@db:5432/dsdojo_db 5 rows affected.
store_cd | amount_50per |
---|---|
S13052 | 190.0 |
S14010 | 188.0 |
S14050 | 185.0 |
S13003 | 180.0 |
S13018 | 180.0 |
์ค๋ช :
๋ค์์ "receipt"๋ผ๋ ์ด๋ฆ์ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๋ SQL ์ฟผ๋ฆฌ์ด๋ค.
์ด ์ฟผ๋ฆฌ๋ ํ ์ด๋ธ์์ ๋ ๊ฐ์ ์ปฌ๋ผ์ ์ ํํฉ๋๋ค." store_cd"์ "amount_50per"๋ค. "store_cd"์ด์ ๋งค์ฅ ์ฝ๋๋ฅผ ๋ํ๋ด๊ณ , "amount_50per"์ด์ ๊ฐ ๋งค์ฅ ๋ด ์์์ฆ 1์ฅ๋น ์ฌ์ฉ ๊ธ์ก์ ์ค๊ฐ๊ฐ์ ๊ณ์ฐํ๋ค.
์ค์๊ฐ ๊ณ์ฐ์ ์ฌ์ฉํ๋ ํจ์๋ 'PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY amount)'์ด๋ค. ์ด ํจ์๋ '๊ธ์ก' ์ด์ ์ค๋ฆ์ฐจ์์ผ๋ก ์ ๋ ฌํ์ฌ 50ํผ์ผํ์ผ ๊ฐ์ ๊ณ์ฐํ๋ค. ์ฆ, ์์๋๋ก ์ ๋ ฌ๋ ๊ธ์ก ๋ชฉ๋ก์ ์ค๊ฐ ๊ฐ์ ์ฐพ๋ ๊ฒ์ด๋ค.
์ด ์ฟผ๋ฆฌ๋ 'GROUP BY' ๊ตฌ๋ฌธ์ผ๋ก ๊ฒฐ๊ณผ๋ฅผ ์์ ์ฝ๋๋ณ๋ก ๊ทธ๋ฃนํํ๊ณ , 'ORDER BY' ๊ตฌ๋ฌธ์ผ๋ก ๊ธ์ก์ ์ค๊ฐ๊ฐ ๋ด๋ฆผ์ฐจ์์ผ๋ก ๊ฒฐ๊ณผ๋ฅผ ์ ๋ ฌํ๋ค. ๊ทธ๋ฆฌ๊ณ 'LIMIT' ์ ์ ์ฌ์ฉํ์ฌ ์ถ๋ ฅ์ ์์ 5๊ฑด์ผ๋ก ์ ํํ๊ณ ์์ต๋๋ค.
์ฆ, ์ด ์ฟผ๋ฆฌ๋ ์์์ฆ ํ ์ฅ๋น ์ฌ์ฉ ๊ธ์ก์ ์ค๊ฐ๊ฐ์ด ๊ฐ์ฅ ๋์ ์์ 5๊ฐ ๋งค์ฅ์ ๋ฐํํฉ๋๋ค.
ย
S-029: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋ํด ๋งค์ฅ ์ฝ๋(store_cd)๋ณ๋ก ์ํ ์ฝ๋(product_cd)์ ์ต๋น๊ฐ์ ๊ตฌํ์ฌ 10๊ฑด์ฉ ํ์ํ๋ผ.
%%sql
-- ์ฝ๋ ์์ 1: window ํจ์๋ ๋ถ์ ํจ์์์ ๊ฐ์ฅ ๋น๋ฒํ ๊ฐ ์ง๊ณํ๊ธฐ
WITH product_cnt AS (
SELECT
store_cd,
product_cd,
COUNT(1) AS mode_cnt
FROM receipt
GROUP BY
store_cd,
product_cd
),
product_mode AS (
SELECT
store_cd,
product_cd,
mode_cnt,
RANK() OVER(PARTITION BY store_cd ORDER BY mode_cnt DESC) AS rnk
FROM product_cnt
)
SELECT
store_cd,
product_cd,
mode_cnt
FROM product_mode
WHERE
rnk = 1
ORDER BY
store_cd,
product_cd
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
store_cd | product_cd | mode_cnt |
---|---|---|
S12007 | P060303001 | 72 |
S12013 | P060303001 | 107 |
S12014 | P060303001 | 65 |
S12029 | P060303001 | 92 |
S12030 | P060303001 | 115 |
S13001 | P060303001 | 67 |
S13002 | P060303001 | 78 |
S13003 | P071401001 | 65 |
S13004 | P060303001 | 88 |
S13005 | P040503001 | 36 |
์ค๋ช :
"receipt"๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๋ SQL ์ฟผ๋ฆฌ์ ๋๋ค.
์ด ์ฟผ๋ฆฌ์์๋ "product_cnt"๋ผ๋ CTE(Common Table Expression)๋ฅผ ์ฌ์ฉํ์ฌ ๊ฐ ์ํ์ด ๊ฐ ๋งค์ฅ์์ ํ๋งค๋ ํ์๋ฅผ ๊ณ์ฐํ๊ณ , "GROUP BY" ๊ตฌ๋ฌธ์ ์ฌ์ฉํ์ฌ "store_cd"์ "product_cd"๋ก ๋ฐ์ดํฐ๋ฅผ ๊ทธ๋ฃนํํ๊ณ , "COUNT(1)" ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ฐ ๊ทธ๋ฃน๋ณ ํ ์๋ฅผ ๊ณ์ฐํฉ๋๋ค. COUNT(1)' ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ฐ ๊ทธ๋ฃน์ ํ ์๋ฅผ ๊ณ์ฐํ๋ค. ๊ฒฐ๊ณผ๋ "mode_cnt" ์ปฌ๋ผ์ ์ ์ฅ๋๋ค.
๊ทธ๋ฐ ๋ค์ ์ด ์ฟผ๋ฆฌ๋ "product_mode"๋ผ๋ ๋ ๋ค๋ฅธ CTE๋ฅผ ์ฌ์ฉํ์ฌ "RANK() OVER(PARTITION BY store_cd ORDER BY mode_cnt DESC)" ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ฐ ๋งค์ฅ์ ์ํ์ "mode_cnt" ๊ฐ์ ์์๋ฅผ ๋งค๊ธด๋ค. ์ด ํจ์๋ ๊ฐ ์ํ์ "mode_cnt" ๊ฐ์ ๋ฐ๋ผ ๊ฐ ๋งค์ฅ ๋ด ์์๋ฅผ ๋ถ์ฌํ๋ค. ๋ชจ๋ ์นด์ดํธ๊ฐ ๊ฐ์ฅ ๋์ ์ํ์ 1์์๋ฅผ ๋ถ์ฌํ๋ค.
๋ง์ง๋ง์ผ๋ก ์ด ์ฟผ๋ฆฌ๋ "WHERE rnk = 1" ๊ตฌ๋ฌธ์ผ๋ก ๊ฒฐ๊ณผ๋ฅผ ํํฐ๋งํ์ฌ ๊ฐ ๋งค์ฅ์์ ๊ฐ์ฅ ๋ง์ด ํ๋ฆฌ๋ ์ํ์ ์ ํํ๊ณ "ORDER BY" ๊ตฌ๋ฌธ์ผ๋ก ๋งค์ฅ ๋ฐ ์ํ ์ฝ๋๋ณ๋ก ์ ๋ ฌํ๋ค. 'LIMIT' ๊ตฌ๋ฌธ์ผ๋ก ์ถ๋ ฅ์ ์ฒ์ 10์ค๋ก ์ ํํ๊ณ , ๊ฐ ๋งค์ฅ์ ์ธ๊ธฐ ์ํ์ ๋งค์ฅ ์ฝ๋, ์ํ ์ฝ๋, ๋ชจ๋ ์๋ฅผ ๋ฐํํ๋ค.
์์ฝํ๋ฉด, ์ด ์ฟผ๋ฆฌ๋ ํ๋งค ํ์๋ฅผ ๊ธฐ์ค์ผ๋ก ๊ฐ ๋งค์ฅ์์ ๊ฐ์ฅ ๋ง์ด ํ๋ฆฐ ์ํ์ ์ฐพ์ ์์ 10๊ฐ ์ํ์ ๋งค์ฅ ์ฝ๋, ์ํ ์ฝ๋, ๋ชจ๋ ์๋ฅผ ๊ฐ๊ฐ ๋ฐํํ๋ค.
ย
%%sql
-- ์ฝ๋ ์์ 2: MODE()๋ฅผ ์ฌ์ฉํ๋ ๊ฐ๋จํ ๊ฒฝ์ฐ(๋น ๋ฅด์ง๋ง, ์ต๋๊ฐ์ด ์ฌ๋ฌ ๊ฐ์ผ ๊ฒฝ์ฐ ํ ๊ฐ๋ง ์ ํ๋จ)
SELECT
store_cd,
MODE() WITHIN GROUP(ORDER BY product_cd)
FROM receipt
GROUP BY store_cd
ORDER BY store_cd
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
store_cd | mode |
---|---|
S12007 | P060303001 |
S12013 | P060303001 |
S12014 | P060303001 |
S12029 | P060303001 |
S12030 | P060303001 |
S13001 | P060303001 |
S13002 | P060303001 |
S13003 | P071401001 |
S13004 | P060303001 |
S13005 | P040503001 |
์ค๋ช :
๋ค์์ "receipt"๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๋ SQL ์ฟผ๋ฆฌ์ด๋ค.
์ด ์ฟผ๋ฆฌ์์๋ ๋ ๊ฐ์ ์ปฌ๋ผ์ ์ ํํ๋ค." store_cd"์ ๊ฐ ๋งค์ฅ์ "product_cd" ์ด์ ๋ชจ๋์ ๋๋ค.
'MODE() WITHIN GROUP(ORDER BY product_cd)' ํจ์๋ 'store_cd'์ ๊ฐ ๊ทธ๋ฃน ๋ด 'product_cd' ๊ฐ์ ๋ชจ๋๋ฅผ ๊ณ์ฐํ๋ค. mode๋ ๋ฐ์ดํฐ ์งํฉ์์ ๊ฐ์ฅ ๋น๋ฒํ๊ฒ ๋ํ๋๋ ๊ฐ์ ์๋ฏธํ๋ฉฐ, ORDER BY ๊ตฌ๋ฌธ์ ๋ชจ๋๋ฅผ ๊ณ์ฐํ๊ธฐ ์ ์ product_cd ๊ฐ์ ์ค๋ฆ์ฐจ์์ผ๋ก ์ ๋ ฌํ ๊ฒ์ ์ง์ ํ๋ค.
์ด ์ฟผ๋ฆฌ๋ 'GROUP BY' ์ ์ ์ฌ์ฉํ์ฌ 'store_cd'๋ก ๋ฐ์ดํฐ๋ฅผ ๊ทธ๋ฃนํํ๊ณ , 'ORDER BY' ์ ์ ์ฌ์ฉํ์ฌ 'store_cd'๋ก ๊ฒฐ๊ณผ๋ฅผ ์ ๋ ฌํ๋ค. ๋ํ 'LIMIT' ๊ตฌ๋ฌธ์ผ๋ก ์ถ๋ ฅ์ ์ฒ์ 10์ค๋ก ์ ํํ๊ณ ์๋ค.
์ฆ, ์ด ์ฟผ๋ฆฌ๋ ๊ฐ ๋งค์ฅ์ 'product_cd' ๊ฐ์ ๋ชจ๋๋ฅผ ๋ฐํํ๋๋ฐ, ์ด๋ ๊ฐ ๋งค์ฅ์์ ๊ฐ์ฅ ๋ง์ด ํ๋งค๋๋ ์ํ์ ๋ํ๋ธ๋ค. ๊ฒฐ๊ณผ๋ ๋งค์ฅ ์ฝ๋ ์์ผ๋ก ์ ๋ ฌ๋๋ฉฐ ์์ 10๊ฐ ๋งค์ฅ์ผ๋ก ์ ํ๋๋ค.
S-030: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋ํด ๋งค์ฅ ์ฝ๋(store_cd)๋ณ ๋งค์ถ ๊ธ์ก(amount)์ ๋ถ์ฐ์ ๊ณ์ฐํ์ฌ ๋ด๋ฆผ์ฐจ์์ผ๋ก 5๊ฐ์ฉ ํ์ํ์์ค.
%%sql
SELECT
store_cd,
VAR_POP(amount) AS vars_amount
FROM receipt
GROUP BY store_cd
ORDER BY vars_amount DESC
LIMIT 5
;
* postgresql://padawan:***@db:5432/dsdojo_db 5 rows affected.
store_cd | vars_amount |
---|---|
S13052 | 440088.701311269173 |
S14011 | 306314.558163888889 |
S14034 | 296920.081011283873 |
S13001 | 295431.993329035348 |
S13015 | 295294.361115940880 |
์ค๋ช :
๋ค์์ "receipt"๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๋ SQL ์ฟผ๋ฆฌ์ด๋ค.
์ด ์ฟผ๋ฆฌ์์๋ ๋ ๊ฐ์ ์ปฌ๋ผ์ ์ ํํ๋ค." store_cd"์ ๊ฐ ๋งค์ฅ์ "amount" ์ปฌ๋ผ์ ๋ชจ๋ถ์ฐ์ด๋ค.
'VAR_POP(amount)' ํจ์๋ 'store_cd'์ ๊ฐ ๊ทธ๋ฃน ๋ด 'amount' ๊ฐ์ ๋ถ์ฐ์ ๊ณ์ฐํ๋ค. ๋ถ์ฐ์ ๋ฐ์ดํฐ ์ธํธ์ ํ์ฐ๊ณผ ๋ถ์ฐ์ ๋ํ๋ด๋ ์งํ์ด๋ฉฐ, "VAR_POP"์ "POP"๋ "population"์ ์๋ฏธํ๋ฉฐ, ๋ถ์ฐ ๊ณ์ฐ์ ์ํด ์ ์ฒด ๋ฐ์ดํฐ ์ธํธ๊ฐ ์ฌ์ฉ๋จ์ ์๋ฏธํ๋ค.
์ด ์ฟผ๋ฆฌ๋ "GROUP BY" ์ ์ ์ฌ์ฉํ์ฌ "store_cd"๋ก ๋ฐ์ดํฐ๋ฅผ ๊ทธ๋ฃนํํ๊ณ "ORDER BY" ์ ์ ์ฌ์ฉํ์ฌ ๊ฐ ๋งค์ฅ์ "amount" ๊ฐ์ ๋ถ์ฐ์ ํฌํจํ๋ "vars_amount" ์ด๋ก ๊ฒฐ๊ณผ๋ฅผ ๋ด๋ฆผ์ฐจ์์ผ๋ก ์ ๋ ฌํ๊ณ "LIMIT" ์ ์ ์ฌ์ฉํ์ฌ ์ถ๋ ฅ์ ์ฒ์ 5๊ฐ ํ์ผ๋ก ์ ํํ๋ค. ํ์ผ๋ก ์ถ๋ ฅ์ ์ ํํฉ๋๋ค.
์ฆ, ์ด ์ฟผ๋ฆฌ๋ '๊ธ์ก' ๊ฐ์ ๋ชจ์ง๋จ ๋ถ์ฐ์ด ๊ฐ์ฅ ํฐ ์์ 5๊ฐ ๋งค์ฅ์ ๋ฐํํ๋๋ฐ, ์ด๋ ์ด๋ค ๋งค์ฅ์ด ์์์ฆ ํ ์ฅ๋น ์ฌ์ฉ ๊ธ์ก์ ํ์ฐ๊ณผ ๋ถ์ฐ์ด ๊ฐ์ฅ ํฌ๋ค๋ ๊ฒ์ ์๋ฏธํ๋ค.
ย
S-031: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋ํด ๋งค์ฅ ์ฝ๋(store_cd)๋ณ ๋งค์ถ ๊ธ์ก(amount)์ ํ์คํธ์ฐจ๋ฅผ ๊ณ์ฐํ์ฌ ๋ด๋ฆผ์ฐจ์์ผ๋ก 5๊ฑด์ฉ ํ์ํ๋ผ.
%%sql
SELECT
store_cd,
STDDEV_POP(amount) as stds_amount
FROM receipt
GROUP BY store_cd
ORDER BY stds_amount DESC
LIMIT 5
;
* postgresql://padawan:***@db:5432/dsdojo_db 5 rows affected.
store_cd | stds_amount |
---|---|
S13052 | 663.391815830787 |
S14011 | 553.456916267101 |
S14034 | 544.903735545357 |
S13001 | 543.536561170484 |
S13015 | 543.409938366921 |
์ค๋ช :
๋ค์์ "receipt"๋ผ๋ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๋ SQL ์ฟผ๋ฆฌ์ด๋ค.
์ด ์ฟผ๋ฆฌ์์๋ ๋ ๊ฐ์ ์ปฌ๋ผ์ ์ ํํฉ๋๋ค." store_cd"์ ๊ฐ ๋งค์ฅ์ "amount" ์ปฌ๋ผ์ ๋ชจํ์คํธ์ฐจ์ด๋ค.
"STDDEV_POP(amount)" ํจ์๋ "store_cd"์ ๊ฐ ๊ทธ๋ฃน ๋ด "amount" ๊ฐ์ ํ์คํธ์ฐจ๋ฅผ ๊ณ์ฐํ๋ค. ํ์คํธ์ฐจ๋ ๋ฐ์ดํฐ ์ธํธ์ ํ์ฐ๊ณผ ๋ถ์ฐ์ ๋ํ๋ด๋ ์งํ๋ก, "STDDEV_POP"์ "POP"๋ "population"์ ์๋ฏธํ๋ฉฐ, ํ์คํธ์ฐจ ๊ณ์ฐ์ ์ ์ฒด ๋ฐ์ดํฐ ์ธํธ๊ฐ ์ฌ์ฉ๋จ์ ์๋ฏธํ๋ค.
์ด ์ฟผ๋ฆฌ๋ "GROUP BY" ๊ตฌ๋ฌธ์ ์ฌ์ฉํ์ฌ "store_cd"๋ก ๋ฐ์ดํฐ๋ฅผ ๊ทธ๋ฃนํํ๊ณ "ORDER BY" ๊ตฌ๋ฌธ์ ์ฌ์ฉํ์ฌ ๋ด๋ฆผ์ฐจ์์ผ๋ก ๊ฐ ๋งค์ฅ์ "๊ธ์ก"์ ํ์คํธ์ฐจ๋ฅผ ํฌํจํ๋ "stds_amount" ์ด์ ๊ฒฐ๊ณผ๋ฅผ ์ ๋ ฌํ๊ณ "LIMIT" ๊ตฌ๋ฌธ์ ์ฌ์ฉํ์ฌ ์ถ๋ ฅ์ ์ฒ์ 5์ค๋ก ์ ํํ๋ค.
์ฆ, ์ด ์ฟผ๋ฆฌ๋ '๊ธ์ก' ๊ฐ์ ๋ชจํ์คํธ์ฐจ๊ฐ ๊ฐ์ฅ ๋์ ์์ 5๊ฐ ๋งค์ฅ์ ๋ฐํํ๋ฏ๋ก, ์ด๋ค ๋งค์ฅ์ ์์์ฆ ํ ์ฅ๋น ์ฌ์ฉ ๊ธ์ก์ ํ์ฐ๊ณผ ๋ถ์ฐ์ด ๊ฐ์ฅ ๋๋ค๋ ๊ฒ์ ์๋ฏธํ๋ค.
ย
S-032: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ถ ๊ธ์ก(amount)์ ๋ํด 25% ๋จ์๋ก ๋ฐฑ๋ถ์์ ๊ฐ์ ๊ตฌํ๋ผ.
%%sql
SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY amount) AS amount_25per,
PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY amount) AS amount_50per,
PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY amount) AS amount_75per,
PERCENTILE_CONT(1.0) WITHIN GROUP(ORDER BY amount) AS amount_100per
FROM receipt
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
amount_25per | amount_50per | amount_75per | amount_100per |
---|---|---|---|
102.0 | 170.0 | 288.0 | 10925.0 |
์ค๋ช :
"recipate" ํ ์ด๋ธ์ "amount" ์ปฌ๋ผ์ ๋ฐฑ๋ถ์์ ๊ฐ์ ๊ณ์ฐํ๊ธฐ ์ํด ์ฌ์ฉํ๋ SQL ์ฝ๋์ด๋ค. ์ด ์ฝ๋์์๋ PERCENTILE_CONT ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ์ฐ์์ ์ธ ๋ฐฑ๋ถ์์๋ฅผ ๊ณ์ฐํ๊ณ ์๋ค. ์ด๋ ๋ฐฑ๋ถ์์ ๊ฐ์ด ๋ฐ์ดํฐ ํฌ์ธํธ ๊ฐ์ ๋ณด๊ฐ๊ฐ์ด ์๋๋ผ ๋ฐ์ดํฐ ์งํฉ์ ์ค์ ๊ฐ์ ํด๋นํ๋ค๋ ๊ฒ์ ์๋ฏธํฉ๋๋ค.
์ด ์ฝ๋์์๋ 25๋ฒ์งธ ๋ฐฑ๋ถ์์(amount_25per), 50๋ฒ์งธ ๋ฐฑ๋ถ์์(amount_50per), 75๋ฒ์งธ ๋ฐฑ๋ถ์์(amount_75per), 100๋ฒ์งธ ๋ฐฑ๋ถ์์(amount_100per)์ ๋ค ๊ฐ์ง ๋ฐฑ๋ถ์์ ๊ฐ์ ์ง์ ํ์ฌ ๊ณ์ฐํ๊ณ ์๋ค. ์ด ๋ฐฑ๋ถ์์๋ ๋ฐฑ๋ถ์์๋ฅผ ๊ณ์ฐํ๊ธฐ ์ ์ WITHIN GROUP ์ ์ ์ฌ์ฉํ์ฌ 'amount' ์ด์ ์ค๋ฆ์ฐจ์์ผ๋ก ์ ๋ ฌํฉ๋๋ค.
์ฝ๋ ๋ง์ง๋ง์ ์๋ SELECT ๋ฌธ์ ๊ณ์ฐ๋ ๋ฐฑ๋ถ์์ ๊ฐ์ ์ฟผ๋ฆฌ์์ ๊ฐ์ ธ์ ํ ์ค์ ์ถ๋ ฅ์ผ๋ก ๋ฐํํ๋ค. ์ด ์ฝ๋๋ 'RECIPATE' ํ ์ด๋ธ์ 'AMOUNT' ์ปฌ๋ผ์ ๋ถํฌ๋ฅผ ๋ถ์ํ์ฌ ๋ฐ์ดํฐ ๋ด ์ด์๊ฐ์ด๋ ๋น์ ์์ ์ธ ํจํด์ ์๋ณํ๋ ๋ฐ ์ ์ฉํ๋ค.
S-033: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋ํด ๋งค์ฅ ์ฝ๋(store_cd)๋ณ ๋งค์ถ ๊ธ์ก(amount)์ ํ๊ท ์ ๊ณ์ฐํ์ฌ 330๊ฐ ์ด์์ ์ถ์ถํ๋ผ.
%%sql
SELECT
store_cd,
AVG(amount) AS avg_amount
FROM receipt
GROUP BY store_cd
HAVING
AVG(amount) >= 330
;
* postgresql://padawan:***@db:5432/dsdojo_db 13 rows affected.
store_cd | avg_amount |
---|---|
S13052 | 402.8674698795180723 |
S13019 | 330.2086158755484643 |
S13003 | 350.9155188246097337 |
S14045 | 330.0820734341252700 |
S13004 | 330.9439490445859873 |
S13001 | 348.4703862660944206 |
S14026 | 332.3405884723901653 |
S14010 | 348.7912621359223301 |
S13015 | 351.1119604316546763 |
S12013 | 330.1941299790356394 |
S14047 | 330.0770731707317073 |
S14011 | 335.7183333333333333 |
S13020 | 337.8799321170980059 |
์ค๋ช :
๋ค์์ "receipt" ํ ์ด๋ธ์ ๊ฐ ๋งค์ฅ์ ํ๊ท ๊ฑฐ๋๊ธ์ก์ ์กฐํํ๊ณ ์กฐ๊ฑด์ ๋ฐ๋ผ ๊ฒฐ๊ณผ๋ฅผ ํํฐ๋งํ๋ ๋ฐ ์ฌ์ฉํ๋ SQL ์ฝ๋์ ๋๋ค.
์ด ์ฝ๋์์๋ ๋จผ์ SELECT ๋ฌธ์์ ์กฐํํ ๋ ๊ฐ์ ์ปฌ๋ผ('store_cd' ์ปฌ๋ผ๊ณผ ๊ฐ ๋งค์ฅ์ '๊ธ์ก' ์ปฌ๋ผ์ ํ๊ท ๊ฐ)์ ์ง์ ํ๊ณ , AVG ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ๊ฐ ๋งค์ฅ์ 'amount' ์ปฌ๋ผ์ ํ๊ท ์ ๊ณ์ฐํ ํ, AS ํค์๋๋ฅผ ์ฌ์ฉํ์ฌ ๊ณ์ฐ๋ ์ปฌ๋ผ์ 'avg_amount' ๋ผ๋ ๋ณ์นญ์ ๋ถ์ฌํ๊ณ ์์ต๋๋ค. ๋ผ๋ ๋ณ์นญ์ ๋ถ์ฌํ๊ณ ์์ต๋๋ค.
๋ค์ ์ฝ๋ ๋ผ์ธ์์๋ GROUP BY ๊ตฌ๋ฌธ์ ์ฌ์ฉํ์ฌ 'store_cd' ์ด๋ก ๋ฐ์ดํฐ๋ฅผ ๊ทธ๋ฃนํํ์ฌ ํ๊ท ๊ฑฐ๋ ๊ธ์ก์ด ๊ฐ ๋งค์ฅ์์ ๊ฐ๋ณ์ ์ผ๋ก ๊ณ์ฐ๋จ์ ์๋ฏธํฉ๋๋ค.
๊ทธ๋ฐ ๋ค์ ์ด ์ฝ๋๋ HAVING ์ ์ ์ฌ์ฉํ์ฌ ์กฐ๊ฑด์ ๋ฐ๋ผ ๊ฒฐ๊ณผ๋ฅผ ํํฐ๋งํ๋ค. ๊ตฌ์ฒด์ ์ผ๋ก ๊ฐ ๋งค์ฅ์ ํ๊ท ๊ฑฐ๋์ก์ด 330 ์ด์์ผ ๊ฒ์ ์กฐ๊ฑด์ผ๋ก ํ๋ค. ์ด ์กฐ๊ฑด์ ๋ง์กฑํ์ง ์๋ ๋งค์ฅ์ ์ถ๋ ฅ์ ํฌํจ๋์ง ์๋๋ค.
๊ฒฐ๊ณผ์ ์ผ๋ก ์ถ๋ ฅ๋๋ ๊ฒ์ ๋ ๊ฐ์ ์ปฌ๋ผ์ ๊ฐ์ง ํ ์ด๋ธ์ด๋ค. 'store_cd'์ 'avg_amount' ๋ ๊ฐ์ ์ปฌ๋ผ์ ๊ฐ์ง ํ ์ด๋ธ์ด ์ถ๋ ฅ๋๋ค. ๊ฐ ํ์ HAVING ์ ์์ ์ง์ ํ ์กฐ๊ฑด์ ๋ง์กฑํ๋ 'receipt' ํ ์ด๋ธ์ ๋งค์ฅ์ ํด๋นํ๋ฉฐ, 'avg_amount' ์ด์๋ ํด๋น ๋งค์ฅ์ ํ๊ท ๊ฑฐ๋ ๊ธ์ก์ด ํฌํจ๋๋ค. ์ด ์ฝ๋๋ ํ๊ท ๊ฑฐ๋๊ธ์ก์ ๊ธฐ์ค์ผ๋ก ๋ค๋ฅธ ๋งค์ฅ์ ์ฑ๊ณผ๋ฅผ ๋ถ์ํ๋ ๋ฐ ์ ์ฉํ๋ค.
ย
S-034: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋ํด ๊ณ ๊ฐ ID(customer_id)๋ณ๋ก ๋งค์ถ ๊ธ์ก(amount)์ ํฉ์ฐํ์ฌ ์ ์ฒด ๊ณ ๊ฐ์ ํ๊ท ์ ๊ตฌํ์์ค. ๋จ, ๊ณ ๊ฐ ID๊ฐ โZโ๋ก ์์ํ๋ ๊ฒ์ ๋นํ์์ ์๋ฏธํ๋ฏ๋ก ์ ์ธํ์ฌ ๊ณ์ฐํ๋ค.
%%sql
WITH customer_amount AS (
SELECT
customer_id,
SUM(amount) AS sum_amount
FROM receipt
WHERE
customer_id NOT LIKE 'Z%'
GROUP BY customer_id
)
SELECT
AVG(sum_amount)
FROM customer_amount
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
avg |
---|
2547.7422345292559595 |
์ค๋ช :
"์์์ฆ" ํ ์ด๋ธ์์ ID๊ฐ "Z"๋ก ์์ํ๋ ๊ณ ๊ฐ์ ์ ์ธํ ๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ์ด ๊ธ์ก์ ํ๊ท ์ ๊ณ์ฐํ๋ SQL ์ฝ๋์ ๋๋ค.
์ด ์ฝ๋๋ WITH ์ ์ ์ฌ์ฉํ์ฌ ์ ์๋ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ผ๋ก ์์ํ๋ค. ์ด CTE๋ 'customer_amount'๋ผ๋ ์ด๋ฆ์ผ๋ก 'receive' ํ ์ด๋ธ์ ๊ฐ ๊ณ ๊ฐ์ 'customer_id' ์ด๊ณผ 'amount' ์ด์ ํฉ๊ณ๋ฅผ ๊ตฌํ๋ SELECT ๋ฌธ์ด ํฌํจ๋์ด ์์ผ๋ฉฐ, WHERE ์ ์ NOT LIKE ์ฐ์ฐ์๋ฅผ ์ฌ์ฉํ์ฌ ID๊ฐ 'Z'๋ก ์์ํ๋ ๊ณ ๊ฐ์ ํํฐ๋งํ๊ณ ์์ต๋๋ค. ์์ํ๋ ๊ณ ๊ฐ์ ํํฐ๋งํ๊ณ ์์ต๋๋ค. ๊ฒฐ๊ณผ๋ "customer_id" ์ปฌ๋ผ์ผ๋ก ๊ทธ๋ฃนํ๋๋ฉฐ, ๊ฒฐ๊ณผ ํ ์ด๋ธ์๋ ๋ ๊ฐ์ ์ปฌ๋ผ์ด ์์ต๋๋ค. "customer_id"์ "sum_amount"(๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ๊ธ์ก์ ํฉ๊ณ)์ ๋๋ค.
๋ค์ ์ฝ๋ ๋ผ์ธ์ "customer_amount" CTE์์ "sum_amount" ์ปฌ๋ผ์ ํ๊ท ๊ฐ์ ๊ฐ์ ธ์ค๋ ๊ฒ์ผ๋ก, SELECT ๋ฌธ์ AVG ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ID๊ฐ "Z"๋ก ์์ํ์ง ์๋ ๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ์ด ๊ธ์ก์ ํ๊ท ์ ๋ํ๋ด๋ "sum_amount" ์ปฌ๋ผ์ ํ๊ท ๊ฐ์ ๊ณ์ฐํฉ๋๋ค. ๋ฅผ ๊ณ์ฐํฉ๋๋ค.
๊ฒฐ๊ณผ์ ์ผ๋ก ์ถ๋ ฅ๋๋ ๊ฒ์ "customer_amount" CTE์ "sum_amount" ์ด์ ํ๊ท ์ ๋ํ๋ด๋ ํ๋์ ๊ฐ์ ๋๋ค. ์ด ์ฝ๋๋ ๊ณ ๊ฐ์ ํ๊ท ์ง์ถ ํจํด์ ๋ถ์ํ๊ณ ๋ฐ์ดํฐ์์ ์ด์๊ฐ์ด๋ ๋น์ ์์ ์ธ ํจํด์ ์๋ณํ๋ ๋ฐ ์ ์ฉํ๋ค.
S-035: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋ํด ๊ณ ๊ฐ ID(customer_id)๋ณ๋ก ๋งค์ถ ๊ธ์ก(amount)์ ํฉ์ฐํ์ฌ ์ ์ฒด ๊ณ ๊ฐ์ ํ๊ท ์ ๊ตฌํ๊ณ , ํ๊ท ์ด์ ์ผํ์ ํ ๊ณ ๊ฐ์ ์ถ์ถํ์ฌ 10๊ฑด์ฉ ํ์ํ์์ค. ๋จ, ๊ณ ๊ฐ ID๊ฐ โZโ๋ก ์์ํ๋ ๊ฒ์ ๋นํ์์ ์๋ฏธํ๋ฏ๋ก ์ ์ธํ์ฌ ๊ณ์ฐํ๋ค.
%%sql
WITH customer_amount AS (
SELECT
customer_id,
SUM(amount) AS sum_amount
FROM receipt
WHERE
customer_id NOT LIKE 'Z%'
GROUP BY customer_id
)
SELECT
customer_id,
sum_amount
FROM customer_amount
WHERE
sum_amount >= (
SELECT
AVG(sum_amount)
FROM customer_amount
)
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | sum_amount |
---|---|
CS029212000033 | 3604 |
CS007515000119 | 7157 |
CS034515000123 | 3699 |
CS026414000014 | 6671 |
CS007615000070 | 2975 |
CS016414000063 | 6207 |
CS012514000018 | 2562 |
CS029515000142 | 3420 |
CS015215000021 | 3090 |
CS039814000011 | 8031 |
์ค๋ช :
ID๊ฐ "Z"๋ก ์์ํ๋ ๊ณ ๊ฐ์ ์ ์ธํ๊ณ "receipt" ํ ์ด๋ธ์ ๊ณ ๊ฐ ํ๊ท ์ด์ก๋ณด๋ค ๋ ๋ง์ด ์ฌ์ฉํ ์์ 10๋ช ์ ๊ณ ๊ฐ์ "customer_id"์ "sum_amount" ์ด์ ๊ฒ์ํ๋ SQL ์ฝ๋์ ๋๋ค.
์ด ์ฝ๋๋ WITH ์ ์ ์ฌ์ฉํ์ฌ ์ ์๋ ๊ณตํต ํ ์ด๋ธ ํํ์(CTE)์ผ๋ก ์์ํฉ๋๋ค. ์ด CTE๋ 'customer_amount'๋ผ๋ ์ด๋ฆ์ผ๋ก 'receive' ํ ์ด๋ธ์ ๊ฐ ๊ณ ๊ฐ์ 'customer_id' ์ด๊ณผ 'amount' ์ด์ ํฉ๊ณ๋ฅผ ๊ตฌํ๋ SELECT ๋ฌธ์ด ํฌํจ๋์ด ์์ผ๋ฉฐ, WHERE ์ ์ NOT LIKE ์ฐ์ฐ์๋ฅผ ์ฌ์ฉํ์ฌ ID๊ฐ 'Z'๋ก ์์ํ๋ ๊ณ ๊ฐ์ ํํฐ๋งํ๊ณ ์์ต๋๋ค. ์์ํ๋ ๊ณ ๊ฐ์ ํํฐ๋งํ๊ณ ์์ต๋๋ค. ๊ฒฐ๊ณผ๋ "customer_id" ์ปฌ๋ผ์ผ๋ก ๊ทธ๋ฃนํ๋๋ฉฐ, ๊ฒฐ๊ณผ ํ ์ด๋ธ์๋ ๋ ๊ฐ์ ์ปฌ๋ผ์ด ์์ต๋๋ค. "customer_id"์ "sum_amount"(๊ฐ ๊ณ ๊ฐ์ด ์ฌ์ฉํ ๊ธ์ก์ ํฉ๊ณ)์ ๋๋ค.
๋ค์ ์ฝ๋์์๋ "customer_amount" CTE์์ "customer_id"์ "sum_amount" ์ปฌ๋ผ์ ๊ฐ์ ธ์ค๋๋ฐ, "sum_amount"๊ฐ CTE์ ๋ชจ๋ ๊ณ ๊ฐ์ ๋ํด ๊ณ์ฐํ ํ๊ท "sum_amount" ์ด์์ธ ๊ณ ๊ฐ์ ๋ํด์๋ง ๊ฐ์ ธ์ต๋๋ค. ๋ฅผ ์ฌ์ฉํฉ๋๋ค. ์ด๋ AVG ํจ์๋ฅผ ์ฌ์ฉํ์ฌ ํ๊ท 'sum_amount'๋ฅผ ๊ณ์ฐํ๊ณ WHERE ์ ์ ์ฌ์ฉํ์ฌ 'customer_amount' CTE๋ฅผ ํํฐ๋งํ๋ ํ์ ์ฟผ๋ฆฌ๋ฅผ ์ฌ์ฉํ์ฌ ๊ตฌํ๋ฉ๋๋ค.
์ด ์ฟผ๋ฆฌ์ ๊ฒฐ๊ณผ๋ LIMIT ์ ์ ์ฌ์ฉํ์ฌ ์์ 10๊ฐ์ ํ์ผ๋ก ์ ํ๋๋ค.
๊ทธ ๊ฒฐ๊ณผ ๋ ๊ฐ์ ์ปฌ๋ผ์ ๊ฐ์ง ํ ์ด๋ธ์ด ์ถ๋ ฅ๋๋ค. 'customer_id'์ 'sum_amount' ๋ ๊ฐ์ ์ด์ ๊ฐ์ง ํ ์ด๋ธ์ด ์ถ๋ ฅ๋๋ค. ๊ฐ ํ์ ID๊ฐ "Z"๋ก ์์ํ๋ ๊ณ ๊ฐ์ ์ ์ธํ๊ณ "receive" ํ ์ด๋ธ์ ๊ณ ๊ฐ ํ๊ท ์ด์ก๋ณด๋ค ๋ ๋ง์ ๊ธ์ก์ ์ฌ์ฉํ ๊ณ ๊ฐ์ ํด๋นํ๋ค. ํ ์ด๋ธ์ "sum_amount" ์ปฌ๋ผ์ ๊ธฐ์ค์ผ๋ก ๋ด๋ฆผ์ฐจ์์ผ๋ก ์ ๋ ฌ๋๋ฉฐ, ์์ 10๊ฐ์ ํ๋ง ์ถ๋ ฅ์ ํฌํจ๋๋ค. ์ด ์ฝ๋๋ ๊ณ ์ก ์ผํ์ ํ๋ ๊ณ ๊ฐ์ ์๋ณํ๊ณ ๊ทธ๋ค์ ์๋น ํจํด์ ๋ถ์ํ๋ ๋ฐ ์ ์ฉํ๋ค.
S-036: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์ ๋งค์ฅ ๋ฐ์ดํฐ(df_store)๋ฅผ ๋ด๋ถ์ ์ผ๋ก ๊ฒฐํฉํ์ฌ ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ์ ๋ชจ๋ ํญ๋ชฉ๊ณผ ๋งค์ฅ ๋ฐ์ดํฐ์ ๋งค์ฅ๋ช (store_name)์ 10๊ฑด์ฉ ํ์ํ๋ผ.
%%sql
SELECT
r.*,
s.store_name
FROM receipt r
JOIN store s
ON
r.store_cd = s.store_cd
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
sales_ymd | sales_epoch | store_cd | receipt_no | receipt_sub_no | customer_id | product_cd | quantity | amount | store_name |
---|---|---|---|---|---|---|---|---|---|
20181103 | 1541203200 | S14006 | 112 | 1 | CS006214000001 | P070305012 | 1 | 158 | ่ใ่ฐทๅบ |
20181118 | 1542499200 | S13008 | 1132 | 2 | CS008415000097 | P070701017 | 1 | 81 | ๆๅๅบ |
20170712 | 1499817600 | S14028 | 1102 | 1 | CS028414000014 | P060101005 | 1 | 170 | ไบใๆฉๅบ |
20190205 | 1549324800 | S14042 | 1132 | 1 | ZZ000000000000 | P050301001 | 1 | 25 | ๆฐๅฑฑไธๅบ |
20180821 | 1534809600 | S14025 | 1102 | 2 | CS025415000050 | P060102007 | 1 | 90 | ๅคงๅๅบ |
20190605 | 1559692800 | S13003 | 1112 | 1 | CS003515000195 | P050102002 | 1 | 138 | ็ๆฑๅบ |
20181205 | 1543968000 | S14024 | 1102 | 2 | CS024514000042 | P080101005 | 1 | 30 | ไธ็ฐๅบ |
20190922 | 1569110400 | S14040 | 1102 | 1 | CS040415000178 | P070501004 | 1 | 128 | ้ทๆดฅ็ฐๅบ |
20170504 | 1493856000 | S13020 | 1112 | 2 | ZZ000000000000 | P071302010 | 1 | 770 | ๅๆกไปฒๅๅบ |
20191010 | 1570665600 | S14027 | 1102 | 1 | CS027514000015 | P071101003 | 1 | 680 | ๅ่คๆฒขๅบ |
์ค๋ช :
๋ค์์ "receipt" ํ ์ด๋ธ๊ณผ "store" ํ ์ด๋ธ์ ๋ฐ์ดํฐ๋ฅผ ํฌํจํ๋ ๊ฒฐํฉ ํ ์ด๋ธ์์ ์ฒ์ 10๊ฐ์ ํ์ ๊ฐ์ ธ์ค๋ SQL ์ฝ๋์ด๋ค. ๊ฒฐ๊ณผ ํ ์ด๋ธ์๋ "receive" ํ ์ด๋ธ์ ๋ชจ๋ ์ปฌ๋ผ๊ณผ "store" ํ ์ด๋ธ์ ์ถ๊ฐ ์ปฌ๋ผ "store_name"์ด ํฌํจ๋ ๊ฒ์ด๋ค.
์ฝ๋๋ ๊ฐ์ ธ์ฌ ์ปฌ๋ผ์ ์ง์ ํ๋ SELECT ๋ฌธ์ผ๋ก ์์ํ๋ค. ์ด ๊ฒฝ์ฐ SELECT ๋ฌธ์ ์์ผ๋์นด๋(*)๋ฅผ ์ฌ์ฉํ์ฌ 'receipt' ํ ์ด๋ธ์์ ๋ชจ๋ ์ปฌ๋ผ์ ๊ฐ์ ธ์จ๋ค. ๋ํ SELECT ๋ฌธ์์๋ ์ถ๋ ฅ ํ ์ด๋ธ์ ํฌํจ๋ 'store' ํ ์ด๋ธ์ 'store_name' ์ปฌ๋ผ์ ์ง์ ํ๊ณ ์๋ค.
FROM ๊ตฌ๋ฌธ์ 'receipt' ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ฌ ๊ฒ์ ์ง์ ํ๊ณ , JOIN ๊ตฌ๋ฌธ์ 'store' ํ ์ด๋ธ์ 'receipt' ํ ์ด๋ธ์ 'store_cd' ์ปฌ๋ผ์ ๊ฒฐํฉ ์กฐ๊ฑด์ผ๋ก ๊ฒฐํฉํ ๊ฒ์ ์ง์ ํ๋ค. ์ด๋ "store_cd" ์ด์ ์ผ์นํ๋ ๊ฐ์ ๊ธฐ์ค์ผ๋ก "receive" ํ ์ด๋ธ๊ณผ "store" ํ ์ด๋ธ์ ํ์ด ํ๋์ ํ ์ด๋ธ๋ก ๊ฒฐํฉ๋จ์ ์๋ฏธํ๋ค.
๊ฒฐ๊ณผ์ ์ผ๋ก ์ถ๋ ฅ๋๋ ๊ฒ์ "receipt" ํ ์ด๋ธ์ ๋ชจ๋ ์ปฌ๋ผ๊ณผ "store" ํ ์ด๋ธ์ ์ถ๊ฐ ์ปฌ๋ผ "store_name"์ ํฌํจํ๋ ํ ์ด๋ธ์ด๋ค. ์ด ํ ์ด๋ธ์ LIMIT ์ ์ ์ฌ์ฉํ์ฌ ์ฒ์ 10๊ฐ์ ํ์ผ๋ก ์ ํ๋ฉ๋๋ค. ์ด ์ฝ๋๋ ๊ด๋ จ ๋ ํ ์ด๋ธ์ ๋ฐ์ดํฐ๋ฅผ ๋ถ์ํ๊ณ ๋ฐ์ดํฐ๋ฅผ ํ๋์ ํ ์ด๋ธ๋ก ๊ฒฐํฉํ์ฌ ๋ถ์ํ๋ ๋ฐ ํธ๋ฆฌํฉ๋๋ค.
S-037: ์ํ ๋ฐ์ดํฐ(df_product)์ ์นดํ ๊ณ ๋ฆฌ ๋ฐ์ดํฐ(df_category)๋ฅผ ๋ด๋ถ์ ์ผ๋ก ๊ฒฐํฉํ์ฌ ์ํ ๋ฐ์ดํฐ์ ์ ์ฒด ํญ๋ชฉ๊ณผ ์นดํ ๊ณ ๋ฆฌ ๋ฐ์ดํฐ์ ์นดํ ๊ณ ๋ฆฌ ์๋ถ๋ฅ ์ด๋ฆ(category_small_name) 10๊ฐ๋ฅผ ํ์ํ๋ผ.
%%sql
SELECT
p.*,
c.category_small_name
FROM product p
JOIN category c
ON
p.category_small_cd = c.category_small_cd
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
product_cd | category_major_cd | category_medium_cd | category_small_cd | unit_price | unit_cost | category_small_name |
---|---|---|---|---|---|---|
P040101001 | 04 | 0401 | 040101 | 198 | 149 | ๅผๅฝ้ก |
P040101002 | 04 | 0401 | 040101 | 218 | 164 | ๅผๅฝ้ก |
P040101003 | 04 | 0401 | 040101 | 230 | 173 | ๅผๅฝ้ก |
P040101004 | 04 | 0401 | 040101 | 248 | 186 | ๅผๅฝ้ก |
P040101005 | 04 | 0401 | 040101 | 268 | 201 | ๅผๅฝ้ก |
P040101006 | 04 | 0401 | 040101 | 298 | 224 | ๅผๅฝ้ก |
P040101007 | 04 | 0401 | 040101 | 338 | 254 | ๅผๅฝ้ก |
P040101008 | 04 | 0401 | 040101 | 420 | 315 | ๅผๅฝ้ก |
P040101009 | 04 | 0401 | 040101 | 498 | 374 | ๅผๅฝ้ก |
P040101010 | 04 | 0401 | 040101 | 580 | 435 | ๅผๅฝ้ก |
์ค๋ช :
์ด ์ฝ๋๋ ๊ด๊ณํ ๋ฐ์ดํฐ๋ฒ ์ด์ค๋ฅผ ๊ด๋ฆฌํ๊ณ ์ฟผ๋ฆฌํ๋ ๋ฐ ์ฌ์ฉ๋๋ ํ๋ก๊ทธ๋๋ฐ ์ธ์ด์ธ SQL๋ก ์์ฑ๋์์ต๋๋ค.
์ด ์ฝ๋์์๋ ๋ ๊ฐ์ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํ๊ณ ์์ต๋๋ค. "product"์ "category"์ด๋ฉฐ, "JOIN" ํค์๋๋ฅผ ์ฌ์ฉํ์ฌ "category_small_cd" ์ปฌ๋ผ์ ๊ฐ์ด ์ผ์นํ๋ ๋ ํ ์ด๋ธ์ ํ์ ๊ฒฐํฉํ๊ณ ์์ต๋๋ค. ์ด๋ ๊ฒ ํ๋ฉด ๋ ํ ์ด๋ธ์ ์ปฌ๋ผ์ ํฌํจํ๋ ์๋ก์ด ํ ์ด๋ธ์ด ์์ฑ๋๋ค.
'SELECT' ํค์๋๋ ์ถ๋ ฅ์ ํฌํจํ ์ปฌ๋ผ์ ์ ํํ๋ ๋ฐ ์ฌ์ฉ๋๋ค. ์ด ๊ฒฝ์ฐ 'product' ํ ์ด๋ธ์ ๋ชจ๋ ์ปฌ๋ผ('p.*' ๊ตฌ๋ฌธ์ผ๋ก ์ง์ )๊ณผ 'category' ํ ์ด๋ธ์ 'category_small_name' ์ปฌ๋ผ์ด ์ ํ๋๋ค.
'LIMIT' ํค์๋๋ ์ถ๋ ฅ์ ์ฒ์ 10์ค๋ก ์ ํํ๋ค. ์ด๋ ๋๊ท๋ชจ ๋ฐ์ดํฐ ์ธํธ๋ฅผ ๋ค๋ฃฐ ๋ ์ฟผ๋ฆฌ๋ฅผ ๋น ๋ฅด๊ฒ ํ ์คํธํ๊ฑฐ๋ ๋ฐ์ดํฐ ์ํ์ ๊ฐ์ ธ์ค๋ ๋ฐ ์ ์ฉํ๋ค.
์ฆ, ์ด ์ฝ๋๋ 'product' ํ ์ด๋ธ๊ณผ 'category' ํ ์ด๋ธ์ ๊ฒฐํฉํ์ฌ 'category_small_cd' ๊ฐ์ด ์ผ์นํ๋ ์ฒ์ 10๊ฐ์ ํ์ ๋ฐํํ๊ณ , 'product' ํ ์ด๋ธ์ ๋ชจ๋ ์ด๊ณผ 'category' ํ ์ด๋ธ์ 'category_small_name' ์ด์ ํฌํจํ 'product' ํ ์ด๋ธ์ ๋ชจ๋ ์ด์ ๋ฐํํ๋ค. small_name' ์ด์ ํฌํจํ๊ฒ ๋๋ค.
S-038: ๊ณ ๊ฐ ๋ฐ์ดํฐ(df_customer)์ ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์์ ๊ณ ๊ฐ๋ณ ๋งค์ถ ๊ธ์ก์ ์ดํฉ์ ๊ตฌํ์ฌ 10๊ฑด์ ํ์ํ์์ค. ๋จ, ํ๋งค ์ค์ ์ด ์๋ ๊ณ ๊ฐ์ ๋ํด์๋ ํ๋งค๊ธ์ก์ 0์ผ๋ก ํ์ํ๋ค. ๋ํ, ๊ณ ๊ฐ์ ์ฑ๋ณ ์ฝ๋(gender_cd)๊ฐ ์ฌ์ฑ(1)์ธ ๊ณ ๊ฐ์ ๋์์ผ๋ก ํ๋ฉฐ, ๋นํ์(๊ณ ๊ฐ ID๊ฐ โZโ๋ก ์์ํ๋ ๊ณ ๊ฐ)์ ์ ์ธํ๋ค.
%%sql
WITH customer_amount AS (
SELECT
customer_id,
SUM(amount) AS sum_amount
FROM receipt
GROUP BY
customer_id
),
customer_data AS (
SELECT
customer_id
FROM customer
WHERE
gender_cd = '1'
AND customer_id NOT LIKE 'Z%'
)
SELECT
c.customer_id,
COALESCE(a.sum_amount, 0)
FROM customer_data c
LEFT OUTER JOIN customer_amount a
ON
c.customer_id = a.customer_id
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
customer_id | coalesce |
---|---|
CS021313000114 | 0 |
CS031415000172 | 5088 |
CS028811000001 | 0 |
CS001215000145 | 875 |
CS015414000103 | 3122 |
CS033513000180 | 868 |
CS035614000014 | 0 |
CS011215000048 | 3444 |
CS009413000079 | 0 |
CS040412000191 | 210 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ ์ด์ ์ฝ๋๋ณด๋ค ์กฐ๊ธ ๋ ๋ณต์กํ๋ค. ์ด ์ฝ๋๋ ์ธ ๊ฐ์ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํ๋ค." receive", "customer", "customer_amount"์ 3๊ฐ ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํฉ๋๋ค.
์ด ์ฝ๋์์๋ "WITH" ์ ์ ์ฌ์ฉํ์ฌ ๋ ๊ฐ์ ํ์ ์ฟผ๋ฆฌ๋ฅผ ์ ์ํ๊ณ ์๋ค. 'customer_amount'์ 'customer_data'์ ๋๋ค.
'customer_amount' ์๋ธ์ฟผ๋ฆฌ๋ 'SUM' ํจ์๋ฅผ ์ฌ์ฉํ์ฌ 'customer_id' ๋ณ๋ก 'recipate' ํ ์ด๋ธ์ 'amount' ์ด์ ์ง๊ณํ๊ณ ๊ทธ ๊ฒฐ๊ณผ์ 'sum_amount'๋ผ๋ ๋ณ์นญ์ ๋ถ์ฌํ๋ค. ์ด ํ์ ์ฟผ๋ฆฌ๋ ๋ ๊ฐ์ ์ปฌ๋ผ์ ํฌํจํ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๋ค. 'customer_id'์ 'sum_amount' ๋ ๊ฐ์ ์ปฌ๋ผ์ ํฌํจํ๋ ์๋ก์ด ํ ์ด๋ธ์ด ์์ฑ๋ฉ๋๋ค.
'customer_data' ํ์ ์ฟผ๋ฆฌ๋ 'customer' ํ ์ด๋ธ์์ 'gender_cd'๊ฐ '1'๊ณผ ๊ฐ๊ณ 'customer_id'๊ฐ ๋ฌธ์ 'Z'๋ก ์์ํ์ง ์๋ ๋ชจ๋ ํ์ ์ ํํ๋ค. ์ด ํ์ ์ฟผ๋ฆฌ๋ ํ๋์ ์ปฌ๋ผ์ ํฌํจํ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๋ค. 'customer_id'๋ฅผ ํฌํจํ๋ ์ ํ ์ด๋ธ์ ์์ฑํ๋ค.
๋ง์ง๋ง ์ฟผ๋ฆฌ๋ 'LEFT OUTER JOIN' ์ฐ์ฐ์ผ๋ก 'customer_data' ํ ์ด๋ธ๊ณผ 'customer_amount' ํ ์ด๋ธ์ ๊ฒฐํฉํ๋ค. "customer_data" ํ ์ด๋ธ์ ๋ชจ๋ ํ๊ณผ "customer_amount" ํ ์ด๋ธ์ ์ผ์นํ๋ ํ์ด ์์ผ๋ฉด ์ด๋ฅผ ๋ฐํํ๊ณ , "customer_amount" ํ ์ด๋ธ์ ์ผ์นํ๋ ํ์ด ์์ผ๋ฉด "COALESCE" ํจ์๋ฅผ ์ฌ์ฉํ์ฌ "sum_amount" ๊ฐ์ 0์ผ๋ก ์ค์ ํ๋ค. amount' ๊ฐ์ 0์ผ๋ก ๋์ฒดํ๋ค.
SELECT ๊ตฌ๋ฌธ์ ์ถ๋ ฅ์ ํฌํจํ ๋ ๊ฐ์ ์ปฌ๋ผ์ ์ ํํ๋ค. 'customer_data' ํ ์ด๋ธ์ 'customer_id'์ 'customer_amount' ํ ์ด๋ธ์ 'COALESCE(a.sum_amount, 0)'('customer_amount'์ ์ผ์นํ๋ ํ์ด ์์ผ๋ฉด 0) 'LIMIT' ๊ตฌ๋ฌธ์ ์ถ๋ ฅ์ ์ฒ์ 10๊ฐ์ ํ์ผ๋ก ์ ํํ๋ค. 10ํ์ผ๋ก ์ ํํ๋ค.
์ฆ, ์ด ์ฝ๋๋ 'customer_id'๊ฐ 'Z'๋ก ์์ํ์ง ์๋ ๋จ์ฑ ๊ณ ๊ฐ์ ๋ํด 'customer_data' ํ ์ด๋ธ์ ์ฒ์ 10๊ฐ์ ํ๊ณผ ํด๋น ์์์ฆ ํฉ๊ณ(๋๋ ์์์ฆ์ด ์๋ ๊ฒฝ์ฐ 0)๋ฅผ ๋ฐํํ๋ค.
S-039: ์์์ฆ ๋ด์ญ ๋ฐ์ดํฐ(df_receipt)์์ ๋งค์ถ ์ผ์๊ฐ ๋ง์ ๊ณ ๊ฐ ์์ 20๊ฑด์ ์ถ์ถํ ๋ฐ์ดํฐ์ ๋งค์ถ ๊ธ์ก ํฉ๊ณ๊ฐ ๋ง์ ๊ณ ๊ฐ ์์ 20๊ฑด์ ์ถ์ถํ ๋ฐ์ดํฐ๋ฅผ ๊ฐ๊ฐ ์์ฑํ๊ณ , ์ด ๋ ๊ฐ์ง๋ฅผ ์์ ํ ์ธ๋ถ ๊ฒฐํฉํ๋ผ. ๋จ, ๋นํ์(๊ณ ๊ฐ ID๊ฐ โZโ๋ก ์์ํ๋ ๊ณ ๊ฐ)์ ์ ์ธํ๋ค.
%%sql
WITH customer_data AS (
select
customer_id,
sales_ymd,
amount
FROM receipt
WHERE
customer_id NOT LIKE 'Z%'
),
customer_days AS (
select
customer_id,
COUNT(DISTINCT sales_ymd) come_days
FROM customer_data
GROUP BY
customer_id
ORDER BY
come_days DESC
LIMIT 20
),
customer_amount AS (
SELECT
customer_id,
SUM(amount) buy_amount
FROM customer_data
GROUP BY
customer_id
ORDER BY
buy_amount DESC
LIMIT 20
)
SELECT
COALESCE(d.customer_id, a.customer_id) customer_id,
d.come_days,
a.buy_amount
FROM customer_days d
FULL OUTER JOIN customer_amount a
ON
d.customer_id = a.customer_id
;
* postgresql://padawan:***@db:5432/dsdojo_db 34 rows affected.
customer_id | come_days | buy_amount |
---|---|---|
CS040214000008 | 23 | None |
CS015415000185 | 22 | 20153 |
CS010214000010 | 22 | 18585 |
CS028415000007 | 21 | 19127 |
CS010214000002 | 21 | None |
CS017415000097 | 20 | 23086 |
CS016415000141 | 20 | 18372 |
CS021514000045 | 19 | None |
CS022515000226 | 19 | None |
CS031414000051 | 19 | 19202 |
CS039414000052 | 19 | None |
CS014214000023 | 19 | None |
CS021515000172 | 19 | None |
CS031414000073 | 18 | None |
CS007515000107 | 18 | None |
CS014415000077 | 18 | None |
CS021515000056 | 18 | None |
CS032415000209 | 18 | None |
CS021515000211 | 18 | None |
CS022515000028 | 18 | None |
CS011415000006 | None | 16094 |
CS016415000101 | None | 16348 |
CS030415000034 | None | 15468 |
CS021515000089 | None | 17580 |
CS034415000047 | None | 16083 |
CS006515000023 | None | 18372 |
CS038415000104 | None | 17847 |
CS015515000034 | None | 15300 |
CS032414000072 | None | 16563 |
CS011414000106 | None | 18338 |
CS001605000009 | None | 18925 |
CS009414000059 | None | 15492 |
CS035414000024 | None | 17615 |
CS007514000094 | None | 15735 |
์ค๋ช :
์ด ์ฝ๋๋ SQL๋ก ์์ฑ๋์์ผ๋ฉฐ ์ด์ ์ฝ๋๋ณด๋ค ์กฐ๊ธ ๋ ๋ณต์กํ๋ค. "receipt" ํ ์ด๋ธ์์ ๋ฐ์ดํฐ๋ฅผ ์ ํํ๊ณ 3๊ฐ์ ํ์ ์ฟผ๋ฆฌ๋ฅผ ์์ฑํฉ๋๋ค. 'customer_data', 'customer_days', 'customer_amount'์ ๋๋ค.
customer_data ํ์ ์ฟผ๋ฆฌ๋ 'customer_id'๊ฐ 'Z'๋ก ์์ํ์ง ์๋ 'receive' ํ ์ด๋ธ์ ํ์ ์ ํํ๊ณ ์ธ ๊ฐ์ ์ปฌ๋ผ์ ๋ณ์นญ์ ๋ถ์ฌํ๋ค." customer_id", "sales_ymd", "amount"์ ์ธ ์ด์ ๋ณ์นญ์ ๋ถ์ฌํ๋ค. ์ด ํ์ ์ฟผ๋ฆฌ๋ 3๊ฐ์ ์ปฌ๋ผ์ ํฌํจํ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๋ค. 'customer_id', 'sales_ymd', 'amount' 3๊ฐ์ ์ปฌ๋ผ์ ํฌํจํ๋ ์ ํ ์ด๋ธ์ด ์์ฑ๋๋ค.
customer_days ์๋ธ์ฟผ๋ฆฌ๋ 'customer_data' ์๋ธ์ฟผ๋ฆฌ์ 'sales_ymd' ์ปฌ๋ผ์ 'COUNT(DISTINCT sales_ymd)' ํจ์๋ฅผ ์ฌ์ฉํ์ฌ 'customer_id' ๋ณ๋ก ์ง๊ณํ๊ณ , ๊ทธ ๊ฒฐ๊ณผ์ 'come_days'๋ผ๋ ๋ณ์นญ์ ๋ถ์ธ๋ค. ๋ถ์ ๋๋ค. ๊ทธ๋ฆฌ๊ณ ๊ฒฐ๊ณผ๋ฅผ "come_days"๋ก ๋ด๋ฆผ์ฐจ์์ผ๋ก ์ ๋ ฌํ์ฌ ์ถ๋ ฅ์ ์์ 20๊ฐ ํ์ผ๋ก ์ ํํฉ๋๋ค. ์ด ํ์ ์ฟผ๋ฆฌ๋ ๋ ๊ฐ์ ์ปฌ๋ผ์ ํฌํจํ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํฉ๋๋ค. "customer_id"์ "come_days" ๋ ๊ฐ์ ์ปฌ๋ผ์ ํฌํจํ๋ ์ ํ ์ด๋ธ์ ์์ฑํฉ๋๋ค.
'customer_amount' ํ์ ์ฟผ๋ฆฌ๋ 'customer_data' ํ์ ์ฟผ๋ฆฌ์ 'amount' ์ปฌ๋ผ์ 'SUM' ํจ์๋ฅผ ์ฌ์ฉํ์ฌ 'customer_id' ๋ณ๋ก ์ง๊ณํ๊ณ ๊ทธ ๊ฒฐ๊ณผ์ 'buy_amount'๋ผ๋ ๋ณ์นญ์ ๋ถ์ฌํ๋ค. ๊ทธ๋ฆฌ๊ณ ๊ฒฐ๊ณผ๋ฅผ "buy_amount"๋ก ๋ด๋ฆผ์ฐจ์์ผ๋ก ์ ๋ ฌํ์ฌ ์ถ๋ ฅ์ ์์ 20๊ฐ ํ์ผ๋ก ์ ํํฉ๋๋ค. ์ด ํ์ ์ฟผ๋ฆฌ๋ ๋ ๊ฐ์ ์ปฌ๋ผ์ ํฌํจํ๋ ์๋ก์ด ํ ์ด๋ธ์ ์์ฑํ๋ค. "customer_id"์ "buy_amount" ๋ ๊ฐ์ ์ปฌ๋ผ์ ํฌํจํ๋ ์ ํ ์ด๋ธ์ด ์์ฑ๋ฉ๋๋ค.
๋ง์ง๋ง ์ฟผ๋ฆฌ๋ "FULL OUTER JOIN" ์ฐ์ฐ์ ์ฌ์ฉํ์ฌ "customer_days" ํ ์ด๋ธ๊ณผ "customer_amount" ํ ์ด๋ธ์ ๊ฒฐํฉํ๋ค. ๋ ํ ์ด๋ธ์ ๋ชจ๋ ํ, ๋ ํ ์ด๋ธ์ ์ผ์นํ๋ ํ, ์ผ์นํ์ง ์๋ ๊ฒฝ์ฐ NULL ๊ฐ์ ๋ฐํํ๊ณ , "COALESCE" ํจ์๋ฅผ ์ฌ์ฉํ์ฌ "customer_id"์ NULL ๊ฐ์ ๋ค๋ฅธ ํ ์ด๋ธ์ ํด๋น ๊ฐ์ผ๋ก ๋ฐ๊พผ๋ค.
SELECT ๊ตฌ๋ฌธ์ ์ถ๋ ฅ์ ํฌํจํ 3๊ฐ์ ์ปฌ๋ผ์ ์ ํํ๋ค." COALESCE(d.customer_id, a.customer_id)"์์ customer_id๋ฅผ, "customer_days" ํ ์ด๋ธ์์ "d.come_days"๋ฅผ, "customer_amount"์์ "a.buy_amount"๋ฅผ ๊ฐ์ ธ์ค๋ ๊ฒ์ด๋ค.
์ฆ, ์ด ์ฝ๋์์๋ 'come_days'(=๋ฐฉ๋ฌธ์ผ์๊ฐ ๊ฐ์ฅ ๋ง์ ์์ 20๋ช ์ ๊ณ ๊ฐ๊ณผ 'buy_amount'(=๋งค์ฅ์์ ์ฌ์ฉํ ์ด์ก)๊ฐ ๊ฐ์ฅ ๋ง์ ์์ 20๋ช ์ ๊ณ ๊ฐ์ด customer_id์ ํจ๊ป ๋ฐํ๋ฉ๋๋ค. ๊ณ ๊ฐ์ด ๋ ํ ์ด๋ธ์ ๋ชจ๋ ๋ํ๋๋ ๊ฒฝ์ฐ, ์ถ๋ ฅ์๋ ํ ๋ฒ๋ง ๋ํ๋ฉ๋๋ค.
S-040: ๋ชจ๋ ๋งค์ฅ๊ณผ ๋ชจ๋ ์ํ์ ์กฐํฉํ ๋ฐ์ดํฐ๋ฅผ ์์ฑํ๊ณ ์ถ๋ค. ๋งค์ฅ ๋ฐ์ดํฐ(df_store)์ ์ํ ๋ฐ์ดํฐ(df_product)๋ฅผ ์ง์ ๋ถํ์ฌ ๊ฑด์๋ฅผ ๊ณ์ฐํ๋ผ.
%%sql
SELECT
COUNT(1)
FROM store
CROSS JOIN product
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
count |
---|
531590 |
์ค๋ช :
์ด SQL ์ฝ๋๋ "store" ํ ์ด๋ธ๊ณผ "product" ํ ์ด๋ธ ๊ฐ์ ๋ฐ์นด๋ฅดํธ ๊ณฑ์ ์ํํ์ฌ ์์ฑ๋ ๊ฒฐ๊ณผ ์งํฉ์ ํ ๊ฐ์๋ฅผ ๊ตฌํ๋ค.
์ฝ๋๋ COUNT ํจ์๋ฅผ ์ง์ ํ SELECT ๋ฌธ์ผ๋ก ์์ํ๋ฉฐ, ์นด์ดํธํ ๊ฐ์ '1', ์ฆ ํ ์๋ฅผ ์นด์ดํธํ๋ ๊ฒ์ ์๋ฏธํ๋ค.
FROM ์ ์์๋ ์ฟผ๋ฆฌ์์ ์ฌ์ฉํ ๋ ๊ฐ์ ํ ์ด๋ธ, "store"์ "product"๋ฅผ ์ง์ ํ๊ณ ์๋ค. ํ์ง๋ง ๋ ํ ์ด๋ธ์ ์ด๋ค ๋งค์นญ ์กฐ๊ฑด์ ๋ฐ๋ผ ๊ฒฐํฉํ๋ ๊ฒ์ด ์๋๋ผ CROSS JOIN ํค์๋๊ฐ ์ฌ์ฉ๋์์ต๋๋ค. ์ฆ, "store" ํ ์ด๋ธ์ ๊ฐ ํ๊ณผ "product" ํ ์ด๋ธ์ ๊ฐ ํ์ด ์ง์ ์ด๋ฃจ์ด ๋ฐ์นด๋ฅดํธ ๊ณฑ์ด ๋๋ค.
๊ฒฐ๊ณผ์ ์ผ๋ก ์ถ๋ ฅ๋๋ ๊ฒ์ ๋ฐ์นด๋ฅดํธ ๊ณฑ์ ํ ์ ๊ฐ์๋ฅผ ํฌํจํ๋ ํ๋์ ์ด์ ๊ฐ์ง ํ๋์ ํ์ด ๋๋ค. ์ด ๊ฐ์๋ "store" ํ ์ด๋ธ์ ํ ์์ "product" ํ ์ด๋ธ์ ํ ์๋ฅผ ๊ณฑํ ๊ฒ์ด๋ค. ์ด ์ ํ์ ์ฟผ๋ฆฌ๋ ์ผ๋ฐ์ ์ผ๋ก ๋ ๊ฐ ์ด์์ ํ ์ด๋ธ์์ ์ป์ ์ ์๋ ๋ฐ์ดํฐ ์กฐํฉ ๊ฐ๋ฅ์ฑ์ ์ด ์๋ฅผ ๊ณ์ฐํ๋ ๋ฐ ์ฌ์ฉ๋๋ค.
ย
ย
Comment