반응형

/*******************************************************************************************
-- 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;

 

 

반응형

+ Recent posts