Implement the ranking Window Function in MySQL and Python

MySQL and Python implement ranking window functions most databases provide window functions, such as RANK and ROW_NUMBER. MySQL is not directly provided in this respect, but it can be implemented in disguise. I have previously written the implementation of row_number. Today I have time to post the implementation of rank. Here, I use MySQL and Python to implement rank

MySQL and Python implement ranking window functions most databases provide window functions, such as RANK and ROW_NUMBER. MySQL is not directly provided in this respect, but it can be implemented in disguise. I have previously written the implementation of row_number. Today I have time to post the implementation of rank. Here, I use MySQL and Python to implement rank

Implement ranking window functions in MySQL and Python
Most databases provide window functions, such as RANK and ROW_NUMBER. MySQL is not directly provided in this respect, but it can be implemented in disguise. I have previously written the implementation of row_number. Today I have time to post the implementation of rank.

Here, I use MySQL and Python to implement rank window functions respectively.
Original table information:

t_girl=# \d group_concat;           Table "ytt.group_concat"  Column  |         Type          | Modifiers ----------+-----------------------+----------- rank     | integer               |  username | character varying(20) | 

Table Data

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 Window Function example:

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 provides group_concat Aggregate functions for disguised implementation:

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)

Of course, if MySQL SQL is unfamiliar, it can be processed by a program. For example, if I use python to implement the rank function, the execution result is as follows: (the final part of the script source code)

>>> ================================ 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.

Attach the script code:

from __future__ import print_functionfrom datetime import date, datetime, timedeltaimport mysql.connectorimport time# Created by ytt 2014/5/14.# Rank function implement.def db_connect(is_true):    cnx = mysql.connector.connect(host='192.168.1.131',port='3306',user='python_user', password='python_user',database='t_girl',autocommit=is_true)    return cnxdef db_rs_rank(c1 ='username desc' ,c2 = ' rank desc'):    # c1: partition column.    # c2: sort column.    time_start = time.time()    cnx = db_connect(True)    rs = cnx.cursor()    query0 = "select username,rank from group_concat order by " + c1 + ", " + c2    rs.execute(query0,multi=False)    if rs.with_rows:        rows = rs.fetchall()    else:        return "No rows affected."    i = 0    j = 0    k = 1    result = []    field1_compare = rows[0][0]    field2_compare = rows[0][1]    while i < len(rows):        if field1_compare == rows[i][0]:            j += 1            if field2_compare != rows[i][1]:                field2_compare =rows[i][1]                k = j            result.append((rows[i][0],rows[i][1],k))        else:            j = 1            k = 1            field1_compare = rows[i][0]            result.append((rows[i][0],rows[i][1],k))        i += 1    i = 0    rows_header = list(rs.column_names)    rows_header.append('rank_cnt')    print (rows_header[0].center(10,' ') + '|' + rows_header[1].center(10,' ') + '|' + rows_header[2].center(10,' '))    print ('-'.center(32,'-'))    while i < len(result):        print (result[i][0].ljust(10,' ') + '|' + str(result[i][1]).ljust(10,' ') + '|' + str(result[i][2]).ljust(10,' '))        i += 1    rs.close()    cnx.close()    time_end = time.time()    print ('(' + str(len(rows))+ ' Rows.)')    print ("Time:" + str(round((time_end-time_start),3)).rjust(10,' ') + ' Seconds.')    if __name__=='__main__':    db_rs_rank()