【MySql】关于排名计算的几种方法汇总及算法分析

vlambda
2020-07-31

【MySql】关于排名计算的几种方法汇总及算法分析

昨天因为想看一个用户倒数第一笔和倒数第二笔交易的间隔时间【分析用户的交易频次及周期规律】，所以想着怎么把倒数第一笔和倒数第二笔的交易查出来。

前几天在看存储过程的时候，用到过自定义变量。回头看了下，之前写代理商排名变化，是定义了一个变量，大概是自增的写法。语句是这样的：

USE test; #转到test库

DROP TABLE IF EXISTS `stu_mark`; #如果这个表存在，就将这个表删除

CREATE TABLE `stu_mark` (

`id` INT(10) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(20) NOT NULL,

`mark` INT(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8; #创建表，定义字段及字段类型

INSERT INTO stu_mark(`name`, `mark`)

VALUES ('lisi',10),('zhangsan',20),('wangwu',20),('zhaoliu',30),('liuqi',50),('anjiu',100);

#在表中插入数据

SELECT `name`, `mark`,convert(sor,signed) FROM(

SELECT `name`, `mark`

,(CASE WHEN @tMark=mark THEN @tm ELSE @tm :=@tm+1 END) AS sor

,(@tMark:=mark) AS tm

FROM `stu_mark` AS m ,(SELECT @tm :=0,@tMark:=0) AS t1

ORDER BY mark DESC

) AS t2

ORDER BY t2.sor desc

其实这个写法，是在网页上copy的一个案例，实际上用到语句中的，我记得好像写过，但是找不到了。

上面这个写法有点过于复杂，我其实并没有搞明白，相当于只是在我的笔记本上记了一个符号。

今天又百度了关于排名的写法【其实我是想看看在IMPALA数据库中，有没有直接的函数可以用的，我记得oracle中是有分析函数的，例如他们可以分组排序】，今天百度到这一篇：

https://blog.csdn.net/qq_22508585/article/details/79334811?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-7.compare&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-7.compare

我根据自己的理解，改了语句，写了示例【自己加了分组排序的功能，默默的为自己点个赞】：

SELECT merchant_no,trans_amount+0,

trans_time,

(SELECT count(0) FROM (SELECT distinct merchant_no m,trans_time s FROM transaction_detail_202006 where merchant_no in('207631005231291','215201004582046') and trans_time>'2020-06-30') tmp WHERE s >= trans_time and m=merchant_no ) Rank,

(SELECT count(0) FROM (SELECT distinct merchant_no m,trans_amount+0 s FROM transaction_detail_202006 where merchant_no in('207631005231291','215201004582046') and trans_time>'2020-06-30') tmp WHERE s >= trans_amount+0 and m=merchant_no) Rank2

FROM transaction_detail_202006 where merchant_no in('207631005231291','215201004582046') and trans_time>'2020-06-30'

ORDER BY trans_time,trans_amount+0 desc;

记得10年前，刚转行做数据的时候，公司的DBA是个海龟，每天牛逼轰轰的，经常跑过去吐槽我们组的SQL写的烂，好像他经常会用这种写法，我当初好像研究过，但是没记太清楚，也没懂到底怎么用……

看到这个，我突然觉得，其实自己曾经有很多机会把一些事情做的更好，只是自己放弃了。时隔十年，再看到这种写法，我瞬间有种内流满面的感觉。

在这里总结下自己对排序算法的理解吧。

回头看上面的两种写法，其实都差不多，差异是一个需要引入一个变量，一个是比较直观的，去数比指定的值大的有多少个。其实从算法的性能上来讲，可能第一种写法会更好一点。

其实，生活中也有很多的未完待续，只是有些我们已经忘了去继续……

vlambda博客
学习文章列表