Fonction de rang dans MySQL

Je dois trouver le rang des clients. Ici, j'ajoute la requête SQL Standard ANSI correspondante pour mon exigence. Aidez-moi à le convertir en MySQL .

SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], 
  FirstName, 
  Age,
  Gender 
FROM Person

Y a-t-il une fonction pour trouver le rang dans MySQL?

135
demandé sur Lukas Eder 2010-07-26 13:35:48

9 réponses

Une option consiste à utiliser une variable de classement, telle que la suivante:

SELECT    first_name,
          age,
          gender,
          @curRank := @curRank + 1 AS rank
FROM      person p, (SELECT @curRank := 0) r
ORDER BY  age;

La partie (SELECT @curRank := 0) permet l'initialisation de la variable sans nécessiter une commande SET distincte.

Cas de Test:

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');

Résultat:

+------------+------+--------+------+
| first_name | age  | gender | rank |
+------------+------+--------+------+
| Kathy      |   18 | F      |    1 |
| Jane       |   20 | F      |    2 |
| Nick       |   22 | M      |    3 |
| Bob        |   25 | M      |    4 |
| Anne       |   25 | F      |    5 |
| Jack       |   30 | M      |    6 |
| Bill       |   32 | M      |    7 |
| Steve      |   36 | M      |    8 |
+------------+------+--------+------+
8 rows in set (0.02 sec)
234
répondu Daniel Vassallo 2010-07-26 09:40:31

Voici une solution générique qui trie une table en fonction d'une colonne et attribue un rang; les lignes avec des liens sont affectées au même rang (utilise une variable supplémentaire à cet effet):

SET @prev_value = NULL;
SET @rank_count = 0;
SELECT id, rank_column, CASE
    WHEN @prev_value = rank_column THEN @rank_count
    WHEN @prev_value := rank_column THEN @rank_count := @rank_count + 1
END AS rank
FROM rank_table
ORDER BY rank_column

Notez qu'il y a deux instructions d'affectation dans la deuxième clause WHEN. Exemple de données:

CREATE TABLE rank_table(id INT, rank_column INT);
INSERT INTO rank_table (id, rank_column) VALUES
(1, 10),
(2, 20),
(3, 30),
(4, 30),
(5, 30),
(6, 40),
(7, 50),
(8, 50),
(9, 50);

Sortie:

+------+-------------+------+
| id   | rank_column | rank |
+------+-------------+------+
|    1 |          10 |    1 |
|    2 |          20 |    2 |
|    3 |          30 |    3 |
|    4 |          30 |    3 |
|    5 |          30 |    3 |
|    6 |          40 |    4 |
|    7 |          50 |    5 |
|    8 |          50 |    5 |
|    9 |          50 |    5 |
+------+-------------+------+

SQL Violon

46
répondu Salman A 2013-01-12 19:32:30

Alors que la réponse la plus votée se classe, elle ne partitionne pas, vous pouvez faire une auto-jointure pour que le tout soit partitionné aussi:

SELECT    a.first_name,
      a.age,
      a.gender,
        count(b.age)+1 as rank
FROM  person a left join person b on a.age>b.age and a.gender=b.gender 
group by  a.first_name,
      a.age,
      a.gender

Cas D'Utilisation De

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');

Réponse:

Bill    32  M   4
Bob     25  M   2
Jack    30  M   3
Nick    22  M   1
Steve   36  M   5
Anne    25  F   3
Jane    20  F   2
Kathy   18  F   1
40
répondu Rahul Agarwal 2014-09-10 10:36:16

Un tweak de la version de Daniel pour calculer le percentile avec le rang. Aussi deux personnes avec les mêmes marques obtiendront le même rang.

set @totalStudents = 0;
select count(*) into @totalStudents from marksheets;
SELECT id, score, @curRank := IF(@prevVal=score, @curRank, @studentNumber) AS rank, 
@percentile := IF(@prevVal=score, @percentile, (@totalStudents - @studentNumber + 1)/(@totalStudents)*100),
@studentNumber := @studentNumber + 1 as studentNumber, 
@prevVal:=score
FROM marksheets, (
SELECT @curRank :=0, @prevVal:=null, @studentNumber:=1, @percentile:=100
) r
ORDER BY score DESC

Résultats de la requête pour un exemple de données -

+----+-------+------+---------------+---------------+-----------------+
| id | score | rank | percentile    | studentNumber | @prevVal:=score |
+----+-------+------+---------------+---------------+-----------------+
| 10 |    98 |    1 | 100.000000000 |             2 |              98 |
|  5 |    95 |    2 |  90.000000000 |             3 |              95 |
|  6 |    91 |    3 |  80.000000000 |             4 |              91 |
|  2 |    91 |    3 |  80.000000000 |             5 |              91 |
|  8 |    90 |    5 |  60.000000000 |             6 |              90 |
|  1 |    90 |    5 |  60.000000000 |             7 |              90 |
|  9 |    84 |    7 |  40.000000000 |             8 |              84 |
|  3 |    83 |    8 |  30.000000000 |             9 |              83 |
|  4 |    72 |    9 |  20.000000000 |            10 |              72 |
|  7 |    60 |   10 |  10.000000000 |            11 |              60 |
+----+-------+------+---------------+---------------+-----------------+
23
répondu Mukesh Soni 2012-01-18 16:26:57

