MySQL之伪列实现与实践

最新推荐文章于 2021-02-08 05:29:02 发布

crty2245

最新推荐文章于 2021-02-08 05:29:02 发布

阅读量227

点赞数

文章标签：数据库数据结构与算法

-------------------------------------------------------------------------------------------------正文--------------------------------------------------------------------------------------------------------------
问题来源：基情
问题描述：看图说明一切

建表语句与模板数据：

点击(此处)折叠或打开

CREATE TABLE `tb_score` (
`id` bigint(20) NOT NULL AUTO_INCREMENT ,
`country` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`score` double NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=20
ROW_FORMAT=COMPACT
;
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (1, '中国', '张三', 81);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (2, '美国', 'Tom', 78);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (3, '英国', 'James', 67.5);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (4, '澳大利亚', 'Jack', 81);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (5, '澳大利亚', 'Roby', 64);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (6, '美国', 'Jory', 69);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (7, '中国', '李四', 92);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (8, '中国', '李天', 82);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (9, '中国', '王智', 71);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (10, '中国', '杨彦', 68.5);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (11, '澳大利亚', 'Jimmy', 92);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (12, '美国', 'Will', 81.5);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (13, '美国', 'Smirth', 79.5);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (14, '英国', 'Toki', 66);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (15, '澳大利亚', 'Kate', 89);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (16, '澳大利亚', 'Mercy', 88);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (17, '美国', 'Lance', 84.5);
INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (18, '英国', 'Bandy', 77);

实践环境： MySQL-5.7.7-rc

实践过程：
首先看到这个需求，第一时间想到的就是Oracle的分组统计分析，当然了，MySQL没有，所以先生成了数据，直观的看一看效果：

总共四组，每一组都要选取成绩最好的前N(N=3)

直观的看，已有的ID，country，name，socre都无法作为where的条件去筛选出score最高的三个

那么跳出这些实际的数据， 如果以上图这种顺序的数据结构为基础，要实现这个需求的话，

可以用这样子的抽象描述来表达实际需求的意思： 以country的值为分组条件，每一组选第一行数据，第二行数据，...，第N行数据

那么在这种抽象描述里面， where的条件就可以做出来：每一组选第一行数据，第二行数据，...，第N行数据

so，可行方法就得出来了： 以country的值为分组条件，构建伪列，最终结果筛选前N行数据，伪列值<=N(N=3)

问题来了， MySQL的伪列怎么构造？

构造位列的思想也是靠自连接来完成，使用count(*)来充当伪列的计数器，然后附加上计数的规则，
简单的构造示例：

点击(此处)折叠或打开

select t1.*,
(select count(*) from tb where id<=t1.id) as rownum
from tb t1;

那么在这次的问题里面，这个伪列是有前提条件的：

1. 以country的值为分组条件，那么显然，在构造伪列的where条件里面，我们必须限定这个伪列的count(*)所在的范围必须是在同一个country里面 ，

添加clause1：连接条件为country

2. 要选取分数最高的N(N=3)个 ，所以生成这个伪列的序列号的count(*)的计算方式，也是一个限制条件，

之前的clause1已经把范围限定在了同一个country， 那么取分数最高，无非就是算一下比其他低的有多少，

比如以中国为例，想要最高分92的行作为伪列的第一行，代表着，92应该是<=(country=中国)的score的count

所以添加clause2：内表.score<=外表.score

分析完伪列构造的条件，那么就来看看实际构造的效果：

鼓掌撒花~按照每一个country为一组，根据score的大小成功构建了伪列

剩下，各位应该都知道怎么做了~加上 where rownum <=3即可~

最后贴上未经优化的强迫症SQL
点击( 此处 )折叠或打开

select t3.id,t3.country,t3.score
from (select t1.*, (select count(*) from tb_score t2 where t1.score<=t2.score and t1.country=t2.country) as rownum
from tb_score t1) t3
where rownum <=3 order by country,score DESC;

实际上MySQL对这个渣语句已经进行了优化：

目测是独立子查询往上提，果不其然：

------------------------------------------------------------------------------------------------全文完-----------------------------------------------------------------------------------------------------------

PS：伪列不错，灵活使用能够解决很多奇奇怪怪的需求~

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29510932/viewspace-1807179/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29510932/viewspace-1807179/