๋ฐ์ดํ„ฐ ์‚ฌ์ด์–ธ์Šค 100๋ฒˆ์˜ ๋…ธํฌ(๊ตฌ์กฐํ™” ๋ฐ์ดํ„ฐ ์ฒ˜๋ฆฌํŽธ) โ€“ SQL Part 2 (Q21 to Q40)

๋ฐ์ดํ„ฐ ์‚ฌ์ด์–ธ์Šค
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” "receipt"๋ผ๋Š” ํ…Œ์ด๋ธ”์—์„œ ํ–‰ ์ˆ˜๋ฅผ ์„ ํƒํ•˜๋Š” SQL ์ฟผ๋ฆฌ์ž…๋‹ˆ๋‹ค.

SELECT ํ‚ค์›Œ๋“œ๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜จ๋‹ค๋Š” ๊ฒƒ์„ ๋‚˜ํƒ€๋‚ด๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉ๋œ๋‹ค.

COUNT ํ•จ์ˆ˜๋Š” 'receipt' ํ…Œ์ด๋ธ”์˜ ํ–‰ ์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค. ์ด ๊ฒฝ์šฐ 'COUNT(1)' ๋ฌธ์€ ๊ฐ ํ–‰์˜ ์ฒซ ๋ฒˆ์งธ ์—ด์˜ ๊ฐ’์ด NULL์ด ์•„๋‹Œ ํ…Œ์ด๋ธ”์˜ ํ–‰ ์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค. ์ด๋Š” ์–ด๋–ค ์—ด์ด NULL์ธ์ง€์— ๊ด€๊ณ„์—†์ด ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ํ–‰์„ ๊ณ„์‚ฐํ•˜๋Š” "COUNT(*)"๋ฅผ ์‚ฌ์šฉํ•˜๋Š” ๊ฒƒ๊ณผ ๋™์ผํ•˜๋‹ค.

FROM ํ‚ค์›Œ๋“œ๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ ์˜ค๋ ค๋Š” ํ…Œ์ด๋ธ”์„ ๋‚˜ํƒ€๋‚ด๋Š” ๋ฐ ์‚ฌ์šฉ๋˜๋ฉฐ, ์ด ๊ฒฝ์šฐ "receipt" ํ…Œ์ด๋ธ”์„ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.

์ฆ‰, ์š”์•ฝํ•˜๋ฉด ์ด ์ฟผ๋ฆฌ๋Š” "receive" ํ…Œ์ด๋ธ”์˜ ํ–‰ ์ˆ˜๋ฅผ ์„ ํƒํ•˜๊ณ  ๋ฐ˜ํ™˜ํ•˜๋Š” ๊ฒƒ์ด๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” "receipt"๋ผ๋Š” ํ…Œ์ด๋ธ”์—์„œ ํ–‰ ์ˆ˜๋ฅผ ์„ ํƒํ•˜๋Š” SQL ์ฟผ๋ฆฌ์ž…๋‹ˆ๋‹ค.

SELECT ํ‚ค์›Œ๋“œ๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜จ๋‹ค๋Š” ๊ฒƒ์„ ๋‚˜ํƒ€๋‚ด๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉ๋œ๋‹ค.

COUNT ํ•จ์ˆ˜๋Š” "receipt" ํ…Œ์ด๋ธ”์˜ ํ–‰ ์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค. ์ด ๊ฒฝ์šฐ "COUNT(*)"๋ฌธ์€ ์–ด๋–ค ์—ด์ด NULL์ธ์ง€ ์—ฌ๋ถ€์— ๊ด€๊ณ„์—†์ด ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ํ–‰์„ ๊ณ„์‚ฐํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค.

FROM ํ‚ค์›Œ๋“œ๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ ์˜ค๋ ค๋Š” ํ…Œ์ด๋ธ”, ์ด ๊ฒฝ์šฐ "RECIPATE" ํ…Œ์ด๋ธ”์„ ๋‚˜ํƒ€๋‚ด๋Š” ๋ฐ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค.