Combinaison de la réponse de Daniel et de Salman. Cependant le rang ne donnera pas comme continue la séquence avec les liens existe . Au lieu de cela il saute le rang suivant. Donc, le maximum atteint toujours le nombre de lignes.

    SELECT    first_name,
              age,
              gender,
              IF(age=@_last_age,@curRank:=@curRank,@curRank:=@_sequence) AS rank,
              @_sequence:=@_sequence+1,@_last_age:=age
    FROM      person p, (SELECT @curRank := 1, @_sequence:=1, @_last_age:=0) r
    ORDER BY  age;

Schéma et cas de Test:

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');
INSERT INTO person VALUES (9, 'Kamal', 25, 'M');
INSERT INTO person VALUES (10, 'Saman', 32, 'M');

Sortie:

+------------+------+--------+------+--------------------------+-----------------+
| first_name | age  | gender | rank | @_sequence:=@_sequence+1 | @_last_age:=age |
+------------+------+--------+------+--------------------------+-----------------+
| Kathy      |   18 | F      |    1 |                        2 |              18 |
| Jane       |   20 | F      |    2 |                        3 |              20 |
| Nick       |   22 | M      |    3 |                        4 |              22 |
| Kamal      |   25 | M      |    4 |                        5 |              25 |
| Anne       |   25 | F      |    4 |                        6 |              25 |
| Bob        |   25 | M      |    4 |                        7 |              25 |
| Jack       |   30 | M      |    7 |                        8 |              30 |
| Bill       |   32 | M      |    8 |                        9 |              32 |
| Saman      |   32 | M      |    8 |                       10 |              32 |
| Steve      |   36 | M      |   10 |                       11 |              36 |
+------------+------+--------+------+--------------------------+-----------------+
13
répondu erandac 2014-08-29 05:25:47

@Sam, votre point est excellent dans le concept, mais je pense que vous avez mal compris ce que disent les docs MySQL sur la page référencée-ou je comprends mal : -) - et je voulais juste ajouter ceci pour que si quelqu'un se sent mal à l'aise avec la réponse de @Daniel, il sera plus rassuré ou au moins creuser un peu

Vous voyez le "@curRank: = @ curRank + 1 as rank " à l'intérieur de la sélection n'est pas "une instruction", c'est une partie "atomique" de l'instruction donc elle devrait être sûre.

Le le document que vous référencez montre des exemples où la même variable définie par l'utilisateur dans 2 parties (atomiques) de l'instruction, par exemple, "SELECT @curRank, @curRank := @curRank + 1 as rank".

On pourrait soutenir que @curRank est utilisé deux fois dans la réponse de @Daniel: (1) Le "@curRank := @curRank + 1 as rank" et(2) Le "(SELECT @curRank := 0) r" mais puisque la deuxième utilisation fait partie de la clause FROM, je suis à peu près sûr qu'il est garanti d'être évalué en premier; essentiellement en précédent, déclaration.

En fait, sur cette même page de docs MySQL que vous avez référencée, vous verrez la même solution dans les commentaires - cela pourrait être d'où @Daniel l'a obtenue; Oui, je sais que ce sont les commentaires mais ce sont des commentaires sur la page officielle de docs et cela a un certain poids.

5
répondu David Husnian 2012-09-15 22:08:55

Si vous voulez classer une seule personne, vous pouvez faire ce qui suit:

SELECT COUNT(Age) + 1
 FROM PERSON
WHERE(Age < age_to_rank)

