๊ด€๋ฆฌ ๋ฉ”๋‰ด

gi_dor

๐Ÿ”‘ SQL ํŠœ๋‹ - CH 4. ์กฐ์ธ ํŠœ๋‹ - ์„œ๋ธŒ์ฟผ๋ฆฌ ์กฐ์ธ ๋ณธ๋ฌธ

First/SQL ํŠœ๋‹

๐Ÿ”‘ SQL ํŠœ๋‹ - CH 4. ์กฐ์ธ ํŠœ๋‹ - ์„œ๋ธŒ์ฟผ๋ฆฌ ์กฐ์ธ

๊ธฐ๋Œ 2023. 8. 21. 10:42
728x90

 

๐Ÿ˜ ์„œ๋ธŒ์ฟผ๋ฆฌ ์กฐ์ธ

1 - 1 ์„œ๋ธŒ์ฟผ๋ฆฌ ๋ณ€ํ™˜์ด ํ•„์š”ํ•œ ์ด์œ 

์˜ตํ‹ฐ๋งˆ์ด์ €๋Š” ๋น„์šฉ์„ ํ‰๊ฐ€ํ•˜๊ณ  ์‹คํ–‰๊ณ„ํš์„ ์ƒ์„ฑํ•˜๊ธฐ์— ์•ž์„œ ์‚ฌ์šฉ์ž๋กœ๋ถ€ํ„ฐ
์ „๋‹ฌ๋ฐ›์€ SQL์„ ์ตœ์ ํ™”์— ์œ ๋ฆฌํ•œ ํ˜•ํƒœ๋กœ ๋ณ€ํ™˜ํ•˜๋Š” ์ž‘์—…

์ฟผ๋ฆฌ๋ณ€ํ™˜๋ถ€ํ„ฐ ์ง„ํ–‰ํ•œ๋‹ค

 

์˜ตํ‹ฐ๋งˆ์ด์ € ์—”์ง„์€ ๋ฌด๊ฑฐ์›Œ์ง€๊ณ  ์ตœ์ ํ™”์— ์†Œ์š”๋˜๋Š” ์‹œ๊ฐ„๋„ ์ ์  ๋Š˜์–ด๋‚˜๊ณ  ์žˆ๋‹ค

 

์ฟผ๋ฆฌ๋ณ€ํ™˜์€ ์˜ตํ‹ฐ๋งˆ์ด์ €๊ฐ€ SQL์„ ๋ถ„์„ํ•ด ๋™์ผํ•˜๋ฉด์„œ  ๋” ๋‚˜์€ ์„ฑ๋Šฅ์ด ๊ธฐ๋Œ€๋˜๋Š” ํ˜•ํƒœ๋กœ ์žฌ์ž‘์„ฑ์„ ๋งํ•œ๋‹ค

 

์„œ๋ธŒ์ฟผ๋ฆฌ๋Š” ํ•˜๋‚˜์˜ SQL๋ฌธ ์•ˆ์— ๊ด„ํ˜ธ๋กœ ๋ฌถ์€ ๋ณ„๋„์˜ ์ฟผ๋ฆฌ ๋ธ”๋ก์„ ๋งํ•œ๋‹ค.
DBMS๋งˆ๋‹ค ์กฐ๊ธˆ์”ฉ ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ๋‹ค๋ฅด๊ฒŒ ๋ถ„๋ฅ˜ํ•˜๋Š”๋ฐ ํ•˜๋‹จ ๊ทธ๋ฆผ์—์žˆ๋Š” ์˜ค๋ผํด์€ 3๊ฐ€์ง€๋กœ ๋ถ„๋ฅ˜ํ•œ๋‹ค

