[SQL] MySQL은 여러 열을 기준으로 순위를 결정

MySQL은 여러 열을 기준으로 순위를 결정

내가 3 필드가있는 테이블을 가지고, 나는 USER_ID와 GAME_ID에 따라 순위 열을합니다.

여기에 SQL 바이올린은 다음과 같습니다 http://sqlfiddle.com/#!9/883e9d/1

테이블은 이미 내가 가진 :

 user_id | game_id |   game_detial_sum  |
 --------|---------|--------------------|
 6       | 10      |  1000              |   
 6       | 11      |  260               |
 7       | 10      |  1200              |
 7       | 11      |  500               |
 7       | 12      |  360               |
 7       | 13      |  50                | 

예상 출력 :

user_id  | game_id |   game_detial_sum  |  user_game_rank  |
 --------|---------|--------------------|------------------|
 6       | 10      |  1000              |   1              |
 6       | 11      |  260               |   2              |
 7       | 10      |  1200              |   1              |
 7       | 11      |  500               |   2              |
 7       | 12      |  360               |   3              |
 7       | 13      |  50                |   4              |

내 노력이 지금까지 :

SET @s := 0; 
SELECT user_id,game_id,game_detail, 
       CASE WHEN user_id = user_id THEN (@s:=@s+1) 
            ELSE @s = 0 
       END As user_game_rank 
FROM game_logs

편집은 : (OP 댓글에서) : 주문이 game_detail의 내림차순을 기반으로

해결법

1. ==============================

   1.파생 테이블 (FROM 절 내부 부질)에서는 동일한 USER_ID 값을 갖는 모든 행 내림차순 game_detail에 기초하여 그들 사이에 상기 정렬과 함께 제공되도록 우리의 데이타를 주문한다.

   파생 테이블 (FROM 절 내부 부질)에서는 동일한 USER_ID 값을 갖는 모든 행 내림차순 game_detail에 기초하여 그들 사이에 상기 정렬과 함께 제공되도록 우리의 데이타를 주문한다.

   이제, 우리는이 결과 세트를 사용하여 행 번호를 평가하는 조건 CASE..WHEN 식을 사용합니다. 그것은 (: PHP 예를 들어, 우리는 응용 프로그램 코드에서 사용)을 반복 기법처럼 될 것입니다. 우리는 사용자 정의 변수에 이전 행 값을 저장하고 이전 행에 대한 현재 행의 값 (들)을 확인한다. 결국, 우리는 그에 따라 행 번호를 할당합니다.

   편집 : MySQL의 문서 및 @Gordon Linoff의 관찰을 바탕으로 :

   우리는 행 번호를 평가하고 동일한 표현식 내에서 @u 변수에 USER_ID 값을 할당해야합니다.

   SET @r := 0, @u := 0; 
   SELECT
     @r := CASE WHEN @u = dt.user_id 
                     THEN @r + 1
                WHEN @u := dt.user_id /* Notice := instead of = */
                     THEN 1 
           END AS user_game_rank, 
     dt.user_id, 
     dt.game_detail, 
     dt.game_id 
   
   FROM 
   ( SELECT user_id, game_id, game_detail
     FROM game_logs 
     ORDER BY user_id, game_detail DESC 
   ) AS dt 
   

   결과

   | user_game_rank | user_id | game_detail | game_id |
   | -------------- | ------- | ----------- | ------- |
   | 1              | 6       | 260         | 11      |
   | 2              | 6       | 100         | 10      |
   | 1              | 7       | 1200        | 10      |
   | 2              | 7       | 500         | 11      |
   | 3              | 7       | 260         | 12      |
   | 4              | 7       | 50          | 13      |
   

   DB 바이올린에보기

   내가 최근에 발견 된 MySQL의 문서에서 흥미로운 참고 :

   또한, 동료 SO 회원 덕분에, MySQL의 팀이이 블로그를 건너 왔어요 : https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/

   일반 관찰 동일한 쿼리 블록에서 사용자 변수의 평가에 의해 ORDER를 사용하여 값이 항상 올바른 될 수 있도록하지 않습니다. 로, MySQL의 최적화는 장소로 와서 평가의 우리의 추정 ​​순서를 변경할 수 있습니다.

   이 문제에 대한 가장 좋은 방법은 MySQL로 업그레이드 8 이상과 ROW_NUMBER () 기능을 활용하는 것입니다 :

   스키마 (MySQL의 V8.0)

   SELECT user_id, 
          game_id, 
          game_detail, 
          ROW_NUMBER() OVER (PARTITION BY user_id 
                             ORDER BY game_detail DESC) AS user_game_rank 
   FROM game_logs 
   ORDER BY user_id, user_game_rank;
   

   결과

   | user_id | game_id | game_detail | user_game_rank |
   | ------- | ------- | ----------- | -------------- |
   | 6       | 11      | 260         | 1              |
   | 6       | 10      | 100         | 2              |
   | 7       | 10      | 1200        | 1              |
   | 7       | 11      | 500         | 2              |
   | 7       | 12      | 260         | 3              |
   | 7       | 13      | 50          | 4              |
   

   DB 바이올린에보기

2. ==============================

   2.MySQL은 가장 좋은 방법은, 버전 8.0 이전은 다음과 같다 :

   MySQL은 가장 좋은 방법은, 버전 8.0 이전은 다음과 같다 :

   select gl.*, 
          (@rn := if(@lastUserId = user_id, @rn + 1,
                     if(@lastUserId := user_id, 1, 1)
                    )
           ) as user_game_rank
   from (select gl.*
         from game_logs gl
         order by gl.user_id, gl.game_detail desc
        ) gl cross join
        (select @rn := 0, @lastUserId := 0) params;
   

   순서는 하위 쿼리에서 이루어집니다. 이것은 MySQL의 5.7 주위에 시작해야합니다. 변수 할당 식의 평가 때문에 다른 순서는 중요하지 않습니다 (그리고 MySQL은 표현의 평가 순서를 보장하지 않음), 하나 개의 표현에 모두.

3. ==============================

   3.

   SELECT user_id, game_id, game_detail, 
          CASE WHEN user_id = @lastUserId 
               THEN @rank := @rank + 1 
               ELSE @rank := 1 
          END As user_game_rank,
          @lastUserId := user_id
   FROM game_logs
   cross join (select @rank := 0, @lastUserId := 0) r
   order by user_id, game_detail desc
   

4. ==============================

   4.당신은 아주 간단한 상관 하위 쿼리를 사용할 수 있습니다 :

   당신은 아주 간단한 상관 하위 쿼리를 사용할 수 있습니다 :

   SELECT *, (
       SELECT COUNT(DISTINCT game_detail) + 1
       FROM game_logs AS x
       WHERE user_id = t.user_id AND game_detail > t.game_detail
   ) AS user_game_rank
   FROM game_logs AS t
   ORDER BY user_id, user_game_rank
   

   DB 바이올린

   그것은 느리지 만 사용자 변수보다 훨씬 더 신뢰할 수있다. 걸리는 모두는 하나를 깰 가입입니다.

from https://stackoverflow.com/questions/53465111/determine-rank-based-on-multiple-columns-in-mysql by cc-by-sa and MIT license