数据蒋堂 | 还原分组运算的本意

最新推荐文章于 2021-01-19 06:34:36 发布

数据派THU

最新推荐文章于 2021-01-19 06:34:36 发布

阅读量449

点赞数

本文链接：https://blog.csdn.net/tmb8z9vdm66wh68vx1/article/details/78138785

版权

来源：数据蒋堂

作者：蒋步星

本文长度为2600字，建议阅读4分钟

分组是SQL中常见的运算，但未必所有人都能深刻地理解它。

分组运算的实质是将一个集合按照某种规则拆分成若干个子集，也就是说，返回值应当是一个由集合构成的集合，但人们一般不太关心构成这个集合的成员集合（我们称为分组子集），而是对这些子集的聚合值更感兴趣，因此，分组运算常常伴随着对子集的进一步汇总计算。

SQL就是这么做的，在写有GROUP BY子句时，SELECT部分除了分组字段外，就只能写入聚合运算表达式了。当然还有个原因是SQL没有显式的集合数据类型，无法返回集合的集合这类数据，也只能强迫实施聚合运算了。

久而久之，人们会认为分组总是需要配合后续的聚合运算，而忘记了分组和聚合其实是两个独立的步骤。

但是，我们仍然有对这些分组子集而不是聚合值更感兴趣的时候。

比如，我们想找出公司里有哪些员工和其他员工会在同一天过生日，很简单的思路是将员工按生日分组，然后找出成员数大于1的分组子集，再合并起来。这时候我们就不是只对聚合值（分组子集的成员数）感兴趣，而是对分组子集本身更感兴趣。

这个运算用SQL写起来就会比较啰嗦，需要用子查询，并且要遍历两次原集合。

SELECT * FROM employee WHERE birthday IN

( SELECT birthday FROM employee GROUP BY birthday HAVING COUNT(*)>1 )

