大部分數據庫都提供了窗口函數，比如RANK,ROW_NUMBER等等。 MySQL 這方面沒有直接提供，但是可以變相的實現，我以前寫了row_number 的實現，今天有時間把 rank 的實現貼出來。
《Python核心編程 第二版》.(Wesley J. Chun ).[高清PDF中文版] http://www.linuxidc.com/Linux/2013-06/85425.htm
《Python開發技術詳解》.( 周偉,宗傑).[高清PDF掃描版+隨書視頻+代碼] http://www.linuxidc.com/Linux/2013-11/92693.htm
Python腳本獲取Linux系統信息 http://www.linuxidc.com/Linux/2013-08/88531.htm
在Ubuntu下用Python搭建桌面算法交易研究環境 http://www.linuxidc.com/Linux/2013-11/92534.htm
這裡，我用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)
更多詳情見請繼續閱讀下一頁的精彩內容： http://www.linuxidc.com/Linux/2014-06/102567p2.htm
