Функция ранга в MySQL

Мне нужно узнать ранг клиентов. Здесь я добавляю соответствующий стандартный SQL-запрос ANSI для моего требования. Пожалуйста, помогите мне преобразовать его в MySQL .



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

  FirstName, 

  Age,

  Gender 

FROM Person


есть ли функция, чтобы узнать ранг в MySQL?

527   9  

9 ответов:

одним из вариантов является использование переменной ранжирования, например:

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

The (SELECT @curRank := 0) часть позволяет инициализацию переменной, не требуя отдельного .

тест:

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

результат:

+------------+------+--------+------+
| 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  
2010-07-26 12:40:31

вот общее решение, которое сортирует таблицу на основе столбца и присваивает ранг; строки в связи присваиваются одинаковые ранги (для этого используется дополнительная переменная):

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

обратите внимание, что есть два оператора присваивания во втором WHEN предложения. Пример данных:

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

выход:

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

46  
2013-01-12 21:32:30

В то время как наиболее востребованный ответ ранжируется, он не разбивается, вы можете сделать самостоятельное соединение, чтобы также разделить все это:

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

Use Case

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

ответ:

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  
2014-09-10 13:36:16

настройка версии Даниэля для вычисления процентиля вместе с рангом. Также два человека с одинаковыми отметками получат одинаковый ранг. 

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

результаты запроса на выборку данных - 

+----+-------+------+---------------+---------------+-----------------+
| 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  
2012-01-18 18:26:57

комбинация ответа Даниэля и Салмана. Однако звание не дадут, так как продолжает существовать последовательность со связями . Вместо этого он пропускает ранг к следующему. Поэтому максимум всегда достигает количества строк.

    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;

схема и тестовый случай:

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

выход:

+------------+------+--------+------+--------------------------+-----------------+
| 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  
2014-08-29 08:25:47

@Sam, ваша точка зрения превосходна в концепции, но я думаю, что вы неправильно поняли, что говорят документы MySQL на ссылочной странице-или я неправильно понимаю: -) - и я просто хотел добавить это, чтобы, если кто-то чувствует себя некомфортно с ответом @Daniel'S, они будут более успокоены или, по крайней мере, копать немного глубже.

вы видите "@curRank: = @curRank + 1 Как ранг" внутри SELECT не является "одним оператором", это одна "атомарная" часть оператора, поэтому она должна быть безопасной.

документ, на который вы ссылаетесь, показывает примеры, где одна и та же пользовательская переменная в 2 (атомарных) частях инструкции, например, "SELECT @curRank, @curRank := @curRank + 1 AS rank".

можно утверждать, что @curRank используется дважды в ответе @Daniel: (1)" @curRank := @curRank + 1 AS rank "и (2)" (SELECT @curRank := 0) r " но поскольку второе использование является частью предложения FROM, я уверен, что оно гарантированно будет оценено первым; по существу делая это вторым и предшествующим утверждением.

на самом деле, на той же странице документов MySQL, на которую вы ссылались, вы увидите то же самое решение в комментариях-это может быть то, откуда @Daniel получил его; да, я знаю, что это комментарии, но это комментарии на официальной странице документов, и это имеет некоторый вес.

5  
2012-09-16 01:08:55

Если вы хотите ранжировать только одного человека, вы можете сделать следующее:

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

этот рейтинг соответствует функции oracle RANK (где, если у вас есть люди с одинаковым возрастом, они получают один и тот же ранг, а рейтинг после этого не является последовательным).

Это немного быстрее, чем использовать одно из вышеуказанных решений в подзапросе и выбирать из него, чтобы получить рейтинг одного человека.

Это может быть использовано для ранжирования всех, но это медленнее, чем выше решения. 

SELECT
  Age AS age_var,
(
  SELECT COUNT(Age) + 1
  FROM Person
  WHERE (Age < age_var)
 ) AS rank
 FROM Person
3  
2014-07-11 05:07:42

самое прямое решение для определения ранга данного значения-это подсчет количества значений до его. Предположим, что мы имеем следующие значения:

10 20 30 30 30 40
  • все 30 значения 3-й
  • все 40 значения 6-й (ранг) или 4-й (плотная ранг)

теперь вернемся к изначальному вопросу. Вот некоторые примеры данных который сортируется, как описано в OP (ожидаемые ранги добавляются справа):

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

вычислить RANK() OVER (PARTITION BY Gender ORDER BY Age) на Сара, вы можете использовать этот запрос:

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

вычислить RANK() OVER (PARTITION BY Gender ORDER BY Age) на все строк, вы можете использовать этот запрос:

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

и вот результат (объединенные значения добавляются справа):

+------+------+------------+    +-----------+-----+--------+
| 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  
2018-01-25 13:38:41

начиная с MySQL 8, вы можете, наконец, использовать оконные функции также в MySQL: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

ваш запрос может быть написан точно так же:

SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS `Partition by Gender`, 
  FirstName, 
  Age,
  Gender 
FROM Person
1  
2018-09-18 23:06:01

Comments

    Ничего не найдено.
Click here to cancel reply.