๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
ORACLE/SQL

[Oracle] ๋ˆ„์  ํ•ฉ๊ณ„ ๊ตฌํ•˜๊ธฐ, WINDOWING ํ–‰ ๊ธฐ์ค€ ๋ฒ”์œ„ ์—ฐ์‚ฐ :: ๋งˆ์ด์ž๋ชฝ

by ๐ŸŒปโ™š 2020. 4. 3.

๋ˆ„์  ํ•ฉ๊ณ„ ๊ตฌํ•˜๊ธฐ

EMPํ…Œ์ด๋ธ”์—์„œ ์‚ฌ์›์˜ ์ •๋ณด์™€ ๋ถ€์„œ๋ฒˆํ˜ธ๋กœ ์ •๋ ฌ๋˜์–ด ์žˆ์„๋•Œ ๊ธ‰์—ฌ์˜ ๋ˆ„์ ํ•ฉ๊ณ„๋„ ๊ฐ™์ด ์ถœ๋ ฅํ•ด๋ผ.

ORACLE SCOTT ๊ณ„์ •์œผ๋กœ ์‹ค์Šต์„ ์ง„ํ–‰ํ•ฉ๋‹ˆ๋‹ค.

1
2
3
4
5
6
7
8
9
10
SELECT
    DEPTNO
    ,EMPNO
    ,ENAME
    ,SAL
    ,SUM(SAL) OVER(
        ORDER BY DEPTNO
        ROWS UNBOUNDED PRECEDING
    ) AS SUM_SAL
FROM EMP;
1
2
3
4
5
6
7
8
9
10
11
SELECT
    DEPTNO
    ,EMPNO
    ,ENAME
    ,SAL
    ,SUM(SAL) OVER(
        ORDER BY DEPTNO
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW
    ) AS SUM_SAL
FROM EMP;

๋ˆ„์  ํ•ฉ๊ณ„๋ฅผ ๊ตฌํ•˜๊ธฐ ์œ„ํ•ด ๋ถ„์„ํ•จ์ˆ˜ OVER์ ˆ์„ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค. ๊ทธ๋ฆฌ๊ณ  ORDER BY ์ ˆ ๋’ค๋กœ WINDOWING ์ ˆ์„ ์‚ฌ์šฉํ•˜๋Š” ๊ฒƒ์ด ํ•ต์‹ฌ์ž…๋‹ˆ๋‹ค. WINDOWING์ ˆ์—์„œ๋Š” ๋ถ„์„ํ•จ์ˆ˜์˜ ๋Œ€์ƒ์ด ๋˜๋Š” ํ–‰๋“ค์˜ ๋ฒ”์œ„ ์กฐ์ •์ด ๊ฐ€๋Šฅํ•ฉ๋‹ˆ๋‹ค. ๋ˆ„์  ํ•ฉ๊ณ„๋ฅผ ๊ตฌํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” ์ฒซ๋ฒˆ์งธ ํ–‰๋ถ€ํ„ฐ ์ฝ๊ณ  ์žˆ๋Š” ํ–‰๊นŒ์ง€(ROWS UNBOUNDED PRECEDING)๋ฅผ ๋ถ„์„ํ•จ์ˆ˜์˜ ๋ฒ”์œ„ ๋Œ€์ƒ์œผ๋กœ ์ง€์ •ํ•œ ๊ฒƒ ์ž…๋‹ˆ๋‹ค.

 

WINDOWING์ ˆ์—์„œ๋Š” ๋ฌผ๋ฆฌ์ (ROWS), ๋…ผ๋ฆฌ์ (RANGE) ๋ฒ”์œ„๋ฅผ ์ง€์ •ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 

 

WINDOWING ๋ฌผ๋ฆฌ์  ๋ฒ”์œ„

๋ฌผ๋ฆฌ์  ๋ฒ”์œ„๋Š” ํ–‰์„ ๊ธฐ์ค€์œผ๋กœ ๋ฒ”์œ„๋ฅผ ์ง€์ •ํ•˜๋Š” ๊ฒƒ ์ž…๋‹ˆ๋‹ค. ROWS๋ฅผ ์‚ฌ์šฉํ•ด์„œ ํ–‰์œผ๋กœ ๋ถ€ํ„ฐ ์ „(PRECEDING)๊ณผ ํ›„(FOLLOWING)์˜ ๋ฒ”์œ„๋ฅผ ์ง€์ •ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์œ„์˜ ๋ˆ„์  ํ•ฉ๊ณ„๋ฅผ ๊ตฌํ•˜๋Š” ์˜ˆ์‹œ๋„ ๋ฌผ๋ฆฌ์  ๋ฒ”์œ„์˜ WINDOWING์ž…๋‹ˆ๋‹ค.

 

