๋ฐ์ดํ„ฐ ์‚ฌ์ด์–ธ์Šค 100๋ฒˆ์˜ ๋…ธํฌ(๊ตฌ์กฐํ™” ๋ฐ์ดํ„ฐ ์ฒ˜๋ฆฌํŽธ)- SQL Part 5 (Q81 to Q100)

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

์ด ์ฝ”๋“œ๋Š” SQL ์–ธ์–ด๋กœ ์ž‘์„ฑ๋˜์—ˆ์œผ๋ฉฐ, ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋‹จ๊ณ„๋ฅผ ์ˆ˜ํ–‰ํ•ฉ๋‹ˆ๋‹ค.

product_2๋ผ๋Š” ํ…Œ์ด๋ธ”์ด ์ด๋ฏธ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ์กด์žฌํ•˜๋Š” ๊ฒฝ์šฐ, ํ•ด๋‹น ํ…Œ์ด๋ธ”์„ ์‚ญ์ œํ•œ๋‹ค. ์ด๋Š” ์•„๋ฌด๊ฒƒ๋„ ์—†๋Š” ์ƒํƒœ์—์„œ ์‹œ์ž‘ํ•˜๋„๋ก ํ•˜๊ธฐ ์œ„ํ•ด ์ˆ˜ํ–‰๋ฉ๋‹ˆ๋‹ค.

CREATE TABLE ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ product_2๋ผ๋Š” ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์ด ์ƒ์„ฑ๋œ๋‹ค.

์ƒˆ ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๋Š” SELECT ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ product ํ…Œ์ด๋ธ”์—์„œ ๊ฐ€์ ธ์˜จ๋‹ค.

SELECT ๋ฌธ์—์„œ๋Š” product_cd, category_major_cd, category_medium_cd, category_small_cd๋ผ๋Š” ์—ด์ด product ํ…Œ์ด๋ธ”์—์„œ ์„ ํƒ๋œ๋‹ค.

SELECT ๋ฌธ์—์„œ COALESCE ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๋‘ ๊ฐœ์˜ ์ƒˆ๋กœ์šด ์—ด์ด ์ƒ์„ฑ๋˜๋ฉฐ, COALESCE๋Š” ์ˆ˜์‹ ๋ชฉ๋ก์—์„œ ์ฒซ ๋ฒˆ์งธ NULL์ด ์•„๋‹Œ ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•œ๋‹ค. ์ด ๊ฒฝ์šฐ unit_price๊ฐ€ NULL์ธ ๊ฒฝ์šฐ ์ „์ฒด ์ƒํ’ˆ์˜ ํ‰๊ท  ๋‹จ๊ฐ€(unit_avg)๊ฐ€ ๋Œ€์‹  ์‚ฌ์šฉ๋œ๋‹ค. ๋งˆ์ฐฌ๊ฐ€์ง€๋กœ unit_cost๊ฐ€ NULL์ธ ๊ฒฝ์šฐ ์ „์ฒด ์ƒํ’ˆ์˜ ํ‰๊ท  ๋‹จ๊ฐ€(cost_avg)๊ฐ€ ๋Œ€์‹  ์‚ฌ์šฉ๋œ๋‹ค.

SELECT ๋ฌธ์˜ FROM ๊ตฌ๋ฌธ์€ ์ƒํ’ˆ ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ์ƒํ’ˆ์˜ ํ‰๊ท  ๋‹จ๊ฐ€์™€ ๋‹จ๊ฐ€๋ฅผ ๊ณ„์‚ฐํ•˜๋Š” ํ•˜์œ„ ์ฟผ๋ฆฌ์™€ CROSS JOIN์„ ์ˆ˜ํ–‰ํ•˜๋ฉฐ, ROUND ํ•จ์ˆ˜๋Š” ํ‰๊ท ๊ฐ’์„ ๊ฐ€์žฅ ๊ฐ€๊นŒ์šด ์ •์ˆ˜๋กœ ๋ฐ˜์˜ฌ๋ฆผํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

SELECT ๋ฌธ ๊ฒฐ๊ณผ๋Š” CREATE TABLE ๋ฌธ์— ์˜ํ•ด ์•”๋ฌต์ ์œผ๋กœ ์‹คํ–‰๋˜๋Š” INSERT INTO ๋ฌธ์œผ๋กœ product_2 ํ…Œ์ด๋ธ”์— ์ž…๋ ฅํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

์ด ์ฝ”๋“œ์—์„œ๋Š” product_2๋ผ๋Š” ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์ด ์ƒ์„ฑ๋˜์–ด product ํ…Œ์ด๋ธ”๊ณผ ๋™์ผํ•œ ์—ด์„ ํฌํ•จํ•˜์ง€๋งŒ, unit_price์™€ unit_cost ์—ด์˜ null ๊ฐ’์€ ๋ชจ๋“  ์ƒํ’ˆ์— ๋Œ€ํ•ด ๊ณ„์‚ฐ๋œ ํ‰๊ท  ๋‹จ๊ฐ€์™€ ๋‹จ๊ฐ€๋กœ ๊ฐ๊ฐ ๋Œ€์ฒด๋ฉ๋‹ˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” SQL ์–ธ์–ด๋กœ ์ž‘์„ฑ๋˜์—ˆ์œผ๋ฉฐ, ๋‹ค์Œ ๋‹จ๊ณ„๋ฅผ ์ˆ˜ํ–‰ํ•œ๋‹ค.

SELECT ๋ฌธ์„ ์‹คํ–‰ํ•˜์—ฌ product_2 ํ…Œ์ด๋ธ”์„ ์กฐํšŒํ•œ๋‹ค.

SUM ํ•จ์ˆ˜๋ฅผ CASE ์‹๊ณผ ํ•จ๊ป˜ ์‚ฌ์šฉํ•˜์—ฌ unit_price ์—ด๊ณผ unit_cost ์—ด์˜ NULL ๊ฐ’์˜ ๊ฐœ์ˆ˜๋ฅผ ๋ณ„๋„๋กœ ๊ณ„์‚ฐํ•œ๋‹ค.

๊ฐ CASE ์‹์—์„œ WHEN unit_price IS NULL ๋˜๋Š” WHEN unit_cost IS NULL์€ ํ•ด๋‹น ์ปฌ๋Ÿผ์˜ ๊ฐ’์ด NULL์ธ์ง€ ์—ฌ๋ถ€๋ฅผ ํ™•์ธํ•œ๋‹ค.

์ปฌ๋Ÿผ์— NULL ๊ฐ’์ด ์žˆ์œผ๋ฉด CASE ํ‘œํ˜„์‹์€ 1๋กœ ํ‰๊ฐ€๋˜๊ณ , ๊ทธ๋ ‡์ง€ ์•Š์œผ๋ฉด 0์œผ๋กœ ํ‰๊ฐ€๋œ๋‹ค.

๊ทธ๋Ÿฐ ๋‹ค์Œ SUM ํ•จ์ˆ˜๊ฐ€ ๊ฐ ํ–‰์˜ 1๊ณผ 0์„ ํ•ฉํ•˜์—ฌ unit_price์™€ unit_cost ์—ด์˜ NULL ๊ฐ’์˜ ์ด ๊ฐœ์ˆ˜๋ฅผ ๊ฐ๊ฐ ๊ณ„์‚ฐํ•œ๋‹ค.

AS ํ‚ค์›Œ๋“œ๋Š” ๊ฒฐ๊ณผ ์ง‘ํ•ฉ์˜ ์—ด์„ ์˜๋ฏธ ์žˆ๋Š” ์ด๋ฆ„, ์ฆ‰ unit_price์™€ unit_cost๋กœ ๋งŒ๋“ค๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉ๋œ๋‹ค.

๋งˆ์ง€๋ง‰์œผ๋กœ LIMIT ๊ตฌ๋ฌธ์€ ์ถœ๋ ฅ์„ ์ฒ˜์Œ 10์ค„๋กœ ์ œํ•œํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

์š”์•ฝํ•˜๋ฉด, ์ด ์ฝ”๋“œ๋Š” product_2 ํ…Œ์ด๋ธ”์˜ unit_price ๋ฐ unit_cost ์—ด์˜ NULL ๊ฐ’์˜ ์ˆ˜๋ฅผ ํ‘œ์‹œํ•˜๋Š” ๊ฒฐ๊ณผ ์ง‘ํ•ฉ์„ ๋ฐ˜ํ™˜ํ•œ๋‹ค. ์ด ์ถœ๋ ฅ์€ ์ด๋Ÿฌํ•œ ์—ด์—์„œ ๋ฐ์ดํ„ฐ๊ฐ€ ๋ˆ„๋ฝ๋œ ํ–‰์ด ๋ช‡ ๊ฐœ ์žˆ๋Š”์ง€ ๋ณด์—ฌ ์ฃผ๋ฉฐ, ์ฒ˜๋ฆฌํ•ด์•ผ ํ•  ๋ฐ์ดํ„ฐ ํ’ˆ์งˆ ๋ฌธ์ œ๋ฅผ ์‹๋ณ„ํ•˜๋Š” ๋ฐ ์œ ์šฉํ•ฉ๋‹ˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” SQL ์–ธ์–ด๋กœ ์ž‘์„ฑ๋˜์—ˆ์œผ๋ฉฐ, ๋‹ค์Œ ๋‹จ๊ณ„๋ฅผ ์ˆ˜ํ–‰ํ•œ๋‹ค.

product_3์ด๋ผ๋Š” ํ…Œ์ด๋ธ”์ด ์ด๋ฏธ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ์กด์žฌํ•˜๋Š” ๊ฒฝ์šฐ ์‚ญ์ œํ•œ๋‹ค. ์ด๋Š” ์•„๋ฌด๊ฒƒ๋„ ์—†๋Š” ์ƒํƒœ์—์„œ ์‹œ์ž‘ํ•˜๋„๋ก ํ•˜๊ธฐ ์œ„ํ•œ ๊ฒƒ์ด๋‹ค.

CREATE TABLE ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ product_3์ด๋ผ๋Š” ์ƒˆ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•œ๋‹ค.

์ƒˆ ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๋Š” SELECT ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ product ํ…Œ์ด๋ธ”์—์„œ ๊ฐ€์ ธ์˜จ๋‹ค.

SELECT ๋ฌธ์—์„œ๋Š” product_cd, category_major_cd, category_medium_cd, category_small_cd๋ผ๋Š” ์—ด์ด product ํ…Œ์ด๋ธ”์—์„œ ์„ ํƒ๋œ๋‹ค.

SELECT ๋ฌธ์—์„œ COALESCE ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๋‘ ๊ฐœ์˜ ์ƒˆ๋กœ์šด ์—ด์ด ์ƒ์„ฑ๋˜๋ฉฐ, COALESCE๋Š” ์ˆ˜์‹ ๋ชฉ๋ก์—์„œ ์ฒซ ๋ฒˆ์งธ NULL์ด ์•„๋‹Œ ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•œ๋‹ค. ์ด ๊ฒฝ์šฐ unit_price๊ฐ€ NULL์ธ ๊ฒฝ์šฐ ์ „์ฒด ์ƒํ’ˆ์˜ ๋‹จ๊ฐ€ ์ค‘๊ฐ„๊ฐ’(unit_med)์ด ๋Œ€์‹  ์‚ฌ์šฉ๋œ๋‹ค. ๋งˆ์ฐฌ๊ฐ€์ง€๋กœ unit_cost๊ฐ€ NULL์ธ ๊ฒฝ์šฐ, ์ „์ฒด ์ƒํ’ˆ์˜ ๋‹จ๊ฐ€ ์ค‘๊ฐ„๊ฐ’(cost_med)์ด ๋Œ€์‹  ์‚ฌ์šฉ๋œ๋‹ค.

SELECT ๋ฌธ์˜ FROM ๊ตฌ๋ฌธ์€ ์ƒํ’ˆ ํ…Œ์ด๋ธ”์˜ ์ „์ฒด ์ƒํ’ˆ ๋‹จ๊ฐ€์˜ ์ค‘์•™๊ฐ’๊ณผ ๋‹จ๊ฐ€๋ฅผ ๊ณ„์‚ฐํ•˜๋Š” ํ•˜์œ„ ์ฟผ๋ฆฌ์™€์˜ CROSS JOIN์„ ์ˆ˜ํ–‰ํ•œ๋‹ค. ์ค‘์•™๊ฐ’ ์‚ฐ์ถœ์—๋Š” PERCENTILE_CONT ํ•จ์ˆ˜๊ฐ€ ์‚ฌ์šฉ๋˜๋ฉฐ, WITHIN GROUP ๊ตฌ๋ฌธ์€ ํ–‰์˜ ์ˆœ์„œ๋ฅผ ์ง€์ •ํ•˜์—ฌ ํ•จ์ˆ˜๊ฐ€ ์ค‘์•™๊ฐ’ ์‚ฐ์ถœ์— ์‚ฌ์šฉํ•  ๊ฐ’์„ ์ธ์‹ํ•  ์ˆ˜ ์žˆ๋„๋ก ํ•œ๋‹ค.

ROUND ํ•จ์ˆ˜๋Š” ์ค‘์•™๊ฐ’์„ ๊ฐ€์žฅ ๊ฐ€๊นŒ์šด ์ •์ˆ˜๋กœ ๋ฐ˜์˜ฌ๋ฆผํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

SELECT ๊ตฌ๋ฌธ์˜ ๊ฒฐ๊ณผ๋Š” CREATE TABLE ๊ตฌ๋ฌธ์— ์˜ํ•ด ์•”๋ฌต์ ์œผ๋กœ ์‹คํ–‰๋˜๋Š” INSERT INTO ๊ตฌ๋ฌธ์„ ํ†ตํ•ด product_3 ํ…Œ์ด๋ธ”์— ์ž…๋ ฅํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

์ด ์ฝ”๋“œ์—์„œ๋Š” product_3์ด๋ผ๋Š” ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์ด ์ƒ์„ฑ๋˜์–ด product ํ…Œ์ด๋ธ”๊ณผ ๋™์ผํ•œ ์—ด์„ ํฌํ•จํ•˜์ง€๋งŒ, unit_price์™€ unit_cost ์—ด์˜ null ๊ฐ’์€ ๋ชจ๋“  ์ œํ’ˆ์—์„œ ๊ณ„์‚ฐ๋œ ๊ฐ๊ฐ์˜ ๋‹จ๊ฐ€์™€ ๋‹จ๊ฐ€์˜ ์ค‘๊ฐ„๊ฐ’์œผ๋กœ ๋Œ€์ฒด๋œ๋‹ค. ์ค‘๊ฐ„๊ฐ’์€ ํ–‰์˜ ์ˆœ์„œ๋ฅผ ์ง€์ •ํ•˜๋Š” WITHIN GROUP ๊ตฌ๋ฌธ์„ ๊ฐ€์ง„ PERCENTILE_CONT ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๊ณ„์‚ฐ๋œ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” SQL ์–ธ์–ด๋กœ ์ž‘์„ฑ๋˜์—ˆ์œผ๋ฉฐ, ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋‹จ๊ณ„๋ฅผ ์ˆ˜ํ–‰ํ•œ๋‹ค.