select  c.๊ณ ๊ฐ๋ฒˆํ˜ธ, c.๊ณ ๊ฐ๋ช…, t.ํ‰๊ท ๊ฑฐ๋ž˜, t.์ตœ์†Œ๊ฑฐ๋ž˜, t.์ตœ๋Œ€๊ฑฐ๋ž˜
   , (select ๊ณ ๊ฐ๋ถ„๋ฅ˜๋ช… from ๊ณ ๊ฐ๋ถ„๋ฅ˜ where ๊ณ ๊ฐ๋ถ„๋ฅ˜์ฝ”๋“œ = c.๊ณ ๊ฐ๋ถ„๋ฅ˜์ฝ”๋“œ)  /* ์Šค์นผ๋ผ ์„œ๋ธŒ์ฟผ๋ฆฌ */
      
from    ๊ณ ๊ฐ c , (select ๊ณ ๊ฐ๋ฒˆํ˜ธ, avg(๊ฑฐ๋ž˜๊ธˆ์•ก) ํ‰๊ท ๊ฑฐ๋ž˜, min(๊ฑฐ๋ž˜๊ธˆ์•ก) ์ตœ์†Œ๊ฑฐ๋ž˜, max(๊ฑฐ๋ž˜๊ธˆ์•ก) ์ตœ๋Œ€๊ฑฐ๋ž˜
                  from ๊ฑฐ๋ž˜					/* ์ธ๋ผ์ธ ๋ทฐ */
                  where ๊ฑฐ๋ž˜์ผ์‹œ >= trunc(sysdate, 'mm')
                  group by ๊ณ ๊ฐ๋ฒˆํ˜ธ) t                                  
      
where c.๊ฐ€์ž…์ผ์‹œ >= trunc(add_months(sysdate, -1), 'mm')
and   t.๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
and   exist ( select 'x'                                            /* ์ค‘์ฒฉ๋œ ์„œ๋ธŒ์ฟผ๋ฆฌ */
              from   ๊ณ ๊ฐ๋ณ€๊ฒฝ์ด๋ ฅ h
              where  h.๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
              and    h.๋ณ€๊ฒฝ์‚ฌ์œ ์ฝ”๋“œ = 'ZCH'
              and    c.์ตœ์ข…๋ณ€๊ฒฝ์ผ์‹œ between h.์‹œ์ž‘์ผ์‹œ and h.์ข…๋ฃŒ์ผ์‹œ );

 

1. ์ธ๋ผ์ธ ๋ทฐ : FROM ์ ˆ์— ์‚ฌ์šฉํ•œ ์„œ๋ธŒ์ฟผ๋ฆฌ

2. ์ค‘์ฒฉ๋œ ์„œ๋ธŒ์ฟผ๋ฆฌ : ๊ฒฐ๊ณผ์ง‘ํ•ฉ์„ ํ•œ์ •ํ•˜๊ธฐ ์œ„ํ•œ WHERE ์ ˆ์— ์‚ฌ์šฉํ•œ ์„œ๋ธŒ์ฟผ๋ฆฌ
์„œ๋ธŒ์ฟผ๋ฆฌ๊ฐ€ ๋ฉ”์ธ์ฟผ๋ฆฌ ์ปฌ๋Ÿผ์„ ์ฐธ์กฐํ•˜๋Š” ํ˜•ํƒœ๋ฅผ '์ƒ๊ด€๊ด€๊ณ„ ์žˆ๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ' ๋ผ๊ณ  ๋งํ•œ๋‹ค.

3. ์Šค์นผ๋ผ ์„œ๋ธŒ์ฟผ๋ฆฌ : ํ•œ ๋ ˆ์ฝ”๋“œ๋‹น ํ•˜๋‚˜์˜ ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•˜๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ
SELECT-LIST์—์„œ ์‚ฌ์šฉํ•˜์ง€๋งŒ ์˜ˆ์™ธ์‚ฌํ•ญ์„ ์ œ์™ธํ•˜๋ฉด ์ปฌ๋Ÿผ์ด ์˜ฌ์ˆ˜์žˆ๋Š” ๋Œ€๋ถ€๋ถ„ ์œ„์น˜์— ์‚ฌ์šฉ ํ• ์ˆ˜ ์žˆ๋‹ค.

 


 

1 - 2 ์„œ๋ธŒ์ฟผ๋ฆฌ์™€ ์กฐ์ธ

 

