윈도우함수와 문제풀이

윈도우 함수

• select 결과에 대하여, 행과 행 간의 관계를 파악
• select 결과에 윈도우 함수를 써도 행 수는 그대로 

 

윈도우함수 () over (partition by 컬럼 order by 컬럼 asc / desc)

윈도우함수()	순위함수, 집계함수, 행순서함수, 비율함수
over	over은 윈도의 함수에서 꼭 들어가야 하며 over 내부에 partition by 결과 order by 가 온다.
partition by	전체 집합을 어떤 기준(컬럼)에 따라 나눌지를 결정
order by	어떤 항목(컬럼)을 기준으로 순위를 정할 지 결정하는 부분

 

순위함수	ROW_NUMBER (동점자처리 x) 1,2,3,4 RANK(동점자 처리 후 동점자 수만큼 건너뜀) 1,2,2,4,5 DENSE_RANK (동점자 처리 후 그 다음 순위) 1,2,2,3,4
집계함수	COUNT / SUM / MAX / MIN / AVG
행의 순서 함수	LAG (이전 값) LEAD (다음 값) FIRST_VALUE (가장 처음에 나온 값) LAST_VALUE (가장 나중에 나온 값)
비율 함수	RATIO_TO_REPORT CUM_DIST NTILE PERCENT_RANK

 

1. 다음 중 윈도우 함수에 대한 설명으로 가장 부적절한 것은?

1) Partition과 Group by 구문은 의미적으로 유사하다.

2) partition 구문이 없으면 전체 집합을 하나의 partition으로 정의한 것과 동일하다.

3) 윈도우 함수 처리로 안해 결과 건수가 줄어든다. ▶ 늘어난다.

4) 윈도우 함수 적용 범위는 partition을 넘을 수 없다.

 

2. 아래와 같은 테이블에서 sql 을 실행할 때 결과로 가장 적절한 것은?

SELECT 고객번호
	, 고객명
    , 매출액
    , RANK() OVER(ORDER BY 매출액 DESC) AS 순위
 FROM
 	(
    	SELECT A.고객번호
        	,MAX(A.고객명) AS 고객명
            ,SUM(B.매출액) AS 매출액
        FROM 고객 A INNER JOIN 월별매출 B
        ON (A.고객번호 = B.고객번호)
        GROUP BY A.고객번호
     )
 ORDER BY RANK

SELECT A.고객번호
         ,MAX(A.고객명) AS 고객명
            ,SUM(B.매출액) AS 매출액
        FROM 고객 A INNER JOIN 월별매출 B
        ON (A.고객번호 = B.고객번호)
        GROUP BY A.고객번호

고객번호	고객명	매출액
001	홍길동	350
002	이순신	550
003	강감찬	350
004	이상화	700
005	이규혁	700

 

RANK(동점자 처리 후 동점자 수만큼 건너뜀) DESC

고객번호	고객명	매출액	순위
004	이상화	700	1
005	이규혁	700	1
002	이순신	550	3
001	홍길동	350	4
003	강감찬	350	4

 

3. 아래 데이터 모델에서 활동점수가 높은 고객을 개인상품ID별로 10등까지 선별하여 사은행사를 진행하려고 한다. 다음 SQL중 가장 적절한것은? (단, 활동점수가 동일한 고객은 동일 등수로 한다. )

113446 ... ▶ RANK

게임 상품 ID 안에서 등수를 매기기 때문에 PARTITION BY 게임상품ID

SELECT 
	게임상품ID, 
    고객ID, 
    활동점수,
    순위
FROM(
    SELECT 
	    RANK() OVER(PARTITION BY 게임상품ID ORDER BY 활동점수 DESC) AS 순위,
    	고객ID,
   		게임상품ID,
    	활동점수
    FROM 고객활동)

 

4. 다음 중 추천내역 테이블에서 아래와 같은 SQL을 수행하였을 때의 결과로 가장 적절한 것은?

추천경로	추천인	피추천인	추천점수
SNS	나한일	강감찬	75
SNS	이순신	강감찬	80
이벤트응모	홍길동	강감찬	88
이벤트응모	저절로	이순신	78
홈페이지	저절로	이대로	93
홈페이지	홍두께	심청이	98

SELECT 추천경로,
	추천인,
    피추천인,
    추천점수
FROM (SELECT 추천경로,
	추천인,
    피추천인,
    추천장소,
    ROW_NUMBER() OVER(PARTITION BY 추천경로 ORDER BY 추천점수 DESC)AS RNUM
  	FROM 추천내역)
WHERE RNUM = 1;

SELECT 추천경로,
추천인,
    피추천인,
    추천장소,
    ROW_NUMBER() OVER(PARTITION BY 추천경로 ORDER BY 추천점수 DESC)AS RNUM
   FROM 추천내역

추천경로	추천인	피추천인	추천점수	RNUM
SNS	나한일	강감찬	75	2
SNS	이순신	강감찬	80	1
이벤트응모	홍길동	강감찬	88	1
이벤트응모	저절로	이순신	78	2
홈페이지	저절로	이대로	93	2
홈페이지	홍두께	심청이	98	1

 

RNUM = 1

추천경로	추천인	피추천인	추천점수	RNUM
SNS	이순신	강감찬	80	1
이벤트응모	홍길동	강감찬	88	1
홈페이지	홍두께	심청이	98	1