SELECT ๋ฌธ์„ ์‹คํ–‰ํ•˜์—ฌ product_3 ํ…Œ์ด๋ธ”์„ ์กฐํšŒํ•œ๋‹ค.

SUM ํ•จ์ˆ˜๋ฅผ CASE ์‹๊ณผ ํ•จ๊ป˜ ์‚ฌ์šฉํ•˜์—ฌ unit_price ์—ด๊ณผ unit_cost ์—ด์˜ NULL ๊ฐ’์˜ ๊ฐœ์ˆ˜๋ฅผ ๋ณ„๋„๋กœ ๊ณ„์‚ฐํ•œ๋‹ค.

๊ฐ CASE ์‹์—์„œ WHEN unit_price IS NULL ๋˜๋Š” WHEN unit_cost IS NULL์€ ๊ฐ ์—ด์˜ ๊ฐ’์ด NULL์ธ์ง€ ์—ฌ๋ถ€๋ฅผ ํ™•์ธํ•œ๋‹ค.

์ปฌ๋Ÿผ์— NULL ๊ฐ’์ด ์žˆ์œผ๋ฉด CASE ํ‘œํ˜„์‹์€ 1๋กœ ํ‰๊ฐ€๋˜๊ณ , ๊ทธ๋ ‡์ง€ ์•Š์œผ๋ฉด 0์œผ๋กœ ํ‰๊ฐ€๋œ๋‹ค.

๊ทธ๋Ÿฐ ๋‹ค์Œ SUM ํ•จ์ˆ˜๊ฐ€ ๊ฐ ํ–‰์˜ 1๊ณผ 0์„ ํ•ฉ์‚ฐํ•˜์—ฌ unit_price์™€ unit_cost ์—ด์˜ NULL ๊ฐ’์˜ ์ด ์ˆ˜๋ฅผ ๊ฐ๊ฐ ๊ณ„์‚ฐํ•œ๋‹ค.

AS ํ‚ค์›Œ๋“œ๋Š” ๊ฒฐ๊ณผ ์ง‘ํ•ฉ์˜ ์—ด์„ ์˜๋ฏธ ์žˆ๋Š” ์ด๋ฆ„, ์ฆ‰ unit_price์™€ unit_cost๋กœ ๋งŒ๋“ค๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉ๋œ๋‹ค.

๋งˆ์ง€๋ง‰์œผ๋กœ LIMIT ๊ตฌ๋ฌธ์€ ์ถœ๋ ฅ์„ ์ฒ˜์Œ 10์ค„๋กœ ์ œํ•œํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

์š”์•ฝํ•˜๋ฉด, ์ด ์ฝ”๋“œ๋Š” product_3 ํ…Œ์ด๋ธ”์˜ unit_price ๋ฐ unit_cost ์—ด์˜ NULL ๊ฐ’์˜ ์ˆ˜๋ฅผ ํ‘œ์‹œํ•˜๋Š” ๊ฒฐ๊ณผ ์ง‘ํ•ฉ์„ ๋ฐ˜ํ™˜ํ•œ๋‹ค. ์ด ์ถœ๋ ฅ์€ ํ•ด๋‹น ์—ด์—์„œ ๋ฐ์ดํ„ฐ๊ฐ€ ๋ˆ„๋ฝ๋œ ํ–‰์ด ๋ช‡ ๊ฐœ ์žˆ๋Š”์ง€ ๋ณด์—ฌ์ฃผ๊ธฐ ๋•Œ๋ฌธ์— ์ฒ˜๋ฆฌํ•ด์•ผ ํ•  ๋ฐ์ดํ„ฐ ํ’ˆ์งˆ ๋ฌธ์ œ๋ฅผ ์‹๋ณ„ํ•˜๋Š” ๋ฐ ์œ ์šฉํ•ฉ๋‹ˆ๋‹ค. product_3 ํ…Œ์ด๋ธ”์€ ์ด์ „ ๋‹จ๊ณ„์—์„œ ์ƒ์„ฑ๋˜์—ˆ์œผ๋ฉฐ, unit_price ๋ฐ unit_cost ์—ด์˜ null ๊ฐ’์€ ์ „์ฒด ์ œํ’ˆ์—์„œ ๊ณ„์‚ฐ๋œ ๊ฐ ๋‹จ์œ„๋‹น ๊ฐ€๊ฒฉ๊ณผ ๋‹จ์œ„๋‹น ํ‰๊ท ๊ฐ’์œผ๋กœ ๋Œ€์ฒด๋ฉ๋‹ˆ๋‹ค. ๋‹จ๊ฐ€์™€ ๋‹จ๊ฐ€์˜ ์ค‘๊ฐ„๊ฐ’์œผ๋กœ ๋Œ€์ฒด๋˜์—ˆ์Šต๋‹ˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” SQL ์–ธ์–ด๋กœ ์ž‘์„ฑ๋˜์—ˆ์œผ๋ฉฐ, ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋‹จ๊ณ„๋ฅผ ์ˆ˜ํ–‰ํ•œ๋‹ค.

DROP TABLE ๋ฌธ์„ ์‹คํ–‰ํ•˜์—ฌ product_4 ํ…Œ์ด๋ธ”์ด ์ด๋ฏธ ์กด์žฌํ•˜๋Š” ๊ฒฝ์šฐ ์‚ญ์ œํ•œ๋‹ค.

CREATE TABLE ๋ฌธ์„ ์‹คํ–‰ํ•˜์—ฌ product_4 ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•œ๋‹ค.

category_median์ด๋ผ๋Š” ์ด๋ฆ„์˜ ๊ณตํ†ต ํ…Œ์ด๋ธ” ํ‘œํ˜„์‹(CTE)์ด ์ •์˜๋œ๋‹ค. ์ด CTE๋Š” ORDER BY ์ ˆ์ด ์žˆ๋Š” SELECT ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ์ƒํ’ˆ ํ…Œ์ด๋ธ”์˜ ํ–‰์„ ๊ฐ ์นดํ…Œ๊ณ ๋ฆฌ ๋‚ด์˜ unit_price์™€ unit_cost ์—ด๋กœ ์ •๋ ฌํ•˜๊ณ , PERCENTILE_CONT ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๊ฐ ์นดํ…Œ๊ณ ๋ฆฌ์˜ unit_price์™€ unit_cost์˜ ์ค‘๊ฐ„๊ฐ’์„ ๊ณ„์‚ฐํ•œ๋‹ค. GROUP BY ๊ตฌ๋ฌธ์€ category_small_cd ์—ด์„ ๊ธฐ์ค€์œผ๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๊ทธ๋ฃนํ™”ํ•˜๊ณ  ์žˆ๋‹ค.

๋ฉ”์ธ SELECT ๋ฌธ์€ product ํ…Œ์ด๋ธ”์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์™€ category_small_cd ์—ด์—์„œ category_median CTE์™€ ๊ฒฐํ•ฉํ•˜๊ณ , COALESCE ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ unit_price์™€ unit_cost ์—ด์˜ NULL ๊ฐ’์„ ํ•ด๋‹น ์นดํ…Œ๊ณ ๋ฆฌ์˜ ์ค‘์•™๊ฐ’์œผ๋กœ ๋Œ€์ฒดํ•œ๋‹ค. ํ•ด๋‹น ์นดํ…Œ๊ณ ๋ฆฌ์˜ ์ค‘์•™๊ฐ’์œผ๋กœ ๋Œ€์ฒดํ•ฉ๋‹ˆ๋‹ค.

๋งˆ์ง€๋ง‰์œผ๋กœ CREATE TABLE AS ๊ตฌ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ๊ฒฐ๊ณผ ๋ฐ์ดํ„ฐ๋ฅผ product_4 ํ…Œ์ด๋ธ”์— ์‚ฝ์ž…ํ•œ๋‹ค.

์š”์•ฝํ•˜๋ฉด, ์ด ์ฝ”๋“œ์—์„œ๋Š” product ํ…Œ์ด๋ธ”๊ณผ CTE category_median์„ ๊ฒฐํ•ฉํ•˜์—ฌ new table product_4๋ฅผ ์ƒ์„ฑํ•˜๊ณ , PERCENTILE_CONT ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ product ํ…Œ์ด๋ธ”์˜ ๊ฐ ์นดํ…Œ๊ณ ๋ฆฌ์— ๋Œ€ํ•ด unit_price์™€ unit_cost์˜ ์ค‘๊ฐ„๊ฐ’์ด ๊ณ„์‚ฐ๋ฉ๋‹ˆ๋‹ค. ๊ฒฐ๊ณผ ํ…Œ์ด๋ธ” product_4์—๋Š” unit_price์™€ unit_cost ์—ด์˜ NULL ๊ฐ’์„ ํ•ด๋‹น ์นดํ…Œ๊ณ ๋ฆฌ์˜ ์ค‘์•™๊ฐ’์œผ๋กœ ๋Œ€์ฒดํ•œ ์ƒํ’ˆ ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๊ฐ€ ํฌํ•จ๋˜์–ด ์žˆ์Šต๋‹ˆ๋‹ค.

ย 

ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋„ SQL๋กœ ์ž‘์„ฑ๋˜์—ˆ์œผ๋ฉฐ, ๋‹ค์Œ ๋‹จ๊ณ„๋ฅผ ์ˆ˜ํ–‰ํ•œ๋‹ค.

SELECT ๋ฌธ์„ ์‹คํ–‰ํ•˜์—ฌ product_4 ํ…Œ์ด๋ธ”์˜ unit_price ์—ด๊ณผ unit_cost ์—ด์˜ null ๊ฐ’์˜ ํ•ฉ๊ณ„๋ฅผ ๊ฐ€์ ธ์˜จ๋‹ค.

SUM ํ•จ์ˆ˜์˜ CASE ๋ฌธ์€ unit_price ์—ด๊ณผ unit_cost ์—ด์˜ ๊ฐ ๊ฐ’์ด null์ธ์ง€ ์—ฌ๋ถ€๋ฅผ ํ™•์ธํ•œ๋‹ค. ์ด๋ ‡๊ฒŒ ํ•ด์„œ SUM ํ•จ์ˆ˜๋Š” ๊ฐ ์—ด์˜ NULL ๊ฐ’์˜ ์ด ๊ฐœ์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•œ๋‹ค.

AS ํ‚ค์›Œ๋“œ๋Š” ์—ด์˜ ์ด๋ฆ„์„ ๊ฐ๊ฐ unit_price์™€ unit_cost๋กœ ๋ณ€๊ฒฝํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

LIMIT ๊ตฌ๋ฌธ์€ ์ฟผ๋ฆฌ๊ฐ€ ๋ฐ˜ํ™˜ํ•˜๋Š” ํ–‰ ์ˆ˜๋ฅผ 10์œผ๋กœ ์ œํ•œํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

์š”์•ฝํ•˜๋ฉด, ์ด ์ฝ”๋“œ๋Š” product_4 ํ…Œ์ด๋ธ”์˜ unit_price์™€ unit_cost ์—ด์˜ null ๊ฐ’์˜ ์ด ๊ฐœ์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•ฉ๋‹ˆ๋‹ค. ์ด๋ฅผ ํ†ตํ•ด ํ…Œ์ด๋ธ”์—์„œ ๋ˆ„๋ฝ๋œ ๋ฐ์ดํ„ฐ๋ฅผ ์‹๋ณ„ํ•˜๊ณ  ์ถ”๊ฐ€ ๋ฐ์ดํ„ฐ ์ •๋ฆฌ ๋˜๋Š” ์ธํ“จํ…Œ์ด์…˜์ด ํ•„์š”ํ•œ์ง€ ์—ฌ๋ถ€๋ฅผ ํŒ๋‹จํ•˜๋Š” ๋ฐ ๋„์›€์ด ๋ฉ๋‹ˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋„ SQL๋กœ ์ž‘์„ฑ๋˜์–ด ์žˆ์œผ๋ฉฐ, ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋‹จ๊ณ„๋ฅผ ์ˆ˜ํ–‰ํ•ฉ๋‹ˆ๋‹ค.

SELECT ๋ฌธ์ด ์‹คํ–‰๋˜์–ด ์˜์ˆ˜์ฆ ํ…Œ์ด๋ธ”๊ณผ ๊ณ ๊ฐ ํ…Œ์ด๋ธ”์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜ต๋‹ˆ๋‹ค.

๋‘ ๊ฐœ์˜ ํ•˜์œ„ ์ฟผ๋ฆฌ๊ฐ€ ์ƒ์„ฑ๋˜์–ด 2019๋…„ ๋ฐ ๋ชจ๋“  ์—ฐ๋„์˜ ๊ฐ ๊ณ ๊ฐ์— ๋Œ€ํ•œ ์ด ๋งค์ถœ์•ก์„ ๊ณ„์‚ฐํ•ฉ๋‹ˆ๋‹ค.

SELECT ๋ฌธ์€ LEFT JOIN ์—ฐ์‚ฐ์ž๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๊ณ ๊ฐ ํ…Œ์ด๋ธ”๊ณผ ๋‘ ๊ฐœ์˜ ํ•˜์œ„ ์ฟผ๋ฆฌ๋ฅผ ๊ฒฐํ•ฉํ•œ๋‹ค. ์ด๋ ‡๊ฒŒ ํ•˜๋ฉด ์˜์ˆ˜์ฆ ํ…Œ์ด๋ธ”์— ๋งค์ถœ ๋ฐ์ดํ„ฐ๊ฐ€ ์—†๋Š” ๊ฒฝ์šฐ์—๋„ ๊ณ ๊ฐ ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ๊ณ ๊ฐ์ด ์ถœ๋ ฅ์— ํฌํ•จ๋œ๋‹ค.

COALESCE ํ•จ์ˆ˜๋Š” ์˜์ˆ˜์ฆ ํ…Œ์ด๋ธ”์— ๊ณ ๊ฐ์— ๋Œ€ํ•œ ๋งค์ถœ ๋ฐ์ดํ„ฐ๊ฐ€ ์—†๋Š” ๊ฒฝ์šฐ NULL ๊ฐ’์„ 0์œผ๋กœ ๋Œ€์ฒดํ•ด์ค€๋‹ค.

CASE ๋ฌธ์€ ๊ฐ ๊ณ ๊ฐ์˜ ๋งค์ถœ ๋น„์œจ์„ ๊ณ„์‚ฐํ•ฉ๋‹ˆ๋‹ค. ์ „์ฒด ์—ฐ๋„ ๋งค์ถœ ๊ธˆ์•ก์˜ ํ•ฉ๊ณ„๊ฐ€ 0์ธ์ง€ ํ™•์ธํ•˜๊ณ , 0์„ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค. ๊ทธ๋ ‡์ง€ ์•Š์€ ๊ฒฝ์šฐ 2019๋…„ ๋งค์ถœ์•ก์„ ์ „์ฒด ์—ฐ๋„ ๋งค์ถœ์•ก ํ•ฉ๊ณ„๋กœ ๋‚˜๋ˆˆ๋‹ค.