ํ•„ํผ ์˜คํผ๋ ˆ์ด์…˜

์„œ๋ธŒ์ฟผ๋ฆฌ๋Š” ๋‹จ๋…์œผ๋กœ ์‹คํ–‰ํ•  ์ˆ˜ ์—†๊ธฐ ๋•Œ๋ฌธ์— ํ•„ํ„ฐ๋ฅผ ์‚ฌ์šฉํ•ด ๋ฉ”์ธ์ฟผ๋ ˆ์ด์„œ ๊ฐ’์„ ๋ฐ›์•„ ์‹คํ–‰ํ•ด์•ผ ํ•œ๋‹ค

  • ๋ฉ”์ธ์ฟผ๋ฆฌ์˜ ํ•œ ๋กœ์šฐ๊ฐ€ ์„œ๋ธŒ์ฟผ๋ฆฌ์˜ ํ•œ ๋กœ์šฐ์™€ ์กฐ์ธ์— ์„ฑ๊ณตํ•˜๋Š” ์ˆœ๊ฐ„ ์ง„ํ–‰์„ ๋ฉˆ์ถ”๊ณ  ๋ฉ”์ธ์ฟผ๋ฆฌ์˜ ๋‹ค์Œ ๋กœ์šฐ๋ฅผ ์ฒ˜๋ฆฌํ•œ๋‹ค
  • ํ•„ํ„ฐ๋Š” ์บ์‹ฑ๊ธฐ๋Šฅ์„ ๊ฐ–๋Š”๋‹ค
์บ์‹œ - ์ž์ฃผ ํ•„์š”ํ•œ ๋ฐ์ดํ„ฐ๋‚˜ ๊ฐ’์˜ ๋ณต์‚ฌ๋ณธ์„ ์ผ์‹œ์ ์œผ๋กœ ์ €์žฅ ํ•˜๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉํ•˜๋Š” ๊ณต๊ฐ„

์บ์‹ฑ - ์บ์‹œ + ing  ์บ์‹œ๋ฅผ ์‚ฌ์šฉํ•˜๋Š”๊ฒƒ

 

์„œ๋ธŒ์ฟผ๋ฆฌ Unnesting

'nest' ,  "์ƒ์ž๋“ฑ์„ ์ฐจ๊ณก์ฐจ๊ณก ํฌ๊ฐœ๋„ฃ๋‹ค"
๋ฐ˜๋Œ€์˜ ์˜๋ฏธ์ธ 'un'์„ ํ•˜์šฉํ•ด unnest๋Š” "์ค‘์ฒฉ๋œ ์ƒํƒœ๋ฅผ ํ’€์–ด๋‚ด๋ผ"

์„œ๋ธŒ์ฟผ๋ฆฌ Unnesting์€ ๋ฉ”์ธ๊ณผ ์„œ๋ธŒ์ฟผ๋ฆฌ๊ฐ„์˜ ๊ณ„์ธต๊ตฌ์กฐ๋ฅผ ํ’€์–ด ์„œ๋กœ ๊ฐ™์€ ๋ ˆ๋ฒจ๋กœ ๋งŒ๋“ค์–ด์ค€๋‹ค

์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ๊ทธ๋ž˜๋„ ๋‘๋ฉด ํ•„ํ„ฐ ๋ฐฉ์‹์„ ์‚ฌ์šฉ ํ• ์ˆ˜ ๋ฐ–์— ์—†์ง€๋งŒ
Unnestring ํ•˜๊ณ  ๋‚˜๋ฉด ์ผ๋ฐ˜ JOIN๋ฌธ ์ฒ˜๋Ÿผ ๋‹ค์–‘ํ•œ ์ตœ์ ํ™” ๊ธฐ๋ฒ•์„ ์‚ฌ์šฉ ํ• ์ˆ˜ ์žˆ๋‹ค

