Наиболее прямолинейное решение для определения ранга заданного значения - подсчет количества значений до . Предположим, что мы имеем следующие значения:
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)
для Sarah , вы можете использовать этот запрос:
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)
для строк All , вы можете использовать этот запрос:
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 |
+------+------+------------+ +-----------+-----+--------+