大部分數據庫都提供了窗口函數，比如RANK,ROW_NUMBER等等。 MySQL 這方面沒有直接提供，但是可以變相的實現，我以前寫了row_number 的實現，今天有時間把 rank 的實現貼出來。
這裡，我用MySQL 以及Python 分別實現了rank 窗口函數。
原始表信息：
[sql] view plaincopyprint?01.t_girl=# \d group_concat;
02. Table "ytt.group_concat"
03. Column | Type | Modifiers
04.----------+-----------------------+-----------
05. rank | integer |
06. username | character varying(20) |
t_girl=# \d group_concat;
Table "ytt.group_concat"
Column | Type | Modifiers
----------+-----------------------+-----------
rank | integer |
username | character varying(20) |
表數據
[sql] view plaincopyprint?01.t_girl=# select * from group_concat;
02. rank | username
03.------+----------
04. 100 | Lucy
05. 127 | Lucy
06. 146 | Lucy
07. 137 | Lucy
08. 104 | Lucy
09. 121 | Lucy
10. 136 | Lily
11. 100 | Lily
12. 100 | Lily
13. 105 | Lily
14. 136 | Lily
15. 149 | ytt
16. 116 | ytt
17. 116 | ytt
18. 149 | ytt
19. 106 | ytt
20. 117 | ytt
21.(17 rows)
22.
23.
24.Time: 0.638 ms
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 窗口函數示例：
[sql] view plaincopyprint?01.t_girl=# select username,rank,rank() over(partition by username order by rank desc) as rank_cnt from group_concat;
02. username | rank | rank_cnt
03.----------+------+----------
04. Lily | 136 | 1
05. Lily | 136 | 1
06. Lily | 105 | 3
07. Lily | 100 | 4
08. Lily | 100 | 4
09. Lucy | 146 | 1
10. Lucy | 137 | 2
11. Lucy | 127 | 3
12. Lucy | 121 | 4
13. Lucy | 104 | 5
14. Lucy | 100 | 6
15. ytt | 149 | 1
16. ytt | 149 | 1
17. ytt | 117 | 3
18. ytt | 116 | 4
19. ytt | 116 | 4
20. ytt | 106 | 6
21.(17 rows)
22.
23.
24.Time: 131.150 ms
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 聚合函數可以變相的實現：
[sql] view plaincopyprint?01.mysql>
02.select a.username, a.rank, find_in_set(a.rank,b.rank_gp) as rank_cnt
03.from group_concat as a ,
04.(select username,group_concat(rank order by rank desc separator ',') as rank_gp from group_concat group by username
05.) b
06.where a.username = b.username order by a.username asc,a.rank desc;
07.+----------+------+----------+
08.| username | rank | rank_cnt |
09.+----------+------+----------+
10.| Lily | 136 | 1 |
11.| Lily | 136 | 1 |
12.| Lily | 105 | 3 |
13.| Lily | 100 | 4 |
14.| Lily | 100 | 4 |
15.| Lucy | 146 | 1 |
16.| Lucy | 137 | 2 |
17.| Lucy | 127 | 3 |
18.| Lucy | 121 | 4 |
19.| Lucy | 104 | 5 |
20.| Lucy | 100 | 6 |
21.| ytt | 149 | 1 |
22.| ytt | 149 | 1 |
23.| ytt | 117 | 3 |
24.| ytt | 116 | 4 |
25.| ytt | 116 | 4 |
26.| ytt | 106 | 6 |
27.+----------+------+----------+
28.17 rows in set (0.02 sec)
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)
當然了，如果MySQL SQL不太熟悉，可以用程序來處理，比如我下面用python 實現了rank 函數，執行結果如下：（腳本源代碼最後）
[sql] view plaincopyprint?01.>>> ================================ RESTART ================================
02.>>>
03. username | rank | rank_cnt
04.--------------------------------
05.ytt |149 |1
06.ytt |149 |1
07.ytt |117 |3
08.ytt |116 |4
09.ytt |116 |4
10.ytt |106 |6
11.Lucy |146 |1
12.Lucy |137 |2
13.Lucy |127 |3
14.Lucy |121 |4
15.Lucy |104 |5
16.Lucy |100 |6
17.Lily |136 |1
18.Lily |136 |2
19.Lily |105 |3
20.Lily |100 |4
21.Lily |100 |4
22.(17 Rows.)
23.Time: 0.162 Seconds.
>>> ================================ RESTART ================================
>>>
username | rank | rank_cnt
--------------------------------
ytt |149 |1
ytt |149 |1
ytt |117 |3
ytt |116 |4
ytt |116 |4
ytt |106 |6
Lucy |146 |1
Lucy |137 |2
Lucy |127 |3
Lucy |121 |4
Lucy |104 |5
Lucy |100 |6
Lily |136 |1
Lily |136 |2
Lily |105 |3
Lily |100 |4
Lily |100 |4
(17 Rows.)
Time: 0.162 Seconds.
附上腳本代碼：
[python] view plaincopyprint?01.from __future__ import print_function
02.from datetime import date, datetime, timedelta
03.import mysql.connector
04.import time
05.# Created by ytt 2014/5/14.
06.# Rank function implement.
07.def db_connect(is_true):
08. cnx = mysql.connector.connect(host='192.168.1.131',port='3306',user='python_user', password='python_user',database='t_girl',autocommit=is_true)
09. return cnx
10.def db_rs_rank(c1 ='username desc' ,c2 = ' rank desc'):
11. # c1: partition column.
12. # c2: sort column.
13. time_start = time.time()
14. cnx = db_connect(True)
15. rs = cnx.cursor()
16. query0 = "select username,rank from group_concat order by " + c1 + ", " + c2
17. rs.execute(query0,multi=False)
18. if rs.with_rows:
19. rows = rs.fetchall()
20. else:
21. return "No rows affected."
22. i = 0
23. j = 0
24. k = 1
25. result = []
26. field1_compare = rows[0][0]
27. field2_compare = rows[0][1]
28. while i < len(rows):
29. if field1_compare == rows[i][0]:
30. j += 1
31. if field2_compare != rows[i][1]:
32. field2_compare =rows[i][1]
33. k = j
34. result.append((rows[i][0],rows[i][1],k))
35. else:
36. j = 1
37. k = 1
38. field1_compare = rows[i][0]
39. result.append((rows[i][0],rows[i][1],k))
40. i += 1
41. i = 0
42. rows_header = list(rs.column_names)
43. rows_header.append('rank_cnt')
44. print (rows_header[0].center(10,' ') + '|' + rows_header[1].center(10,' ') + '|' + rows_header[2].center(10,' '))
45. print ('-'.center(32,'-'))
46. while i < len(result):
47. print (result[i][0].ljust(10,' ') + '|' + str(result[i][1]).ljust(10,' ') + '|' + str(result[i][2]).ljust(10,' '))
48. i += 1
49. rs.close()
50. cnx.close()
51. time_end = time.time()
52. print ('(' + str(len(rows))+ ' Rows.)')
53. print ("Time:" + str(round((time_end-time_start),3)).rjust(10,' ') + ' Seconds.')
54.if __name__=='__main__':
55. db_rs_rank()
56.
