순위 정하는 함수 [ RANK(), ROW_NUMBER(), DENSE_RANK() ]

이 세개의 함수는 그룹으로 묶고, 그것에 대한 순위를 정하는 함수이다.

SELECT

    ROW_NUMBER() OVER(PARTITION BY [그룹핑할 컬럼] ORDER BY [정렬할 컬럼])
    , RANK() OVER(PARTITION BY [그룹핑할 컬럼]  ORDER BY [정렬할 컬럼])
    , DENSE_RANK() OVER(PARTITION BY [그룹핑할 컬럼] ORDER BY [정렬할 컬럼])
FROM TABLE_NAME;

기본입력된 데이타가 5명의 학생에 대한 성적정보가 들어있고, 1등이 2명이라면 등수가 어찌될까?
결과는 다음과 같다.

ROW_NUMBER : 1, 2, 3, 4, 5
RANK             : 1, 1, 3, 4, 5
DENSE_RANK  :  1, 1, 2, 3, 4

ROW_NUMBER는 정렬된 순서로 유일값의 번호를 부여한다. 말 그대로 쿼리결과에 순번을 mssql 이 붙여준다.
RANK는 동점자에 대해서는 같은 번호를 부여하고 다음 사람에게는 동점자의 수만큼의 번호를 지난 다음번호를 부여한다.
DENSE_RANK는 동점자에 대해서는 같은 번호를 부여하고, 다음 사람에게는 동점자에게 부여된 다음 번호를 부여한다.





* ROW_NUMBER() OVER (PARTITION BY)예제
SELECT * FROM
(
   SELECT empno, job, ename, sal,  ROW_NUMBER() OVER( PARTITION BY job ORDER BY sal )  CNT
   FROM emp
)

- PARTITION BY job : job 칼럼의 값이 같은 모든 ROW들 내에서 ROW_NUMBER() 를 먹인다.
- ORDER BY sal : ROW_NUMBER()를 먹일 때에, job 칼럼의 값이 같은 모든 ROW들을  sal ASC로 정렬한 값을 기준으로 먹인다.

- 이렇게 작업한 후에 바깥쪽 쿼리에서 CNT = 1 같은 조건을 사용하여, 원하는 결과값만 (ex : 각 job의 1위들만) 추려낼 수 있다.

 

[1] 시험점수를 점수별로 내림차순으로 출력해보자.

SELECT  select name, subject, point , row_number() over( ORDER BY point DESC) AS 'tmp'  FROM 성적표

Row_number에 점수높은사람순으로 번호를 준다는 의미이다.

name	subject	point	tmp
홍길동	국어	99	1
고길동	국어	87	2
...	..	..	..

위와 같이 tmp 란에 Row 번호가 생성이 되니 페이징할때 편리하게 사용할 수 있다.

 

 

 

[2] 과목별로 가장 점수를 많이 받은 사람을 찾는 쿼리
1. 과목별, 점수별로 내림차순으로 정렬하고 ROW_NUMBER()를 이용하여 번호를 부여한다.
2. 1에서 나온 결과를 다시 한번 열번호 1인것만 쿼리한다.
3. 과목별로 상위 1위인 데이터만 가져올수 있다.

SELECT sub.name, sub.subject, sub.point

FROM
(
   SELECT name, subject, point,  ROW_NUMBER() OVER( PARTITION BY subject ORDER BY point DESC ) AS cnt
   FROM 성적표
) AS sub

WHERE sub.cnt = 1

 

cnt를 이용하면 상위 몇 위인 데이터까지 가져올 수도 있다.

 

 

 

[3-1] 평가기준 테이블에 등록된 회사별 평가기준 분류 3개를 한 행에 출력한다.

SELECT CD_COMPANY, 
  MAX(CASE WHEN RID=1 THEN CLS_M ELSE "" END) AS A, 
  MAX(CASE WHEN RID=2 THEN CLS_M ELSE "" END) AS B,
  MAX(CASE WHEN RID=3 THEN CLS_M ELSE "" END) AS C