๊ฒฐ๊ณผ๋Š” sales_rate๋ผ๋Š” ์ƒˆ ํ…Œ์ด๋ธ”์— ์‚ฝ์ž…๋œ๋‹ค.

์š”์•ฝํ•˜๋ฉด, ์ด ์ฝ”๋“œ๋Š” ์˜์ˆ˜์ฆ ํ…Œ์ด๋ธ”์˜ ๋งค์ถœ ๋ฐ์ดํ„ฐ๋ฅผ ๊ธฐ๋ฐ˜์œผ๋กœ ๊ฐ ๊ณ ๊ฐ์˜ ๋งค์ถœ ๋น„์œจ์„ ๊ณ„์‚ฐํ•œ๋‹ค. ํŒ๋งค์œจ์€ ์ „์ฒด ์—ฐ๋„ ํŒ๋งค ๊ธˆ์•ก์˜ ์ด์•ก์— ๋Œ€ํ•œ 2019 ๋…„๋„ ํŒ๋งค ๊ธˆ์•ก์˜ ๋น„์œจ์ž…๋‹ˆ๋‹ค. ๊ฒฐ๊ณผ๋Š” sales_rate๋ผ๋Š” ์ƒˆ ํ…Œ์ด๋ธ”์— ์ €์žฅ๋ฉ๋‹ˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋„ SQL๋กœ ์ž‘์„ฑ๋˜์–ด ์žˆ์œผ๋ฉฐ, ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋‹จ๊ณ„๋ฅผ ์ˆ˜ํ–‰ํ•œ๋‹ค.

SELECT ๋ฌธ์„ ์‹คํ–‰ํ•˜์—ฌ sales_rate ํ…Œ์ด๋ธ”์—์„œ sales_rate ๊ฐ’์ด 0๋ณด๋‹ค ํฐ ๋ชจ๋“  ํ–‰์„ ๊ฐ€์ ธ์˜ต๋‹ˆ๋‹ค.

LIMIT ์ ˆ์€ ์ถœ๋ ฅ์„ ์ฒ˜์Œ 10๊ฐœ์˜ ํ–‰์œผ๋กœ ์ œํ•œํ•˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค.

๊ฒฐ๊ณผ๊ฐ€ ์ถœ๋ ฅ์œผ๋กœ ๋ฐ˜ํ™˜๋ฉ๋‹ˆ๋‹ค.

์š”์•ฝํ•˜๋ฉด, ์ด ์ฝ”๋“œ๋Š” sales_rate ํ…Œ์ด๋ธ”์—์„œ sales_rate๊ฐ€ 0๋ณด๋‹ค ํฐ ๋ชจ๋“  ํ–‰, ์ฆ‰ ๊ณ ๊ฐ์ด 2019๋…„์— ์ ์–ด๋„ ํ•œ ๋ฒˆ ์ด์ƒ ํŒ๋งค๋ฅผ ํ•œ ๊ฒƒ์„ ์˜๋ฏธํ•˜๋Š” ๋ชจ๋“  ํ–‰์„ ์„ ํƒํ•œ๋‹ค.

ย 

์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” SQL๋กœ ์ž‘์„ฑ๋˜์—ˆ์œผ๋ฉฐ, ๋‹ค์Œ ๋‹จ๊ณ„๋ฅผ ์ˆ˜ํ–‰ํ•ฉ๋‹ˆ๋‹ค.

WITH ์ ˆ์„ ์‚ฌ์šฉํ•˜์—ฌ geocode ํ…Œ์ด๋ธ”์„ ์‚ฌ์šฉํ•˜์—ฌ ๊ฐ ์šฐํŽธ๋ฒˆํ˜ธ์˜ ํ‰๊ท  ๊ฒฝ๋„์™€ ์œ„๋„๋ฅผ ๊ณ„์‚ฐํ•˜๋Š” ํ•˜์œ„ ์ฟผ๋ฆฌ๋ฅผ ์ •์˜ํ•œ๋‹ค.

SELECT ๋ฌธ์€ postal_cd ์—ด์„ ์‚ฌ์šฉํ•˜์—ฌ customer ํ…Œ์ด๋ธ”๊ณผ WITH ์ ˆ์—์„œ ์ •์˜ํ•œ ํ•˜์œ„ ์ฟผ๋ฆฌ๋ฅผ ๊ฒฐํ•ฉํ•œ๋‹ค.

๊ฒฐํ•ฉ ์ž‘์—…์˜ ๊ฒฐ๊ณผ๋Š” CREATE TABLE ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ customer_1์ด๋ผ๋Š” ์ƒˆ ํ…Œ์ด๋ธ”์— ์‚ฝ์ž…๋œ๋‹ค. ๊ฐ™์€ ์ด๋ฆ„์˜ ํ…Œ์ด๋ธ”์ด ์ด๋ฏธ ์กด์žฌํ•˜๋Š” ๊ฒฝ์šฐ ๋จผ์ € DROP TABLE IF EXISTS ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ์‚ญ์ œ๋œ๋‹ค.

์š”์•ฝํ•˜๋ฉด, ์ด ์ฝ”๋“œ์—์„œ๋Š” postal_cd ์—ด์„ ์‚ฌ์šฉํ•˜์—ฌ customer ํ…Œ์ด๋ธ”๊ณผ geocode ํ…Œ์ด๋ธ”์„ ๊ฒฐํ•ฉํ•˜๊ณ , ๊ฐ ์šฐํŽธ๋ฒˆํ˜ธ์˜ ํ‰๊ท  ๊ฒฝ๋„์™€ ์œ„๋„๋ฅผ customer ํ…Œ์ด๋ธ”์— ์ถ”๊ฐ€ํ•˜์—ฌ customer_1์ด๋ผ๋Š” ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค. ์™„์„ฑ๋œ ํ…Œ์ด๋ธ”์—๋Š” customer ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ์—ด๊ณผ ํ‰๊ท  ๊ฒฝ๋„์™€ ์œ„๋„๋ฅผ ์œ„ํ•œ ๋‘ ๊ฐœ์˜ ์—ด์ด ์ถ”๊ฐ€๋˜์–ด ์žˆ์Šต๋‹ˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” SQL๋กœ ์ž‘์„ฑ๋˜์—ˆ์œผ๋ฉฐ, customer_1 ํ…Œ์ด๋ธ”์— ๋Œ€ํ•ด ๊ฐ„๋‹จํ•œ SELECT ๋ฌธ์„ ์‹คํ–‰ํ•œ๋‹ค.

SELECT ๋ฌธ์€ ์™€์ผ๋“œ์นด๋“œ ๋ฌธ์ž *๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ customer_1 ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ์—ด๊ณผ ํ–‰์„ ๊ฐ€์ ธ์˜จ๋‹ค.

LIMIT ์ ˆ์€ ์ฟผ๋ฆฌ๊ฐ€ ๋ฐ˜ํ™˜ํ•˜๋Š” ํ–‰ ์ˆ˜๋ฅผ ์ฒ˜์Œ 10๊ฐœ์˜ ํ–‰์œผ๋กœ ์ œํ•œํ•œ๋‹ค.

์ด ํ…Œ์ด๋ธ”์—๋Š” customer ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ์—ด๊ณผ ๊ฐ ๊ณ ๊ฐ์˜ ์šฐํŽธ๋ฒˆํ˜ธ์˜ ํ‰๊ท  ๊ฒฝ๋„์™€ ์œ„๋„ ๋‘ ๊ฐœ์˜ ์—ด์ด ์ถ”๊ฐ€๋˜์–ด ์žˆ์Šต๋‹ˆ๋‹ค.
ย 

ย 

์„ค๋ช…:

์ด SQL ์ฝ”๋“œ๋Š” customer_1๊ณผ store ๋‘ ํ…Œ์ด๋ธ”์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์™€ ๊ฐ ๊ณ ๊ฐ์˜ ์ฃผ์†Œ์™€ ํ•ด๋‹น ๋งค์žฅ์˜ ์ฃผ์†Œ ์‚ฌ์ด์˜ ๊ฑฐ๋ฆฌ๋ฅผ ๊ณ„์‚ฐํ•œ๋‹ค.

SELECT ๋ฌธ์€ ๋‹ค์Œ๊ณผ ๊ฐ™์€ ์ปฌ๋Ÿผ์„ ๊ฐ€์ ธ์˜ต๋‹ˆ๋‹ค.

customer_1 ํ…Œ์ด๋ธ”์—์„œ customer_id๋ฅผ ๊ฐ€์ ธ์˜จ๋‹ค.
customer_1 ํ…Œ์ด๋ธ”์—์„œ customer_id๋ฅผ ๊ฐ€์ ธ์™€ customer_address๋กœ ์ด๋ฆ„์„ ๋ฐ”๊พผ๋‹ค.
store ํ…Œ์ด๋ธ”์˜ address(store_address๋กœ ์ด๋ฆ„ ๋ณ€๊ฒฝ).

๊ณ ๊ฐ๊ณผ ๋งค์žฅ ์‚ฌ์ด์˜ ๊ฑฐ๋ฆฌ ๊ณ„์‚ฐ ๊ฐ’์œผ๋กœ Haversine์˜ ๊ณต์‹์„ ์‚ฌ์šฉํ•˜์—ฌ ACOS, SIN, COS ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๋‘ ์ฃผ์†Œ์˜ ์œ„๋„์™€ ๊ฒฝ๋„์˜ ์ •ํ˜„๊ณผ ์ฝ”์‚ฌ์ธ์˜ ์•„ํฌ ์ฝ”์‚ฌ์ธ์„ ๊ฐ๊ฐ ๊ณ„์‚ฐํ•œ๋‹ค. ๊ทธ๋ฆฌ๊ณ  ๊ทธ ๊ฒฐ๊ณผ์— ์ง€๊ตฌ ๋ฐ˜๊ฒฝ(6371km)์„ ๊ณฑํ•˜์—ฌ ํ‚ฌ๋กœ๋ฏธํ„ฐ ๋‹จ์œ„์˜ ๊ฑฐ๋ฆฌ๋ฅผ ๊ตฌํ•ฉ๋‹ˆ๋‹ค.

JOIN ๋ฌธ์€ application_store_cd ์—ด๊ณผ store_cd ์—ด์˜ ์ผ์น˜๋ฅผ ๊ธฐ๋ฐ˜์œผ๋กœ customer_1 ํ…Œ์ด๋ธ”๊ณผ store ํ…Œ์ด๋ธ”์˜ ํ–‰์„ ๊ฒฐํ•ฉํ•˜๊ณ , LIMIT ๊ตฌ๋ฌธ์€ ๋ฐ˜ํ™˜๋˜๋Š” ํ–‰์˜ ์ˆ˜๋ฅผ 10์œผ๋กœ ์ œํ•œํ•œ๋‹ค.

ย 

ย 
์„ค๋ช…:

์ด SQL ์ฝ”๋“œ๋Š” "customer"์™€ "recipate" ๋‘ ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฒฐํ•ฉํ•˜์—ฌ "customer_u"๋ผ๋Š” ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค. ๋จผ์ € "sales_amount"๋ผ๋Š” ๊ณตํ†ต ํ…Œ์ด๋ธ” ํ‘œํ˜„์‹(CTE)์„ ์ •์˜ํ•˜๊ณ , "customer" ํ…Œ์ด๋ธ”๊ณผ "recipate" ํ…Œ์ด๋ธ”์„ ์™ผ์ชฝ ๊ฒฐํ•ฉ์„ ์‚ฌ์šฉํ•˜์—ฌ ๊ฒฐํ•ฉํ•˜์—ฌ ๊ฐ ๊ณ ๊ฐ์˜ ์ด ๋งค์ถœ์•ก์„ ๊ณ„์‚ฐํ•ฉ๋‹ˆ๋‹ค. ์ด CTE๋Š” COALESCE ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๋งค์ถœ์ด ์—†๋Š” ๊ณ ๊ฐ์—๊ฒŒ๋Š” 0์„ ๋ฐ˜ํ™˜ํ•œ๋‹ค.

๊ทธ๋Ÿฐ ๋‹ค์Œ "sales_ranking"์ด๋ผ๋Š” ์ด๋ฆ„์˜ ๋˜ ๋‹ค๋ฅธ CTE๊ฐ€ ์ •์˜๋˜์–ด customer_name๊ณผ postal_cd์˜ ๊ฐ ๊ทธ๋ฃน ๋‚ด์—์„œ ์ด ํŒ๋งค ๊ธˆ์•ก์„ ๊ธฐ์ค€์œผ๋กœ ๊ฐ ๊ณ ๊ฐ์˜ ํŒ๋งค ์ˆœ์œ„๋ฅผ ๊ณ„์‚ฐํ•˜๊ณ  ROW_NUMBER() ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ sum_amount๋ฅผ ๊ธฐ์ค€์œผ๋กœ ๋‚ด๋ฆผ์ฐจ์ˆœ์œผ๋กœ ๊ฐ ๊ณ ๊ฐ์—๊ฒŒ ์ˆœ์œ„๋ฅผ ํ• ๋‹นํ•˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค.

๋งˆ์ง€๋ง‰์œผ๋กœ SELECT ๋ฌธ์€ 'customer' ํ…Œ์ด๋ธ”๊ณผ 'sales_ranking' CTE๋ฅผ ๊ฒฐํ•ฉํ•˜์—ฌ ์ˆœ์œ„๊ฐ€ 1์ธ ํ–‰๋งŒ ํ•„ํ„ฐ๋งํ•œ๋‹ค. ์ด๋ฅผ ํ†ตํ•ด customer_name๊ณผ postal_cd ๊ทธ๋ฃน๋ณ„๋กœ ์ƒ์œ„ ๊ณ ๊ฐ๋งŒ ํšจ๊ณผ์ ์œผ๋กœ ์„ ํƒ๋œ๋‹ค. ์™„์„ฑ๋œ ๋ฐ์ดํ„ฐ๋Š” "customer_u"๋ผ๋Š” ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์— ์‚ฝ์ž…๋œ๋‹ค.
ย 
์„ค๋ช…:

์ด SQL ์ฝ”๋“œ๋Š” ๋‘ ๊ฐœ์˜ ์„œ๋กœ ๋‹ค๋ฅธ ํ…Œ์ด๋ธ”์˜ ๊ณ ๊ฐ ์ˆ˜๋ฅผ ์กฐํšŒํ•˜๊ณ  ๊ทธ ์ฐจ์ด๋ฅผ ๊ณ„์‚ฐํ•˜๋Š” ์ฝ”๋“œ์ž…๋‹ˆ๋‹ค.

์ฝ”๋“œ์˜ ์ฒซ ๋ฒˆ์งธ ๋ถ€๋ถ„์€ "customer" ํ…Œ์ด๋ธ”์—์„œ ์ด ๊ณ ๊ฐ ์ˆ˜๋ฅผ ์„ ํƒํ•˜๊ณ  "customer_cnt"๋ผ๋Š” ๋ณ„์นญ์„ ๋ถ€์—ฌํ•˜๋Š” ํ•˜์œ„ ์ฟผ๋ฆฌ์ž…๋‹ˆ๋‹ค.

