【每周一期-数据蒋堂】SQL的有序分组
我们知道,SQL延用了数学上的无序集合概念,所以SQL的分组并不关注过待分组集合中成员的次序。我们在前面讨论过的等值分组和非等值分组,也都没有关注过这个问题,分组规则都是建立在成员取值本身上。但如果我们要拓展SQL,以有序集合为考虑对象时,那就必须考虑成员次序对分组的影响了,而且,现实业务中有大量的有序分组应用场景。
一个简单的例子:将一个班的学生平均分成三份(假定人数能被3整除)。按我们在前面所说的分组定义,这也可以看成是一种分组,但这个运算在SQL中却很难写出来,因为分组依据和成员取值没有关系。
如果使用我们在前面讲有序遍历语法时的#符号,这个问题就很容易解决了。
A.group( (#-1)*3\A.len() ) // 按序号分成前1/3,中1/3,后1/3
A.group( (#-1)%3 ) // 还可以按序号每三个中取一个构成分组子集
用SQL实现这个运算就麻烦很多,需要先用子查询造出一个序号,然后再执行类似的分组规则。
上面这个例子中其实还没有真正关注成员的次序,只是说明了序号的作用,待分组集合的成员是其它次序时也可以得到可用的结果。
我们再看更多例子。
处理文本日志时,有些日志的基本单位不是1行,而可能是3行,即每个事件总是写出3行文本,这并不是多罕见的情况。对付这种日志时,就需要把文本每3行拆成一个分组子集,然后针对每个分组再进行详细的分析处理。这时要正确的分组运算就必须依赖于待分组集合中成员(文本日志的行)的次序了。
入学考试之后,把学生按成绩排序蛇行分拆成两个班,即名次1,4,5,8,...在一个,而2,3,6,7,...在另一个班,这样能保证两个班的平均名次是相同的。这个分组也可以用序号做出来:
A.sort@z(score).group(#%4<2)
这里用的分组值不再是常见的普通数值,而是一个布尔量,相当于按“真“值和“假”值分成两个组,真值对应第一个班,假值对应另一个班。本质上讲,这还是个等值分组,只是用到的分组值可以是任意泛型。
显然,这个分组的正确性也严重依赖于待分组集成的成员次序。
顺便说一句,这又是一个只关注分组子集而不关心聚合值的例子。按序号分组在很多情况下就是用序号来计算出分组依据,然后就变成普通的等值分组了。那么有没有不能简单地转换成等值分组的情况呢?
有一组婴儿出生记录,是按出生次序排序的,我们现在关心连续出生的同性别婴儿数量超过5的有多少批?
简单想,这就是先GROUP,计算每组COUNT值,然后数出有几个大于5的。后两步很简单,问题是怎么GROUP?
直接按婴儿性别分组当然是不对的,必须考虑次序,依次扫描记录,当婴儿性别发生变化时则产生一个新组。这种分组显然没法直接用等值分组做出来了。
我们可以提供一个有序分组方法来实现这种分组:当考察值发生变化时就产生一个新的分组。
A.group@o(gender).count(~.len()>5) // @o选项表示分组值变化时将产生新分组。
用SQL就麻烦很多,需要先造成中间标志和变量来生成组的序号,大概是这样
SELECT COUNT(*) FROM
(SELECT ChangeNumber FROM
(SELECT SUM(ChangeFlag) OVER (ORDER BY birthday) ChangeNumber FROM
(SELECT CASE WHEN gender=LAG(gender) OVER ( ORDER BY birthday) THEN 0 ELSE 1 END ChangeFlag FROM A))
GROUP ChangeNumber HAVING COUNT(*)>5)
这样的SQL,看懂都不是很容易的。而且必须借助birthday这种字段来形成次序,而前述的有序分组写法在原数据有序时根本用不着这个信息。
这种场景同样可能出现在文本分析中。每个用户的事件日志可能多行,而且行数不确定,但写日志时会在每个行开始处写上用户号。这样我们可以按这个用户号进行有序分组,它变化时就说明是另一个用户的事件了。
即使是普通的等值分组,如果事先知道原集合对分组字段有序,也可以使用这种方案来实施,这将获得更高的性能,比数据库常用的HASH分组方案要快得多,而且特别适合大数据遍历的情况。
再看一个著名的问题:一支股票最长连续上涨了多少天?
这个问题当然可以直接遍历去解决,不过我们现在用分组的思路来处理,至少在SQL体系下只能这么做(严格些说,这是目前找到的最简单可行的办法)。
将股票收盘价按日期排序,然后将连续上涨的日期分到同一组,这样只要考虑哪一组成员数最多即可。更明确地说,就是当某天上涨了,就把这一天和前一天分到一个组中,某天下跌了,则产生一个新组。
用SQL实现这个思路,同样需要用中间标志和变量来生成组序号:
SELECT MAX(ContinuousDays) FROM
(SELECT COUNT(*) ContinuousDays FROM
(SELECT SUM(RisingFlag) OVER (ORDER BY TradingDate ) NoRisingDays FROM
(SELECT TradingDate,
CASE WHEN ClosingPrice>LAG(ClosingPrice) OVER (ORDER BY TradingDate THEN 0 ELSE 1 END) RisingFlag
FROM A))
GROUP BY NoRisingDays)
如果有专门的有序分组方法以及以前说过的有序遍历语法,这个运算就很简单了:
A.sort(TradingDate).group@i(ClosingPrice
与SQL不同,虽然实现思路完全一样,但写出来是分步的,而不是一个多层嵌套语句,书写和理解都要容易得多。
同样地,这种场景也会在文本分析中有用。不确定行数的日志中,有时会在事件分始时写一个标志串,当扫描到这个标志串的时候就产生一个新的分组,有序分析的条件可设定为当前扫描行和指定文字相同,这样就能保证同一事件的日志信息在同一个组中。
后两种有序分组的情况,理论上当然也可以转换成等值分组来处理(用SQL就要这么做,这也能从另一个侧面说明SQL运算体系的完备性),但确实是相当麻烦的,所以我们一般不把它再当成等值分组来处理了。
到目前为止的分组讨论,都是假定待分组集合已经准备好,其成员可以被随机访问到。但如果数据量巨大而不能全部读入时,如果继续做这种假定,会导致频繁的外存交换而性能极差,这时需要再设计以流方式边读入边分组并且边聚合的运算体系。事实上日志分析中更常见的是这种情况,这些问题我们将再撰文研究,但基本方法思路仍然离不开上面这些内容。
数据分析咨询请扫描二维码
若不方便扫码,搜微信号:CDAshujufenxi
在数字化浪潮中,数据驱动决策已成为企业发展的核心竞争力,数据分析人才的需求持续飙升。世界经济论坛发布的《未来就业报告》, ...
2025-03-28你有没有遇到过这样的情况?流量进来了,转化率却不高,辛辛苦苦拉来的用户,最后大部分都悄无声息地离开了,这时候漏斗分析就非 ...
2025-03-27TensorFlow Datasets(TFDS)是一个用于下载、管理和预处理机器学习数据集的库。它提供了易于使用的API,允许用户从现有集合中 ...
2025-03-26"不谋全局者,不足谋一域。"在数据驱动的商业时代,战略级数据分析能力已成为职场核心竞争力。《CDA二级教材:商业策略数据分析 ...
2025-03-26当你在某宝刷到【猜你喜欢】时,当抖音精准推来你的梦中情猫时,当美团外卖弹窗刚好是你想吃的火锅店…… 恭喜你,你正在被用户 ...
2025-03-26当面试官问起随机森林时,他到底在考察什么? ""请解释随机森林的原理""——这是数据分析岗位面试中的经典问题。但你可能不知道 ...
2025-03-25在数字化浪潮席卷的当下,数据俨然成为企业的命脉,贯穿于业务运作的各个环节。从线上到线下,从平台的交易数据,到门店的运营 ...
2025-03-25在互联网和移动应用领域,DAU(日活跃用户数)是一个耳熟能详的指标。无论是产品经理、运营,还是数据分析师,DAU都是衡量产品 ...
2025-03-24ABtest做的好,产品优化效果差不了!可见ABtest在评估优化策略的效果方面地位还是很高的,那么如何在业务中应用ABtest? 结合企业 ...
2025-03-21在企业数据分析中,指标体系是至关重要的工具。不仅帮助企业统一数据标准、提升数据质量,还能为业务决策提供有力支持。本文将围 ...
2025-03-20解锁数据分析师高薪密码,CDA 脱产就业班助你逆袭! 在数字化浪潮中,数据驱动决策已成为企业发展的核心竞争力,数据分析人才的 ...
2025-03-19在 MySQL 数据库中,查询一张表但是不包含某个字段可以通过以下两种方法实现:使用 SELECT 子句以明确指定想要的字段,或者使 ...
2025-03-17在当今数字化时代,数据成为企业发展的关键驱动力,而用户画像作为数据分析的重要成果,改变了企业理解用户、开展业务的方式。无 ...
2025-03-172025年是智能体(AI Agent)的元年,大模型和智能体的发展比较迅猛。感觉年初的deepseek刚火没多久,这几天Manus又成为媒体头条 ...
2025-03-14以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-13以下的文章内容来源于刘静老师的专栏,如果您想阅读专栏《10大业务分析模型突破业务瓶颈》,点击下方链接 https://edu.cda.cn/go ...
2025-03-12以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-11随着数字化转型的加速,企业积累了海量数据,如何从这些数据中挖掘有价值的信息,成为企业提升竞争力的关键。CDA认证考试体系应 ...
2025-03-10推荐学习书籍 《CDA一级教材》在线电子版正式上线CDA网校,为你提供系统、实用、前沿的学习资源,助你轻松迈入数据分析的大门! ...
2025-03-07在数据驱动决策的时代,掌握多样的数据分析方法,就如同拥有了开启宝藏的多把钥匙,能帮助我们从海量数据中挖掘出关键信息,本 ...
2025-03-06