์ฆ‰, ์š”์•ฝํ•˜๋ฉด, ์ด ์ฟผ๋ฆฌ๋Š” "RECIPATE" ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ํ–‰์„ ์„ ํƒํ•˜๊ณ  ๊ทธ ๊ฐœ์ˆ˜๋ฅผ ๋ฐ˜ํ™˜ํ•œ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” "receipt"๋ผ๋Š” ํ…Œ์ด๋ธ”์—์„œ ๋‹ค๋ฅธ ๊ณ ๊ฐ ID์˜ ๊ฐœ์ˆ˜๋ฅผ ์„ ํƒํ•˜๋Š” SQL ์ฟผ๋ฆฌ์ด๋‹ค.

sql์€ ์ด ์ฝ”๋“œ๊ฐ€ SQL ์ฝ”๋“œ์ž„์„ ๋‚˜ํƒ€๋‚ด๋Š” Jupyter Notebook์˜ ์…€ ๋งค์ง ๋ช…๋ น์ด๋‹ค.

SELECT ํ‚ค์›Œ๋“œ๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜จ๋‹ค๋Š” ๊ฒƒ์„ ๋‚˜ํƒ€๋‚ด๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉ๋œ๋‹ค.

COUNT ํ•จ์ˆ˜๋Š” 'RECIPATE' ํ…Œ์ด๋ธ”์—์„œ ๋‹ค๋ฅธ ๊ณ ๊ฐ ID์˜ ์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค. ์ด ๊ฒฝ์šฐ 'COUNT(DISTINCT customer_id)' ๋ฌธ์€ ํ…Œ์ด๋ธ” ๋‚ด ๊ณ ์œ ํ•œ ๊ณ ๊ฐ ID์˜ ์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

DISTINCT ํ‚ค์›Œ๋“œ๋Š” 'customer_id' ์—ด์˜ ๊ณ ์œ ํ•œ ๊ฐ’๋งŒ ๊ณ„์‚ฐํ•˜๋„๋ก ์ง€์ •ํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

FROM ํ‚ค์›Œ๋“œ๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ ์˜ค๋ ค๋Š” ํ…Œ์ด๋ธ”์„ ๋‚˜ํƒ€๋‚ด๋Š” ๋ฐ ์‚ฌ์šฉ๋˜๋ฉฐ, ์ด ๊ฒฝ์šฐ "receipt" ํ…Œ์ด๋ธ”์„ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.

์ฆ‰, ์š”์•ฝํ•˜๋ฉด ์ด ์ฟผ๋ฆฌ๋Š” 'receive' ํ…Œ์ด๋ธ”์˜ ๊ณ ์œ ํ•œ ๊ณ ๊ฐ ID ๊ฐœ์ˆ˜๋ฅผ ์„ ํƒํ•˜์—ฌ ๋ฐ˜ํ™˜ํ•œ๋‹ค.

ย 

์„ค๋ช…:

SQL ์ฝ”๋“œ๊ฐ€ ๋‘ ๋ฒˆ ๋ฐ˜๋ณต๋œ๋‹ค.

์ด ์ฝ”๋“œ๋Š” "receipt"๋ผ๋Š” ํ…Œ์ด๋ธ”์—์„œ "store_cd", "amount", "quantity"๋ผ๋Š” ์„ธ ๊ฐœ์˜ ์—ด์„ ์„ ํƒํ•œ๋‹ค. ์ด ํ…Œ์ด๋ธ”์—๋Š” ํŠน์ • ๋งค์žฅ์˜ ๋งค์ถœ ๋ฐ์ดํ„ฐ๊ฐ€ ํฌํ•จ๋˜์–ด ์žˆ๋‹ค.

SUM ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๊ฐ ๋งค์žฅ์˜ "๊ธˆ์•ก" ์—ด๊ณผ "์ˆ˜๋Ÿ‰" ์—ด์˜ ํ•ฉ๊ณ„๋ฅผ ๊ณ„์‚ฐํ•œ๋‹ค.

