集算器如何优化SQL计算（2）分组_MySQL

非等值分组

 

按段分组是常见的需求，如成绩段（优秀，良好，…）、年龄段（青年、中年、…）等。

 

SQL实现分段一直很繁琐，段数不多的静态分段，可以用case when条件比较实现；而段数很多或规则变化的动态分段，一般则要建立临时表用非等值JOIN实现了。无论哪种情况的处理方法都很繁琐。

 

集算器中用penum函数即可返回枚举条件的序号：

 

[”?=60&&?=75&&?=90”].penum(成绩)

 

如果分段是连续的，还可以用pseg函数更简单地获得分段序号：

 

[60,75,90].pseg(成绩)

 

这里的条件和分段都是普通数组，可作为参数传递进来，长度也不限制。基于分段号即可将枚举分组和按段分组转变成普通的等值分组：

	A	B
1	[”?=60&&?=75&&?=90”]	条件段，可以是参数
2	[60,75,90]	区间段，可以是参数
3	=db.query("select * from 成绩表")
4	=A3.groups(A1.penum(成绩);count(1):人数)	按条件段分组
5	=A3.groups(A2.pseg(成绩);count(1):人数)	按区间段分组

与非等值分组相关的问题还有固定排序：统计结果呈现时经常要求按指定的次序而不是数据本身排序，比如中国省份排列时一般要将北京放在第一个。SQL处理方法和分段类似，对于条目较少且静态的规则可以用decode转码成序号，而条目较多或规则动态时也需要建立临时表用JOIN生成序号。

 

集算器提供了align@s函数专门用于对齐排序：

 

         T.align@s([“北京”,”河北”,”山东”,…],地区)

 

即可将表T以字段地区按指定的次序排序，同样的，排序依据是个普通数据，可以作为参数传递：

	A	B
1	[“北京”,”河北”,”山东”,…]	排序依据，可以是参数
2	=db.query("select * from T")
3	=A2.align@s(A1,地区)	按指定次序排列

与不产生空子集的等值分组不同，有时我们要求分组的结果是连续区间，要补齐中间缺省的空子集。SQL实现这个过程非常麻烦，要手工先造出连续不断的分组区间再left join要统计的数据表，复杂的子查询将不可避免。而集算器有专门的对齐函数，基准区间准备也很方便，实现该运算要简单得多。

 

简化的交易记录表结构为：序号、日期、金额。现需要按周统计累计的交易金额，没有交易记录的周也要列出。

	A	B
