
【每周一期-数据蒋堂】非常规聚合
标准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理解成聚合运算后,一切都变得很轻松
=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函数也可以写成取集合成员的形式。
这里讨论了非常规聚合的两种常见情况,都是SQL不易支持的。当然按照定义还会有更多形式的聚合运算,即使这两种情况也还会有许多变种,比如取出排序位置居中的成员、取出针对某一字段的唯一值(DISTINCT)集合等。深入理解聚合运算及其与分组运算的关系,将能够扩展这些运算的应用范围,对计算的描述和实施都有不小的意义。
数据分析咨询请扫描二维码
若不方便扫码,搜微信号:CDAshujufenxi
CDA 数据分析师报考条件详解与准备指南 在数据驱动决策的时代浪潮下,CDA 数据分析师认证愈发受到瞩目,成为众多有志投身数 ...
2025-07-18刚入职场或是在职场正面临岗位替代、技能更新、人机协作等焦虑的打工人,想要找到一条破解职场焦虑和升职瓶颈的系统化学习提升 ...
2025-07-182025被称为“AI元年”,而AI,与数据密不可分。网易公司创始人丁磊在《AI思维:从数据中创造价值的炼金术 ...
2025-07-18CDA 数据分析师:数据时代的价值挖掘者 在大数据席卷全球的今天,数据已成为企业核心竞争力的重要组成部分。从海量数据中提取有 ...
2025-07-18SPSS 赋值后数据不显示?原因排查与解决指南 在 SPSS( Statistical Package for the Social Sciences)数据分析过程中,变量 ...
2025-07-18在 DBeaver 中利用 MySQL 实现表数据同步操作指南 在数据库管理工作中,将一张表的数据同步到另一张表是常见需求,这有助于 ...
2025-07-18数据分析师的技能图谱:从数据到价值的桥梁 在数据驱动决策的时代,数据分析师如同 “数据翻译官”,将冰冷的数字转化为清晰的 ...
2025-07-17Pandas 写入指定行数据:数据精细化管理的核心技能 在数据处理的日常工作中,我们常常需要面对这样的场景:在庞大的数据集里精 ...
2025-07-17解码 CDA:数据时代的通行证 在数字化浪潮席卷全球的今天,当企业决策者盯着屏幕上跳动的数据曲线寻找增长密码,当科研人员在 ...
2025-07-17CDA 精益业务数据分析:数据驱动业务增长的实战方法论 在企业数字化转型的浪潮中,“数据分析” 已从 “加分项” 成为 “必修课 ...
2025-07-16MySQL 中 ADD KEY 与 ADD INDEX 详解:用法、差异与优化实践 在 MySQL 数据库表结构设计中,索引是提升查询性能的核心手段。无论 ...
2025-07-16解析 MySQL Update 语句中 “query end” 状态:含义、成因与优化指南 在 MySQL 数据库的日常运维与开发中,开发者和 DBA 常会 ...
2025-07-16如何考取数据分析师证书:以 CDA 为例 在数字化浪潮席卷各行各业的当下,数据分析师已然成为企业挖掘数据价值、驱动决策的 ...
2025-07-15CDA 精益业务数据分析:驱动企业高效决策的核心引擎 在数字经济时代,企业面临着前所未有的数据洪流,如何从海量数据中提取有 ...
2025-07-15MySQL 无外键关联表的 JOIN 实战:数据整合的灵活之道 在 MySQL 数据库的日常操作中,我们经常会遇到需要整合多张表数据的场景 ...
2025-07-15Python Pandas:数据科学的瑞士军刀 在数据驱动的时代,面对海量、复杂的数据,如何高效地进行处理、分析和挖掘成为关键。 ...
2025-07-15用 SQL 生成逆向回滚 SQL:数据操作的 “后悔药” 指南 在数据库操作中,误删数据、错改字段或误执行批量更新等问题时有发生。 ...
2025-07-14t检验与Wilcoxon检验的选择:何时用t.test,何时用wilcox.test? t 检验与 Wilcoxon 检验的选择:何时用 t.test,何时用 wilcox. ...
2025-07-14AI 浪潮下的生存与进阶: CDA数据分析师—开启新时代职业生涯的钥匙(深度研究报告、发展指导白皮书) 发布机构:CDA数据科 ...
2025-07-13LSTM 模型输入长度选择技巧:提升序列建模效能的关键 在循环神经网络(RNN)家族中,长短期记忆网络(LSTM)凭借其解决长序列 ...
2025-07-11