当前位置:Gxlcms > mysql > 集算器如何优化SQL计算(2)分组_MySQL

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

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

人气教程排行