GROUP BY ๊ตฌ๋ฌธ์€ ๋งค์ถœ ๋ฐ์ดํ„ฐ๋ฅผ ๋งค์žฅ๋ณ„๋กœ ๊ทธ๋ฃนํ™”ํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค. ์ฆ‰, ์ด ์ฟผ๋ฆฌ๋Š” 'store_cd' ์ปฌ๋Ÿผ์˜ ๊ณ ์œ ํ•œ ๋งค์žฅ ์ฝ”๋“œ๋ณ„๋กœ '๊ธˆ์•ก'๊ณผ '์ˆ˜๋Ÿ‰'์˜ ํ•ฉ๊ณ„๋ฅผ ๋ฐ˜ํ™˜ํ•œ๋‹ค.

์ฒซ ๋ฒˆ์งธ ์ฝ”๋“œ๊ฐ€ ๋‘ ๋ฒˆ์งธ ์ฝ”๋“œ์™€ ์™„์ „ํžˆ ๋™์ผํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์ค‘๋ณต ๋˜๋Š” ์‹ค์ˆ˜์ผ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ๊ทธ๋Ÿผ์—๋„ ๋ถˆ๊ตฌํ•˜๊ณ  ์ด ์ฟผ๋ฆฌ๋Š” 'receipt' ํ…Œ์ด๋ธ”์—์„œ ๊ฐ ๋งค์žฅ์˜ ํŒ๋งค ์ƒํ’ˆ์˜ ์ด ๊ธˆ์•ก๊ณผ ์ˆ˜๋Ÿ‰์„ ์„ ํƒํ•˜์—ฌ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” "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๋ช…์˜ ๊ณ ๊ฐ์œผ๋กœ ์ œํ•œํ•œ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” "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๋ช…์˜ ๊ณ ๊ฐ์œผ๋กœ ์ œํ•œํ•˜๊ณ  ์žˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” "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๋ช…์˜ ๊ณ ๊ฐ์œผ๋กœ ์ œํ•œํ•˜๊ณ  ์žˆ๋‹ค.

ย 

์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” "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๊ฐœ ๋งค์žฅ์œผ๋กœ๋งŒ ๊ฒฐ๊ณผ๋ฅผ ์ œํ•œํ•˜์—ฌ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.
ย 
์„ค๋ช…:

๋‹ค์Œ์€ "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๊ฐœ ๋งค์žฅ์„ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.

ย 

์„ค๋ช…:

"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๊ฐœ ์ƒํ’ˆ์˜ ๋งค์žฅ ์ฝ”๋“œ, ์ƒํ’ˆ ์ฝ”๋“œ, ๋ชจ๋“œ ์ˆ˜๋ฅผ ๊ฐ๊ฐ ๋ฐ˜ํ™˜ํ•œ๋‹ค.

ย 

์„ค๋ช…:

๋‹ค์Œ์€ "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๊ฐœ ๋งค์žฅ์œผ๋กœ ์ œํ•œ๋œ๋‹ค.
ย 
์„ค๋ช…:

๋‹ค์Œ์€ "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๊ฐœ ๋งค์žฅ์„ ๋ฐ˜ํ™˜ํ•˜๋Š”๋ฐ, ์ด๋Š” ์ด๋“ค ๋งค์žฅ์ด ์˜์ˆ˜์ฆ ํ•œ ์žฅ๋‹น ์‚ฌ์šฉ ๊ธˆ์•ก์˜ ํ™•์‚ฐ๊ณผ ๋ถ„์‚ฐ์ด ๊ฐ€์žฅ ํฌ๋‹ค๋Š” ๊ฒƒ์„ ์˜๋ฏธํ•œ๋‹ค.

ย 

์„ค๋ช…:

๋‹ค์Œ์€ "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๊ฐœ ๋งค์žฅ์„ ๋ฐ˜ํ™˜ํ•˜๋ฏ€๋กœ, ์ด๋“ค ๋งค์žฅ์€ ์˜์ˆ˜์ฆ ํ•œ ์žฅ๋‹น ์‚ฌ์šฉ ๊ธˆ์•ก์˜ ํ™•์‚ฐ๊ณผ ๋ถ„์‚ฐ์ด ๊ฐ€์žฅ ๋†’๋‹ค๋Š” ๊ฒƒ์„ ์˜๋ฏธํ•œ๋‹ค.

