ITPub博客

首页 > 数据库 > MySQL > MySQL之伪列实现与实践

MySQL之伪列实现与实践

原创 MySQL 作者:wangwenan6 时间:2015-09-23 14:08:20 2 删除 编辑
-------------------------------------------------------------------------------------------------正文--------------------------------------------------------------------------------------------------------------
问题来源:基情
问题描述:看图说明一切



建表语句与模板数据:

点击(此处)折叠或打开

  1. CREATE TABLE `tb_score` (
  2. `id` bigint(20) NOT NULL AUTO_INCREMENT ,
  3. `country` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
  4. `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
  5. `score` double NULL DEFAULT NULL ,
  6. PRIMARY KEY (`id`)
  7. )
  8. ENGINE=InnoDB
  9. DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
  10. AUTO_INCREMENT=20
  11. ROW_FORMAT=COMPACT
  12. ;
  13. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (1, '中国', '张三', 81);
  14. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (2, '美国', 'Tom', 78);
  15. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (3, '英国', 'James', 67.5);
  16. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (4, '澳大利亚', 'Jack', 81);
  17. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (5, '澳大利亚', 'Roby', 64);
  18. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (6, '美国', 'Jory', 69);
  19. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (7, '中国', '李四', 92);
  20. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (8, '中国', '李天', 82);
  21. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (9, '中国', '王智', 71);
  22. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (10, '中国', '杨彦', 68.5);
  23. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (11, '澳大利亚', 'Jimmy', 92);
  24. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (12, '美国', 'Will', 81.5);
  25. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (13, '美国', 'Smirth', 79.5);
  26. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (14, '英国', 'Toki', 66);
  27. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (15, '澳大利亚', 'Kate', 89);
  28. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (16, '澳大利亚', 'Mercy', 88);
  29. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (17, '美国', 'Lance', 84.5);
  30. 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(*)来充当伪列的计数器,然后附加上计数的规则,
简单的构造示例:

点击(此处)折叠或打开

  1. select t1.*,
  2. (select count(*) from tb where id<=t1.id) as rownum
  3. 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
点击(此处)折叠或打开
  1. select t3.id,t3.country,t3.score
  2. from (select t1.*, (select count(*) from tb_score t2 where t1.score<=t2.score and t1.country=t2.country) as rownum
  3. from tb_score t1) t3
  4. where rownum <=3 order by country,score DESC;

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


目测是独立子查询往上提,果不其然:


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

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

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

请登录后发表评论 登录
全部评论

注册时间:2014-02-28

  • 博文量
    102
  • 访问量
    1350157