当前位置：首页 -> AI编程基础 -> 数据库编程

集算器如何优化SQL计算（2）分组(一)

2015-11-21 01:29:15 · 作者: · 浏览: 6
标签: 如何 优化 SQL 计算 2）分组
非等值分组
?
按段分组是常见的需求，如成绩段（优秀，良好，…）、年龄段（青年、中年、…）等。
?
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聚合函数，利用集合数据直接返回