ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • [SQL] ์ง‘๊ณ„ ํ•จ์ˆ˜๋ฅผ ์“ฐ๊ธฐ ์–ด๋ ค์šธ ๋•Œ over()์™€ ์„œ๋ธŒ ์ฟผ๋ฆฌ ์ค‘ ๋ญ๋ฅผ ์‚ฌ์šฉํ•ด์•ผ ํ• ๊นŒ?
    Computer Science/Database 2023. 10. 13. 17:52
    728x90

    ๐ŸŽˆ์‹œ์ž‘

    MySQL์—์„œ world database๋ฅผ ์‚ฌ์šฉํ•ด ์ฟผ๋ฆฌ๋ฅผ ๋šฑ๋•…๊ฑฐ๋ฆฌ๋˜ ์ค‘ ๋‹ค์Œ๊ณผ ๊ฐ™์€ ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•ด์•ผํ•˜๋Š” ๊ฒฝ์šฐ๊ฐ€ ์žˆ์—ˆ๋‹ค.

    ์–ด๋–ค ๋ฐ์ดํ„ฐ์— ๋Œ€ํ•œ ์กฐํšŒ๋ฅผ ํ•ด์•ผํ•  ๋•Œ ๊ฐ ํŠœํ”Œ์˜ ๋ฐ์ดํ„ฐ๋ฅผ ํ™•์ธํ•˜๋ฉด์„œ ํŠœํ”Œ์ด ์†ํ•œ ๊ทธ๋ฃน์˜ ํ‰๊ท  ๊ฐ’์„ ํ™•์ธํ•˜๊ณ  ์‹ถ์€ ๊ฒฝ์šฐ๊ฐ€ ์žˆ์—ˆ๋Š”๋ฐ (์˜ˆ๋ฅผ ๋“ค์ž๋ฉด ์ „์ฒด ํ•™์ƒ์„ ์กฐํšŒํ•  ๋•Œ ํ•™์ƒ์ด ์†ํ•œ ๋ฐ˜์˜ ํ‰๊ท  ํ‚ค๋ฅผ ํ•จ๊ป˜ ์กฐํšŒํ•˜๊ณ  ์‹ถ์€ ๊ฒฝ์šฐ...) 

    ์—ฌ๊ธฐ์„œ over์™€ ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ์‚ฌ์šฉํ•˜๋ฉฐ ์•Œ๊ฒŒ๋œ ์ ์„ ์ž‘์„ฑํ•ด๋ณด๋ ค๊ณ  ํ•œ๋‹ค.

     

     

    ๐Ÿงฉ ์„œ๋ธŒ ์ฟผ๋ฆฌ์™€ OVER()

    • ๋ณธ๋ฌธ์— ๋“ค์–ด๊ฐ€๊ธฐ ์ „ ์„œ๋ธŒ ์ฟผ๋ฆฌ์™€ OVER() ๊ฐ€ ๋ฌด์—‡์ธ์ง€ ์•Œ์•„๋ณด์ž

     

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

    • ์„œ๋ธŒ ์ฟผ๋ฆฌ๋ž€ SQL ์ฟผ๋ฆฌ ๋‚ด์—์„œ ๋‹ค๋ฅธ SQL ์ฟผ๋ฆฌ์˜ ์ผ๋ถ€๋กœ ์‚ฌ์šฉ๋˜๋Š” ์ฟผ๋ฆฌ์ด๋‹ค.
    • ์ฆ‰, ํ•˜๋‚˜์˜ ์ฟผ๋ฆฌ ์•ˆ์— ๋‹ค๋ฅธ SQL ์ฟผ๋ฆฌ๊ฐ€ ํฌํ•จ๋˜๋Š” ๊ฒƒ!

    ์˜ˆ๋ฅผ ๋“ค์–ด, ๋„์‹œ ํ…Œ์ด๋ธ”์—์„œ ์ „์ฒด ์ธ๊ตฌ ํ‰๊ท  ์ด์ƒ์˜ ๋„์‹œ๋ฅผ ์กฐํšŒํ•˜๋Š” ๊ตฌ๋ฌธ์ด๋‹ค.

    SELECT name, population
    FROM city
    WHERE population > (
        SELECT AVG(population)
        FROM city
    );

    ์ด์ฒ˜๋Ÿผ where ์ ˆ ์•ˆ์— ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ๋‘์–ด ์„œ๋ธŒ ์ฟผ๋ฆฌ์—์„œ ์ „์ฒด ์ธ๊ตฌ์˜ ํ‰๊ท ์„ ๋ฐ˜ํ™˜ํ•˜๋„๋ก ํ•œ๋‹ค.

     

     

    ์ด๋ ‡๊ฒŒ ์„œ๋ธŒ์ฟผ๋ฆฌ๋Š” ์ฟผ๋ฆฌ๋‚ด์— ๋‹ค์Œ ์œ„์น˜์—์„œ ์‚ฌ์šฉ๋  ์ˆ˜ ์žˆ๋‹ค.

    1. WHERE ์ ˆ
      • ์œ„์—์„œ ๋ดค๋˜ ์˜ˆ์‹œ์ฒ˜๋Ÿผ where ์ ˆ ๋‚ด์—์„œ ๋ฉ”์ธ ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ๋ฅผ ํ•„ํ„ฐ๋ง ํ•˜๋Š”๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.
    2. FROM ์ ˆ
      • ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ํ…Œ์ด๋ธ”์ฒ˜๋Ÿผ ์‚ฌ์šฉํ•˜์—ฌ ๋ฐ์ดํ„ฐ๋ฅผ JOINํ•˜๊ฑฐ๋‚˜ ๋‹ค๋ฅธ ํ…Œ์ด๋ธ”๊ณผ ํ•จ๊ป˜ ์ฟผ๋ฆฌํ•  ์ˆ˜ ์žˆ๋‹ค.
    3. SELECT ์ ˆ
      • ์„œ๋ธŒ ์ฟผ๋ฆฌ์˜ ๊ฒฐ๊ณผ๋ฅผ ๋ฉ”์ธ ์ฟผ๋ฆฌ์˜ SELECT ์ ˆ์—์„œ ์‚ฌ์šฉํ•˜์—ฌ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฒ€์ƒ‰ํ•˜๊ฑฐ๋‚˜ ํ‘œ์‹œํ•  ์ˆ˜ ์žˆ๋‹ค.
    4. HAVING ์ ˆ
      • GROUP BY์™€ ํ•จ๊ป˜ ์‚ฌ์šฉํ•˜์—ฌ ๊ทธ๋ฃนํ™”๋œ ๋ฐ์ดํ„ฐ๋ฅผ ํ•„ํ„ฐ๋งํ•˜๋Š”๋ฐ ์‚ฌ์šฉ๋  ์ˆ˜ ์žˆ๋‹ค.

     

     

    ์„œ๋ธŒ ์ฟผ๋ฆฌ์˜ ๋ชฉ์ 

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

     

     

    OVER() ํ•จ์ˆ˜

    • SQL์˜ ์œˆ๋„์šฐ ํ•จ์ˆ˜์™€ ํ•จ๊ป˜ ์‚ฌ์šฉ๋˜๋Š” ๊ตฌ๋ฌธ์ด๋‹ค.
      • ์œˆ๋„์šฐ ํ•จ์ˆ˜๋Š” ๋ฐ์ดํ„ฐ ์ง‘๊ณ„ ๋ฐ ๋ถ„์„ ์ž‘์—…์„ ์ˆ˜ํ–‰ํ•˜๋Š”๋ฐ ์‚ฌ์šฉ๋˜๋ฉฐ,
      • ๋ฐ์ดํ„ฐ ์ง‘ํ•ฉ์„ ์—ฌ๋Ÿฌ ๋ถ€๋ถ„( ์ฐฝ ๋˜๋Š” ์œˆ๋„์šฐ)์œผ๋กœ ๋‚˜๋ˆ„์–ด ๊ฐ ๋ถ€๋ถ„์— ๋Œ€ํ•œ ๊ณ„์‚ฐ์„ ์ˆ˜ํ–‰ํ•œ๋‹ค.
    • OVER() ๊ตฌ๋ฌธ์€ ์œˆ๋„์šฐ ํ•จ์ˆ˜์˜ ํŒŒํ‹ฐ์…”๋‹ ๋ฐ ์ •๋ ฌ(์ •๋ ฌ ์ˆœ์„œ)์„ ์ •์˜ํ•˜๋Š”๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.
    <์œˆ๋„์šฐ ํ•จ์ˆ˜>() OVER (
        [PARTITION BY <ํŒŒํ‹ฐ์…˜ ์—ด ๋ชฉ๋ก>]
        [ORDER BY <์ •๋ ฌ ์—ด ๋ชฉ๋ก>]
        [<๊ธฐํƒ€ ์˜ต์…˜>]
    )
    • ์œˆ๋„์šฐ ํ•จ์ˆ˜ 
      • count, sum, avg, row_number ๋“ฑ๊ณผ ๊ฐ™์€ ์œˆ๋„์šฐ ํ•จ์ˆ˜
    • partition by
      • ๋ฐ์ดํ„ฐ๋ฅผ ํŒŒํ‹ฐ์…˜์œผ๋กœ ๋‚˜๋ˆ„๊ธฐ ์œ„ํ•œ ์—ด ๋ชฉ๋ก
      • ๋™์ผํ•œ ํŒŒํ‹ฐ์…˜ ๋‚ด์—์„œ ์œˆ๋„์šฐ ํ•จ์ˆ˜ ์ž‘๋™
    • order by
      • ๋ฐ์ดํ„ฐ๋ฅผ ์ •๋ ฌํ•˜๊ธฐ ์œ„ํ•œ ์—ด ๋ชฉ๋ก
      • ์ •๋ ฌ ์ˆœ์„œ
    • ๊ธฐํƒ€์˜ต์…˜
      • ํ•„์š”์— ๋”ฐ๋ผ ์ถ”๊ฐ€ ์˜ต์…˜ ์ง€์ •

     

     

    ์˜ˆ๋ฅผ ๋“ค์–ด ๋„์‹œ ์ธ๊ตฌ์˜ ํ‰๊ท ๊ณผ ๊ฐ ๋„์‹œ๋ฅผ ๋น„๊ตํ•˜๋Š” ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•ด๋ณด์ž

    SELECT name, population,
      AVG(population) OVER () AS global_avg_population
    FROM city;
    • OVER()๋ฅผ ํ†ตํ•ด ์ „์ฒด ๋ฐ์ดํ„ฐ๋ฅผ ๊ธฐ์ค€์œผ๋กœ ์ง‘๊ณ„ํ•  ์ˆ˜ ์žˆ๋‹ค.

     

     

     

    ๐Ÿงฉ ์„œ๋ธŒ ์ฟผ๋ฆฌ VS OVER() 

    ๋ฌธ์ œ

    MySQL์—์„œ ์ œ๊ณตํ•˜๋Š” World DB์—์„œ country์—์„œ ๊ตญ๊ฐ€๋ณ„๋กœ gnp์™€ ํ•จ๊ป˜ ์ „์„ธ๊ณ„ ํ‰๊ท , ๋Œ€๋ฅ™๋ณ„ ํ‰๊ท ์„ ํ•จ๊ป˜ ์กฐํšŒํ•ด๋ผ!

     

     

    ์„œ๋ธŒ ์ฟผ๋ฆฌ ์‚ฌ์šฉ

    select continent, name, gnp, 
    	(
    		select avg(gnp)
    		from country p
    	) as "์ „์„ธ๊ณ„ ํ‰๊ท ", 
    	(
    		select avg(gnp)
    		from country p
    		where c.continent = p.continent
    	) as "๋Œ€๋ฅ™ ํ‰๊ท "
    from country c
    order by continent;

     

     

    OVER() ์‚ฌ์šฉ

    use world;
    select continent, name, gnp, 
    avg(gnp) over() '์ „์„ธ๊ณ„ ํ‰๊ท ',
    avg(gnp) over( partition by continent ) '๋Œ€๋ฅ™๋ณ„ ํ‰๊ท '
    from country;

     

     

    ํ”„๋กœํŒŒ์ผ๋ง์„ ํ†ตํ•œ ์ฒ˜๋ฆฌ ์†๋„ ํ™•์ธ

    ํ”„๋กœํŒŒ์ผ๋ง ์„ค์ • ํ™•์ธ

    SET profiling = 1; -- ํ”„๋กœํŒŒ์ผ๋ง ํ™œ์„ฑํ™”
    
    -- ์—ฌ๊ธฐ์— ์ˆ˜ํ–‰ํ•˜๋ ค๋Š” ์ฟผ๋ฆฌ ์ž‘์„ฑ
    
    SHOW PROFILES; -- ์‹คํ–‰ ์ค‘์ธ ํ”„๋กœํŒŒ์ผ๋ง ํ”„๋กœ์„ธ์Šค ๋ชฉ๋ก ํ™•์ธ
    
    -- ์‹คํ–‰ ์ค‘์ธ ํ”„๋กœํŒŒ์ผ๋ง ํ”„๋กœ์„ธ์Šค ID๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์„ธ๋ถ€ ์ •๋ณด ํ™•์ธ
    SHOW PROFILE FOR QUERY query_id;

     

    ์ฒ˜๋ฆฌ ์†๋„

    • 2๋ฒˆ์€ ์„œ๋ธŒ ์ฟผ๋ฆฌ ์‚ฌ์šฉํ•œ ์ฟผ๋ฆฌ ์‹คํ–‰
    • 3๋ฒˆ์€ OVER() ์‚ฌ์šฉํ•œ ์ฟผ๋ฆฌ ์‹คํ–‰

     

    ์™œ OVER()๋ฅผ ์‚ฌ์šฉํ–ˆ์„ ๋•Œ ๋” ๋น ๋ฅผ๊นŒ?

    • OVER ํ•จ์ˆ˜๋Š” ์œˆ๋„์šฐ ํ•จ์ˆ˜๋ฅผ ํ™œ์šฉํ•ด์„œ ํ•œ๋ฒˆ์˜ ์ฟผ๋ฆฌ ์‹คํ–‰์œผ๋กœ ํ‰๊ท ์„ ๊ณ„์‚ฐํ•œ๋‹ค.
    • ์„œ๋ธŒ์ฟผ๋ฆฌ์˜ ๊ฒฝ์šฐ ๊ฐ ํ–‰์— ๋Œ€ํ•ด ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ์‹คํ–‰ํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์กฐํšŒ๋œ ํŠœํ”Œ์˜ ๊ฐœ์ˆ˜๋งŒํผ ์‹คํ–‰๋œ๋‹ค.
    • OVER(partition by)๋ฅผ ์‚ฌ์šฉํ•œ ๊ฒฝ์šฐ๋„ partition์— ๋”ฐ๋ผ ์ง‘๊ณ„ํ•˜์—ฌ ํ‰๊ท ์„ ๊ณ„์‚ฐํ•˜๊ณ  ์ด๋ฅผ ์‚ฌ์šฉํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ์‚ฌ์šฉํ•œ ๊ฒฝ์šฐ๋ณด๋‹ค ๋น ๋ฅด๋‹ค

     

     

    ๐ŸŽ ๊ฒฐ๋ก 

    • ์ง‘๊ณ„์— ๋Œ€ํ•œ ๊ฒฐ๊ณผ๊ฐ€ ํ•„์š”ํ•  ๋•Œ๋Š” ์ตœ๋Œ€ํ•œ OVER์™€ ์œˆ๋„์›€ํ•จ์ˆ˜๋ฅผ ํ™œ์šฉํ•˜์ž!

     

     

    728x90

    ๋Œ“๊ธ€

Designed by Tistory.