sql — функция ранга в MySQL
Мне нужно выяснить звание клиентов. Здесь я добавляю соответствующий стандартный запрос SQL ANSI для моего требования. Пожалуйста, помогите мне преобразовать его в MySQL.
SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
FirstName,
Age,
Gender
FROM Person
Есть ли какая-нибудь функция для определения ранга в MySQL?
Решение
Один из вариантов — использовать переменную ранжирования, например:
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)
Другие решения
Вот общее решение, которое присваивает строки плотный ранг над разделением. Он использует пользовательские переменные:
CREATE TABLE person (
id INT NOT NULL PRIMARY KEY,
firstname VARCHAR(10),
gender VARCHAR(1),
age INT
);
INSERT INTO person (id, firstname, gender, age) VALUES
(1, 'Adams', 'M', 33),
(2, 'Matt', 'M', 31),
(3, 'Grace', 'F', 25),
(4, 'Harry', 'M', 20),
(5, 'Scott', 'M', 30),
(6, 'Sarah', 'F', 30),
(7, 'Tony', 'M', 30),
(8, 'Lucy', 'F', 27),
(9, 'Zoe', 'F', 30),
(10, 'Megan', 'F', 26),
(11, 'Emily', 'F', 20),
(12, 'Peter', 'M', 20),
(13, 'John', 'M', 21),
(14, 'Kate', 'F', 35),
(15, 'James', 'M', 32),
(16, 'Cole', 'M', 25),
(17, 'Dennis', 'M', 27),
(18, 'Smith', 'M', 35),
(19, 'Zack', 'M', 35),
(20, 'Jill', 'F', 25);
SELECT person.*, @rank := CASE
WHEN @partval = gender AND @rankval = age THEN @rank
WHEN @partval = gender AND (@rankval := age) IS NOT NULL THEN @rank + 1
WHEN (@partval := gender) IS NOT NULL AND (@rankval := age) IS NOT NULL THEN 1
END AS rnk
FROM person, (SELECT @rank := NULL, @partval := NULL, @rankval := NULL) AS x
ORDER BY gender, age;
Обратите внимание, что назначения переменных размещаются внутри CASE выражение. Это (в теории) заботится о порядке оценки вопроса. IS NOT NULL добавлена для обработки преобразования типов данных и короткого замыкания.
PS: Он может быть легко преобразован в номер строки в разделе путем удаления всех условий, которые проверяют связь.
| id | firstname | gender | age | rank |
|----|-----------|--------|-----|------|
| 11 | Emily | F | 20 | 1 |
| 20 | Jill | F | 25 | 2 |
| 3 | Grace | F | 25 | 2 |
| 10 | Megan | F | 26 | 3 |
| 8 | Lucy | F | 27 | 4 |
| 6 | Sarah | F | 30 | 5 |
| 9 | Zoe | F | 30 | 5 |
| 14 | Kate | F | 35 | 6 |
| 4 | Harry | M | 20 | 1 |
| 12 | Peter | M | 20 | 1 |
| 13 | John | M | 21 | 2 |
| 16 | Cole | M | 25 | 3 |
| 17 | Dennis | M | 27 | 4 |
| 7 | Tony | M | 30 | 5 |
| 5 | Scott | M | 30 | 5 |
| 2 | Matt | M | 31 | 6 |
| 15 | James | M | 32 | 7 |
| 1 | Adams | M | 33 | 8 |
| 18 | Smith | M | 35 | 9 |
| 19 | Zack | M | 35 | 9 |
В то время как ответ с наибольшим количеством голосов занимает место, он не разбивается на разделы, вы можете выполнить самостоятельное присоединение, чтобы разделить все это также:
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 говорят на указанной странице — или я неправильно понял 🙂 — и я просто хотел добавить это, чтобы, если кто-то чувствует себя некомфортно с @ Ответ Даниэля, они будут более уверены или, по крайней мере, будут копать глубже.
Вы видите, что «@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значения считаются шестые (ранг) или четвёртая (плотный ранг)
Теперь вернемся к первоначальному вопросу. Вот некоторые примеры данных, которые отсортированы, как описано в 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 |
+------+------+------------+ +-----------+-----+--------+
Начиная с 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