윈도우 함수와 문제풀이 2

윈도우 함수() OVER (PARTITION BY 컬럼 ORDER BY 컬럼 ASC/DESC RANGE BETWEEN A  AND B OR ROWS BETWEEN A AND B)

DEFAULT 세팅 : RANGE BETWEEN UNBOUNDED PERCEDING AND CURRENT ROW

RANGE BETWEEN A AND B : 현재 컬럼의 값 기준, 연산에 참여할 행 선택

•  UNBOUNDED PERCEDING : 최종 출력될 값의 맨 처음 ROW의 값
• CURRENT ROW : 현재 ROW의 값
•  UNBOUNDED FOLLOWING : 최종 출력될 값의 맨 마지막 ROW의 값 (AND뒤)

 

1. 다음 중 아래의 SQL에 대한 설명으로 가장 적절한 것은?

SELECT 상품유형코드
	, AVG(상품가격) AS 상품가격
    , COUNT(*) OVER(ORDER BY AVG(상품가격)
    				RANGE BETWEEN 10000 PERCEDING
                    AND 10000 FOLLOSING) AS 유사개수
 FROM 상품
 GROUP BY 상품분류코드;

1) WINDOW FUNCTION GROUP BY 절과 함께 사용하였으므로 위의 SQL은 오류가 발생한다.

2) WINDOW FUNCTION의 ORDER BY 절에 AVG집계 함수를 사용하였으므로 위의 SQL은 오류가 발생한다.

3) 유사개수 컬럼은 상품분류코드별 평균상품가격을 서로 비교하여 -10000 ~ +10000 사이에 존재하는 상품분류코드의 개수를 구한 것이다.

4) 유사개수 컬럼은 상품 전체의 평균상품가격을 서로 비교하여 -10000 ~ +10000 사이에 존재하는 상품의 개수를 구한 것이다.

 

2. 다음 [사원]테이블에 대하여 아래와 같은 SQL을 수행하였을 때 예상되는 결과로 가장 적절한 것은?

사원ID	부서ID	사원명	연봉
001	100	홍길동	2500
002	100	강감찬	3000
003	200	김유신	4500
004	200	김선달	3000
005	200	유학생	2500
006	300	변사또	4500
007	300	박문수	3000

SELECT Y.사원ID,
	Y.부서ID,
    Y.사원명,
    Y.연봉
FROM 
	(SELECT 사원ID,
    	MAX(연봉) OVER(PARTITION BY 부서ID) AS 최고연봉
     FROM 사원) X, 사원 Y
WHERE X.사원ID = Y.사원ID
	AND X.최고연봉 = Y.연봉

 

사원ID	부서ID	사원명	연봉
002	100	강감찬	3000
003	200	김유신	4500
006	300	변사또	4500

 

3. 아래의 SQL 실행 결과로 가장 적절한 것은?

TBL

ID	START_VAL	END_VAL
A	10	14
A	14	15
A	15	15
A	15	18
A	20	25
A	25

SELECT ID, START_VAL, END_VAL
FROM (
	SELECT ID,
    	STRAT_VAL,
        NVL(END_VAL, 99) END_VAL,
        (CASE 
        	WHEN START_VAL = LAG(END_VAL) OVER (PARTITION BY ID ORDER BY START_VAL, NVL(END_VAL, 99)) THEN 1
            ELSE 0
         END) FLAG1,
          (CASE 
        	WHEN END_VAL = LEAD(START_VAL) OVER (PARTITION BY ID ORDER BY START_VAL, NVL(END_VAL, 99)) THEN 1
            ELSE 0
         END) FLAG2
      FROM TBL)
 WHERE FLAG1 = 0
 	OR FLAG2 = 0;

SELECT ID,
     STRAT_VAL,
        NVL(END_VAL, 99) END_VAL,
        (CASE 
        WHEN START_VAL = LAG(END_VAL) OVER (PARTITION BY ID ORDER BY START_VAL, NVL(END_VAL, 99))     THEN 1
            ELSE 0
         END) FLAG1,
          (CASE 
        WHEN END_VAL = LEAD(START_VAL) OVER (PARTITION BY ID ORDER BY START_VAL, NVL(END_VAL, 99)) THEN 1
            ELSE 0
         END) FLAG2
      FROM TBL

ID	START_VAL	END_VAL	FLAG1	FLAG2
A	10	14	0	1
A	14	15	1	1
A	15	15	1	1
A	15	18	1	0
A	20	25	0	1
A	25	99	1	0

 

정답

ID	START_VAL	END_VAL
A	10	14
A	15	18
A	20	25
A	25	99