（题外话：这里假定birthday字段就是生日，其实我们日常意义的生日是没有年份的，而数据表中的birthday字段则会有，这时候还需要把birthday转换成月和日再做GROUP和WHERE，但对于集合化不彻底的SQL，涉及两个成员的IN运算很难写，上面的birthday要改写类似month(birthday()*100+day(birthday)的样子，拼成一个单独的表达式才能使用IN来判断，书写要繁琐很多。）

有集合化更彻底的语法时，就可以保持住分组子集。这就是需要离散性来支持了，分组子集仍然是原集合成员构成。这样，分组和聚合还原成两个步骤，上面的运算就可以很清晰地写出来：

employee.group(month(birthday),day(birthday)).select(~.len()>1).conj()

（在这个表达式中我们使用了前面讲遍历语法时的~符号表示当前成员，也就是遍历过程中的某个分组子集。）

按birthday的月/日分组，过滤出成员数大于1的分组子集，然后求并集。事实上在做过滤时仍然要再二次遍历数据，但只是计数，不需要像SQL那样做比较，性能要好很多。

退一步讲，就算我们只对聚合值感兴趣，我们也可能需要保持住这些分组子集以便反复利用，计算出多种聚合值，而不是完成一次聚合后就将其丢弃，下次再计算时又要重新分组。分组是个成本不低的运算，现在一般使用HASH方法实现分组，计算和比较HASH值都要比简单遍历复杂很多。有些优化不好的计算方案还会使用排序的方法实现分组（很多报表工具是这么做的），性能更会差出一个级别来。

比如我们计算每个部门的人数，再计算出10人以上部门的人员平均年龄。这在SQL中就要写成两句，因为后者需要一个HAVING条件：

SELECT department, COUNT(*) FROM employee GROUP BY department

SELECT department,AVERAGE(age) FROM employee GROUP BY department HAVING COUNT(*)>=10

这里GROUP动作就要被执行两遍。

而如果能够保持分组子集，则只要做一次group就可以了：

g=employee.group(department)

g.new(~.department,~.len())

g.select(~.len()>=10).new(~.department,~.avg(age))

还有的可能是，我们确实只对一个聚合值感兴趣，但这个聚合值很难计算，并不能简单地用SUM/COUNT计算出来的，需要编段程序才行，这时候也需要保留分组子集，而用SQL就很难实现这种运算了。我们会在后续文章中举例。

分组的结果是集合的集合，它仍然是个集合，那显然还可以进一步分组。

g1=employee.group(year(birthday)) //按出生年份分组

g2=g1.group(year(birthday)%100\10) //将所有分组子集按年代分组

g3=g1.(~.group(month(birthday)) //将每个分组子集按出生月份分组

后两步运算都会得到集合的集合的集合，三层或更深的情况在现实业务中很少碰到，但可以用来体会集合的思维方式以及分组运算的本质。

我们知道，SQL针对GROUP后的结果集过滤专门设计了HAVING关键字，许多初学者对HAVING的理解和运用都不到位。其实，HAVING从概念上讲是多余的，它和WHERE并没有任何差别，只是因为SQL无法保持分组子集，要把分组和聚合写在一句话中，又要和WHERE区分，然后硬造出来的一个关键字。如果能够保持分组子集后实现分步计算，HAVING是没有必要的。

专栏作者简介

润乾软件创始人、首席科学家

清华大学计算机硕士，著有《非线性报表模型原理》等，1989年，中国首个国际奥林匹克数学竞赛团体冠军成员，个人金牌；2000年，创立润乾公司；2004年，首次在润乾报表中提出非线性报表模型，完美解决了中国式复杂报表制表难题，目前该模型已经成为报表行业的标准；2014年，经过7年开发，润乾软件发布不依赖关系代数模型的计算引擎——集算器，有效地提高了复杂结构化大数据计算的开发和运算效率；2015年，润乾软件被福布斯中文网站评为“2015福布斯中国非上市潜力企业100强”；2016年，荣获中国电子信息产业发展研究院评选的“2016年中国软件和信息服务业十大领军人物”；2017年, 自主创新研发新一代的数据仓库、云数据库等产品即将面世。

数据蒋堂

《数据蒋堂》的作者蒋步星，从事信息系统建设和数据处理长达20多年的时间。他丰富的工程经验与深厚的理论功底相互融合、创新思想与传统观念的相互碰撞，虚拟与现实的相互交织，产生出了一篇篇的沥血之作。此连载的内容涉及从数据呈现、采集到加工计算再到存储以及挖掘等各个方面。大可观数据世界之远景、小可看技术疑难之细节。针对数据领域一些技术难点，站在研发人员的角度从浅入深，进行全方位、360度无死角深度剖析；对于一些业内观点，站在技术人员角度阐述自己的思考和理解。蒋步星还会对大数据的发展，站在业内专家角度给予预测和推断。静下心来认真研读你会发现，《数据蒋堂》的文章，有的会让用户避免重复前人走过的弯路，有的会让攻城狮面对扎心的难题茅塞顿开，有的会为初入行业的读者提供一把开启数据世界的钥匙，有的甚至会让业内专家大跌眼镜，产生思想交锋。

往期回顾：

数据蒋堂 | 有序遍历语法

数据蒋堂 | 常规遍历语法

数据蒋堂 | 从SQL语法看离散性

数据蒋堂 | 从SQL语法看集合化

数据蒋堂 | SQL用作大数据计算语法好吗？

数据蒋堂 | 功夫都在报表外--漫谈报表性能优化

数据蒋堂 | 非结构化数据分析是忽悠？

数据蒋堂 | 多维分析的后台性能优化手段

校对：谭佳瑶

为保证发文质量、树立口碑，数据派现设立“错别字基金”，鼓励读者积极纠错。

若您在阅读文章过程中发现任何错误，请在文末留言，或到后台反馈，经小编确认后，数据派将向检举读者发8.8元红包。

同一位读者指出同一篇文章多处错误，奖金不变。不同读者指出同一处错误，奖励第一位读者。

感谢一直以来您的关注和支持，希望您能够监督数据派产出更加高质的内容。