ย 

์„ค๋ช…:

"recipate" ํ…Œ์ด๋ธ”์˜ "amount" ์ปฌ๋Ÿผ์˜ ๋ฐฑ๋ถ„์œ„์ˆ˜ ๊ฐ’์„ ๊ณ„์‚ฐํ•˜๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉํ•˜๋Š” SQL ์ฝ”๋“œ์ด๋‹ค. ์ด ์ฝ”๋“œ์—์„œ๋Š” PERCENTILE_CONT ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์—ฐ์†์ ์ธ ๋ฐฑ๋ถ„์œ„์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•˜๊ณ  ์žˆ๋‹ค. ์ด๋Š” ๋ฐฑ๋ถ„์œ„์ˆ˜ ๊ฐ’์ด ๋ฐ์ดํ„ฐ ํฌ์ธํŠธ ๊ฐ„์˜ ๋ณด๊ฐ„๊ฐ’์ด ์•„๋‹ˆ๋ผ ๋ฐ์ดํ„ฐ ์ง‘ํ•ฉ์˜ ์‹ค์ œ ๊ฐ’์— ํ•ด๋‹นํ•œ๋‹ค๋Š” ๊ฒƒ์„ ์˜๋ฏธํ•ฉ๋‹ˆ๋‹ค.

์ด ์ฝ”๋“œ์—์„œ๋Š” 25๋ฒˆ์งธ ๋ฐฑ๋ถ„์œ„์ˆ˜(amount_25per), 50๋ฒˆ์งธ ๋ฐฑ๋ถ„์œ„์ˆ˜(amount_50per), 75๋ฒˆ์งธ ๋ฐฑ๋ถ„์œ„์ˆ˜(amount_75per), 100๋ฒˆ์งธ ๋ฐฑ๋ถ„์œ„์ˆ˜(amount_100per)์˜ ๋„ค ๊ฐ€์ง€ ๋ฐฑ๋ถ„์œ„์ˆ˜ ๊ฐ’์„ ์ง€์ •ํ•˜์—ฌ ๊ณ„์‚ฐํ•˜๊ณ  ์žˆ๋‹ค. ์ด ๋ฐฑ๋ถ„์œ„์ˆ˜๋Š” ๋ฐฑ๋ถ„์œ„์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•˜๊ธฐ ์ „์— WITHIN GROUP ์ ˆ์„ ์‚ฌ์šฉํ•˜์—ฌ 'amount' ์—ด์„ ์˜ค๋ฆ„์ฐจ์ˆœ์œผ๋กœ ์ •๋ ฌํ•ฉ๋‹ˆ๋‹ค.

์ฝ”๋“œ ๋งˆ์ง€๋ง‰์— ์žˆ๋Š” SELECT ๋ฌธ์€ ๊ณ„์‚ฐ๋œ ๋ฐฑ๋ถ„์œ„์ˆ˜ ๊ฐ’์„ ์ฟผ๋ฆฌ์—์„œ ๊ฐ€์ ธ์™€ ํ•œ ์ค„์˜ ์ถœ๋ ฅ์œผ๋กœ ๋ฐ˜ํ™˜ํ•œ๋‹ค. ์ด ์ฝ”๋“œ๋Š” 'RECIPATE' ํ…Œ์ด๋ธ”์˜ 'AMOUNT' ์ปฌ๋Ÿผ์˜ ๋ถ„ํฌ๋ฅผ ๋ถ„์„ํ•˜์—ฌ ๋ฐ์ดํ„ฐ ๋‚ด ์ด์ƒ๊ฐ’์ด๋‚˜ ๋น„์ •์ƒ์ ์ธ ํŒจํ„ด์„ ์‹๋ณ„ํ•˜๋Š” ๋ฐ ์œ ์šฉํ•˜๋‹ค.
ย 
์„ค๋ช…:

