Funkcja Rank w MySQL

Oto ogólne rozwiązanie, które sortuje tabelę na podstawie kolumny i przypisuje rangę; rzędom z powiązaniami przypisano tę samą rangę (używa do tego celu dodatkowej zmiennej):

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

Zauważ, że w drugiej WHEN klauzuli znajdują się dwa polecenia przypisania. Przykładowe dane:

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);

Wyjście:

+------+-------------+------+
| 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 Fiddle

Podczas gdy najbardziej upvoted odpowiedzi szereguje, to nie partycji, można zrobić self Join, aby uzyskać całą rzecz partycjonowane również:

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

Przypadek Użycia

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');

ODPOWIEDŹ :

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

Zmiana wersji Daniela, aby obliczyć percentyl wraz z rangą. Również dwie osoby z tymi samymi ocenami otrzymają tę samą 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

Wyniki zapytania o przykładowe dane -

+----+-------+------+---------------+---------------+-----------------+
| 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 |
+----+-------+------+---------------+---------------+-----------------+

Połączenie odpowiedzi Daniela i Salmana. Jakkolwiek ranga nie daje jak kontynuuje sekwencje z krawatami istnieja . Zamiast tego przeskakuje rangę do następnego. Tak więc maksymalna liczba wierszy zawsze osiąga.

    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;

Schemat i przypadek testowy:

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');

Wyjście:

+------------+------+--------+------+--------------------------+-----------------+
| 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 |
+------------+------+--------+------+--------------------------+-----------------+

@Sam, twój punkt widzenia jest doskonały w koncepcji, ale myślę, że źle zrozumiałeś to, co dokumenty MySQL mówią na odnośnej stronie-lub źle zrozumiałem: -) - i chciałem tylko dodać to, że jeśli ktoś czuje się nieswojo z odpowiedzią @Daniel będą bardziej uspokojeni lub przynajmniej kopać trochę głębiej.

Widzisz "@ curRank: = @curRank + 1 AS rank "wewnątrz SELECT nie jest "jednym oświadczeniem", jest to jedna "atomowa" część oświadczenia, więc powinno być bezpieczne.

The dokument, do którego się odwołujesz, pokazuje przykłady, w których ta sama zmienna zdefiniowana przez użytkownika w 2 (atomowych) częściach instrukcji, na przykład "SELECT @curRank, @curRank: = @curRank + 1 AS rank".

Można by argumentować, że @curRank jest używany dwa razy w odpowiedzi @ Daniel: (1) "@ curRank: = @ curRank + 1 AS rank" i (2) "(SELECT @curRank := 0) r", ale ponieważ drugie użycie jest częścią klauzuli FROM, jestem prawie pewien, że jest gwarantowane, że zostanie ocenione jako pierwsze; zasadniczo czyniąc go drugim, a / align = "left" /

W rzeczywistości, na tej samej stronie dokumentów MySQL, o której wspomniałeś, zobaczysz to samo rozwiązanie w komentarzach - może to być skąd @ Daniel je wziął; tak, Wiem, że to komentarze, ale to komentarze na oficjalnej stronie dokumentów i to ma pewną wagę.

Jeśli chcesz rangować tylko jedną osobę, możesz wykonać następujące czynności:

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

Ten ranking odpowiada funkcji Oracle RANK (gdzie jeśli masz osoby w tym samym wieku, otrzymują tę samą rangę, a ranking po nim nie jest kolejny).

Jest to trochę szybsze niż użycie jednego z powyższych rozwiązań w zapytaniu podrzędnym i wybranie z niego rankingu jednej osoby.

To może być użyte do rankingu wszystkich, ale jest wolniejsze niż powyższe rozwiązania.

SELECT
  Age AS age_var,
(
  SELECT COUNT(Age) + 1
  FROM Person
  WHERE (Age < age_var)
 ) AS rank
 FROM Person

Najbardziej prostym rozwiązaniem do określenia rangi danej wartości jest policzenie liczby wartości przed. Załóżmy, że mamy następujące wartości:

10 20 30 30 30 40

• wszystkie 30 wartości są uważane za 3.
• wszystkie 40 wartości są uważane za 6th (ranga) lub 4th (ranga gęsta)

+------+-----------+------+--------+    +------+------------+
| 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 |
+------+-----------+------+--------+    +------+------------+

Aby obliczyć RANK() OVER (PARTITION BY Gender ORDER BY Age) dla Sarah, możesz użyć tego zapytania:

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 |
+------+------------+

Aby obliczyć RANK() OVER (PARTITION BY Gender ORDER BY Age) dla Wszystkie wiersze można użyć tego zapytania:

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

I oto wynik (połączone wartości są dodawane po prawej stronie):

+------+------+------------+    +-----------+-----+--------+
| 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      |
+------+------+------------+    +-----------+-----+--------+

Począwszy od MySQL 8, możesz wreszcie korzystać z funkcji okien również w MySQL: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

Twoje zapytanie można napisać dokładnie w ten sam sposób:

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