集算器如何优化SQL计算（1）动态列_MySQL

结构化计算是SQL的设计目标，常规的聚合、分组、连接等结构化计算用SQL实现没有问题。SQL的计算体系是完备的，但就方便性而言，SQL对复杂运算的考虑仍有不足。具体来讲主要表现为集合化不够彻底、对离散数据和有序运算支持不足、没有对象引用机制、动态性不充分等，造成许多计算的理解和编写都很困难。而集算器在这些方面都有所改进，可以协助SQL更自然地实现复杂计算。

 

当然，集算器并不能也不打算完全替代SQL。对于SQL易于描述的运算，在数据库厂商多年努力下，已经几乎做到极致，集算器不可能也没必要超越。这里列举的都是SQL体系下难以实施或实施得很繁琐的运算，配合集算器实现可以起到事半功倍的效果。

 

这类SQL困难的运算非常杂乱，难以系统地分类罗列，这里只挑选若干较有代表性的例子呈现。

 

动态列

 

SQL认为数据列是数据的属性，应当是静态的，因而没有提供针对列上的集合运算。这样在我们事先不知道列的信息或列很多需要通用方式处理时就会显得很麻烦。

 

列间统计

 

体育测验表结构为：姓名、短跑、长跑、跳远、铅球、…；成绩等级分为优秀、良好、及格、不及格四档，现在要统计各等级在所有项目上的人数合计。

 

思路很简单，把各项目成绩合并起来再分组汇总即可。SQL要用大长串的union合并各项目，写起来很繁琐。而且列数不确定时还要从数据库中动态获取列名拼接，更为复杂。

 

集算器支持列上的集合运算，全动态写法轻松简单：

	A
1	=db.query("select * from 测验表")
2	=A1.conj(~.array().to(2,))	从第2字段的各项目成绩合并起来
3	=A2.groups(~:等级;count(1):数量)	分组汇总

对于简单的静态转置，某些数据库提供了pivot和unpivot语句实现，不支持这些语句的数据库也可以用较繁琐的条件表达式和union语句写出来。但转置结果的列由行变换而来，经常是动态的，这时就需要用SQL先算出目标列和行，然后动态拼出另一句SQL来执行，SQL实施这种运算即繁琐又难理解。

 

转置经常是为了呈现，只是行转列也可以不做处理，由报表工具完成。但相当多报表工具的行列并不对称，无法在呈现阶段实现列转行。

 

学生成绩表结构为学生、学期、数学、语文、科学、体育、艺术、…，需要双向转置为学生、科目、学期一、学期二、…。集算器脚本为：

	A	B	C
1	=db.query("select * from 成绩表 order by 学生,学期")
2	=create(学生,科目,${A1.id(学期).string()})
3	for A1.group(学生)	for 3,A1.fno()	=A3.field(B3)
4			>A2.record(A1.学生|A2.fname(B3)|C3)
5	return A2

A2中先用宏生成目标结果集，再在A3-C4的循环中将数据变换后插入到结果集，这是集算器实现转换任务的标准流程，采用分步运算使代码更清晰易懂。这个方案也可用于静态或单向转置，代码会更简单。集算器的列访问机制和动态语言的灵活性，使得各种转置，静态或动态、行转列还是列转行甚至双向同时，都可以采用一致的方案实现。

 

转置计算

 

设有帐户状态变化表T：

序号	帐户	状态	日期
1	A	透支	2014-1-4
2	A	正常	2014-1-8
3	A	挂失	2014-3-21
…

需要输出指定月份帐户每日的状态，若当日无记录，则延用前一日的状态：

帐户	1	2	3	4	5	6	7	8	9	…	31
A				透支	透支	透支	透支	正常	正常	…	正常
...

严格地说，这是个静态转置，但列数较多且有规律，完全静态写出很麻烦。而且转置过程中还涉及到列间计算，即使有pivot语句用SQL也难以写出。

 

而采用集算器则仍然按上述的流程即可简单实现：

	A	B
1	=db.query("select * from T where year(日期)=? and month(日期)=?",2014,1)
2	=create(帐号,${to(31).string()})
3	for A1.group(帐号)	=31.(null)
4		>A3.run(B3(day(日期))=状态)
5		>B3.run(~=ifn(~,~[-1])
6		>A2.record(A3.帐号|B3)
7	return A2

这里只涉及单向转置，比上例少一层循环，B3-B5中按规则计算插入数据的过程稍复杂些，但整体过程并无不同。