FROM
( 

   SELECT CD_COMPANY, ROW_NUMBER() OVER (PARTITION BY  CD_COMPANY ORDER BY CLS_M) AS RID, CLS_M

   FROM WEB_Z_MNSOFT_TECHEVA_BSC 
   WHERE CD_COMPANY = 'MN_WEB'
   AND NO_REQ_WEB = 'SRN20131100019'
   AND NO_APP = 'SAN20131100003' 
) AS SUB
GROUP BY CD_COMPANY 

 

 

 

[3-2] 배정업체 테이블에 등록된 회사별 배정된 거래처  3곳을 한 행에 출력한다. 

SELECT CD_COMPANY, 
  MAX(CASE WHEN RID=1 THEN ID_PARTNER ELSE 0 END) AS A_PARTNER, 
  MAX(CASE WHEN RID=2 THEN ID_PARTNER ELSE 0 END) AS B_PARTNER,
  MAX(CASE WHEN RID=3 THEN ID_PARTNER ELSE 0 END) AS C_PARTNER
FROM
( 

   SELECT CD_COMPANY, ROW_NUMBER() OVER (PARTITION BY  CD_COMPANY ORDER BY ID_PARTNER)AS RID, ID_PARTNER 

   FROM WEB_Z_MNSOFT_ASSIGN AS A
   WHERE CD_COMPANY = 'MN_WEB'
   AND NO_REQ_WEB = 'SRN20131100019'
   AND NO_APP = 'SAN20131100003' 
) AS SUB
GROUP BY CD_COMPANY 

 

 

 

 

* SUM() OVER(PARTITION BY) 예제

WITH T AS
     (  SELECT 'a' PRDNO ,1 SEQ ,10 MONEY FROM DUAL UNION ALL
        SELECT 'a', 2, 100 FROM DUAL UNION ALL
        SELECT 'a', 3, 20 FROM DUAL UNION ALL
        SELECT 'a', 4, 30 FROM DUAL UNION ALL
        SELECT 'b', 1, 50 FROM DUAL UNION ALL
        SELECT 'b', 2, 0 FROM DUAL UNION ALL
        SELECT 'b', 3, 0 FROM DUAL UNION ALL
        SELECT 'b', 4, 30 FROM DUAL
     ) -- 임시테이블 작성

SELECT PRDNO, SEQ, MONEY
-- 아래와 같이 ORDER BY 를 사용하지 않으면 각 그룹 내에서 ROW들이 동일한 SUM 값을 리턴
,SUM(MONEY) OVER(PARTITION BY PRDNO) SUM_MONEY 
-- 아래와 같이 ORDER BY 를 사용하면 ORDER BY의 기준칼럼의 순서대로 ROW가 증가됨에 따라 누적 SUM을 리턴
,SUM(MONEY) OVER(PARTITION BY PRDNO ORDER BY SEQ) SUM_MONEY
-- 아래와 같이 SUM(1)을 대입하고 ORDER BY를 사용하면  ROW_NUMBER() 를 사용한 것 같은 효과를 낸다.
--,SUM(1) OVER(PARTITION BY PRDNO ORDER BY SEQ) SUM_MONEY
--,ROW_NUMBER() OVER(PARTITION BY PRDNO ORDER BY SEQ) SUM_MONEY
--,MAX(MONEY) OVER(PARTITION BY PRDNO) SUM_MONEY
-- 아래와 같이 MAX의 경우에도 ORDER BY를 사용하면 누적의 성격을 지니게 된다.
--,MAX(MONEY) OVER(PARTITION BY PRDNO ORDER BY SEQ DESC) SUM_MONEYFROM T

 

 

 

 

 

- 출처 : http://planmaster.tistory.com/150 -
-츌처 : http://redpepe.tistory.com/category/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%B0%8D/MSSQL -