๋‹ค์Œ์€ "receipt" ํ…Œ์ด๋ธ”์˜ ๊ฐ ๋งค์žฅ์˜ ํ‰๊ท  ๊ฑฐ๋ž˜๊ธˆ์•ก์„ ์กฐํšŒํ•˜๊ณ  ์กฐ๊ฑด์— ๋”ฐ๋ผ ๊ฒฐ๊ณผ๋ฅผ ํ•„ํ„ฐ๋งํ•˜๋Š” ๋ฐ ์‚ฌ์šฉํ•˜๋Š” SQL ์ฝ”๋“œ์ž…๋‹ˆ๋‹ค.

์ด ์ฝ”๋“œ์—์„œ๋Š” ๋จผ์ € SELECT ๋ฌธ์—์„œ ์กฐํšŒํ•  ๋‘ ๊ฐœ์˜ ์ปฌ๋Ÿผ('store_cd' ์ปฌ๋Ÿผ๊ณผ ๊ฐ ๋งค์žฅ์˜ '๊ธˆ์•ก' ์ปฌ๋Ÿผ์˜ ํ‰๊ท ๊ฐ’)์„ ์ง€์ •ํ•˜๊ณ , AVG ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๊ฐ ๋งค์žฅ์˜ 'amount' ์ปฌ๋Ÿผ์˜ ํ‰๊ท ์„ ๊ณ„์‚ฐํ•œ ํ›„, AS ํ‚ค์›Œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๊ณ„์‚ฐ๋œ ์ปฌ๋Ÿผ์— 'avg_amount' ๋ผ๋Š” ๋ณ„์นญ์„ ๋ถ€์—ฌํ•˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค. ๋ผ๋Š” ๋ณ„์นญ์„ ๋ถ€์—ฌํ•˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค.

๋‹ค์Œ ์ฝ”๋“œ ๋ผ์ธ์—์„œ๋Š” GROUP BY ๊ตฌ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ 'store_cd' ์—ด๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๊ทธ๋ฃนํ™”ํ•˜์—ฌ ํ‰๊ท  ๊ฑฐ๋ž˜ ๊ธˆ์•ก์ด ๊ฐ ๋งค์žฅ์—์„œ ๊ฐœ๋ณ„์ ์œผ๋กœ ๊ณ„์‚ฐ๋จ์„ ์˜๋ฏธํ•ฉ๋‹ˆ๋‹ค.

๊ทธ๋Ÿฐ ๋‹ค์Œ ์ด ์ฝ”๋“œ๋Š” HAVING ์ ˆ์„ ์‚ฌ์šฉํ•˜์—ฌ ์กฐ๊ฑด์— ๋”ฐ๋ผ ๊ฒฐ๊ณผ๋ฅผ ํ•„ํ„ฐ๋งํ•œ๋‹ค. ๊ตฌ์ฒด์ ์œผ๋กœ ๊ฐ ๋งค์žฅ์˜ ํ‰๊ท  ๊ฑฐ๋ž˜์•ก์ด 330 ์ด์ƒ์ผ ๊ฒƒ์„ ์กฐ๊ฑด์œผ๋กœ ํ•œ๋‹ค. ์ด ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜์ง€ ์•Š๋Š” ๋งค์žฅ์€ ์ถœ๋ ฅ์— ํฌํ•จ๋˜์ง€ ์•Š๋Š”๋‹ค.

