랭킹 관련 쿼리 도와주세요~! 0 5 656

by 광어얌 [SQL Query] [2020.06.29 20:32:43]


랭킹.JPG (79,460Bytes)

안녕하세요 랭킹을 구하는 쿼리(postgresql)를 만들려고 하는데 생각보다 잘 되지 않아서 고수님들에게 도움 요청 드려요~

테이블A가 있고 보통 랭킹을 구하면 아래처럼 순위가 매겨지는데요

문제1. 특정점수 기준(400점)인것들만 모두 1등처리 해버리는 것.

문제2. 특정사람의 랭킹을 구하는건데요 D라는 사람이 원래는 4등인데 문제1처럼 400점을 기준으로 점수가 병합되어

        2등으로 나오겠끔 해야됩니다.

도와주세요ㅠ

 

Table A              
이름 점수              
A 420              
B 410              
C 400              
D 399              
E 380              
F 370              
G 360              
H 350              
I 320              
J 310              
K 300              
                 
랭킹   문제1   문제2
점수 순위   점수 순위   이름 점수 순위
420 1   400 1   D 399 2
410 2   399 2        
400 3   380 3        
399 4   370 4        
380 5   360 5        
370 6   350 6        
360 7   320 7        
350 8   310 8        
320 9   300 9        
310 10              
300 11              
by pajama [2020.06.29 20:45:11]

400점 이상은 제외하는건지요?

with t as (select 'A' lname, 420 score
union all
select 'B', 410 
union all
select 'C', 400 
union all
select 'D', 399 
union all
select 'E', 380 
union all
select 'F', 370 
union all
select 'G', 360 
union all
select 'H', 350 
union all
select 'I', 320 
union all
select 'J', 310 
union all
select 'K', 300 
)
select lname, score, rank() over (order by score desc) from t where score <= 400

postgres-# select lname, score, rank() over (order by score desc) from t where score <= 400;
 lname | score | rank
-------+-------+------
 C     |   400 |    1
 D     |   399 |    2
 E     |   380 |    3
 F     |   370 |    4
 G     |   360 |    5
 H     |   350 |    6
 I     |   320 |    7
 J     |   310 |    8
 K     |   300 |    9

 


by 광어얌 [2020.06.30 00:07:34]

답변 정말 감사합니다!ㅠ

문제1에서는 400점 이상 포함되지 않아도 됩니다! 사용자 이름이 나오지 않아도 되는 전체 랭킹이라서요

문제2에 쿼리 주신거요 사용자 마다 직접 넣어 주셨는데 만약에 5만건 일경우 이걸 동적으로 할수 있는 방법은 없을까요?


by 마농 [2020.06.30 08:22:38]

with 문은 테스트 용도로 넣은 것입니다.
아래쪽 Select 쿼리 작성 스타일을 보세요.


by 마농 [2020.06.30 08:11:16]
-- 1. 전체 Rank 구하기
-- 1.1. Group By 후 Rank
SELECT LEAST(score, 400) score
     , RANK() OVER(ORDER BY LEAST(score, 400) DESC) rk
  FROM t
 GROUP BY LEAST(score, 400)
;
-- 1.2. Dense_Rank 후 Distinct
SELECT DISTINCT
       LEAST(score, 400) score
     , DENSE_RANK() OVER(ORDER BY LEAST(score, 400) DESC) rk
  FROM t
 ORDER BY score DESC
;
-- 2. 특정 lname 의 Rank 구하기
-- 2.1. Dense_Rank 후 조건 처리
SELECT *
  FROM (SELECT lname
             , score
             , DENSE_RANK() OVER(ORDER BY LEAST(score, 400) DESC) rk
          FROM t
        ) a
 WHERE lname = 'D'
;
-- 2.2. Self Join
SELECT a.lname
     , a.score
     , COUNT(DISTINCT LEAST(b.score, 400)) + 1 rk
  FROM t a
  LEFT OUTER JOIN t b
    ON a.score < b.score
 WHERE a.lname = 'D'
 GROUP BY a.lname, a.score
;

 


by 광어얌 [2020.06.30 10:36:16]

고수님 매번 감사드립니다! 
도움 정말 많이 되었습니다~ㅠ

공부 더 열심히 해야겠네요ㅠㅠ

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