MySQL以及Python实现排名窗口函数_MySQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL以及Python实现排名窗口函数_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3975字，纯文字阅读大概需要6分钟。

内容图文

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 | Lucy127 | Lucy146 | Lucy137 | Lucy104 | Lucy121 | Lucy136 | Lily100 | Lily100 | Lily105 | Lily136 | Lily149 | ytt116 | ytt116 | ytt149 | ytt106 | ytt117 | ytt(17 rows)Time: 0.638 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> 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)

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

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()

内容总结

以上是互联网集市为您收集整理的MySQL以及Python实现排名窗口函数_MySQL全部内容，希望文章能够帮你解决MySQL以及Python实现排名窗口函数_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端