查看原文
其他

数据蒋堂 | 非常规聚合

2017-10-25 数据派THU

来源:数据蒋堂

作者:蒋步星

本文长度为1200,建议阅读2分钟

本文为你研究业务上有意义的其它形式聚合运算。


标准SQL中提供了五种最常用的聚合运算:SUM/COUNT/AVG/MIN/MAX。观察这几个运算,我们发现它们都可以看成是一个以集合为参数返回单值的函数,我们就先把这个共同点理解为聚合运算的定义,把集合变成单值,多个值变成一个值,也就是发生了"聚合“,所以叫聚合运算。

那么很显然,有集合的时候就可以应用聚合运算了,所以SUM/COUNT这些运算可以针对一个数据表(记录集合)实施。

分组运算的结果是一批分组子集,所以每个子集上也可以应用聚合运算,这也就是SQL的分组运算了。其实针对全集的聚合运算也可以理解为只分了一个组的特殊分组(也是一种完全划分),这样理解后,我们可以认为聚合运算总是发生在分组运算之后(但分组运算后不一定总有聚合运算,前面已说过)。而且,还可以反过来说,只要被认定为是聚合运算(符合前述定义的运算),就一定可以用在分组之后。我们在下面会看到,这个理解将大幅度地扩展分组+聚合运算的应用范围。

除了这五种聚合运算外,有的数据库还提供了方差、标准差等聚合函数,其性质和这五种差不多,可以称为是常规的聚合运算。我们下面来研究业务上有意义的其它形式聚合运算。

1. 返回记录

上述的常规聚合都是针对数值的运算,特别地,对于结构化数据来说,是针对某个字段(或表达式)的运算,返回值也是这些数值的运算结果。但有时候我们关心的不是结果数值本身,而是与结果数值相关的信息。

比如我们想从日志表中找出某个用户第一次登录时用的IP地址,而不是登录时刻。用标准SQL写这个运算大概是这样:

SELECT ip_address FROM  LogTable WHERE user=? AND logintime=
    (SELECT MIN(logintime) FROM LogTable WHERE user=?)

用子查询先计算出该用户的第一次登录的时刻,再查找出该时刻登陆时用到的IP地址,这要把数据集遍历两次。

ORACLE提供了一个KEEP函数,可以不用子查询写出这样的运算:

SELECT MIN(ip_address) KEEP(DENSE_RANK   FIRST ORDER BY logintime) FROM LogTable WHERE user=?

但是,我们关心的可能还不止是IP地址,还可能是日志表中的其它字段,比如所用浏览器、是否移动端等,其实就是关心最小值对应的那条完整记录。而由于SQL缺乏离散性,即使有KEEP函数,也不容易写出这种运算,要么每个字段分别用KEEP,要么还是用子查询遍历两次,都很繁琐。

如果有一个用于返回最大值/最小值对应记录而非值本身的聚合函数,那这个运算写起来就简单了,也只要遍历一次:

=LogTable.select(user=?).minp(logintime)

像前面说的,这样的聚合运算还可以用在GROUP中,比如找出每个用户首次登录的日志记录

=LogTable.group(user).(~.minp(logintime))

类似地,还可以将maxp方法用于返回最大值对应记录。

日志记录常常本来就是按事件发生时刻有序,利用这个特点时就不需要再用比较来计算最小值了,而是直接取出第一条即可。

=LogTable.select(user=?).first()   // 聚合函数first返回第1个成员

在分组中也可以:

=LogTable.group(user).(~.first())

当然实际编码时也可以直接取集合成员,这里写成first只是为了强调可以把取某成员的动作理解为一种聚合运算。

这种运算较为常用,我们可以为group函数做一个选项:

=LogTable.group@1(user)

SQL建立在无序集合概念上,无法保证返回记录的次序,想写出这种运算就又需要人为制造序号后再用过滤条件来做。

2. 返回集合

我们把上面的问题改一下:找出一群人中年龄最小的那些人的姓名。

和前述问题不同的是,同一个用户不会有多个相同的登录时间,但一批人中则可能有年龄相同的人,年龄最小的人可能不止一个。minp函数的返回值应当是一个集合才合理。

仔细观察我们在文章开始对聚合运算的定义,我们会发现,其实返回单值的要求并无必要,只要参数是集合,随便返回什么东西都可以认定为是聚合运算,这种定义下,返回集合的minp/maxp仍然可以作为聚合运算处理。

需要返回集合的聚合运算中,更常见是topN。

SQL并不把topN理解成一种聚合运算,而只是返回结果集时的一种修饰符。原理上,SQL会先把完整的结果集计算出来,然后再只取前N条返回。topN总是在排序动作之后,大集合的排序是个时间成本很高的动作,但其实只做topN并不需要全集的排序。这时候只能依靠数据库在工程上的优化,但这并不是总能做好的。另外,如果topN只作为结果集的修饰,那就不能把这个运算实施到分组子集上了,而且运算复杂化后优化也很难做了。

把topN理解成聚合运算后,一切都变得很轻松

=a=LogTable.select(user=?).top(logingtime,-2), a(2)-a(1) //某用户最后的两次登录时间间隔
=LogTable.groups(user;(a=~.top(logintime,-2),a(2)-a(1)))  //每个用户最后的两次登录时间间隔

而且实施计算也不需要刻意地在工程上优化,在分组后使用也能获得高性能。

topN也有返回记录的情况,即取出某个字段(表达式)在前N名的对应记录。和minp/maxp类似地,这需要再设计一个函数。

同样的,有序情况也会发生,像前面的日志计算,如果假定日志表已经针对事件时刻有序,那可以不必再用topN去做比较运算了。

=a=LogTable.select(user=?).last(2),a(2)-a(1)   //聚合函数last(n)返回最后n个成员

=LogTable.groups(user;(a=~.last(2),a(2)-a(1)))

类似地,last函数也可以写成取集合成员的形式。

3. 总结


这里讨论了非常规聚合的两种常见情况,都是SQL不易支持的。当然按照定义还会有更多形式的聚合运算,即使只看这两种情况也还会有许多变种,比如取出排序位置居中的成员、取出针对某一字段的唯一值(DISTINCT)集合等。深入理解聚合运算及其与分组运算的关系,将能够扩展这些运算的应用范围,对计算的描述和实施都有不小的意义。

专栏作者简介

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


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


数据蒋堂

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


往期回顾:

数据蒋堂 | 再谈有序分组

数据蒋堂 | 有序分组

数据蒋堂 | 非等值分组

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

数据蒋堂 | 有序遍历语法

数据蒋堂 | 常规遍历语法

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

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

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

数据蒋堂 | SQL的困难源于关系代数

数据蒋堂 | SQL像英语是个善意的错误

数据蒋堂 | 开放的计算能力为数据库瘦身

数据蒋堂 | 计算封闭性导致臃肿的数据库

数据蒋堂 | 怎样看待存储过程的移植困难

数据蒋堂 | 存储过程的利之弊

数据蒋堂 | 不要对自助BI期望过高

数据蒋堂 | 报表的数据计算层

数据蒋堂 | 报表应用的三层结构

数据蒋堂 | 列式存储的另一面

数据蒋堂 | 硬盘的性能特征

数据蒋堂 | 我们需要怎样的OLAP?

数据蒋堂 | 1T数据到底有多大?

数据蒋堂 | 索引的本质是排序

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

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

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


校对:王懿璇


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

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

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

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

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存