时间：2021-07-01 10:21:17 帮助过：106人阅读
按段分组是常见的需求，如成绩段（优秀，良好，…）、年龄段（青年、中年、…）等。
SQL实现分段一直很繁琐，段数不多的静态分段，可以用case when条件比较实现；而段数很多或规则变化的动态分段，一般则要建立临时表用非等值JOIN实现了。无论哪种情况的处理方法都很繁琐。
集算器中用penum函数即可返回枚举条件的序号：
[”?<60”,”?>=60&&?<75”, ”?>=75&&?<90”, “?>=90”].penum(成绩)
如果分段是连续的，还可以用pseg函数更简单地获得分段序号：
[60,75,90].pseg(成绩)
这里的条件和分段都是普通数组，可作为参数传递进来，长度也不限制。基于分段号即可将枚举分组和按段分组转变成普通的等值分组：
|
|
A |
B |
|
1 |
[”?<60”,”?>=60&&?<75”, ”?>=75&&?<90”, “?>=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)<=3):num) |
其中~就是按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):还款日,总金额/总期数:金额) |