๊ฒฐ๊ณผ์ ์œผ๋กœ ์ถœ๋ ฅ๋˜๋Š” ๊ฒƒ์€ ๋‘ ๊ฐœ์˜ ์ปฌ๋Ÿผ์„ ๊ฐ€์ง„ ํ…Œ์ด๋ธ”์ด๋‹ค. 'store_cd'์™€ 'avg_amount' ๋‘ ๊ฐœ์˜ ์ปฌ๋Ÿผ์„ ๊ฐ€์ง„ ํ…Œ์ด๋ธ”์ด ์ถœ๋ ฅ๋œ๋‹ค. ๊ฐ ํ–‰์€ HAVING ์ ˆ์—์„œ ์ง€์ •ํ•œ ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” 'receipt' ํ…Œ์ด๋ธ”์˜ ๋งค์žฅ์— ํ•ด๋‹นํ•˜๋ฉฐ, 'avg_amount' ์—ด์—๋Š” ํ•ด๋‹น ๋งค์žฅ์˜ ํ‰๊ท  ๊ฑฐ๋ž˜ ๊ธˆ์•ก์ด ํฌํ•จ๋œ๋‹ค. ์ด ์ฝ”๋“œ๋Š” ํ‰๊ท  ๊ฑฐ๋ž˜๊ธˆ์•ก์„ ๊ธฐ์ค€์œผ๋กœ ๋‹ค๋ฅธ ๋งค์žฅ์˜ ์„ฑ๊ณผ๋ฅผ ๋ถ„์„ํ•˜๋Š” ๋ฐ ์œ ์šฉํ•˜๋‹ค.

ย 

์„ค๋ช…:

"์˜์ˆ˜์ฆ" ํ…Œ์ด๋ธ”์—์„œ 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" ์—ด์˜ ํ‰๊ท ์„ ๋‚˜ํƒ€๋‚ด๋Š” ํ•˜๋‚˜์˜ ๊ฐ’์ž…๋‹ˆ๋‹ค. ์ด ์ฝ”๋“œ๋Š” ๊ณ ๊ฐ์˜ ํ‰๊ท  ์ง€์ถœ ํŒจํ„ด์„ ๋ถ„์„ํ•˜๊ณ  ๋ฐ์ดํ„ฐ์—์„œ ์ด์ƒ๊ฐ’์ด๋‚˜ ๋น„์ •์ƒ์ ์ธ ํŒจํ„ด์„ ์‹๋ณ„ํ•˜๋Š” ๋ฐ ์œ ์šฉํ•˜๋‹ค.
ย 
์„ค๋ช…:

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๊ฐœ์˜ ํ–‰๋งŒ ์ถœ๋ ฅ์— ํฌํ•จ๋œ๋‹ค. ์ด ์ฝ”๋“œ๋Š” ๊ณ ์•ก ์‡ผํ•‘์„ ํ•˜๋Š” ๊ณ ๊ฐ์„ ์‹๋ณ„ํ•˜๊ณ  ๊ทธ๋“ค์˜ ์†Œ๋น„ ํŒจํ„ด์„ ๋ถ„์„ํ•˜๋Š” ๋ฐ ์œ ์šฉํ•˜๋‹ค.
ย 
์„ค๋ช…:

๋‹ค์Œ์€ "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๊ฐœ์˜ ํ–‰์œผ๋กœ ์ œํ•œ๋ฉ๋‹ˆ๋‹ค. ์ด ์ฝ”๋“œ๋Š” ๊ด€๋ จ ๋‘ ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๋ฅผ ๋ถ„์„ํ•˜๊ณ  ๋ฐ์ดํ„ฐ๋ฅผ ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ”๋กœ ๊ฒฐํ•ฉํ•˜์—ฌ ๋ถ„์„ํ•˜๋Š” ๋ฐ ํŽธ๋ฆฌํ•ฉ๋‹ˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” ๊ด€๊ณ„ํ˜• ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ๊ด€๋ฆฌํ•˜๊ณ  ์ฟผ๋ฆฌํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋˜๋Š” ํ”„๋กœ๊ทธ๋ž˜๋ฐ ์–ธ์–ด์ธ 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' ์—ด์„ ํฌํ•จํ•˜๊ฒŒ ๋œ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋„ 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)๋ฅผ ๋ฐ˜ํ™˜ํ•œ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋„ 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์™€ ํ•จ๊ป˜ ๋ฐ˜ํ™˜๋ฉ๋‹ˆ๋‹ค. ๊ณ ๊ฐ์ด ๋‘ ํ…Œ์ด๋ธ”์— ๋ชจ๋‘ ๋‚˜ํƒ€๋‚˜๋Š” ๊ฒฝ์šฐ, ์ถœ๋ ฅ์—๋Š” ํ•œ ๋ฒˆ๋งŒ ๋‚˜ํƒ€๋‚ฉ๋‹ˆ๋‹ค.
ย 
์„ค๋ช…:

