【每周一期-数据蒋堂】还原分组运算的本意
【每周一期-数据蒋堂】还原分组运算的本意
分组是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年创立润乾公司,首次在润乾报表中提出非线性报表模型,完美解决了中国式复杂报表制表难题,目前该模型已经成为报表行业的标准。
2008年开始研发不依赖关系型数据的计算引擎,历经多个版本后,于2014年集算器正式发布。有效地提高了复杂结构化大数据计算的开发速度和运算效率。
2016年荣获中国电子信息产业发展研究院评选的“2016年中国软件和信息服务业 • 十大领军人物”。
2017年将带领润乾软件朝着拥有自主产权的非关系型强计算数据仓库、云数据库等产品迈进。
数据分析咨询请扫描二维码
数据挖掘分析是从大量数据中发现隐藏模式和有用信息的过程。尤其是在图数据挖掘中,提供了分析复杂关系和结构的独特视角。图数据 ...
2024-11-08在当今快速发展的商业环境中,提高运营效率已成为企业取得成功的关键因素。企业需要通过优化工作流程、利用技术创新和提升员工技 ...
2024-11-08Python 是一门非常适合初学者学习的编程语言。其简洁明了的语法、丰富的功能库,以及广泛的应用领域,使其成为学习编程的理想选 ...
2024-11-08在当今快速变化的商业环境中,金融数字化已经成为中小企业(SMEs)发展的关键驱动力。通过采用数字工具和技术,中小企业能够提高 ...
2024-11-08中小企业在全球经济中扮演着重要角色,然而,面对数字化浪潮,这些企业如何有效转型成为一大挑战。数字化转型不仅是技术的升级, ...
2024-11-08选择合适的数据分析方法是数据分析流程中的关键环节。它影响最终结论的准确性和可信度。在这个过程中,需要综合考虑数据的性质、 ...
2024-11-08在当今数据驱动的商业环境中,数据分析师扮演着至关重要的角色。他们帮助企业从大量数据中提取有用的洞察,从而推动决策制定和战 ...
2024-11-07在现代商业环境中,商务数据分析师扮演着至关重要的角色。作为联系业务需求与数据洞察之间的桥梁,数据分析师需要具备一系列技能 ...
2024-11-07在现代商业环境中,商务数据分析师扮演着至关重要的角色。作为联系业务需求与数据洞察之间的桥梁,数据分析师需要具备一系列技能 ...
2024-11-07在现代商业环境中,数据挖掘发挥着至关重要的作用。它不仅帮助企业从庞大的数据集中提取有价值的信息,还为企业的决策和业务运营 ...
2024-11-07数据分析可视化是一种通过图形化方式展现数据的技术,它使复杂的数据变得直观易懂,从而帮助我们更好地做出决策。在这个快速发展 ...
2024-11-07数据分析是一项至关重要的技能,尤其在当今数据驱动的世界中。Python以其强大的库和简单的语法成为了数据分析领域的佼佼者。本文 ...
2024-11-07在现代数据驱动的环境中,数据分析师扮演着至关重要的角色。他们需要掌握多种工具,以满足数据分析、处理和可视化的需求。无论是 ...
2024-11-07作为一名业务分析师,你将发现自己处于企业决策和数据驱动战略之间的桥梁位置。这个角色要求掌握一系列技能,以便有效地将数据转 ...
2024-11-07CDA中科院城市环境研究所(厦门)内训圆满成功 2017年9月12日-15日,CDA数据分析师在中科院城市环境研究所(厦门)进行了 ...
2024-11-07数据分析是现代商业和研究领域不可或缺的重要工具。无论是为了提高业务决策的准确性,还是为了发掘隐藏在数据中的潜在价值,了解 ...
2024-11-06数据分析是一个精细且有序的过程,旨在从海量数据中提取有用的信息,为决策提供有力支持。无论你是新手还是有经验的分析师,理解 ...
2024-11-06在当今竞争激烈的商业环境中,业务分析师(Business Analyst, BA)的角色变得愈加重要。随着企业对数据驱动决策的依赖加深,业务 ...
2024-11-06在现代信息技术的广阔世界中,大数据架构师扮演着至关重要的角色。他们不仅引领着企业的数据战略,还通过技术创新推动业务的不断 ...
2024-11-04在当今数字化时代,数据分析师已成为企业关键角色,帮助决策者通过数据驱动的洞察实现业务目标。成为一名成功的数据分析师,需要 ...
2024-11-03