์ฝ”๋“œ์˜ ๋‘ ๋ฒˆ์งธ ๋ถ€๋ถ„์€ "customer_u" ํ…Œ์ด๋ธ”์—์„œ ์ด ๊ณ ๊ฐ ์ˆ˜๋ฅผ ์„ ํƒํ•˜๊ณ  "customer_u_cnt"๋ผ๋Š” ์ด๋ฆ„์œผ๋กœ ๋ณ„์นญ์„ ์ง€์ •ํ•˜๋Š” ๋˜ ๋‹ค๋ฅธ ํ•˜์œ„ ์ฟผ๋ฆฌ์ž…๋‹ˆ๋‹ค.

๋งˆ์ง€๋ง‰์œผ๋กœ ์™ธ๋ถ€ ์ฟผ๋ฆฌ์—์„œ๋Š” ๋‘ ๊ฐœ์˜ ํ•˜์œ„ ์ฟผ๋ฆฌ์—์„œ 'customer_cnt'์™€ 'customer_u_cnt'๋ฅผ ์„ ํƒํ•˜๊ณ  ๋‘˜์˜ ์ฐจ์ด๋ฅผ 'diff'๋กœ ๊ณ„์‚ฐํ•œ๋‹ค. ์ด๋Š” "customer_cnt"์—์„œ "customer_u_cnt"๋ฅผ ๋นผ๋ฉด ๋œ๋‹ค.

๊ฒฐ๊ณผ๋Š” "customer_cnt", "customer_u_cnt", "diff"์˜ ์„ธ ๊ฐœ์˜ ์—ด๋กœ ๊ตฌ์„ฑ๋œ ํ•œ ์ค„๋กœ, "customer_cnt" ์—ด์€ "customer" ํ…Œ์ด๋ธ”์˜ ์ด ๊ณ ๊ฐ ์ˆ˜๋ฅผ, "customer_u_cnt" ์—ด์€ "customer_u" ํ…Œ์ด๋ธ”์˜ ์ด ๊ณ ๊ฐ ์ˆ˜๋ฅผ, "diff" ์—ด์€ "customer_u" ํ…Œ์ด๋ธ”์˜ ์ด ๊ณ ๊ฐ ์ˆ˜๋ฅผ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค. ํ…Œ์ด๋ธ”์˜ ์ด ๊ณ ๊ฐ ์ˆ˜๋ฅผ, 'diff' ์—ด์€ ์ด ๋‘ ์นด์šดํŠธ์˜ ์ฐจ์ด๋ฅผ ํ‘œ์‹œํ•œ๋‹ค.
ย 
์„ค๋ช… :

์ด SQL ์ฝ”๋“œ๋Š” ๊ธฐ์กด customer ํ…Œ์ด๋ธ”๊ณผ customer_u ํ…Œ์ด๋ธ”์„ ๊ฒฐํ•ฉํ•˜์—ฌ customer_n์ด๋ผ๋Š” ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•œ๋‹ค.

JOIN ์กฐ๊ฑด์€ customer_name๊ณผ postal_cd ์ปฌ๋Ÿผ์— ๋™์ผํ•œ ๊ฐ’์„ ๊ฐ€์ง„ customer_u ํ…Œ์ด๋ธ” ๊ฐ„์˜ ํ–‰์„ ์ผ์น˜์‹œํ‚จ๋‹ค. ์ผ์น˜ํ•˜๋Š” ํ–‰์— ๋Œ€ํ•ด ๊ฒฐ๊ณผ ํ…Œ์ด๋ธ” customer_n์˜ integration_id ์—ด์€ customer_u ํ…Œ์ด๋ธ”์˜ customer_id๋กœ ์„ค์ •๋œ๋‹ค.

๋”ฐ๋ผ์„œ customer_n ํ…Œ์ด๋ธ”์—๋Š” customer ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ํ–‰๊ณผ customer_u ํ…Œ์ด๋ธ”์˜ customer_id ๊ฐ’์„ ํฌํ•จํ•˜๋Š” integration_id ์ปฌ๋Ÿผ์ด ์ถ”๊ฐ€๋˜๊ณ , customer_u ํ…Œ์ด๋ธ”์˜ customer_name๊ณผ postal_cd ๊ฐ’์ด ์ผ์น˜ํ•˜์ง€ ์•Š๋Š” ํ–‰์€ integration_id ์ปฌ๋Ÿผ์— NULL ๊ฐ’์„ ๊ฐ€์ง€๊ฒŒ ๋œ๋‹ค.

ย 

์„ค๋ช…:

์ด SQL ์ฝ”๋“œ๋Š” "customer_n" ํ…Œ์ด๋ธ”์˜ "customer_id" ์ปฌ๋Ÿผ๊ณผ "integration_id" ์ปฌ๋Ÿผ์ด ๊ฐ™์ง€ ์•Š์€ ํ–‰ ์ˆ˜๋ฅผ ์กฐํšŒํ•œ๋‹ค.

customer_n ํ…Œ์ด๋ธ”์€ ์ด์ „ ์ฝ”๋“œ ๋ธ”๋ก์—์„œ "customer" ํ…Œ์ด๋ธ”๊ณผ "customer_u" ํ…Œ์ด๋ธ”์„ ๊ณ ๊ฐ์˜ ์ด๋ฆ„๊ณผ ์šฐํŽธ๋ฒˆํ˜ธ๋ฅผ ๊ฒฐํ•ฉ ์กฐ๊ฑด์œผ๋กœ ๊ฒฐํ•ฉํ•˜์—ฌ ์ƒ์„ฑ๋˜์—ˆ๋‹ค. "customer" ํ…Œ์ด๋ธ”์— ์ƒˆ๋กœ์šด "integration_id" ์ปฌ๋Ÿผ์„ ์ถ”๊ฐ€ํ•˜๊ณ  "customer_u" ํ…Œ์ด๋ธ”์—์„œ ํ•ด๋‹น "customer_id" ๊ฐ’์„ ํฌํ•จํ•จ์œผ๋กœ์จ ๋‘ ํ…Œ์ด๋ธ”์„ ํšจ๊ณผ์ ์œผ๋กœ ํ†ตํ•ฉํ–ˆ๋‹ค.

ํ˜„์žฌ ์ฟผ๋ฆฌ๋Š” "customer_n" ํ…Œ์ด๋ธ”์˜ "customer_id" ๊ฐ’์ด "integration_id" ๊ฐ’๊ณผ ๊ฐ™์ง€ ์•Š์€ ํ–‰ ์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•œ๋‹ค. ์ด๋Š” 'customer' ํ…Œ์ด๋ธ”์— 'customer_u' ํ…Œ์ด๋ธ”์— ํ•ด๋‹นํ•˜๋Š” ๋ ˆ์ฝ”๋“œ๊ฐ€ ์—†๊ฑฐ๋‚˜ 'customer_u' ํ…Œ์ด๋ธ”์˜ 'customer_id' ๊ฐ’์ด ๋‹ค๋ฅธ ๋ ˆ์ฝ”๋“œ๊ฐ€ ์žˆ๋‹ค๋Š” ๊ฒƒ์„ ์˜๋ฏธํ•œ๋‹ค. ์ด๋Š” ๋ฐ์ดํ„ฐ ํ†ตํ•ฉ ๋ฌธ์ œ๋ฅผ ์‹๋ณ„ํ•˜๊ณ  ๋ถˆ์ผ์น˜๋ฅผ ์ถ”๊ฐ€๋กœ ์กฐ์‚ฌํ•˜๋Š” ๋ฐ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” SQL์„ ์‚ฌ์šฉํ•˜์—ฌ ๊ณ ๊ฐ ํŒ๋งค ๋ฐ์ดํ„ฐ๋ฅผ ๊ธฐ๋ฐ˜์œผ๋กœ ํ•œ ๋จธ์‹ ๋Ÿฌ๋‹ ๋ชจ๋ธ์˜ ํ•™์Šต ๋ฐ์ดํ„ฐ ์„ธํŠธ์™€ ํ…Œ์ŠคํŠธ ๋ฐ์ดํ„ฐ ์„ธํŠธ๋ฅผ ์ƒ์„ฑํ•˜๊ณ  ์žˆ๋‹ค. ๊ฐ ํ–‰์ด ์ˆ˜ํ–‰ํ•˜๋Š” ์ž‘์—…์€ ๋‹ค์Œ๊ณผ ๊ฐ™์Šต๋‹ˆ๋‹ค.

sql SELECT SETSEED(0.1); %%sql SELECT SETSEED(0.1);
์ฟผ๋ฆฌ์—์„œ ์‚ฌ์šฉํ•  ๋‚œ์ˆ˜ ์ƒ์„ฑ๊ธฐ์˜ ์”จ์•—์„ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค. ์ด๋Š” ์ฟผ๋ฆฌ๋ฅผ ์‹คํ–‰ํ•  ๋•Œ๋งˆ๋‹ค ๋™์ผํ•œ ๊ฒฐ๊ณผ๋ฅผ ์–ป์„ ์ˆ˜ ์žˆ๋„๋ก ํ•˜๊ธฐ ์œ„ํ•จ์ด๋‹ค.

CREATE TEMP TABLE IF NOT EXISTS sales_customer AS ( SELECT customer_id, ROW_NUMBER() OVER(ORDER BY RANDOM()) AS row FROM customer JOIN receipt USING( customer_id) GROUP BY customer_id HAVING SUM(AMOUNT) > 0 );

์ด๊ฒƒ์€ ๊ฐ ๊ณ ๊ฐ์˜ customer_id์™€ ๋ฌด์ž‘์œ„๋กœ ํ• ๋‹น๋œ ํ–‰ ๋ฒˆํ˜ธ๋ฅผ ํฌํ•จํ•˜๋Š” sales_customer๋ผ๋Š” ์ž„์‹œ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๊ณ , JOIN์„ ์‚ฌ์šฉํ•˜์—ฌ customer์™€ receipate๋ผ๋Š” ๋‘ ํ…Œ์ด๋ธ”์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ์„ ํƒํ•œ๋‹ค. ๋ฐ์ดํ„ฐ๋ฅผ ๊ทธ๋ฃนํ™”ํ•˜์—ฌ ๊ตฌ๋งคํ•œ ๊ณ ๊ฐ(SUM(AMOUNT) > 0)๋งŒ์„ ํฌํ•จํ•˜๋„๋ก ๊ฒฐ๊ณผ๋ฅผ ํ•„ํ„ฐ๋งํ•˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค.

DROP TABLE IF EXISTS customer_train; CREATE TABLE customer_train AS SELECT customer.* FROM sales_customer JOIN customer USING(customer_id) WHERE sales_customer.row <= (SELECT COUNT(1) FROM sales_customer) * 0.8 ;

์ด๋ ‡๊ฒŒ ํ•˜๋ฉด sales_customer ํ…Œ์ด๋ธ”์˜ ๊ณ ๊ฐ ์ค‘ 80%๋ฅผ ํฌํ•จํ•˜๋Š” customer_train์ด๋ผ๋Š” ํ…Œ์ด๋ธ”์ด ์ƒ์„ฑ๋ฉ๋‹ˆ๋‹ค. ์ด๋Š” sales_customer ํ…Œ์ด๋ธ”์˜ ํ–‰ ์ˆ˜๊ฐ€ sales_customer ํ…Œ์ด๋ธ”์˜ ์ด ํ–‰ ์ˆ˜์˜ 80% ์ดํ•˜์ธ ๊ณ ๊ฐ์— ๋Œ€ํ•ด ๊ณ ๊ฐ ํ…Œ์ด๋ธ”์—์„œ ๋ชจ๋“  ์—ด์„ ์„ ํƒํ•œ๋‹ค.

DROP TABLE IF EXISTS customer_test; CREATE TABLE customer_test AS SELECT customer.* FROM sales_customer JOIN customer USING(customer_id) EXCEPT SELECT * FROM customer_train ;

์ด๊ฒƒ์€ sales_customer ํ…Œ์ด๋ธ”์—์„œ ๋‚˜๋จธ์ง€ 20%์˜ ๊ณ ๊ฐ์„ ํฌํ•จํ•˜๋Š” customer_test๋ผ๋Š” ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•œ๋‹ค. ์ด๋Š” customer_id๊ฐ€ sales_customer ํ…Œ์ด๋ธ”์— ์žˆ๊ณ  customer_train ํ…Œ์ด๋ธ”์— ์—†๋Š” ๊ณ ๊ฐ์˜ customer ํ…Œ์ด๋ธ”์—์„œ ๋ชจ๋“  ์ปฌ๋Ÿผ์„ ์„ ํƒํ•œ๋‹ค. ์ด๋ ‡๊ฒŒ ํ•˜๋ฉด ํ…Œ์ŠคํŠธ ์„ธํŠธ์— ํŠธ๋ ˆ์ด๋‹ ์„ธํŠธ์— ์—†๋Š” ๊ณ ๊ฐ์ด ํ…Œ์ŠคํŠธ ์„ธํŠธ์— ํฌํ•จ๋œ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” ์•ž์˜ SQL ์ฝ”๋“œ์—์„œ ์ƒ์„ฑํ•œ ํ…Œ์ด๋ธ”์„ ๊ธฐ๋ฐ˜์œผ๋กœ ํŠธ๋ ˆ์ด๋‹ ์„ธํŠธ์™€ ํ…Œ์ŠคํŠธ ์„ธํŠธ์˜ ๋ฐ์ดํ„ฐ ๋น„์œจ์„ ๊ณ„์‚ฐํ•˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค. ๊ฐ ํ–‰์ด ๋ฌด์—‡์„ ํ•˜๋Š”์ง€ ๊ทธ ๋‚ด์—ญ์„ ์†Œ๊ฐœํ•ฉ๋‹ˆ๋‹ค.

sql SELECT train_cnt * 1.0 / all_cnt as Percentage of training data, test_cnt * 1.0 / all_cnt as Test Data Percentage

์ด๊ฒƒ์€ ํ›ˆ๋ จ ์„ธํŠธ์™€ ํ…Œ์ŠคํŠธ ์„ธํŠธ์˜ ๋ฐ์ดํ„ฐ ๋น„์œจ์„ ์„ ํƒํ•˜๋Š” ๋ฉ”์ธ ์ฟผ๋ฆฌ๋กœ, train_cnt, test_cnt, all_cnt ๋ณ€์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜๋ฉฐ, ์ด๋Š” ์•„๋ž˜์˜ ์„œ๋ธŒ ์ฟผ๋ฆฌ์—์„œ ๊ณ„์‚ฐ๋œ๋‹ค.

FROM (SELECT COUNT(1) AS all_cnt FROM sales_customer) all_data CROSS JOIN (SELECT COUNT(1) AS train_cnt FROM customer_train) train_data CROSS JOIN ( SELECT COUNT(1) AS test_cnt FROM customer_test) test_data ;