1	=db.query("select * from 交易记录表 order by 日期")
2	>start=A1(1).日期
3	=interval(start,A1.m(-1).日期)\7+1	计算总周数
4	=A1.align@a(A2,interval(start,日期)\7)	按周分组，可能有空集
5	=A4.new(#:周,累计金额[-1]+~.sum(金额):累计金额)	汇总并计算累计

分组子集

 

由于没有显式的集合数据类型，SQL在分组时会强迫计算出聚合值。但有时我们感兴趣的不只是聚合值，还有分组子集，这时用SQL就很难处理，要用子查询反复计算。

 

集算器有集合数据，也提供了返回子集的分组函数。这样就能方便地处理分组后运算。

 

比如找出总分500分以上的学生的各科成绩记录。SQL需要先分组计算出各学生总分，从中过滤出500分以上的，再用这个名单与原成绩记录JOIN或用IN判断，较麻烦且要重复取数。而集算器则可以按自然思路写出来：

	A
1	=db.query("select * from 成绩表")
2	=A1.group(学生).select(~.sum(成绩)>=500).conj()

这种分组后却要返回子集明细记录的情况很多，分组聚合是用来实现某种过滤的中间步骤而不是结果。事实上，后面要讲到的报表按分组汇总值排序的例子也是类似的运算。

 

有时即使是只要返回聚合值，但聚合计算较为特别，难以用简单聚合函数表示时，也需要保留分组子集用于再计算。

 

这类计算在现实中并不少见，但因为计算复杂，常常涉及较多的业务背景，不适合举例说明，这里改造了一个简化后的例子：

 

设有用户登录表结构为：user（帐号），login（登录时刻）；现要计算出每个帐号最后登录时刻以及该时刻前三天内的登录次数。

 

找出最后登录时刻很容易，但如果不保留分组子集时则很难计算出那个时间段登录次数。用SQL需要先分组计算出最后登录时间，与原表JOIN后过滤相应时间段的记录再次分组汇总，不仅麻烦而且记录效率很低。而使用集算器保留了分组子集则容易实现分步式计算：

	A
1	=db.query("select * from 登录表")
2	=A1. .group(user;~.max(login):last,~.count(interval(login,last)

其中~就是按user分组后的子集。

 

如果数据有序还可以用高效的方法计算：

	A
1	=db.query("select * from 登录表 order by login desc")
2	=A1. .group(user;~(1).login:last,~.pselect@n(interval(login,last)>3)-1:num)

有序聚合

 

取出每组的前N条、最大值对应记录等也是较常见的运算。显然，这些都可以用保留分组子集的方法实现，但由于这类运算较常见，集算器将其理解成某种聚合而提供了专门的函数，这样就可以采用和普通的分组汇总基本一致的处理方式。

 

先看最简单的情况，用户登录表结构为：用户、登录时刻、IP地址、…；列出每个用户首次登录的记录。

 

SQL可以用窗口函数生成组内排序序号，并取出所有序号为1的记录，但窗口函数是在结果集上再计算的，因而必须用子查询再过滤的形式，写法有些复杂。而不支持窗口函数的数据库写起来就会更困难了。

 

集算器提供了group@1方法可直接取出每个分组的第一个成员。

	A
1	=db.query("select * from 登录表 order by 登录时刻")
2	=A1.group@1(用户)

这类日志数据经常存在文件中，且已经对时刻有序，用集算器就可以直接取出第一条而不必再排序。数据量大到内存放不下时也可以基于游标实现类似的运算。

 

股价表的结构为：股票、交易日、收盘价；计算每支股票最近的涨幅。

 

计算涨幅涉及到最后两个交易日的记录，需要用两重窗口函数分别实施组内跨行计算再取出结果的第一行，写法很繁琐。集算器提供了topN聚合函数，利用集合数据直接返回多条记录作为汇总值参与进一步计算。

	A	B
1	=db.query("select * from 股价表")
2	=A1.groups(股票;top(2,-交易日))	最后2个交易日的数据
3	=A2.new(股票,#2(1).收盘价-#2(2).收盘价:涨幅)	计算涨幅

聚合函数并不会先计算出分组子集，而是直接在已有结果上累积，这样可获得更高的性能，而且在数据量大到内存放不下时还可以基于游标工作。

 

如果数据已有序，则可以更高效地用位置取出相应记录：

	A	B
1	=db.query("select * from 股价表 order by 交易日 desc")
2	=A1.groups(股票;top(2,0))	直接取前2条
3	=A2.new(股票,#2(1).收盘价-#2(2).收盘价:涨幅)

取出最大值对应记录、第1条最后1条等类似计算都是topN聚合的特例了。 

 

逆分组

 

与分组汇总相反，逆分组指将汇总数据拆分成多条明细数据。这种情况不多见，但碰到了用SQL很难处理，这里仅举一例。

 

分期付款表结构为：编号、总金额、起始日、总期数；要将每笔贷款拆分成多期记录，结构为：编号、期数、还款日、金额。总金额将简单地平均分配到每一期，一期为一个月。

 

从明细到汇总很容易，反过来就困难很多，用SQL将记录数变多一般是和一个序号表JOIN或用递归查询，思路都不直接。而用集算器则按常规思路写出来即可：

	A
1	=db.query("select * from 分期付款表")
2	=A1.news(总期数;编号,~:期数,after@m(起始日,~-1):还款日,总金额/总期数:金额)