데이터베이스 질문과 답변

그룹별 RANK함수 사용 0 3 2,567

by 엘시아 [SQL Query] RANK DENSE_RANK PARTITION BY 그룹 [2017.10.12 17:21:31]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT 1 AS NO1, 1 AS NO2 FROM DUAL
UNION ALL
SELECT 1 AS NO1, 1 AS NO2 FROM DUAL
UNION ALL
SELECT 2 AS NO1, 2 AS NO2 FROM DUAL
UNION ALL
SELECT 2 AS NO1, 2 AS NO2 FROM DUAL
UNION ALL
SELECT 2 AS NO1, 2 AS NO2 FROM DUAL
UNION ALL
SELECT 2 AS NO1, 3 AS NO2 FROM DUAL
UNION ALL
SELECT 2 AS NO1, 3 AS NO2 FROM DUAL
UNION ALL
SELECT 3 AS NO1, 4 AS NO2 FROM DUAL
UNION ALL
SELECT 3 AS NO1, 4 AS NO2 FROM DUAL
UNION ALL
SELECT 3 AS NO1, 4 AS NO2 FROM DUAL
UNION ALL
SELECT 3 AS NO1, 5 AS NO2 FROM DUAL
UNION ALL
SELECT 3 AS NO1, 5 AS NO2 FROM DUAL
UNION ALL
SELECT 3 AS NO1, 6 AS NO2 FROM DUAL
UNION ALL
SELECT 3 AS NO1, 6 AS NO2 FROM DUAL

안녕하세요

그룹별 RANK를 하고 싶은데 잘안되서 질문드립니다.

데이터가 NO1, NO2같이 있을때 NO3로 번호를 매기고 싶습니다.

NO1의 번호그룹별로 NO2값을 다시 그룹을 매겨서 번호를 1로 다시 주고 싶습니다.

NO1    NO2    NO3(원하는값)

  1          1         1

  1          1         1

  2          2         1

  2          2         1

  2          2         1 

  2          3         2

  2          3         2

  3          4         1

  3          4         1

  3          4         1

  3          5         2

  3          5         2

  3          6         3

  3          6         3

 

감사합니다.

by 랑에1 [2017.10.12 17:49:18]
?
1
DENSE_RANK() OVER(PARTITION BY NO1 ORDER BY NO2) AS NO3

 

by 박군two [2017.10.12 17:54:27]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH T AS (
    SELECT 1 AS NO1, 1 AS NO2 FROM DUAL UNION ALL
    SELECT 1 AS NO1, 1 AS NO2 FROM DUAL UNION ALL
    SELECT 2 AS NO1, 2 AS NO2 FROM DUAL UNION ALL
    SELECT 2 AS NO1, 2 AS NO2 FROM DUAL UNION ALL
    SELECT 2 AS NO1, 2 AS NO2 FROM DUAL UNION ALL
    SELECT 2 AS NO1, 3 AS NO2 FROM DUAL UNION ALL
    SELECT 2 AS NO1, 3 AS NO2 FROM DUAL UNION ALL
    SELECT 3 AS NO1, 4 AS NO2 FROM DUAL UNION ALL
    SELECT 3 AS NO1, 4 AS NO2 FROM DUAL UNION ALL
    SELECT 3 AS NO1, 4 AS NO2 FROM DUAL UNION ALL
    SELECT 3 AS NO1, 5 AS NO2 FROM DUAL UNION ALL
    SELECT 3 AS NO1, 5 AS NO2 FROM DUAL UNION ALL
    SELECT 3 AS NO1, 6 AS NO2 FROM DUAL UNION ALL
    SELECT 3 AS NO1, 6 AS NO2 FROM DUAL
)
SELECT NO1, NO2,
       DENSE_RANK() OVER (PARTITION BY NO1 ORDER BY NO1, NO2) RANK
FROM   T

 

by 엘시아 [2017.10.12 18:40:58]

이렇게 간단한 거였다니.. 더 해볼껄그랬네요 ㅎㅎ.. 감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입