์ด ์„œ๋ธŒ์ฟผ๋ฆฌ๋Š” sales_customer ํ…Œ์ด๋ธ”์˜ ์ด ํ–‰ ์ˆ˜(all_cnt), customer_train ํ…Œ์ด๋ธ”์˜ ํ–‰ ์ˆ˜(train_cnt), customer_test ํ…Œ์ด๋ธ”์˜ ํ–‰ ์ˆ˜(test_cnt)๋ฅผ ๊ณ„์‚ฐํ•œ๋‹ค.

์ด๋Ÿฌํ•œ ํ•˜์œ„ ์ฟผ๋ฆฌ๋ฅผ ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ”๋กœ ํ†ตํ•ฉํ•˜๋ ค๋ฉด CROSS JOIN ์—ฐ์‚ฐ์ž๋ฅผ ์‚ฌ์šฉํ•œ๋‹ค. ๊ฐ ํ•˜์œ„ ์ฟผ๋ฆฌ๋Š” ํ•˜๋‚˜์˜ ํ–‰๋งŒ ๋ฐ˜ํ™˜ํ•˜๋ฏ€๋กœ ํฌ๋กœ์Šค ์กฐ์ธ์˜ ๊ฒฐ๊ณผ๋Š” 3๊ฐœ์˜ ์—ด(all_cnt, train_cnt, test_cnt)์„ ๊ฐ€์ง„ ํ•˜๋‚˜์˜ ํ–‰์ด ๋œ๋‹ค.

๊ทธ๋Ÿฐ ๋‹ค์Œ ๋ฉ”์ธ ์ฟผ๋ฆฌ๋Š” train_cnt์™€ test_cnt๋ฅผ ๊ฐ๊ฐ all_cnt๋กœ ๋‚˜๋ˆ„์–ด ํ›ˆ๋ จ ์„ธํŠธ์™€ ํ…Œ์ŠคํŠธ ์„ธํŠธ์˜ ๋ฐ์ดํ„ฐ ๋น„์œจ์„ ๊ณ„์‚ฐํ•œ๋‹ค. ๊ฒฐ๊ณผ๋ฅผ ๋ถ€๋™ ์†Œ์ˆ˜์  ์ˆซ์ž๋กœ ๋ณ€ํ™˜ํ•˜๊ธฐ ์œ„ํ•ด * 1.0 ์‹์„ ์‚ฌ์šฉํ•˜์—ฌ ๋ถ€๋™ ์†Œ์ˆ˜์  ์ •ํ™•๋„๋กœ ๋‚˜๋ˆ—์…ˆ์ด ์ด๋ฃจ์–ด์ง€๋„๋ก ๋ณด์žฅํ•œ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ์—์„œ๋Š” ๋งค์ถœ ๋ฐ์ดํ„ฐ ์ง‘๊ณ„ํ‘œ(ts_amount)์— ์œˆ๋„์šฐ ํ•จ์ˆ˜๋ฅผ ์ ์šฉํ•˜์—ฌ 3๊ฐœ์˜ ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”(series_data_1, series_data_2, series_data_3)์„ ์ƒ์„ฑํ•˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค. ์•„๋ž˜๋Š” ๊ฐ ํ–‰์ด ํ•˜๋Š” ์ผ์˜ ๋‚ด์—ญ์ž…๋‹ˆ๋‹ค.

%%sql
CREATE TEMP TABLE IF NOT EXISTS ts_amount AS
(
SELECT
SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6) AS sales_ym,
SUM(amount) AS sum_amount,
ROW_NUMBER() OVER(ORDER BY SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6)) AS rn
FROM
receipt
GROUP BY
sales_name
);;

์ด ์ฝ”๋“œ์—์„œ๋Š” ์›”๋ณ„๋กœ ๋งค์ถœ ๋ฐ์ดํ„ฐ๋ฅผ ์ •๋ฆฌํ•˜๊ณ (SUBSTR ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ sales_ymd ์—ด์—์„œ ์—ฐ๋„์™€ ์›”์„ ์ถ”์ถœ), ๋งค์ถœ ์ด์•ก์„ ๊ณ„์‚ฐํ•˜๊ณ (SUM(amount)), ๊ฐ ํ–‰์— ํ–‰ ๋ฒˆํ˜ธ๋ฅผ ๋ถ€์—ฌ(ROW_NUMBER() OVER(...)) AS rn) ์ž„์‹œ ํ…Œ์ด๋ธ”(ts_amount)์„ ์ƒ์„ฑํ•˜๊ณ  ์žˆ๋‹ค. ํ–‰ ๋ฒˆํ˜ธ๋Š” ์ดํ›„ ์ฟผ๋ฆฌ์—์„œ ์—ฌ๋Ÿฌ ๊ธฐ๊ฐ„์˜ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฒฐํ•ฉํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

DROP TABLE IF EXISTS series_data_1 ; DROP TABLE IF EXISTS series_data_1 ;
CREATE TABLE series_data_1 AS (
WITH lag_amount AS
(
SELECT
sales_ym์ž…๋‹ˆ๋‹ค.
sum_amount๊ฐ€ ๋ฉ๋‹ˆ๋‹ค.
LAG(rn, 0) OVER (ORDER BY rn) AS rn
FROM
ts_amount
)
SELECT
sales_ym์ž…๋‹ˆ๋‹ค.
sum_amount๊ฐ€ ๋ฉ๋‹ˆ๋‹ค.
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM
lag_amount
WHERE
rn BETWEEN 1 AND 18).

์ด ์ฝ”๋“œ๋Š” ํ˜„์žฌ ๊ธฐ๊ฐ„์˜ ๋งค์ถœ ๋ฐ์ดํ„ฐ์™€ ์ด์ „ ๊ธฐ๊ฐ„์˜ ๋งค์ถœ ๋ฐ์ดํ„ฐ๋ฅผ (window ํ•จ์ˆ˜์™€ LAG ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ) ๊ฒฐํ•ฉํ•˜์—ฌ ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”(series_data_1)์„ ์ƒ์„ฑํ•˜๊ณ , ๊ฐ ํ–‰์ด ํŠธ๋ ˆ์ด๋‹ ์„ธํŠธ(test_flg = 0)์— ์†ํ•˜๋Š”์ง€ ํ…Œ์ŠคํŠธ ์„ธํŠธ(test_flg = 1)์— ์†ํ•˜๋Š”์ง€ ์— ๋”ฐ๋ผ ๋ฐ”์ด๋„ˆ๋ฆฌ ํ”Œ๋ž˜๊ทธ(test_flg)๋ฅผ ํ• ๋‹นํ•œ๋‹ค. ์ด๋Š” ํ–‰ ๋ฒˆํ˜ธ(rn)๋ฅผ ์ž„๊ณ„๊ฐ’(12)๊ณผ ๋น„๊ตํ•˜์—ฌ ๋ฐ์ดํ„ฐ๋ฅผ ๋‘ ๋ถ€๋ถ„์œผ๋กœ ๋‚˜๋ˆˆ๋‹ค: ์ฒ˜์Œ 12๊ฐœ์˜ ํ–‰์€ ํ›ˆ๋ จ์—, ๋‚˜๋จธ์ง€ 6๊ฐœ์˜ ํ–‰์€ ํ…Œ์ŠคํŠธ์— ์‚ฌ์šฉ๋œ๋‹ค. ์ด๋ ‡๊ฒŒ ๋งŒ๋“ค์–ด์ง„ ํ‘œ๋Š” ์ด 18๊ฐœ์˜ ํ–‰์œผ๋กœ ๊ตฌ์„ฑ๋œ๋‹ค. ์ด ์ฝ”๋“œ์—์„œ๋Š” ์ด ๊ณผ์ •์„ ๊ธฐ๊ฐ„์„ ๋ฐ”๊ฟ”์„œ ๋‘ ๋ฒˆ ๋” ๋ฐ˜๋ณตํ•˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค.

