欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

集算器如何优化SQL计算(2)分组_MySQL

程序员文章站 2022-04-29 18:06:30
...
非等值分组

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

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):还款日,总金额/总期数:金额)