์‚ฌ์›์˜ ์ •๋ณด๋ฅผ ๋ถ€์„œ ๋ฒˆํ˜ธ ๊ธฐ์ค€์œผ๋กœ ์ •๋ ฌ ํ–ˆ์„๋•Œ ๋ฐ”๋กœ ์ „๊ณผ ํ›„ ํ–‰์˜ ๊ธ‰์—ฌํ‰๊ท  ๊ฐ’๊ณผ ํ•จ๊ป˜ ์ถœ๋ ฅํ•ด๋ผ.
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    DEPTNO
    ,EMPNO
    ,ENAME
    ,SAL
    ,ROUND(
        AVG(SAL) OVER(
            ORDER BY DEPTNO
            ROWS BETWEEN 1 PRECEDING
            AND 1 FOLLOWING
        )
    ) AS AVG_SAL
FROM EMP;

 

 

WINDOWING ๋…ผ๋ฆฌ์  ๋ฒ”์œ„

๋…ผ๋ฆฌ์  ๋ฒ”์œ„๋Š” ๊ฐ’์„ ๊ธฐ์ค€์œผ๋กœ ๋ฒ”์œ„๋ฅผ ์ง€์ •ํ•˜๋Š” ๊ฒƒ ์ž…๋‹ˆ๋‹ค. RANGE๋ฅผ ์‚ฌ์šฉํ•ด์„œ ํ•ด๋‹น ํ–‰์˜ ๊ฐ’์„ ๊ธฐ์ค€์œผ๋กœ ์ „(PRECEDING)๊ณผ ํ›„(FOLLOWING)์˜ ๋ฒ”์œ„๋ฅผ ์ง€์ •ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์˜ˆ๋ฅผ ๋“ค์–ด 2๋ฒˆ์งธ ํ–‰์—์„œ ๊ธ‰์—ฌ 4000์˜ 1000 PRECEDING๊ณผ 1000 FOLLOWING ๋ฒ”์œ„๋Š” 3000 ~ 5000๊นŒ์ง€์˜ ๊ฐ’์„ ๋ถ„์„ํ•จ์ˆ˜์— ์ ์šฉํ•˜๋Š” ๊ฒƒ ์ž…๋‹ˆ๋‹ค.

 

์‚ฌ์›์˜ ์ •๋ณด๋ฅผ ๋ถ€์„œ ๋ฒˆํ˜ธ ๊ธฐ์ค€์œผ๋กœ ์ •๋ ฌ ํ–ˆ์„๋•Œ ๊ธฐ์ค€ ํ–‰์˜ -1000 ~ 1000์— ํ•ด๋‹นํ•˜๋Š” ๊ฐ’๋“ค์˜ ๊ธ‰์—ฌํ‰๊ท  ๊ฐ’๊ณผ ํ•จ๊ป˜ ์ถœ๋ ฅํ•ด๋ผ.
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    DEPTNO
    ,EMPNO
    ,ENAME
    ,SAL
    ,ROUND(
        AVG(SAL) OVER(
            ORDER BY SAL --์–ด๋–ค ์ปฌ๋Ÿผ์œผ๋กœ ์ •๋ ฌํ•˜๋Š๋ƒ์— ๋”ฐ๋ผ์„œ ๋…ผ๋ฆฌ์ ์ธ ๊ฐ’์ด ๋ณ€๊ฒฝ๋œ๋‹ค.
            RANGE BETWEEN 1000 PRECEDING
            AND 1000 FOLLOWING
        )
    ) AS AVG_SAL
FROM EMP;
 

 

 

๊ด€๋ จ๊ธ€ ์ฐธ์กฐ

๋ถ„์„ํ•จ์ˆ˜๋ž€? ๋ถ„์„์ ˆ OVER

 

[Oracle] OVER ... PARTITION BY ๋ถ„์„์ ˆ ANALYTIC FUNCTION :: ๋งˆ์ด์ž๋ชฝ

์‚ฌ์› ์ •๋ณด๋ฅผ ์ž…๋ ฅํ•˜๋Š”๋ฐ, ์ „์ฒด ๊ธ‰์—ฌ ํ‰๊ท ๊ฐ’๊ณผ ํ•จ๊ป˜ ์ถœ๋ ฅํ•˜์‹œ์˜ค. ORACLE SCOTT ๊ณ„์ • EMP ํ…Œ์ด๋ธ”๋กœ ์ถœ๋ ฅ๋œ ๊ฒฐ๊ณผ์ž…๋‹ˆ๋‹ค. ์œ„ ๋ฌธ์ œ๋ฅผ ํ’€๊ธฐ ์œ„ํ•ด ์–ด๋–ป๊ฒŒ ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•˜์‹ค๊ฑด๊ฐ€์š”? 1 2 3 4 5 6 7 8 SELECT DEPTNO ,EMPNO..

myjamong.tistory.com

 

๋Œ“๊ธ€