ACTUALIZAR el rango de 3 maneras dentro del grupo

2022-10-06 23:24:58

Después de buscar y probar muchos ejemplos, no pude resolver esto. Espero que puedas ayudarme.

Esta es mi tabla Prueba:

    +-------+-----------+----------+---------+---------+-------+
    |Id     |   Class   |   Score  |  Rank1  |  Rank2  | Rank3 |
    +-------+-----------+----------+---------+---------+-------+
    |1      |     1     |     9    |    0    |    0    |   0   |
    |2      |     1     |     9    |    0    |    0    |   0   |
    |3      |     1     |     8    |    0    |    0    |   0   |
    |4      |     1     |     7    |    0    |    0    |   0   |
    |5      |     2     |     9    |    0    |    0    |   0   |
    |6      |     2     |     8    |    0    |    0    |   0   |
    |7      |     2     |     8    |    0    |    0    |   0   |
    |8      |     2     |     7    |    0    |    0    |   0   |
    |9      |     2     |     6    |    0    |    0    |   0   |
    +-------+-----------+----------+---------+---------+-------+

Lo que me gustaría es ACTUALIZAR mi tabla de prueba con 3 tipos de clasificaciones:

  1. = Rango 1 = Clasificado en puntuación por clase con rangos consecutivos (sin doble)
  2. = Rango 2 = Clasificado en Puntuación por Clase con rangos consecutivos (con doble)
  3. = Rango 3 = Clasificado en Puntuación por Clase sin rangos consecutivos (con doble)

Por ejemplo:

    +-------+-----------+----------+---------+---------+-------+
    |Id     |   Class   |   Score  |  Rank1  |  Rank2  | Rank3 |
    +-------+-----------+----------+---------+---------+-------+
    |1      |     1     |     9    |    1    |    1    |   1   |
    |2      |     1     |     9    |    2    |    1    |   1   |
    |3      |     1     |     8    |    3    |    2    |   3   |
    |4      |     1     |     7    |    4    |    3    |   4   |
    |5      |     2     |     9    |    1    |    1    |   1   |
    |6      |     2     |     8    |    2    |    2    |   2   |
    |7      |     2     |     8    |    3    |    2    |   2   |
    |8      |     2     |     7    |    4    |    3    |   4   |
    |9      |     2     |     6    |    5    |    4    |   5   |
    +-------+-----------+----------+---------+---------+-------+

NOTA: tiene que ser aplicable dentro de una instrucción UPDATE.

Para 1. He encontrado (pero no sé cómo convertirlo en una ACTUALIZACIÓN):

SET @prev := null;
SET @cnt := 0;
SELECT IF(@prev <> Class, @cnt := 1, @cnt := @cnt + 1) AS Rank, @prev := Class
FROM Test
ORDER BY Class;

Para 3. He encontrado (pero no sé cómo convertirlo en una ACTUALIZACIÓN):

SELECT  a.Id,
    a.Score,
    a.Class,
    count(b.Score)+1 as Rank
FROM    Test a left join Test b 
    on a.Score>b.Score and a.Class=b.Class 
GROUP BY    a.Id,
        a.Score,
        a.Class;

Agregué los resultados cuando basé la puntuación en valores con decimales. Aparecen clasificaciones extrañas:

SET @prev_class = 0,@class = 0,@prev_score = 0,@score = 0,@rank3 = 0,@count=0;
UPDATE 1i SET
   Score_pq_raw_rank = (@prev_class := IFNULL(@class,0)),
   Score_pq_raw_rank = (@class := Profile_id),
   Score_pq_raw_rank = (@prev_score := IFNULL(@score,-1)),
   Score_pq_raw_rank = (@score := Score_pq_raw),
   Score_pq_raw_rank = (
   CASE WHEN @prev_class != @class THEN @rank3 := 1
        WHEN @prev_class = @class AND @prev_score = @score THEN @rank3
        WHEN @prev_class = @class AND @prev_score != @score THEN @rank3:[email protected][email protected]
   END),
   Score_pq_raw_rank = (CASE WHEN @prev_class != @class THEN @count := 0
        WHEN @prev_class = @class AND @prev_score = @score THEN @count := @count + 1
        WHEN @prev_class = @class AND @prev_score != @score THEN @count := 0
   END),
   Score_pq_raw_rank = @rank3
ORDER BY Profile_id ASC, Score_pq_raw DESC, Rowresult_id ASC;

+--------------+--------------+------------+-------------------+
| Rowresult_id | Score_pq_raw | Profile_id | Score_pq_raw_rank |
+--------------+--------------+------------+-------------------+
|            1 | 3.69054000   |          1 |                 1 |
|            2 | 0.10568000   |          1 |                 2 |
|            3 | -2.08058000  |          1 |                 3 |
|            4 | -2.07316000  |          1 |                 3 |
|            5 | -2.39066000  |          1 |                 3 |
|            6 | -10.23852000 |          2 |                 3 |
|            7 | -8.77718000  |          2 |                 2 |
|            8 | -7.38480000  |          2 |                 1 |
|            9 | -13.49128000 |          2 |                 4 |
|           10 | -19.36774000 |          2 |                 5 |
+--------------+--------------+------------+-------------------+

- Dev

Source
zh
Responder


