2.4 有序计算方案
在充分利用遍历一次的特点进行优化后,可能我们还会觉得计算性能有点慢,希望有进一步优化的空间。由于每次只需要取出总数据量的很小一部分 (100 个指标涉及的所有科目号大概几百个,即在几百万记录中取几百条),这时我们通常能想到的是:如果能利用数据有序直接进行有序查找(若源数据有序,可以快速定位到这几百条记录,不需要遍历几百万记录甚至更多的数据),将能够获得更好的查询效率。
我们可以利用集算器提供的 iselect() 函数对每个计算的指标进行有序查找,从而减少遍历次数。这里需要注意两个关键点:
1、 iselect()函数用单个主键的查找速度会比用多个主键查找更快,并且写法上也会简单很多。
2、 在数据预处理时,遇到多个主键时应该想办法合并成一个,并且数字化后进行排序,以便使用 iselect() 函数。
关于 iselect() 函数的具体用法和有序计算的解释这里不再赘述,可参考集算器教程的相关章节。
2.4.1 合并主键、排序
为了满足上面提出的两个关键点,我们需要对源数据重新预处理一遍,关于分组计算汇总值、利用跨行组计算累计值等原理上面已经讲过了,这里主要说合并主键和排序。
第一步,在原始数据中,用“年”和“月”两列字段动态计算一个变量值,称为“月号”,以便与“科目”字段合并成唯一主键。代码中相应的改动如下:
A |
B |
|
1 |
=file("总账凭证 -pre.btx") |
|
2 |
=file("总账凭证 -mid.btx") |
|
3 |
=A1.cursor@b() |
>A3.run(((年 -inityear)*12+ 月): 月 ) |
4 |
=A3.groupx(科目, 月:月号;sum(金额): 金额 ) |
|
5 |
for A4;科目 |
=A5.run(金额 = 金额 [-1]+ 金额 ) |
6 |
>A2.export@ab(B5,#1:科目,#2: 月号,#3: 累计金额 ) |
其他格子的代码,前面已经解释过了,这里不再赘述。
B3:首先在集算器中定义参数名称:inityear,设置值为 2014,如下图:
假设原始数据是从 2014 年开始的,所以把初始年份的默认值设置为 2014。所谓“月号”就是每条记录的时间是从初始年份 1 月开始的第几个月。比如:当前一条数据记录中年是 2017,月是 3 的话,那么根据这个公式的结果:月号 =(2017-2014)*12+3,也就是 2014 年 1 月开始的第 39 个月。将计算结果利用 run() 函数重新赋值给月字段,以便后面与科目构造唯一主键。
A4:按科目、月号进行分组,金额进行求和(前面已经解释过)
B5:对金额字段进行累计(前面已经解释过)
B6:计算后的结果集以追加的方式保存到集文件中(前面已经解释过),即总账凭证 -mid.btx,执行结果如下图:
第二步,对科目前 N 位分别汇总金额;如何计算多层科目汇总值前面已经讲过了,这里主要关注月号和科目合并成主键 key,然后进行排序。月号计算出来是 2 位(假设数据记录跨度不超过 99 个月),科目为固定的 10 位,这样为了保证合并成主键后的唯一性,需要定义新主键的总长度为 12 位。
这样,新主键的构造规则就是:key(12 位)= 月号 (月号为 2 位)10000000000+ 总账科目 (最长为 10 位)。有一个技巧需要说明一下:这里设定 key 的长度为 12 位,可以存放在一个 long 类型中,如果更长 (与需求有关),就要用字符串了,虽然会相对慢一点,但也影响不大。
集算器的 SPL 脚本如下:
A |
|
1 |
=file("总账凭证 -mid.btx") |
2 |
=file("总账凭证 -later.btx") |
3 |
=A1.cursor@b() |
4 |
=channel(A3).groupx((科目 \100): 科目, 月号;sum( 累计金额): 累计金额汇总 ) |
5 |
=channel(A3).groupx((科目 \10000): 科目, 月号;sum |