【数据蒋堂】第23期:还原分组运算的本意-pg游戏官网登录入口

发布时间:2017-09-19 分类:数据蒋堂 tag:,,,

sjjt-23

分组是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)0\10)       //将所有分组子集按年代分组

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

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

 

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

网站地图