2
  • aquí está la selección que le da sus rangos deseados.

    SELECT Id,Class,Score,
           @prev_class := IFNULL(@class,0),
           @class := Class,
           @prev_score := IFNULL(@score,-1),
           @score := Score,
    
           CASE WHEN @prev_class != @class THEN @rank1 := 1
                ELSE @rank1 := @rank1 + 1
           END as rank1,
           CASE WHEN @prev_class != @class THEN @rank2 := 1
                WHEN @prev_class = @class AND @prev_score = @score THEN @rank2
                WHEN @prev_class = @class AND @prev_score != @score THEN @rank2:[email protected]+1
           END as rank2,
           CASE WHEN @prev_class != @class THEN @rank3 := 1
                WHEN @prev_class = @class AND @prev_score = @score THEN @rank3
                WHEN @prev_class = @class AND @prev_score != @score THEN @rank3:[email protected][email protected]
           END as rank3,
           CASE WHEN @prev_class != @class THEN @count := 0
                WHEN @prev_class = @class AND @prev_score = @score THEN @count := @count + 1
                WHEN @prev_class = @class AND @prev_score != @score THEN @count := 0
           END as count
    FROM Test
    ORDER BY Class ASC, Score DESC, Id ASC;
    

    y aquí está la ACTUALIZACIÓN que hará ese trabajo, solo uso SET rank1 varias veces porque mysql no parece permitirle configurar @variables sin usar el resultado para algunas columnas...

    SET @prev_class = 0,@rank1 = 0,@score = 0,@prev_score = 0,@class =0,@rank2=0,@rank3 =0,@count=0;
    UPDATE Test SET
           rank1 = (@prev_class := IFNULL(@class,0)),
           rank1 = (@class := Class),
           rank1 = (@prev_score := IFNULL(@score,-1)),
           rank1 = (@score := Score),
           rank1 = (
           CASE WHEN @prev_class != @class THEN @rank1 := 1
                ELSE @rank1 := @rank1 + 1
           END),
           rank2 = (
           CASE WHEN @prev_class != @class THEN @rank2 := 1
                WHEN @prev_class = @class AND @prev_score = @score THEN @rank2
                WHEN @prev_class = @class AND @prev_score != @score THEN @rank2:[email protected]+1
           END),
           rank3 = (
           CASE WHEN @prev_class != @class THEN @rank3 := 1
                WHEN @prev_class = @class AND @prev_score = @score THEN @rank3
                WHEN @prev_class = @class AND @prev_score != @score THEN @rank3:[email protected][email protected]
           END),
           rank3 = (CASE WHEN @prev_class != @class THEN @count := 0
                WHEN @prev_class = @class AND @prev_score = @score THEN @count := @count + 1
                WHEN @prev_class = @class AND @prev_score != @score THEN @count := 0
           END),
           rank3 = @rank3
    ORDER BY Class ASC, Score DESC, Id ASC
    

    sqlfiddle

    Actualizar solo rango 1

    SET @prev_class = 0,@class = 0,@prev_score = 0,@score = 0,@rank1 = 0;
    UPDATE Test SET
           rank1 = (@prev_class := IFNULL(@class,0)),
           rank1 = (@class := Class),
           rank1 = (@prev_score := IFNULL(@score,-1)),
           rank1 = (@score := Score),
           rank1 = (
           CASE WHEN @prev_class != @class THEN @rank1 := 1
                ELSE @rank1 := @rank1 + 1
           END)
    ORDER BY Class ASC, Score DESC, Id ASC;
    

    Actualizar solo rango 2

    SET @prev_class = 0,@class = 0,@prev_score = 0,@score = 0,@rank2 = 0;
    UPDATE Test SET
           rank2 = (@prev_class := IFNULL(@class,0)),
           rank2 = (@class := Class),
           rank2 = (@prev_score := IFNULL(@score,-1)),
           rank2 = (@score := Score),
           rank2 = (
           CASE WHEN @prev_class != @class THEN @rank2 := 1
                WHEN @prev_class = @class AND @prev_score = @score THEN @rank2
                WHEN @prev_class = @class AND @prev_score != @score THEN @rank2:[email protected]+1
           END)
    ORDER BY Class ASC, Score DESC, Id ASC;
    

    Actualizar solo rango 3

    SET @prev_class = 0,@class = 0,@prev_score = 0,@score = 0,@rank3 = 0,@count=0;
    UPDATE Test SET
           rank3 = (@prev_class := IFNULL(@class,0)),
           rank3 = (@class := Class),
           rank3 = (@prev_score := IFNULL(@score,-1)),
           rank3 = (@score := Score),
           rank3 = (
           CASE WHEN @prev_class != @class THEN @rank3 := 1
                WHEN @prev_class = @class AND @prev_score = @score THEN @rank3
                WHEN @prev_class = @class AND @prev_score != @score THEN @rank3:[email protected][email protected]
           END),
           rank3 = (CASE WHEN @prev_class != @class THEN @count := 0
                WHEN @prev_class = @class AND @prev_score = @score THEN @count := @count + 1
                WHEN @prev_class = @class AND @prev_score != @score THEN @count := 0
           END),
           rank3 = @rank3
    ORDER BY Class ASC, Score DESC, Id ASC;