์ด SQL ์ฝ”๋“œ๋Š” "store" ํ…Œ์ด๋ธ”๊ณผ "product" ํ…Œ์ด๋ธ” ๊ฐ„์˜ ๋ฐ์นด๋ฅดํŠธ ๊ณฑ์„ ์ˆ˜ํ–‰ํ•˜์—ฌ ์ƒ์„ฑ๋œ ๊ฒฐ๊ณผ ์ง‘ํ•ฉ์˜ ํ–‰ ๊ฐœ์ˆ˜๋ฅผ ๊ตฌํ•œ๋‹ค.

์ฝ”๋“œ๋Š” COUNT ํ•จ์ˆ˜๋ฅผ ์ง€์ •ํ•œ SELECT ๋ฌธ์œผ๋กœ ์‹œ์ž‘ํ•˜๋ฉฐ, ์นด์šดํŠธํ•  ๊ฐ’์€ '1', ์ฆ‰ ํ–‰ ์ˆ˜๋ฅผ ์นด์šดํŠธํ•˜๋Š” ๊ฒƒ์„ ์˜๋ฏธํ•œ๋‹ค.

FROM ์ ˆ์—์„œ๋Š” ์ฟผ๋ฆฌ์—์„œ ์‚ฌ์šฉํ•  ๋‘ ๊ฐœ์˜ ํ…Œ์ด๋ธ”, "store"์™€ "product"๋ฅผ ์ง€์ •ํ•˜๊ณ  ์žˆ๋‹ค. ํ•˜์ง€๋งŒ ๋‘ ํ…Œ์ด๋ธ”์„ ์–ด๋–ค ๋งค์นญ ์กฐ๊ฑด์— ๋”ฐ๋ผ ๊ฒฐํ•ฉํ•˜๋Š” ๊ฒƒ์ด ์•„๋‹ˆ๋ผ CROSS JOIN ํ‚ค์›Œ๋“œ๊ฐ€ ์‚ฌ์šฉ๋˜์—ˆ์Šต๋‹ˆ๋‹ค. ์ฆ‰, "store" ํ…Œ์ด๋ธ”์˜ ๊ฐ ํ–‰๊ณผ "product" ํ…Œ์ด๋ธ”์˜ ๊ฐ ํ–‰์ด ์ง์„ ์ด๋ฃจ์–ด ๋ฐ์นด๋ฅดํŠธ ๊ณฑ์ด ๋œ๋‹ค.

๊ฒฐ๊ณผ์ ์œผ๋กœ ์ถœ๋ ฅ๋˜๋Š” ๊ฒƒ์€ ๋ฐ์นด๋ฅดํŠธ ๊ณฑ์˜ ํ–‰ ์ˆ˜ ๊ฐœ์ˆ˜๋ฅผ ํฌํ•จํ•˜๋Š” ํ•˜๋‚˜์˜ ์—ด์„ ๊ฐ€์ง„ ํ•˜๋‚˜์˜ ํ–‰์ด ๋œ๋‹ค. ์ด ๊ฐœ์ˆ˜๋Š” "store" ํ…Œ์ด๋ธ”์˜ ํ–‰ ์ˆ˜์™€ "product" ํ…Œ์ด๋ธ”์˜ ํ–‰ ์ˆ˜๋ฅผ ๊ณฑํ•œ ๊ฒƒ์ด๋‹ค. ์ด ์œ ํ˜•์˜ ์ฟผ๋ฆฌ๋Š” ์ผ๋ฐ˜์ ์œผ๋กœ ๋‘ ๊ฐœ ์ด์ƒ์˜ ํ…Œ์ด๋ธ”์—์„œ ์–ป์„ ์ˆ˜ ์žˆ๋Š” ๋ฐ์ดํ„ฐ ์กฐํ•ฉ ๊ฐ€๋Šฅ์„ฑ์˜ ์ด ์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

ย 

ย 

Comment