大部分数据库都提供了窗口函数，比如RANK,ROW_NUMBER等等。 MySQL 这方面没有直接提供，但是可以变相的实现，我以前写了row_number 的实现，今天有时间把 rank 的实现贴出来。
这里，我用MySQL 以及Python 分别实现了rank 窗口函数。
原始表信息：
t_girl=# \d group_concat;
Table "ytt.group_concat"
Column | Type | Modifiers
----------+-----------------------+-----------
rank | integer |
username | character varying(20) |
表数据
t_girl=# select * from group_concat;
rank | username
------+----------
100 | Lucy
127 | Lucy
146 | Lucy
137 | Lucy
104 | Lucy
121 | Lucy
136 | Lily
100 | Lily
100 | Lily
105 | Lily
136 | Lily
149 | ytt
116 | ytt
116 | ytt
149 | ytt
106 | ytt
117 | ytt
(17 rows)
Time: 0.638 ms
PostgreSQL 的rank 窗口函数示例：
t_girl=# select username,rank,rank() over(partition by username order by rank desc) as rank_cnt from group_concat;
username | rank | rank_cnt
----------+------+----------
Lily | 136 | 1
Lily | 136 | 1
Lily | 105 | 3
Lily | 100 | 4
Lily | 100 | 4
Lucy | 146 | 1
Lucy | 137 | 2
Lucy | 127 | 3
Lucy | 121 | 4
Lucy | 104 | 5
Lucy | 100 | 6
ytt | 149 | 1
ytt | 149 | 1
ytt | 117 | 3
ytt | 116 | 4
ytt | 116 | 4
ytt | 106 | 6
(17 rows)
Time: 131.150 ms
MySQL 提供了group_concat 聚合函数可以变相的实现：
mysql>
select a.username, a.rank, find_in_set(a.rank,b.rank_gp) as rank_cnt
from group_concat as a ,
(select username,group_concat(rank order by rank desc separator ',') as rank_gp from group_concat group by username
) b
where a.username = b.username order by a.username asc,a.rank desc;
+----------+------+----------+
| username | rank | rank_cnt |
+----------+------+----------+
| Lily | 136 | 1 |
| Lily | 136 | 1 |
| Lily | 105 | 3 |
| Lily | 100 | 4 |
| Lily | 100 | 4 |
| Lucy | 146 | 1 |
| Lucy | 137 | 2 |
| Lucy | 127 | 3 |
| Lucy | 121 | 4 |
| Lucy | 104 | 5 |
| Lucy | 100 | 6 |
| ytt | 149 | 1 |
| ytt | 149 | 1 |
| ytt | 117 | 3 |
| ytt | 116 | 4 |
| ytt | 116 | 4 |
| ytt | 106 | 6 |
+----------+------+----------+
17 rows in set (0.02 sec)