DROP TABLE IF EXISTS series_data_2 ;.
CREATE TABLE series_data_2 AS (
WITH lag_amount AS (
SELECT
sales_ym์ž…๋‹ˆ๋‹ค.
sum_amount๋กœ ํ•œ๋‹ค.
LAG(rn, 6) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ym์ž…๋‹ˆ๋‹ค.
sum_amount๋กœ ํ•œ๋‹ค.
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18);

DROP TABLE IF EXISTS series_data_3 ;
CREATE TABLE series_data_3 AS (
WITH lag_amount AS (
SELECT
sales_ym์ž…๋‹ˆ๋‹ค.
sum_amount๋กœ ํ•œ๋‹ค.
LAG(rn, 12) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ym์ž…๋‹ˆ๋‹ค.
sum_amount๋กœ ํ•œ๋‹ค.
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18).

series_data_2๋Š” 6๊ฐœ์›” ์ „ ๋งค์ถœ ๋ฐ์ดํ„ฐ๋ฅผ, series_data_3์€ 1๋…„ ์ „ ๋งค์ถœ ๋ฐ์ดํ„ฐ๋ฅผ ์ฐธ์กฐ ๊ธฐ๊ฐ„์œผ๋กœ ํ•œ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” ํ…Œ์ด๋ธ” series_data_1์—์„œ ๋ชจ๋“  ์—ด๊ณผ ํ–‰์„ ์„ ํƒํ•˜๋Š” SQL ์ฟผ๋ฆฌ์ด๋‹ค. series_data_1 ํ…Œ์ด๋ธ”์€ ์•ž์˜ SQL ์ฝ”๋“œ ๋ธ”๋ก์—์„œ CREATE TABLE ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ์ƒ์„ฑ๋˜์—ˆ์Šต๋‹ˆ๋‹ค.

series_data_1 ์ƒ์„ฑ์—์„œ ์ฟผ๋ฆฌ๋Š” ๊ฐ ์›”๋ณ„ ๊ธˆ์•ก์˜ ํ•ฉ๊ณ„๋ฅผ ๊ณ„์‚ฐํ•˜๋Š” ์ž„์‹œ ํ…Œ์ด๋ธ” ts_amount๋ฅผ ์ƒ์„ฑํ•˜๊ณ , ์›”์„ ๊ธฐ์ค€์œผ๋กœ ํ–‰ ๋ฒˆํ˜ธ๋ฅผ ํ• ๋‹นํ•˜๊ณ  ์žˆ๋‹ค. ํ–‰ ๋ฒˆํ˜ธ์™€ LAG() ์ฐฝ ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์ฟผ๋ฆฌ๋Š” ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ” series_data_1์„ ์ƒ์„ฑํ•˜๊ณ  ๊ฐ ์›”์˜ ๋งค์ถœ ๊ธˆ์•ก๊ณผ ํ•ด๋‹น ํ–‰์ด ํ›ˆ๋ จ ๋ฐ์ดํ„ฐ์™€ ํ…Œ์ŠคํŠธ ๋ฐ์ดํ„ฐ ์ค‘ ์–ด๋Š ์ชฝ์— ํฌํ•จ๋˜๋Š”์ง€ ํ‘œ์‹œํ•˜๋Š” ํ”Œ๋ž˜๊ทธ๋ฅผ ์ €์žฅํ•œ๋‹ค.

๋งˆ์ง€๋ง‰์œผ๋กœ ์ด ์ฝ”๋“œ ๋ธ”๋ก์€ ์ฟผ๋ฆฌ๊ฐ€ series_data_1์—์„œ ๋ชจ๋“  ์—ด๊ณผ ํ–‰์„ ์„ ํƒํ•˜์—ฌ ์ด์ „ SQL ๋ฌธ์žฅ์˜ ๊ฒฐ๊ณผ๋ฅผ ํ‘œ์‹œํ•  ์ˆ˜ ์žˆ๋„๋ก ํ•œ๋‹ค.
ย 
์„ค๋ช…:

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

์ฟผ๋ฆฌ์—์„œ๋Š” ์ฒซ ๋ฒˆ์งธ ๋ฌธ์—์„œ ๋ฌด์ž‘์œ„ ์‹œ๋“œ ๊ฐ’์„ ์„ค์ •ํ•˜๊ณ  ์žˆ๋‹ค. ๋‹ค์Œ ๋ฌธ์—์„œ๋Š” ๊ณ ๊ฐ ๋ฐ์ดํ„ฐ์™€ ๊ฐ ๊ณ ๊ฐ์˜ ๊ธˆ์•ก ํ•ฉ๊ณ„๊ฐ€ ํฌํ•จ๋œ down_sampling์ด๋ผ๋Š” ์ž„์‹œ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค. ์ฒซ ๋ฒˆ์งธ CTE์˜ LEFT JOIN ๊ตฌ๋ฌธ์€ ๊ณ ๊ฐ ํ…Œ์ด๋ธ”๊ณผ ์˜์ˆ˜์ฆ ํ…Œ์ด๋ธ”์„ ๊ฒฐํ•ฉํ•˜์—ฌ ๊ฐ ๊ณ ๊ฐ์˜ ๊ธˆ์•ก ํ•ฉ๊ณ„๋ฅผ ๊ณ„์‚ฐํ•ฉ๋‹ˆ๋‹ค.

๋‘ ๋ฒˆ์งธ CTE์—์„œ๋Š” CASE ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ๋‘ ๊ฐœ์˜ ์ถ”๊ฐ€ ์—ด์ด ์ƒ์„ฑ๋˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค. ์ด ์—ด์€ ๊ณ ๊ฐ์ด ๊ตฌ๋งค๋ฅผ ํ–ˆ๋Š”์ง€ ์—ฌ๋ถ€๋ฅผ ๋‚˜ํƒ€๋‚ด๋Š” ํ”Œ๋ž˜๊ทธ์ด๋‹ค. ๊ทธ๋Ÿฐ ๋‹ค์Œ ์„ธ ๋ฒˆ์งธ CTE์—์„œ ROW_NUMBER() ์ฐฝ ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ is_buy_flag ์—ด์„ ๊ธฐ๋ฐ˜์œผ๋กœ ๊ฐ ๊ณ ๊ฐ ๋ ˆ์ฝ”๋“œ์— ํ–‰ ๋ฒˆํ˜ธ๋ฅผ ํ• ๋‹นํ•˜๊ณ  ์žˆ๋‹ค.

๋งˆ์ง€๋ง‰์œผ๋กœ ์ฟผ๋ฆฌ์˜ ๋งˆ์ง€๋ง‰ ๋ฌธ์—์„œ ์ž„์‹œ ํ…Œ์ด๋ธ” down_sampling์ด ์„ ํƒ๋˜๋Š”๋ฐ, ํ–‰ ๋ฒˆํ˜ธ๊ฐ€ ๊ตฌ๋งค ๊ณ ๊ฐ ์ˆ˜์™€ ๋น„๊ตฌ๋งค ๊ณ ๊ฐ ์ˆ˜ ์ดํ•˜์ธ ํ–‰๋งŒ ์„ ํƒ๋œ๋‹ค. ์ด๋ ‡๊ฒŒ ํ•˜๋ฉด ๊ฒฐ๊ณผ ํ…Œ์ด๋ธ”์—์„œ ๊ฐ ํด๋ž˜์Šค์˜ ํ–‰ ์ˆ˜๊ฐ€ ๊ท ํ˜• ์žˆ๊ฒŒ ๋ฐฐ์น˜๋ฉ๋‹ˆ๋‹ค.

ย 

์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” down_sampling ์ž„์‹œ ํ…Œ์ด๋ธ”์˜ ๋ ˆ์ฝ”๋“œ๋ฅผ is_buy_flag ์—ด๋กœ ๊ทธ๋ฃนํ™”ํ•˜์—ฌ ๊ฐ ๊ทธ๋ฃน ๋‚ด ๋ ˆ์ฝ”๋“œ ์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•˜๋Š” SQL ์ฟผ๋ฆฌ๋ฅผ ์‹คํ–‰ํ•œ๋‹ค.

down_sampling ํ…Œ์ด๋ธ”์€ ์•ž์˜ ์ฝ”๋“œ ์…€์—์„œ customer ํ…Œ์ด๋ธ”๊ณผ receive ํ…Œ์ด๋ธ”์˜ amount ์—ด์„ customer_id๋กœ ์ง‘๊ณ„ํ•˜๋Š” ํ•˜์œ„ ์ฟผ๋ฆฌ ์‚ฌ์ด์— ์™ผ์ชฝ ๊ฒฐํ•ฉ์„ ์ˆ˜ํ–‰ํ•˜์—ฌ ์ƒ์„ฑ๋˜์—ˆ๋‹ค. ๊ฒฐ๊ณผ ํ…Œ์ด๋ธ”์—๋Š” ๊ณ ๊ฐ ์ •๋ณด ์—ด๊ณผ ๊ฐ ๊ณ ๊ฐ์ด ๊ตฌ๋งคํ•œ ๊ธˆ์•ก์˜ ํ•ฉ๊ณ„๊ฐ€ ์žˆ์Šต๋‹ˆ๋‹ค.

pre_table_2 ํ•˜์œ„ ์ฟผ๋ฆฌ๋Š” sum_amount ์ปฌ๋Ÿผ์„ ๊ธฐ๋ฐ˜์œผ๋กœ ๋‘ ๊ฐœ์˜ ์ƒˆ๋กœ์šด ์ปฌ๋Ÿผ์„ ์ƒ์„ฑํ•˜๋Š”๋ฐ, is_buy_flag ์ปฌ๋Ÿผ์€ 1ํšŒ ์ด์ƒ ๊ตฌ๋งค๋ฅผ ํ•œ ๊ณ ๊ฐ์—๊ฒŒ๋Š” 1์ด, ๊ตฌ๋งค๋ฅผ ํ•˜์ง€ ์•Š์€ ๊ณ ๊ฐ์—๊ฒŒ๋Š” 0์ด ์„ค์ •๋˜๊ณ , is_not_buy_flag ์ปฌ๋Ÿผ์€ ๋Š” ๊ตฌ๋งค๋ฅผ ํ•œ ๋ฒˆ๋„ ํ•˜์ง€ ์•Š์€ ๊ณ ๊ฐ์—๊ฒŒ๋Š” 1, 1ํšŒ ์ด์ƒ ๊ตฌ๋งค๋ฅผ ํ•œ ๊ณ ๊ฐ์—๊ฒŒ๋Š” 0์ด ์„ค์ •๋œ๋‹ค.

pre_table_3 ํ•˜์œ„ ์ฟผ๋ฆฌ๋Š” is_buy_flag ์ปฌ๋Ÿผ๊ณผ ์ž„์˜์˜ ์ˆœ์„œ์— ๋”ฐ๋ผ pre_table_2 ํ…Œ์ด๋ธ”์˜ ๊ฐ ํ–‰์— ๋Œ€ํ•ด ํ–‰ ๋ฒˆํ˜ธ๋ฅผ ์ƒ์„ฑํ•œ๋‹ค. ๋˜ํ•œ, ๋‘ ๊ฐœ์˜ ๋ณ„๋„ ํ•˜์œ„ ์ฟผ๋ฆฌ์— ์˜ํ•œ ๊ต์ฐจ ์กฐ์ธ์„ ์‚ฌ์šฉํ•˜์—ฌ is_buy_flag๊ฐ€ 1์ผ ๋•Œ์™€ 0์ผ ๋•Œ pre_table_2 ํ…Œ์ด๋ธ”์˜ ์ด ํ–‰ ์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•œ๋‹ค.

๋งˆ์ง€๋ง‰์œผ๋กœ ๋ฉ”์ธ SELECT ๋ฌธ์€ is_buy_flag๊ฐ€ 1์ธ ํ–‰๊ณผ 0์ธ ํ–‰์˜ ์ด ๊ฐœ์ˆ˜๋ณด๋‹ค ์ ์€ ํ–‰ ๋ฒˆํ˜ธ๋ฅผ ๊ฐ€์ง„ pre_table_3์˜ ๋ชจ๋“  ํ–‰์„ ์„ ํƒํ•œ๋‹ค. ์ด๋ ‡๊ฒŒ ํ•˜๋ฉด is_buy_flag์˜ ๊ฐ ๊ฐ’์— ๋Œ€ํ•ด ๋™์ผํ•œ ์ˆ˜์˜ ๋ ˆ์ฝ”๋“œ๊ฐ€ ์กด์žฌํ•˜๊ฒŒ ๋œ๋‹ค.

๋”ฐ๋ผ์„œ SELECT ๋ฌธ ์ถœ๋ ฅ์€ down_sampling ํ…Œ์ด๋ธ”์˜ is_buy_flag๊ฐ€ 0์ธ ๋ ˆ์ฝ”๋“œ์™€ 1์ธ ๋ ˆ์ฝ”๋“œ์˜ ๊ฐœ์ˆ˜๋ฅผ ๋‚˜ํƒ€๋‚ธ๋‹ค.
ย 
์„ค๋ช…:

์ด SQL ์ฝ”๋“œ๋Š” customer๋ผ๋Š” ๊ธฐ์กด ํ…Œ์ด๋ธ”์—์„œ ๋‘ ๊ฐœ์˜ ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•œ๋‹ค.

์ฝ”๋“œ์˜ ์ฒซ ๋ฒˆ์งธ ๋ถ€๋ถ„์—์„œ๋Š” customer_std๋ผ๋Š” ํ…Œ์ด๋ธ”์ด ์กด์žฌํ•˜๋Š” ๊ฒฝ์šฐ ์ด๋ฅผ ์‚ญ์ œํ•˜๊ณ  customer_std๋ผ๋Š” ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค. ์ด ์ƒˆ ํ…Œ์ด๋ธ”์—๋Š” customer ํ…Œ์ด๋ธ”๊ณผ ๋™์ผํ•œ ์—ด์ด ์žˆ๋Š”๋ฐ, customer_id, customer_name, gender_cd, birth_day, age, postal_cd, application_store_cd, application_date, status_cd๋กœ ๊ตฌ์„ฑ๋˜์–ด ์žˆ๋‹ค. ์ด ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๋Š” ๋ชฉ์ ์€ ์ œ๊ณต๋œ ์ฝ”๋“œ์—์„œ ๋ช…ํ™•ํ•˜์ง€ ์•Š๋‹ค. ๋ฐ์ดํ„ฐ๋ฅผ ํ‘œ์ค€ํ™”ํ•˜๊ฑฐ๋‚˜ ์ „์ฒ˜๋ฆฌํ•˜๊ธฐ ์œ„ํ•ด ์ƒ์„ฑ๋œ ๊ฒƒ์ผ ์ˆ˜๋„ ์žˆ๋‹ค.

์ฝ”๋“œ์˜ ๋‘ ๋ฒˆ์งธ ๋ถ€๋ถ„์€ gender_std๋ผ๋Š” ํ…Œ์ด๋ธ”์ด ์กด์žฌํ•œ๋‹ค๋ฉด ์ด๋ฅผ ์‚ญ์ œํ•˜๊ณ  gender_std๋ผ๋Š” ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•œ๋‹ค. ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์—๋Š” gender_cd์™€ gender๋ผ๋Š” ๋‘ ๊ฐœ์˜ ์ปฌ๋Ÿผ์ด ์žˆ๋Š”๋ฐ, gender_cd ์ปฌ๋Ÿผ์€ ๊ณ ๊ฐ ํ…Œ์ด๋ธ”์—์„œ ์ฐพ์€ ๋ชจ๋“  ์„ฑ๋ณ„ ์ฝ”๋“œ์˜ ๋ช…ํ™•ํ•œ ๋ชฉ๋ก์ด๊ณ , gender ์ปฌ๋Ÿผ์€ ํ•ด๋‹น ์„ฑ๋ณ„์— ๋Œ€ํ•œ ์„ค๋ช…์ด๋‹ค. ์ด ํ…Œ์ด๋ธ”์€ ์„ฑ๋ณ„ ์ฝ”๋“œ๋ฅผ ๊ฐ๊ฐ์˜ ์„ฑ๋ณ„ ์„ค๋ช…์— ๋งคํ•‘ํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋  ์ˆ˜ ์žˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ์—์„œ๋Š” ์•ž์„œ ์ƒ์„ฑํ•œ customer_std ํ…Œ์ด๋ธ”์—์„œ ์ฒ˜์Œ 3๊ฐœ ํ–‰์„ ์„ ํƒํ•˜๋Š”๋ฐ, customer_std ํ…Œ์ด๋ธ”์€ customer ํ…Œ์ด๋ธ”์˜ ๋ณต์‚ฌ๋ณธ์œผ๋กœ ์ปฌ๋Ÿผ์˜ ํ•˜์œ„ ์ง‘ํ•ฉ์„ ๊ฐ€์ง€๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค. ์ด๋Š” ๋ฐ์ดํ„ฐ๊ฐ€ ์ƒˆ ํ…Œ์ด๋ธ”๋กœ ์˜ฌ๋ฐ”๋ฅด๊ฒŒ ๊ฐ€์ ธ์™”๋Š”์ง€ ํ™•์ธํ•˜๊ธฐ ์œ„ํ•œ ๊ธฐ๋ณธ์ ์ธ ์ ๊ฒ€์ž…๋‹ˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” ์ด์ „ ์ฝ”๋“œ ๋ธ”๋ก์—์„œ ์ƒ์„ฑ๋œ gender_std ํ…Œ์ด๋ธ”์—์„œ ์ฒ˜์Œ 3๊ฐœ์˜ ํ–‰์„ ์„ ํƒํ•˜๋Š” SQL ์ฟผ๋ฆฌ๋ฅผ ์‹คํ–‰ํ•œ๋‹ค.

LIMIT ์ ˆ์€ ์ฟผ๋ฆฌ์—์„œ ๋ฐ˜ํ™˜๋˜๋Š” ํ–‰ ์ˆ˜๋ฅผ 3ํ–‰์œผ๋กœ ์ œํ•œํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋˜๋ฉฐ, gender_std ํ…Œ์ด๋ธ”์€ SELECT DISTINCT ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ์ƒ์„ฑ๋˜์—ˆ๊ธฐ ๋•Œ๋ฌธ์— gender_cd์˜ ๊ณ ์œ ํ•œ ๊ฐ’๊ณผ ๊ณ ๊ฐ ํ…Œ์ด๋ธ”์—์„œ ํ•ด๋‹น ์„ฑ๋ณ„์˜ ๊ฐ’๋งŒ ํฌํ•จํ•˜๊ฒŒ ๋ฉ๋‹ˆ๋‹ค. ์žˆ์Šต๋‹ˆ๋‹ค. ๋”ฐ๋ผ์„œ ์ด ์ฟผ๋ฆฌ๋Š” gender_std ํ…Œ์ด๋ธ”์—์„œ ์ฒ˜์Œ ์„ธ ๊ฐœ์˜ ๊ณ ์œ ํ•œ ํ–‰์„ ๋ฐ˜ํ™˜ํ•œ๋‹ค.

์ด ์ฟผ๋ฆฌ์˜ ์ถœ๋ ฅ์€ gender_std ํ…Œ์ด๋ธ”์˜ ์ฒ˜์Œ ์„ธ ํ–‰์˜ gender_cd์™€ gender ๊ฐ’์„ ํ‘œ์‹œํ•ฉ๋‹ˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” "product"์™€ "category" ๋‘ ํ…Œ์ด๋ธ”์„ ๊ฒฐํ•ฉํ•˜์—ฌ "product_full"์ด๋ผ๋Š” ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•œ๋‹ค.

product ํ…Œ์ด๋ธ”์—๋Š” ์ƒํ’ˆ ์ฝ”๋“œ, ์นดํ…Œ๊ณ ๋ฆฌ ์ฝ”๋“œ, ๋‹จ๊ฐ€, ๋‹จ๊ฐ€ ๋“ฑ ๊ฐ ์ƒํ’ˆ์— ๋Œ€ํ•œ ์ •๋ณด๊ฐ€ ์ €์žฅ๋˜์–ด ์žˆ๋‹ค.

category ํ…Œ์ด๋ธ”์—๋Š” ๊ฐ ์นดํ…Œ๊ณ ๋ฆฌ ๋ ˆ๋ฒจ์˜ ์นดํ…Œ๊ณ ๋ฆฌ ์ฝ”๋“œ์™€ ์นดํ…Œ๊ณ ๋ฆฌ ์ด๋ฆ„์ด ์ €์žฅ๋œ๋‹ค.

JOIN ๊ตฌ๋ฌธ์€ category_small_cd ์—ด์„ ๊ธฐ์ค€์œผ๋กœ ๋‘ ํ…Œ์ด๋ธ”์„ ์—ฐ๊ฒฐํ•˜๊ณ , USING ํ‚ค์›Œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๋‘ ํ…Œ์ด๋ธ”์—์„œ category_small_cd์˜ ๊ฐ’์ด ๋™์ผํ•œ ํ–‰์„ ์ผ์น˜์‹œํ‚ค๋Š” ์ฟผ๋ฆฌ๋ฅผ ์ˆ˜ํ–‰ํ•œ๋‹ค.

์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ” 'product_full'์—๋Š” 'product' ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ์ปฌ๋Ÿผ๊ณผ 'category' ํ…Œ์ด๋ธ”์˜ ์นดํ…Œ๊ณ ๋ฆฌ ์ฝ”๋“œ๋ฅผ ๊ฒ€์ƒ‰ํ•˜์—ฌ ์–ป์€ ์นดํ…Œ๊ณ ๋ฆฌ ์ด๋ฆ„ ์ปฌ๋Ÿผ์ด ์ถ”๊ฐ€๋œ๋‹ค.

ย 

์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” product์™€ category๋ผ๋Š” ๋‘ ํ…Œ์ด๋ธ”์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ์„ ํƒํ•˜์—ฌ product_full์ด๋ผ๋Š” ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•œ๋‹ค.

JOIN ๊ตฌ๋ฌธ์€ ๊ณตํ†ต ์—ด์ธ category_small_cd๋ฅผ ๊ธฐ์ค€์œผ๋กœ ๋‘ ํ…Œ์ด๋ธ”์„ ๊ฒฐํ•ฉํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค. ์ด๋Š” ๊ฒฐ๊ณผ ํ…Œ์ด๋ธ”์— category_small_cd ๊ฐ’์ด ๋‘ ํ…Œ์ด๋ธ”์—์„œ ๋™์ผํ•œ ํ–‰๋งŒ ์žˆ์Œ์„ ์˜๋ฏธํ•ฉ๋‹ˆ๋‹ค.

๋‘ ํ…Œ์ด๋ธ”์—์„œ ์„ ํƒํ•œ ์ปฌ๋Ÿผ์€ product_cd, category_major_cd, category_major_name, category_medium_cd, category_medium_name, category_small_cd, category_small_name, unit_price, unit_cost์ž…๋‹ˆ๋‹ค.

๊ฒฐ๊ณผ ํ…Œ์ด๋ธ” product_full์—๋Š” ๊ฒฐํ•ฉ ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” ๋ชจ๋“  ํ–‰์— ๋Œ€ํ•ด ์ด๋Ÿฌํ•œ ์ปฌ๋Ÿผ์ด ํฌํ•จ๋  ๊ฒƒ์ด๋‹ค.

LIMIT ์ ˆ์€ ์ถœ๋ ฅ์— ํ‘œ์‹œ๋˜๋Š” ํ–‰์˜ ์ˆ˜๋ฅผ 3๊ฐœ๋กœ ์ œํ•œํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค.

ย 

์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” product_full ํ…Œ์ด๋ธ”์˜ ๋‚ด์šฉ์„ "/tmp/data" ๋””๋ ‰ํ† ๋ฆฌ์— ์žˆ๋Š” "S_product_full_UTF-8_header.csv"๋ผ๋Š” ์ด๋ฆ„์˜ CSV ํŒŒ์ผ๋กœ ๋‚ด๋ณด๋‚ด๋Š” ์ฝ”๋“œ์ž…๋‹ˆ๋‹ค. ๋ช…๋ น์œผ๋กœ, ํŒŒ์ผ์ด๋‚˜ ํ…Œ์ด๋ธ” ๊ฐ„์— ๋ฐ์ดํ„ฐ๋ฅผ ๋ณต์‚ฌํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค. ์ด ๊ฒฝ์šฐ TO ํ‚ค์›Œ๋“œ๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๋ณต์‚ฌํ•  ํŒŒ์ผ์„ ์ง€์ •ํ•˜๊ณ , CSV ํ‚ค์›Œ๋“œ๋Š” ํŒŒ์ผ ํ˜•์‹์„ ์‰ผํ‘œ๋กœ ๊ตฌ๋ถ„๋œ ๊ฐ’์œผ๋กœ ์ง€์ •ํ•˜๋ฉฐ, HEADER ํ‚ค์›Œ๋“œ๋Š” ํŒŒ์ผ์˜ ์ฒซ ๋ฒˆ์งธ ํ–‰์— ์—ด ํ—ค๋”๋ฅผ ํฌํ•จํ•  ๊ฒƒ์„ ์ง€์ •ํ•œ๋‹ค. ๋งˆ์ง€๋ง‰์œผ๋กœ ENCODING ํ‚ค์›Œ๋“œ๋Š” ์ถœ๋ ฅ ํŒŒ์ผ์˜ ๋ฌธ์ž ์ธ์ฝ”๋”ฉ์„ UTF-8๋กœ ์ง€์ •ํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” PostgreSQL์˜ COPY ๋ช…๋ น์„ ์‚ฌ์šฉํ•˜์—ฌ product_full ํ…Œ์ด๋ธ”์˜ ๋‚ด์šฉ์„ S_product_full_SJIS_header.csv๋ผ๋Š” ์ด๋ฆ„์˜ CSV ํŒŒ์ผ๋กœ ๋‚ด๋ณด๋‚ด๋Š” ์ฝ”๋“œ์ž…๋‹ˆ๋‹ค. ์— ์—ด ์ด๋ฆ„์„ ํฌํ•จํ•œ ํ—ค๋” ํ–‰์„ ํฌํ•จํ•˜๋„๋ก ์ง€์ •ํ•˜๊ณ , ENCODING 'SJIS' ์˜ต์…˜์€ ์ถœ๋ ฅ ํŒŒ์ผ์ด Shift-JIS ๋ฌธ์ž ์ธ์ฝ”๋”ฉ์„ ์‚ฌ์šฉํ•˜์—ฌ ์ธ์ฝ”๋”ฉ๋˜๋„๋ก ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.

Shift-JIS๋Š” ์ผ๋ณธ์—์„œ ์ผ๋ฐ˜์ ์œผ๋กœ ์‚ฌ์šฉ๋˜๋Š” ๋ฌธ์ž ์ฝ”๋“œ์ด๋ฉฐ, CSV ํŒŒ์ผ์— ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋Š” ๋ช‡ ๊ฐ€์ง€ ๋ฌธ์ž ์ฝ”๋“œ ์ค‘ ํ•˜๋‚˜์ด๋ฉฐ, Shift-JIS ์ธ์ฝ”๋”ฉ์˜ CSV ํŒŒ์ผ๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๋‚ด๋ณด๋‚ด๋ฉด ์ด ํŠน์ • ์ธ์ฝ”๋”ฉ์ด ํ•„์š”ํ•œ ๋‹ค๋ฅธ ์‘์šฉ ํ”„๋กœ๊ทธ๋žจ์ด๋‚˜ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ํŒŒ์ผ์„ ์‰ฝ๊ฒŒ ๊ฐ€์ ธ์˜ฌ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” product_full ํ…Œ์ด๋ธ”์˜ ๋‚ด์šฉ์„ ์ถœ๋ ฅ ํŒŒ์ผ์— ํ—ค๋” ํ–‰์„ ํฌํ•จํ•˜์ง€ ์•Š๊ณ  /tmp/data/ ๋””๋ ‰ํ† ๋ฆฌ์— ์žˆ๋Š” S_product_full_UTF-8_no.csv๋ผ๋Š” CSV ํŒŒ์ผ๋กœ ๋‚ด๋ณด๋‚ด๊ณ  ์žˆ๋‹ค.

Postgres์—์„œ๋Š” COPY ๋ช…๋ น์„ ์‚ฌ์šฉํ•˜์—ฌ /ํŒŒ์ผ์—์„œ /ํŒŒ์ผ๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๋ณต์‚ฌํ•ฉ๋‹ˆ๋‹ค. WITH CSV ์˜ต์…˜์€ ํŒŒ์ผ์ด ์‰ผํ‘œ๋กœ ๊ตฌ๋ถ„๋œ ๊ฐ’(CSV) ํ˜•์‹์ž„์„ ์ง€์ •ํ•˜๊ณ , ENCODING ์˜ต์…˜์€ ํŒŒ์ผ์˜ ๋ฌธ์ž ์ธ์ฝ”๋”ฉ์„ ์ง€์ •ํ•˜๊ณ , UTF-8์€ ๋‹ค์–‘ํ•œ ์–ธ์–ด์˜ ๋ฌธ์ž๋ฅผ ์ง€์›ํ•˜๋Š” UTF-8์„ ์ง€์ •ํ•œ๋‹ค. ์˜ ๋ฌธ์ž๋ฅผ ๊ด‘๋ฒ”์œ„ํ•˜๊ฒŒ ์ง€์›ํ•˜๋Š” UTF-8 ์ธ์ฝ”๋”ฉ์„ ์‚ฌ์šฉํ•˜๋Š” ํŒŒ์ผ์ž„์„ ๋‚˜ํƒ€๋‚ธ๋‹ค.

HEADER ์˜ต์…˜์ด ์ง€์ •๋˜์ง€ ์•Š์•˜๊ธฐ ๋•Œ๋ฌธ์— ์ถœ๋ ฅ ํŒŒ์ผ์—๋Š” ํ—ค๋” ๋ผ์ธ์ด ํฌํ•จ๋˜์ง€ ์•Š๋Š”๋‹ค. ๋”ฐ๋ผ์„œ ์ถœ๋ ฅ ํŒŒ์ผ์—๋Š” product_full ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ ํ–‰๋งŒ CSV ํ˜•์‹์œผ๋กœ ํฌํ•จ๋œ๋‹ค.
ย 
์„ค๋ช…:

์ด SQL ์ฝ”๋“œ๋Š” product_full ํ…Œ์ด๋ธ”์ด ์กด์žฌํ•˜๋Š” ๊ฒฝ์šฐ ์ด๋ฅผ ์‚ญ์ œํ•˜๊ณ  ๊ฐ™์€ ์ด๋ฆ„์˜ ์ƒˆ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•œ๋‹ค. ์ƒˆ ํ…Œ์ด๋ธ”์—๋Š” product_cd, category_major_cd, category_major_name, category_medium_cd, category_medium_name, category_small_cd, category_small_name, category_small_name, category_small_name, it_price, it_cost์˜ small_name, it_price, it_cost์˜ 8๊ฐœ์˜ ์—ด์ด ์žˆ๋‹ค. ์ฒ˜์Œ 7๊ฐœ์˜ ์—ด์€ ๊ธธ์ด๊ฐ€ ๋‹ค๋ฅธ VARCHAR ํƒ€์ž…์ด๊ณ , ๋งˆ์ง€๋ง‰ 2๊ฐœ์˜ ์—ด์€ INTEGER ํƒ€์ž…์ด๋‹ค.

์ปฌ๋Ÿผ์˜ ๋ฐ์ดํ„ฐ ํƒ€์ž…๊ณผ ๊ธธ์ด๋ฅผ ์ง€์ •ํ•จ์œผ๋กœ์จ ์ฝ”๋“œ๋Š” product_full ํ…Œ์ด๋ธ”์˜ ์Šคํ‚ค๋งˆ๋ฅผ ์ •์˜ํ•˜๋Š” ๊ฒƒ์ด๋‹ค. ์ด๋ ‡๊ฒŒ ํ•˜๋ฉด ๊ฐ ์ปฌ๋Ÿผ์ด ํŠน์ • ๋ฐ์ดํ„ฐ ์œ ํ˜•๊ณผ ๊ธธ์ด๋ฅผ ๊ฐ€์ง€๊ฒŒ ๋˜์–ด ์ด ํ…Œ์ด๋ธ”์— ๋Œ€ํ•œ ์ฟผ๋ฆฌ ์„ฑ๋Šฅ์ด ํ–ฅ์ƒ๋˜๊ณ , ์ž˜๋ชป๋œ ์œ ํ˜•๊ณผ ๊ธธ์ด์˜ ๋ฐ์ดํ„ฐ๊ฐ€ ์‚ฝ์ž…๋˜์—ˆ์„ ๋•Œ ๋ฐœ์ƒํ•  ์ˆ˜ ์žˆ๋Š” ์˜ค๋ฅ˜๋ฅผ ๋ฐฉ์ง€ํ•  ์ˆ˜ ์žˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” '/tmp/data/S_product_full_UTF-8_header.csv'์— ์žˆ๋Š” CSV ํŒŒ์ผ์—์„œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ์ด๋ฏธ ์ƒ์„ฑ๋œ 'product_full'์ด๋ผ๋Š” ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜จ๋‹ค.

PostgreSQL์˜ COPY ๋ช…๋ น์€ ํŒŒ์ผ์—์„œ ํ…Œ์ด๋ธ”๋กœ ๋˜๋Š” ๊ทธ ๋ฐ˜๋Œ€๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๋ณต์‚ฌํ•  ์ˆ˜ ์žˆ๋Š”๋ฐ, FROM ํ‚ค์›Œ๋“œ๋Š” CSV ํŒŒ์ผ์˜ ๊ฒฝ๋กœ๋ฅผ ์ง€์ •ํ•˜๊ณ , WITH ํ‚ค์›Œ๋“œ๋Š” COPY ๋ช…๋ น์˜ ์˜ต์…˜์„ ์ง€์ •ํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค. ์ด ๊ฒฝ์šฐ CSV๋Š” ํŒŒ์ผ์ด ์‰ผํ‘œ๋กœ ๊ตฌ๋ถ„๋œ ๊ฐ’(Comma-Separated Value) ํ˜•์‹์ž„์„, HEADER๋Š” ํŒŒ์ผ์˜ ์ฒซ ๋ฒˆ์งธ ํ–‰์ด ์—ด ์ด๋ฆ„์„ ํฌํ•จํ•˜๊ณ  ์žˆ์Œ์„, ENCODING 'UTF-8'์€ ํŒŒ์ผ์—์„œ ์‚ฌ์šฉ๋˜๋Š” ๋ฌธ์ž ์ธ์ฝ”๋”ฉ์„ ์ง€์ •ํ•˜๋Š” ๊ฒƒ์„ ๊ฐ๊ฐ ์ง€์ •ํ•œ๋‹ค.

๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ์ด๋ฏธ 'product_full' ํ…Œ์ด๋ธ”์ด ์ƒ์„ฑ๋˜์–ด ์žˆ์œผ๋ฏ€๋กœ, COPY ๋ช…๋ น์€ CSV ํŒŒ์ผ์˜ ๋ฐ์ดํ„ฐ๋ฅผ ๊ธฐ์กด ํ…Œ์ด๋ธ”์— ์‚ฝ์ž…ํ•œ๋‹ค.
ย 
์„ค๋ช…:

์ด SQL ์ฝ”๋“œ๋Š” product_full ํ…Œ์ด๋ธ”์—์„œ ์ฒ˜์Œ ์„ธ ์ค„์˜ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜ค๋Š” ๊ฐ„๋‹จํ•œ SELECT ๋ฌธ์ด๋‹ค. ๋ฐ์ดํ„ฐ๋Š” ์ด์ „์— CSV ํŒŒ์ผ์—์„œ COPY ๋ช…๋ น์„ ์‚ฌ์šฉํ•˜์—ฌ ํ…Œ์ด๋ธ”์— ๋กœ๋“œ๋˜์—ˆ์Šต๋‹ˆ๋‹ค. ์ด ์ฟผ๋ฆฌ๋Š” LIMIT ์ ˆ์„ ์‚ฌ์šฉํ•˜์—ฌ ๋ฐ˜ํ™˜๋˜๋Š” ํ–‰ ์ˆ˜๋ฅผ 3ํ–‰์œผ๋กœ ์ œํ•œํ•˜๊ณ  ์žˆ๋‹ค.

SELECT ๋ฌธ์€ product_full ํ…Œ์ด๋ธ”์—์„œ ๋ชจ๋“  ์—ด์„ ๊ฐ€์ ธ์˜ต๋‹ˆ๋‹ค. ์ด ํ…Œ์ด๋ธ”์—๋Š” ์ฝ”๋“œ, ์นดํ…Œ๊ณ ๋ฆฌ, ๊ฐ€๊ฒฉ ๋“ฑ ์ƒํ’ˆ์— ๋Œ€ํ•œ ์ •๋ณด๊ฐ€ ํฌํ•จ๋˜์–ด ์žˆ๋‹ค. ๋ฐ์ดํ„ฐ๋Š” product_cd, category_major_cd, category_major_name, category_medium_cd, category_medium_name, category_small_cd, category_small_cd, category_small_name, it_price name, it_price, it_cost ๋“ฑ์˜ ์—ด๋กœ ์ •๋ฆฌํ•˜๊ณ  ์žˆ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” product_full์ด๋ผ๋Š” ์ด๋ฆ„์˜ ํ…Œ์ด๋ธ”์ด ์กด์žฌํ•˜๋Š” ๊ฒฝ์šฐ ์ด๋ฅผ ์‚ญ์ œํ•˜๊ณ  ๋™์ผํ•œ ์ด๋ฆ„๊ณผ ์Šคํ‚ค๋งˆ๋ฅผ ๊ฐ€์ง„ ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•œ๋‹ค. ์ƒˆ ํ…Œ์ด๋ธ”์—๋Š” 9๊ฐœ์˜ ์ปฌ๋Ÿผ์ด ์žˆ์Šต๋‹ˆ๋‹ค.

product_cd ์ปฌ๋Ÿผ์€ VARCHAR(10) ํƒ€์ž…์ด๋‹ค.
category_major_cd ์ปฌ๋Ÿผ์€ VARCHAR(2) ํƒ€์ž…์ด๋‹ค.
category_major_name ์ปฌ๋Ÿผ์€ VARCHAR(20) ํƒ€์ž…์ด๋‹ค.
category_medium_cd ์นผ๋Ÿผ์€ VARCHAR(4) ํƒ€์ž…์ด๋‹ค.
category_medium_name ์นผ๋Ÿผ์€ VARCHAR(20) ํƒ€์ž…์ด๋‹ค.
category_small_cd ์นผ๋Ÿผ์€ VARCHAR(6) ํƒ€์ž…์ด๋‹ค.
category_small_name ์นผ๋Ÿผ์€ VARCHAR(20) ํƒ€์ž…์ด๋‹ค.
unit_price ์ปฌ๋Ÿผ์€ INTEGER ํƒ€์ž…์ด๋‹ค.
unit_cost ์ปฌ๋Ÿผ์€ INTEGER ํƒ€์ž…์ด๋‹ค.

VARCHAR ๋ฐ์ดํ„ฐ ํƒ€์ž…์€ ์ง€์ •๋œ ์ตœ๋Œ€ ๊ธธ์ด์˜ ๊ฐ€๋ณ€ ๊ธธ์ด ๋ฌธ์ž์—ด์„ ์ €์žฅํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋˜๋ฉฐ, INTEGER ๋ฐ์ดํ„ฐ ํƒ€์ž…์€ ์ •์ˆ˜๋ฅผ ์ €์žฅํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

์ด ์ฝ”๋“œ๋Š” ์ง€์ •๋œ ์Šคํ‚ค๋งˆ๋กœ ๋นˆ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๊ณ  ๋ฐ์ดํ„ฐ๋ฅผ ํ…Œ์ด๋ธ”์— ์ถ”๊ฐ€ํ•˜์ง€ ์•Š๋Š”๋‹ค.

ย 

์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” PostgreSQL์˜ COPY ๋ช…๋ น์„ ์‚ฌ์šฉํ•˜์—ฌ 'S_product_full_UTF-8_no.csv'๋ผ๋Š” CSV ํŒŒ์ผ์—์„œ 'product_full'์ด๋ผ๋Š” ํ…Œ์ด๋ธ”๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜จ๋‹ค.

WITH CSV ์˜ต์…˜์€ ๋ฐ์ดํ„ฐ๊ฐ€ CSV ํ˜•์‹์ž„์„ ์ง€์ •ํ•˜๊ณ , ENCODING ์˜ต์…˜์€ ํŒŒ์ผ์˜ ๋ฌธ์ž ์ธ์ฝ”๋”ฉ(์ด ๊ฒฝ์šฐ UTF-8)์„ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.

ํŒŒ์ผ์—๋Š” ํ—ค๋” ํ–‰์ด ์—†์œผ๋ฏ€๋กœ 'product_full' ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๊ธฐ ์ „์— SQL ๋ฌธ์—์„œ ์—ด ์ด๋ฆ„๊ณผ ์œ ํ˜•์„ ๋ช…์‹œ์ ์œผ๋กœ ์ง€์ •ํ•ด์•ผ ํ•œ๋‹ค.

๋ฐ์ดํ„ฐ๊ฐ€ ํ…Œ์ด๋ธ”๋กœ ์ž„ํฌํŠธ๋˜๋ฉด SELECT ๋ฌธ์œผ๋กœ 'product_full' ํ…Œ์ด๋ธ”์˜ ์ฒซ ์„ธ ํ–‰์„ ๊ฐ€์ ธ์™€์„œ ๋ฐ์ดํ„ฐ๊ฐ€ ์ œ๋Œ€๋กœ ์ž„ํฌํŠธ๋˜์—ˆ๋Š”์ง€ ํ™•์ธํ•œ๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” SQL์˜ SELECT ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ "product_full" ํ…Œ์ด๋ธ”์—์„œ ์ฒ˜์Œ 3๊ฐœ์˜ ํ–‰์„ ์„ ํƒํ•˜๋ฉฐ, SELECT ๋ฌธ์€ "product_full" ํ…Œ์ด๋ธ”์—์„œ ๋ชจ๋“  ์—ด(product_cd, category_major_cd, category_major_name, category_medium_cd, category_small_cd, category_small_name, unit_price, unit_cost)์„ ์ถ”์ถœํ•˜์—ฌ LIMIT ํ‚ค์›Œ๋“œ๋กœ ์ถœ๋ ฅ์„ ์ถœ๋ ฅํ•œ๋‹ค. major_name, category_medium_cd, category_medium_name, category_small_cd, category_small_name, unit_price, unit_cost)๋ฅผ ๋ชจ๋‘ ๊ฐ€์ ธ์˜ค๊ณ  LIMIT ํ‚ค์›Œ๋“œ๋กœ ์ถœ๋ ฅ์„ ์ฒ˜์Œ ์„ธ ์ค„๋กœ ์ œํ•œํ•ฉ๋‹ˆ๋‹ค. ๊ฒฐ๊ณผ๋Š” "product_full" ํ…Œ์ด๋ธ”์˜ ์ฒซ ์„ธ ํ–‰์„ ํ‘œ์‹œํ•ฉ๋‹ˆ๋‹ค.
ย 
์„ค๋ช…: 

์ œํ’ˆ product_full ํ…Œ์ด๋ธ”์˜ ๋‚ด์šฉ์„ ์‰ผํ‘œ ๋Œ€์‹  ํƒญ(หถ~~~~)์„ ๊ตฌ๋ถ„ ๊ธฐํ˜ธ๋กœ ์‚ฌ์šฉํ•˜์—ฌ CSV ํ˜•์‹์œผ๋กœ ๋‚ด๋ณด๋‚ด๊ธฐ ํ•ฉ๋‹ˆ๋‹ค. ๋‚ด๋ณด๋‚ธ ํŒŒ์ผ์€ /tmp/data ๋””๋ ‰ํ† ๋ฆฌ์— ์ €์žฅ๋˜๋ฉฐ, S_product_full_UTF-8_header.tsv๋ผ๋Š” ์ด๋ฆ„์œผ๋กœ ์ €์žฅ๋˜๋ฉฐ, HEADER ์˜ต์…˜์ด ํฌํ•จ๋˜์–ด ์žˆ๊ธฐ ๋•Œ๋ฌธ์— ํŒŒ์ผ์˜ ์ฒซ ๋ฒˆ์งธ ์ค„์— ์ปฌ๋Ÿผ ํ—ค๋”๊ฐ€ ํฌํ•จ๋˜์–ด ์žˆ์Šต๋‹ˆ๋‹ค.

๋‹ค์Œ์€ ์ฝ”๋“œ์˜ ๊ฐœ๋ณ„ ์ปดํฌ๋„ŒํŠธ ๋ถ„์„์ž…๋‹ˆ๋‹ค.

COPY product_full: product_full ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๋ฅผ ํŒŒ์ผ๋กœ ๋ณต์‚ฌํ•  ๊ฒƒ์„ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.

TO '/tmp/data/S_product_full_UTF-8_header.tsv': ๋‚ด๋ณด๋‚ผ ํŒŒ์ผ์˜ ๊ฒฝ๋กœ์™€ ์ด๋ฆ„์„ ์ง€์ •ํ•œ๋‹ค.

WITH CSV: ๋‚ด๋ณด๋‚ผ ํŒŒ์ผ์ด CSV ํ˜•์‹์ž„์„ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.
HEADER: ํŒŒ์ผ์˜ ์ฒซ ๋ฒˆ์งธ ์ค„์— ์ปฌ๋Ÿผ ํ—ค๋”๋ฅผ ํฌํ•จํ•˜๋„๋ก ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.
DELIMITER E' \t': ํƒญ ๋ฌธ์ž๋ฅผ ๊ตฌ๋ถ„ ๊ธฐํ˜ธ๋กœ ์‚ฌ์šฉํ•˜๋„๋ก ์ง€์ •ํ•œ๋‹ค.
ENCODING 'UTF-8': ํŒŒ์ผ์„ ๋‚ด๋ณด๋‚ผ ๋•Œ ์‚ฌ์šฉํ•  ๋ฌธ์ž ์ธ์ฝ”๋”ฉ์„ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.

ย 

์„ค๋ช…:

์ด SQL ์ฝ”๋“œ ๋ธ”๋ก์€ ๋จผ์ € product_full์ด๋ผ๋Š” ํ…Œ์ด๋ธ”์ด ์กด์žฌํ•  ๊ฒฝ์šฐ ์ด๋ฅผ ์‚ญ์ œํ•˜๊ณ  ๋™์ผํ•œ ์ด๋ฆ„๊ณผ ์Šคํ‚ค๋งˆ๋ฅผ ๊ฐ€์ง„ ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•œ๋‹ค.

product_full ํ…Œ์ด๋ธ”์˜ ์Šคํ‚ค๋งˆ์—๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋ฐ์ดํ„ฐ ํƒ€์ž…์„ ๊ฐ€์ง„ 9๊ฐœ์˜ ์ปฌ๋Ÿผ์ด ์žˆ๋‹ค.

product_cd: VARCHAR(10) (์ฆ‰, ์ตœ๋Œ€ ๊ธธ์ด 10๊นŒ์ง€ ๊ฐ€๋ณ€ ๊ธธ์ด์˜ ๋ฌธ์ž์—ด)
category_major_cd: VARCHAR(2)
category_major_name: VARCHAR(20)
category_medium_cd.VARCHAR(4)
category_medium_name: VARCHAR(20)
category_small_cd์ž…๋‹ˆ๋‹ค. VARCHAR(6)
category_small_name: VARCHAR(20)
unit_price์ž…๋‹ˆ๋‹ค. INTEGER
unit_cost: INTEGER

์ด ์ฝ”๋“œ ๋ธ”๋ก์˜ ๋ชฉ์ ์€ ๋ฐ์ดํ„ฐ๋ฅผ ๋ถˆ๋Ÿฌ์˜ค๊ธฐ ์ „์— product_full ํ…Œ์ด๋ธ”์˜ ๊ตฌ์กฐ๋ฅผ ์ •์˜ํ•˜๋Š” ๊ฒƒ์ด๋‹ค.
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” '/tmp/data/S_product_full_UTF-8_header.tsv'์— ์žˆ๋Š” TSV(ํƒญ์œผ๋กœ ๊ตฌ๋ถ„๋œ ๊ฐ’) ํŒŒ์ผ์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ๋ณต์‚ฌํ•˜์—ฌ ํ˜„์žฌ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ 'product_full'์— ์‚ฝ์ž…ํ•œ๋‹ค.

COPY ๋ช…๋ น์€ ์ž…๋ ฅ ํŒŒ์ผ์ด CSV ํŒŒ์ผ์ด๊ณ , ํ—ค๋”๊ฐ€ ํŒŒ์ผ์˜ ์ฒซ ๋ฒˆ์งธ ์ค„์— ํฌํ•จ๋˜์–ด ์žˆ์œผ๋ฉฐ, ์‚ฌ์šฉ๋˜๋Š” ๊ตฌ๋ถ„ ๊ธฐํ˜ธ๊ฐ€ ํƒญ ๋ฌธ์ž('หถ')์ž„์„ ์ง€์ •ํ•˜๊ณ , ENCODING ๋งค๊ฐœ ๋ณ€์ˆ˜๋Š” ํŒŒ์ผ์ด UTF-8๋กœ ์ธ์ฝ”๋”ฉ๋˜์—ˆ์Œ์„ ์ง€์ •ํ•œ๋‹ค.

์ด ๋ช…๋ น์€ 'product_full' ํ…Œ์ด๋ธ”์ด ์ด๋ฏธ ์กด์žฌํ•˜๊ณ , ์ž…๋ ฅ ํŒŒ์ผ์˜ ๋ฐ์ดํ„ฐ(product_cd, category_major_cd, category_major_name, category_medium_cd, category_medium_cd, category_medium_ name, category_small_cd, category_small_name, unit_price, unit_cost)์™€ ๋™์ผํ•œ ๊ตฌ์กฐ๋ฅผ ๊ฐ€์ง€๊ณ  ์žˆ๋‹ค๊ณ  ๊ฐ€์ •ํ•œ๋‹ค. ์ด ๋ช…๋ น์€ ํŒŒ์ผ์˜ ๋ฐ์ดํ„ฐ๋ฅผ ํ…Œ์ด๋ธ”์— ์‚ฝ์ž…ํ•˜๊ณ  ํŒŒ์ผ ๋ฐ์ดํ„ฐ์˜ ๊ฐ ํ–‰์— ๋Œ€ํ•ด ํ…Œ์ด๋ธ”์— ์ƒˆ ํ–‰์„ ์ƒ์„ฑํ•œ๋‹ค.
ย 
ย 
์„ค๋ช…:

์ด ์ฝ”๋“œ๋Š” product_full ํ…Œ์ด๋ธ”์—์„œ ์ฒ˜์Œ 3๊ฐœ ํ–‰์„ ์„ ํƒํ•˜๋Š” SQL ์ฟผ๋ฆฌ๋ฅผ ์‹คํ–‰ํ•˜๋ฉฐ, SELECT ๋ฌธ ๋’ค์— *๋ฅผ ๋ถ™์˜€๋Š”๋ฐ, ์ด๋Š” ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ์—ด์„ ์„ ํƒํ•œ๋‹ค๋Š” ์˜๋ฏธ์ด๋ฉฐ, LIMIT 3์€ ๊ฒฐ๊ณผ ์ง‘ํ•ฉ์„ ํ…Œ์ด๋ธ”์˜ ์ฒ˜์Œ 3๊ฐœ ํ–‰์œผ๋กœ ์ œํ•œํ•œ๋‹ค.

๊ฒฐ๊ณผ๋Š” ํ…Œ์ด๋ธ”์— ์ง€์ •๋œ ์—ด์— ๋”ฐ๋ผ product_full ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๋ฅผ ํ‘œ์‹œํ•œ๋‹ค. ์ •ํ™•ํ•œ ์ถœ๋ ฅ์€ ํ…Œ์ด๋ธ”์— ์ €์žฅ๋œ ๋ฐ์ดํ„ฐ์— ๋”ฐ๋ผ ๋‹ฌ๋ผ์ง‘๋‹ˆ๋‹ค.
ย 

Comment