Ce classement correspond à la fonction Oracle RANK (où si vous avez des personnes du même âge, elles obtiennent le même rang, et le classement après cela n'est pas consécutif).

C'est un peu plus rapide que d'utiliser l'une des solutions ci-dessus dans une sous-requête et de la sélection de qu'obtenir le classement d'une personne.

Cela peut être utilisé pour classer tout le monde, mais c'est plus lent que les solutions ci-dessus.

SELECT
  Age AS age_var,
(
  SELECT COUNT(Age) + 1
  FROM Person
  WHERE (Age < age_var)
 ) AS rank
 FROM Person
3
répondu Sam Kidman 2014-07-11 02:07:42

La solution la plus simple pour déterminer le rang d'une valeur donnée est de compter le nombre de valeurs avant. Supposons que nous ayons les valeurs suivantes:

10 20 30 30 30 40
  • toutes les valeurs 30 sont considérées 3rd
  • toutes les valeurs 40 sont considérées comme 6e (rang) ou 4e (rang dense)

Revenons maintenant à la question initiale. Voici quelques exemples de données triées comme décrit dans OP (les rangs attendus sont ajoutés sur le droit):

+------+-----------+------+--------+    +------+------------+
| id   | firstname | age  | gender |    | rank | dense_rank |
+------+-----------+------+--------+    +------+------------+
|   11 | Emily     |   20 | F      |    |    1 |          1 |
|    3 | Grace     |   25 | F      |    |    2 |          2 |
|   20 | Jill      |   25 | F      |    |    2 |          2 |
|   10 | Megan     |   26 | F      |    |    4 |          3 |
|    8 | Lucy      |   27 | F      |    |    5 |          4 |
|    6 | Sarah     |   30 | F      |    |    6 |          5 |
|    9 | Zoe       |   30 | F      |    |    6 |          5 |
|   14 | Kate      |   35 | F      |    |    8 |          6 |
|    4 | Harry     |   20 | M      |    |    1 |          1 |
|   12 | Peter     |   20 | M      |    |    1 |          1 |
|   13 | John      |   21 | M      |    |    3 |          2 |
|   16 | Cole      |   25 | M      |    |    4 |          3 |
|   17 | Dennis    |   27 | M      |    |    5 |          4 |
|    5 | Scott     |   30 | M      |    |    6 |          5 |
|    7 | Tony      |   30 | M      |    |    6 |          5 |
|    2 | Matt      |   31 | M      |    |    8 |          6 |
|   15 | James     |   32 | M      |    |    9 |          7 |
|    1 | Adams     |   33 | M      |    |   10 |          8 |
|   18 | Smith     |   35 | M      |    |   11 |          9 |
|   19 | Zack      |   35 | M      |    |   11 |          9 |
+------+-----------+------+--------+    +------+------------+

Pour calculer RANK() OVER (PARTITION BY Gender ORDER BY Age) pour Sarah, vous pouvez utiliser cette requête:

SELECT COUNT(id) + 1 AS rank, COUNT(DISTINCT age) + 1 AS dense_rank
FROM testdata
WHERE gender = (SELECT gender FROM testdata WHERE id = 6)
AND age < (SELECT age FROM testdata WHERE id = 6)

+------+------------+
| rank | dense_rank |
+------+------------+
|    6 |          5 |
+------+------------+

Pour calculer RANK() OVER (PARTITION BY Gender ORDER BY Age) pour Tous lignes, vous pouvez utiliser la requête suivante:

SELECT testdata.id, COUNT(lesser.id) + 1 AS rank, COUNT(DISTINCT lesser.age) + 1 AS dense_rank
FROM testdata
LEFT JOIN testdata AS lesser ON lesser.age < testdata.age AND lesser.gender = testdata.gender
GROUP BY testdata.id

Et voici le résultat (les valeurs jointes sont ajoutées à droite):

+------+------+------------+    +-----------+-----+--------+
| id   | rank | dense_rank |    | firstname | age | gender |
+------+------+------------+    +-----------+-----+--------+
|   11 |    1 |          1 |    | Emily     |  20 | F      |
|    3 |    2 |          2 |    | Grace     |  25 | F      |
|   20 |    2 |          2 |    | Jill      |  25 | F      |
|   10 |    4 |          3 |    | Megan     |  26 | F      |
|    8 |    5 |          4 |    | Lucy      |  27 | F      |
|    6 |    6 |          5 |    | Sarah     |  30 | F      |
|    9 |    6 |          5 |    | Zoe       |  30 | F      |
|   14 |    8 |          6 |    | Kate      |  35 | F      |
|    4 |    1 |          1 |    | Harry     |  20 | M      |
|   12 |    1 |          1 |    | Peter     |  20 | M      |
|   13 |    3 |          2 |    | John      |  21 | M      |
|   16 |    4 |          3 |    | Cole      |  25 | M      |
|   17 |    5 |          4 |    | Dennis    |  27 | M      |
|    5 |    6 |          5 |    | Scott     |  30 | M      |
|    7 |    6 |          5 |    | Tony      |  30 | M      |
|    2 |    8 |          6 |    | Matt      |  31 | M      |
|   15 |    9 |          7 |    | James     |  32 | M      |
|    1 |   10 |          8 |    | Adams     |  33 | M      |
|   18 |   11 |          9 |    | Smith     |  35 | M      |
|   19 |   11 |          9 |    | Zack      |  35 | M      |
+------+------+------------+    +-----------+-----+--------+
3
répondu Salman A 2018-01-25 11:38:41

À partir de MySQL 8, Vous pouvez enfin utiliser les fonctions de fenêtre aussi dans MySQL: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

Votre requête peut être écrite exactement de la même manière:

SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS `Partition by Gender`, 
  FirstName, 
  Age,
  Gender 
FROM Person
1
répondu Lukas Eder 2018-09-18 20:06:01