mysql 相减_用MySQL数据库来分析高考一分一段表(开窗分析)

数据分析是数据库中最为常见的一项工作，实际业务会有各种各样的需求。但是针对MySQL数据库来说，数据分析恰恰是其弱项，但有的时候弱项也要变不可能为可能，那就通过一些比较另类的做法来实现。

本次分享的是一个关于高考一分一段表的数据分析。简要介绍一下一分一段表(大部分省份做法相同)，首先表会按照分数从高到低降序排序，包含3个重要指标，分数(成绩fs)、此分数上的人数(rs)、位次/累计到此分数的人数(lj)，当然为了跨年度查询还有引入年度码(ndm)，高考还会有文理科，所以还要有科类代码(kl)，共计5个指标，表名为fstj，表创建语句如下：

Create table fstj(

nd varchar(4),

kl varchar(5),

fs int(3),

rs int(5),

lj int(7)

);

各位可到各省考试院网站获取历年数据。

下面以某省的2018、2017、2016年一分一段表数据作为参考，讲解一下所要达成的效果。由于每年的题目的难度不同，每年的考生的分数分布也不相同，如果参看其他年度各个大学的录取分数报考，那分数就需要一个年度间的对应关系，这个年度间分数的对应关系可以用位次也就是累计到此分数的考生数作为横向参考。比如2018年理科650分，位次为2116，650分在2017年的对应分数是多少呢？那就要找到与位次2116最为接近的位次2098，对应分数为632；同理2016年的最接近位次为2125，对应分数为641。部分数据截图如下：

这种对应关系就能把某一年的所有分数对应到其它年度中去，方便考生作为报考的依据。那么问题来了，如何在数据库中实现呢？能不能用一条语句实现，不用循环呢？那要作者说肯定是能了，要不写这篇文章的目的是啥。废话少说上SQL语句：

select nd,fs,rs,lj,(select fs from fstj b where b.nd='2017' and b.kl=a.kl order by abs(b.lj-a.lj) limit 1) 2017fs,(select lj from fstj b where b.nd='2017' and b.kl=a.kl order by abs(b.lj-a.lj) limit 1) 2017lj,(select fs from fstj b where b.nd='2016' and b.kl=a.kl order by abs(b.lj-a.lj) limit 1) 2016fs,(select lj from fstj b where b.nd='2016' and b.kl=a.kl order by abs(b.lj-a.lj) limit 1) 2016ljfrom fstj a where nd='2018' and kl='理' order by fs desc;

上面的代码是从2018年分数找到2017、2016年中的对应分数，然后可以参考2017、2016年的历史录取数据来进行报考。具体分析一下此语句，外层查询/父查询为找到2018年理科分数，并按照倒叙排序，重点是其中的子查询

(select fs from fstj b where b.nd='2017' and b.kl=a.kl order by abs(b.lj-a.lj) limit 1) 2017fs,(select lj from fstj b where b.nd='2017' and b.kl=a.kl order by abs(b.lj-a.lj) limit 1) 2017lj,(select fs from fstj b where b.nd='2016' and b.kl=a.kl order by abs(b.lj-a.lj) limit 1) 2016fs,(select lj from fstj b where b.nd='2016' and b.kl=a.kl order by abs(b.lj-a.lj) limit 1) 2016lj

这四句话以年度分为两组，作用相同，我们就来分析2017年度的子查询。

(select fs from fstj b where b.nd='2017' and b.kl=a.kl order by abs(b.lj-a.lj) limit 1) 2017fs,(select lj from fstj b where b.nd='2017' and b.kl=a.kl order by abs(b.lj-a.lj) limit 1) 2017lj,

第一句是找到最为接近的对应分数，第二句为最为接近的位次/累计人数。这两个子查询的返回值都是集合，多行集合没法作为返回值返回到单行查询里，聪明如我借用了limit 1返回单行。接下来的重点就是如何构造这个子集合，案例中给出的解决方案是科类相同 (b.kl=a.kl)，然后按照a集合(父查询)中的单行记录与b集合(子查询)中的所有记录进行乘积，返回结果是子查询的所有记录也就是b集合，再利用limit 1截取单行，进一步的问题是取什么样的一行记录，这样的记录是按照a集合的分数-b集合的分数差的绝对值升序排序，也就是说2018年度的650分(举例分数，其它分数做法相同)的位次和2017年度的所有分数的位次相减然后取绝对值后按照升序排序再取第一行作为返回的记录。经过上面的一系列猛操作返回的是记录行啊，由于子查询每次只能返回一个字段，所以又进行了字段限制，这就是为啥2017年度有两个子查询的原因。

这样在各个年度间有了对应关系，这就好比新闻里常说的环比，此查询的结果帮助了很多考生，好物不居奇，在此分享给大家。今天是2019年6月6日，祝福各位考生在接下来的高考中都考出好成绩。

关注我，私信获取某省(看我个人资料)的一分一段表。