select c.๊ณ ๊ฐ๋ฒˆํ˜ธ, c.๊ณ ๊ฐ๋ช…
from   ๊ณ ๊ฐ c
where  c.๊ฐ€์ž…์ผ์‹œ >= trunc(add_months(sysdate, -1), 'mm')
and    exist (
         select /*+ unnest nl_sj */ 'x'
         from ๊ฑฐ๋ž˜
         where ๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
         and ๊ฑฐ๋ž˜์ผ์‹œ >= trunc(sysdate, 'mm'));

์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ Unnestingํ•˜์ง€ ์•Š๊ณ  ์›๋ž˜๋Œ€๋กœ ๋‘” ์ƒํƒœ์—์„œ ์ตœ์ ํ™” ํ•œ๋‹ค๋ฉด

๋ฉ”์ธ์ฟผ๋ฆฌ์™€ ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ๋ณ„๋„์˜ ์„œ๋ธŒํ”Œ๋žœ์œผ๋กœ ๊ตฌ๋ถ„ํ•ด ๊ฐ๊ฐ ์ตœ์ ํ™”๋ฅผ ์ˆ˜ํ–‰ํ•˜๋ฉฐ, 

์ด๋•Œ ์„œ๋ธŒ์ฟผ๋ฆฌ์— ํ•„ํ„ฐ ์˜คํผ๋ ˆ์ด์…˜์ด ๋‚˜ํƒ€๋‚œ๋‹ค. 


 

1 - 3 ์Šค์นผ๋ผ ์„œ๋ธŒ์ฟผ๋ฆฌ ์บ์‹ฑ

์Šค์นผ๋ผ ์„œ๋ธŒ์ฟผ๋ฆฌ๋กœ ์กฐ์ธํ•˜๋ฉด ์˜ค๋ผํด์€ ์กฐ์ธํšŸ์ˆ˜๋ฅผ ์ตœ์†Œํ™” ํ•˜๋ ค๊ณ  ์ž…๋ ฅ ๊ฐ’๊ณผ ์ถœ๋ ฅ ๊ฐ’์„ ๋‚ด๋ถ€ ์บ์‹œ์— ์ €์žฅํ•ด๋‘”๋‹ค.

โ—พ ์กฐ์ธ ์„ฑ๋Šฅ์„ ๋†’์ด๋Š”๋ฐ ํฐ ๋„์›€์ด ๋˜๋Š”๋ฐ ,๋ฉ”์ธ์ฟผ๋ฆฌ ์ง‘ํ•ฉ์ด ์•„๋ฌด๋ฆฌ์ปค๋„ ์กฐ์ธํ•  ๋ฐ์ดํ„ฐ๋ฅผ ๋Œ€๋ถ€๋ถ„ ์บ์‹œ์— ์ฐพ๋Š”๋‹ค๋ฉด
   ์กฐ์ธ ์ˆ˜ํ–‰ํšŸ์ˆ˜๋ฅผ ์ตœ์†Œํ™” ํ• ์ˆ˜ ์žˆ๋‹ค

โ—พ ์Šค์นผ๋ผ ์„œ๋ธŒ์ฟผ๋ฆฌ ์บ์‹ฑํšจ๊ณผ๋Š” ์ž…๋ ฅ ๊ฐ’์˜ ์ข…๋ฅ˜๊ฐ€ ์†Œ์ˆ˜์—ฌ์„œ ํ•ด์‹œ ์ถฉ๋Œ ๊ฐ€๋Šฅ์„ฑ์ด ์ž‘์„๋•Œ ํšจ๊ณผ๊ฐ€ ์žˆ๋‹ค.

โ—พ ๋‹จ์ ์œผ๋กœ ์Šค์นผ๋ผ ์„œ๋ธŒ์ฟผ๋ฆฌ์— ์‚ฌ์šฉ ํ•˜๋Š” ์บ์‹œ๋„ ๋ฉ”๋ชจ๋ฆฌ ๊ณต๊ฐ„์ด์–ด์„œ ์บ์‹œ ๊ณต๊ฐ„์ด ๋Š˜ ๋ถ€์กฑํ•˜๊ณ  ์Œ“์ด๋ฉด ์„ฑ๋Šฅ์ด ๋‚˜๋น ์ง„๋‹ค.

728x90