Функция ранга в MySQL
Мне нужно выяснить звание клиентов. Здесь я добавляю соответствующий стандартный запрос SQL ANSI для моего требования. Пожалуйста, помогите мне преобразовать его в MySQL .
SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
FirstName,
Age,
Gender
FROM Person
Есть ли какая-нибудь функция для определения ранга в MySQL?
14 ответов
Один из вариантов - использовать переменную ранжирования, например:
SELECT first_name,
age,
gender,
@curRank := @curRank + 1 AS rank
FROM person p, (SELECT @curRank := 0) r
ORDER BY age;
(SELECT @curRank := 0) часть позволяет инициализировать переменную, не требуя отдельного SET команда.
Прецедент:
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)
Вот общее решение, которое сортирует таблицу по столбцу и присваивает ранг; строки со связями имеют одинаковый ранг (для этого используется дополнительная переменная):
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 |
+------+-------------+------+
Несмотря на то, что ответ с наибольшим количеством голосов занимает место, он не разбивается на разделы, вы можете выполнить самостоятельное присоединение, чтобы разделить все это также:
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
Случай использования
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
Доработка версии Даниила для расчета процентиля и ранга. Также два человека с одинаковыми оценками получат одинаковое звание.
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 |
+----+-------+------+---------------+---------------+-----------------+
Сочетание ответа Дэниела и Салмана. Однако звание не дадут, поскольку продолжается последовательность с галстуками. Вместо этого он пропускает звание до следующего. Таким образом, максимум всегда достигает количества строк.
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 |
+------------+------+--------+------+--------------------------+-----------------+
Начиная с 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
@Sam, твоя точка зрения отлична в концепции, но я думаю, что ты неправильно понял, что говорят документы MySQL на указанной странице - или я неправильно понял:-) - и я просто хотел добавить это, чтобы, если кто-то чувствует себя некомфортно с @ Ответ Даниэля, они будут более уверены или, по крайней мере, будут копать глубже.
Вы видите, что "@curRank:= @curRank + 1 AS rank" внутри SELECT - это не "одна инструкция", а одна "атомарная" часть инструкции, поэтому она должна быть безопасной.
В документе, на который вы ссылаетесь, приводятся примеры, где одна и та же пользовательская переменная в 2 (атомарных) частях оператора, например, "SELECT @curRank, @curRank:= @curRank + 1 AS rank".
Можно утверждать, что @curRank используется дважды в ответе @ Дэниела: (1) "@curRank:= @curRank + 1 AS rank" и (2) "(SELECT @curRank:= 0) r", но после второго использование является частью предложения FROM, я уверен, что он гарантированно будет оценен первым; по сути, сделав это вторым и предшествующим заявлением.
Фактически, на той же самой странице документации MySQL, на которую вы ссылались, вы увидите то же решение в комментариях - это может быть то, откуда @Daniel получил его; да, я знаю, что это комментарии, но это комментарии на официальной странице документации, и это имеет некоторый вес.
Наиболее простым решением для определения ранга данного значения является подсчет количества значений перед ним. Предположим, у нас есть следующие значения:
10 20 30 30 30 40
- Все
30значения считаются третьими - Все
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 |
+------+------+------------+ +-----------+-----+--------+
Если вы хотите оценить только одного человека, вы можете сделать следующее:
SELECT COUNT(Age) + 1
FROM PERSON
WHERE(Age < age_to_rank)
Это ранжирование соответствует функции оракула RANK (где, если у вас есть люди одного возраста, они получают одинаковый ранг, а ранжирование после этого непоследовательное).
Это немного быстрее, чем использовать одно из приведенных выше решений в подзапросе и выбирать из него, чтобы получить рейтинг одного человека.
Это может быть использовано для ранжирования всех, но это медленнее, чем приведенные выше решения.
SELECT
Age AS age_var,
(
SELECT COUNT(Age) + 1
FROM Person
WHERE (Age < age_var)
) AS rank
FROM Person
Чтобы избежать " однако" в ответе Эрандака в сочетании с ответами Даниэля и Салмана, можно использовать один из следующих "обходных путей раздела"
SELECT customerID, myDate
-- partition ranking works only with CTE / from MySQL 8.0 on
, RANK() OVER (PARTITION BY customerID ORDER BY dateFrom) AS rank,
-- Erandac's method in combination of Daniel's and Salman's
-- count all items in sequence, maximum reaches row count.
, IF(customerID=@_lastRank, @_curRank:=@_curRank, @_curRank:=@_sequence+1) AS sequenceRank
, @_sequence:=@_sequence+1 as sequenceOverAll
-- Dense partition ranking, works also with MySQL 5.7
-- remember to set offset values in from clause
, IF(customerID=@_lastRank, @_nxtRank:=@_nxtRank, @_nxtRank:=@_nxtRank+1 ) AS partitionRank
, IF(customerID=@_lastRank, @_overPart:=@_overPart+1, @_overPart:=1 ) AS partitionSequence
, @_lastRank:=customerID
FROM myCustomers,
(SELECT @_curRank:=0, @_sequence:=0, @_lastRank:=0, @_nxtRank:=0, @_overPart:=0 ) r
ORDER BY customerID, myDate
Ранжирование разделов в третьем варианте в этом фрагменте кода возвращает непрерывные ранжирующие числа. это приведет к структуре данных, аналогичной rank() over partition by результат. В качестве примера см. Ниже. В частности, partitionSequence всегда будет начинаться с 1 для каждого нового partitionRank, используя этот метод:
customerID myDate sequenceRank (Erandac)
| sequenceOverAll
| | partitionRank
| | | partitionSequence
| | | | lastRank
... lines ommitted for clarity
40 09.11.2016 11:19 1 44 1 44 40
40 09.12.2016 12:08 1 45 1 45 40
40 09.12.2016 12:08 1 46 1 46 40
40 09.12.2016 12:11 1 47 1 47 40
40 09.12.2016 12:12 1 48 1 48 40
40 13.10.2017 16:31 1 49 1 49 40
40 15.10.2017 11:00 1 50 1 50 40
76 01.07.2015 00:24 51 51 2 1 76
77 04.08.2014 13:35 52 52 3 1 77
79 15.04.2015 20:25 53 53 4 1 79
79 24.04.2018 11:44 53 54 4 2 79
79 08.10.2018 17:37 53 55 4 3 79
117 09.07.2014 18:21 56 56 5 1 117
119 26.06.2014 13:55 57 57 6 1 119
119 02.03.2015 10:23 57 58 6 2 119
119 12.10.2015 10:16 57 59 6 3 119
119 08.04.2016 09:32 57 60 6 4 119
119 05.10.2016 12:41 57 61 6 5 119
119 05.10.2016 12:42 57 62 6 6 119
...
select id,first_name,gender,age,
rank() over(partition by gender order by age) rank_g
from person
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,'AKSH',32,'M');
В настройке ответа Дэниела некоторые версии MySQL выдают ошибки в операторе IF.
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;
Поэтому мне пришлось обернуть проверку условия IF в Ranking фигурными скобками.
Это всегда вычисляет рейтинг, но не Dense_rank
set @insert_rank = -2;
SELECT c_updated_time,
@insert_rank := IF(true = true, @insert_rank + 1, 1)
from my_table limit 5;
Эта строка напечатает выбранный выше запрос;
select @v_query1;
show variables like '%read_only%';
SHOW GRANTS FOR 'USERNAME'@'localhost';
1660616f-f908-4d7e-ab60-c00b77adaf96
select count(*) from INFORMATION_SCHEMA.COLUMNS;
SHOW DATABASES WHERE `Database` IN ('information_schema', 'mysql', 'performance_schema');
use INFORMATION_SCHEMA;
show tables;
desc INFORMATION_SCHEMA.COLUMNS;
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%columnname%' and TABLE_SCHEMA like '%Database%' ORDER BY TABLE_NAME ,COLUMN_NAME;
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName' and TABLE_SCHEMA <> 'test' ORDER BY TABLE_NAME ,COLUMN_NAME;
Для столбца Дата
SELECT CURRENT_DATE() AS current_date;
Для столбца отметки времени
select now();
SELECT FirstName,Age,Gender, RANK() OVER (partition by Gender order by Age desc) AS 'Partition by Gender' FROM Person
- вы можете использовать asc/desc в зависимости от того, какой порядок вы хотите, по возрастанию или по убыванию