/*******************************************************************************************
-- Title : [2k5] 순위(랭크) 함수 사용 예
-- Reference : hanbitbook.co.kr
-- Key word : sql server 순위 함수 랭크 rank dense_rank row_number ntile 윈도우함수 분석함수 Analytic Function
wnidow function 윈도우 함수 랭크함수 집계 랭크 함수 랭킹 ranking max_rank() rank_max rankmax maxrank
*******************************************************************************************/
USE tempdb;
go
--▶ 테이블 및 데이터 생성
IF object_id('userTbl') IS NOT NULL
DROP TABLE userTbl;
go
CREATE TABLE userTbl -- 회원테이블
( userID nchar(8) , -- 아이디
name nvarchar(10) , -- 이름
birthYear int , -- 출생년도
addr nchar(4) , -- 지역
mobile1 nchar(3), -- 휴대폰의국번
mobile2 nchar(8), -- 휴대폰의나머지전화번호
height float -- 신장
);
go
INSERT INTO userTbl VALUES(N'PJS', N'박지성', 1983, N'서울', N'011', N'1111111', 181.8);
INSERT INTO userTbl VALUES(N'PJY', N'박주영', 1986, N'서울', N'011', N'2222222', 178.4);
INSERT INTO userTbl VALUES(N'JJJ', N'조재진', 1986, N'충북', N'019', N'3333333', 180.5);
INSERT INTO userTbl VALUES(N'LCS', N'이천수', 1983, N'서울', N'011', N'4444444', 180.5);
INSERT INTO userTbl VALUES(N'AJH', N'안정환', 1979, N'충북', NULL , NULL , 182.0);
INSERT INTO userTbl VALUES(N'KNI', N'김남일', 1977, N'충북', N'016', N'6666666', 169.5);
INSERT INTO userTbl VALUES(N'LYP', N'이영표', 1983, N'충북', NULL , NULL , 178.2);
INSERT INTO userTbl VALUES(N'SKH', N'설기현', 1978, N'서울', N'011', N'8888888', 178.2);
INSERT INTO userTbl VALUES(N'SJK', N'송종국', 1979, N'서울', N'018', N'9999999', 179.3);
INSERT INTO userTbl VALUES(N'CJC', N'최진철', 1975, N'제주', N'019', N'0000000', 185.4);
INSERT INTO userTbl VALUES(N'SHM', N'손흥민', 1975, N'강원', N'017', N'7777777', 185.4);
go
--▶ 데이터 확인
SELECT * FROM userTbl;
go
--▶ OVER()
SELECT *, sum(height) OVER(ORDER BY addr)
FROM userTbl
ORDER BY addr;
--▶ ROW_NUMBER() OVER(ORDER BY~)
SELECT ROW_NUMBER( ) OVER(ORDER BY height DESC)[키큰 순위],
name, addr, height
FROM userTbl
ORDER BY height DESC;
SELECT ROW_NUMBER( ) OVER(ORDER BY height DESC, name ASC)[키큰순위],
name, addr, height
FROM userTbl
ORDER BY height DESC;
--▶ ROW_NUMBER() OVER(PARTITION BY~ ORER BY~)
SELECT addr,
ROW_NUMBER( ) OVER(PARTITION BY addr
ORDER BY height DESC, name ASC)[지역별키큰순위],
name, height
FROM userTbl
ORDER BY addr, height DESC;
--▶ DENSE_RANK() OVER(ORDER BY~)
SELECT DENSE_RANK( ) OVER(ORDER BY height DESC)[키큰순위],
name, addr, height
FROM userTbl
ORDER BY height DESC;
--▶ RANK() OVER(ORDER BY~)
SELECT RANK( ) OVER(ORDER BY height DESC)[키큰순위],
name, addr, height
FROM userTbl
ORDER BY height DESC;
--▶ NTILE() OVER(ORDER BY~)
SELECT NTILE(2) OVER(ORDER BY height DESC) [반번호],
name, addr, height
FROM userTbl
ORDER BY height DESC;
SELECT NTILE(4) OVER(ORDER BY height DESC) [반번호],
name, addr, height
FROM userTbl
ORDER BY height DESC;
--▶ PERCENT_RANK()
SELECT addr, height
, PERCENT_RANK() OVER(PARTITION BY addr ORDER BY height) as [랭크비율]
FROM userTbl
ORDER BY addr DESC;
--▶ MAX_RANK 구현하기
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 처음행~현재행 윈도우계산 활용
/* Case 1 */
SELECT *, '▼' "X"
, CASE WHEN Rank2 = 1 THEN 1 ELSE Max_Rank1 END "Max_Rank2"
FROM
(
SELECT *, '▼' "Y"
, MAX(Rank1) OVER(ORDER BY Rank2) "Max_Rank1"
FROM
(
SELECT *
, RANK() OVER(ORDER BY height DESC, userID) "Rank1"
, RANK() OVER(ORDER BY height DESC) "Rank2"
FROM userTbl
) a
) b;
/* Case 2 */
SELECT *, LAST_VALUE(rnum) OVER(ORDER BY rank) "MAXRANK_/w_lastvalue"
FROM
(
SELECT *, '▼' "X"
, ROW_NUMBER() OVER(ORDER BY height DESC, birthyear) "rnum"
, RANK() OVER(ORDER BY height DESC) "Rank"
, '▼' "Y"
, COUNT(*) OVER(ORDER BY height DESC) "MAXRANK_/w_COUNT"
FROM userTbl
) a;