[SQLD] 33회 기출 23번 문제 - 윈도우 함수

 

 

 

 sqld 33회 기출 23번 문제 

다음 주어진 테이블에서 아래의 SQL을 수행하였을 때의 결과가 아래와 같을 때 결과에서 빈칸에 들어갈 MGR_SUM 값으로 알맞은 것을 고르시오.

정답확인🌼

[SQLD_33_23]

MGR   ENAME   HIREDATE   SAL
-----------------------------
유비    관우      2017    1000
유비    장비      2018    1500
유비    제갈량     2015    2000
조조    하후돈     2016    2000
조조    하후연     2017    3500
조조    순욱      2015    3000
손권    주유      2015    2500
손권    황개      2019    3000
손권    노숙      2013    2000

[SQL]

SELECT MGR, ENAME, HIREDATE, SAL,
   SUM(SAL) OVER(PARTITION BY MGR 
            ORDER BY HIREDATE
            ROWS BETWEEN 1 PRECEDING
            AND 1 FOLLOWING) AS MGR_SUM
    FROM SQLD_33_23;

[RESULT]

MGR   ENAME   HIREDATE   SAL   MGR_SUM
---------------------------------------
손권    노숙      2013    2000  (       )
손권    주유      2015    2500  (       )
손권    황개      2019    3000  (       )
유비    제갈량     2015    2000  (       )
유비    관우      2017    1000  (       )
유비    장비      2018    1500  (       )
조조    순욱      2015    3000  (       )
조조    하후돈     2016    2000  (       )
조조    하후연     2017    3500  (       )

1) 2000, 2500, 3000, 2000, 1000, 1500, 3000, 2000, 3500 2) 4500, 7500, 5500, 3000, 4500, 2500, 5000, 8500, 5500 3) 2000, 4500, 7500, 2000, 3000, 4500, 3000, 5000, 8500 4) 4500, 7500, 7500, 3000, 4500, 4500, 5000, 8500, 8500

---

 

 정답 :  2

📖문제확인

 

해설 : 1 PRECEDING / 1 FOLLOWING - 현재행을 기준으로 이전행+현재+다음행 합계를 표시

by yunamom

- ROWS : 물리적인 ROW 단위로 행 집합을 지정한다.(현재 행의 앞 뒤 건수)
- RANGE : 현재 행의 데이터 값을 기준으로 앞 뒤 데이터 값의 범위를 표시한다.
- BETWEEN ~ AND 절 : 윈도우의 시작과 끝 위치를 지정한다.
- UNBOUNDED PRECEDING : PARTITION의 첫 번째 로우에서 윈도우가 시작한다.
- UNBOUNDED FOLLOWING : PARTITION의 마지막 로우에서 윈도우가 시작한다.
- CURRENT ROW : 윈도우의 시작이나 끝 위치가 현재 로우 이다.

👩🏻‍💻윈도우 함수(WINDOW FUNCTION) 란? [ 펼쳐보기 ]

- 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수

- 윈도우절을 사용하면 PARTITION BY 절에 명시된 그룹을 좀 더 세부적으로 그룹화 할수있다.

[ 작성법 예제 ]
SELECT SUM(SAL)
       OVER -- 필수 키워드
       ([PARTITION BY 칼럼] -- 그룹 컬럼명
          [ORDER BY 절] -- 정렬 컬럼명
          [WINDOWING 절])FROM 테이블 명;

 

- WINDOW_FUNCTION

기존에 사용하던 함수도 있고, 분석함수중에서 일부(AVG, COUNT, SUM, MAX, MIN)만 사용 할 수 있다.

- ARGUMENTS (인수)

기존에 사용하던 함수도 있고,

- PARTITION BY 절

 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.(세부적으로 그룹핑)

- ORDER BY 절

어떤 항목에 대해 순위를 지정할지에 대해 기술한다.

- WINDOWING 절 

함수의 대상이 되는 행 기준의 범위를 강력하게 지정한다. ( 오라클 지원 )

ROWS  → 현재 행의 앞 뒤 건수를 의미 (물리적)

RANGE → 현재 행의 데이터 값을 기준으로 앞 뒤 데이터 값의 범위를 표시 (논리적)

ROWS, RANGE 중 하나를 선택하여 사용한다.

--[BETWEEN TYPE]
ROWS | RANGE BETWEEN
UNBOUNDED PRECEDING | 
CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING 
AND
UNBOUNDED FOLLOWING |
CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

--[NOT BETWEEN]
ROWS | RANGE
UNBOUNDED PRECEDING |
CURRENT ROW | VALUE_EXPR PRECEDING

--[DEFAULT]
RANGE BETWEEN UNBOUNDED PRECEDING
AND
CURRENT ROW

-- UNBOUNDED : 한계를 두지 않고, 해당 파티션의 끝까지를 의미
-- PRECEDING : 현재행에서 이전 행을 나타낸다.
-- FOLLOWING : 현재행에서 다음 행을 나타낸다. (BETWEEN ~ AND ~ 형식으로 작성함)

[RANGE, ROWS에서 사용하는 UNBOUNDED PRECEDING, CURRENT ROW, UNBOUNDED FOLLOWING ]

1. UNBOUNDED PRECEDING / UNBOUNDED FOLLOWING 

- 처음행부터 마지막행 결과까지 계산한 합계를 표시해줍니다 

2. UNBOUNDED PRECEDING / CURRENT ROW

- 처음행부터 현재행 결과까지 계산한 합계를 표시해줍니다 

3. CURRENT ROW / UNBOUNDED FOLLOWING

- 현재행부터 마지막행 결과까지 계산한 합계를 표시해줍니다 

4. 1 PRECEDING / 1 FOLLOWING

- 현재행에 이전행+본인+다음행 합계를 표시해줍니다

 

💡알아두기

그룹 내 행 순서 함수

 

FIRST_VALUE -  가장 먼저 나온 값, MIN 함수와 같다

LAST_VALUE - 가장 나중에 나온 값, MAX 함수와 같다

 

LAG : 이전 값

- LAG(SAL, 2, 0)

첫번째 : 대상 컬럼명

두번째 : 몇 번째 이전 행을 가져올 건지 결정(DEFAULT는 1)

세번째 : NULL의 경우 다른 값으로 바꿔준다(NVL, ISNULL과 같음)

 

LEAD : 다음 값

- LEAD(SAL, 2, 0)

첫번째 : 대상 컬럼명

두번째 : 몇 번째 다음 행을 가져올 건지 결정(DEFAULT는 1)

세번째 : NULL의 경우 다른 값으로 바꿔준다(NVL, ISNULL과 같음)

 

RATIO_TO_REPORT - 파티션 내 전체 SUM()값에 대한 행별 칼럼 값의 백분율을 소수점으로 구한다.

PERCENT_RANK - 먼저나오는것을 0, 가장 나중에 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구한다.

CUME_DIST - 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구한다.

NTILE - 파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구한다.(예: 수능등급)