查询？还是计算？这不再是个问题！-九游会登陆
从sql到集算器的基本查询语法迁移（一）单表操作
作者：不讲理
长于自助查询，olap分析以及web系统前后端java、javascript
数据库和数据分析领域，有一个强大的存在，大名sql，全名结构化查询语言(structured query language)。从字面上看，这个语言的目标就是把数据“查询”出来，而查询这个词给人的感觉并不是很难。但实际上，为了支持貌似简单的查询，需要进行大量的计算动作，甚至整个过程就是一个多步骤的计算，前后步骤之间还有很强的依赖关系，前面计算的结果要被后面使用，而后面的输出有可能需要我们对前面的计算进行调整。
打个比方，这有点类似于去各类网点办事，填表递交后，相关办事人员开始在窗口后忙忙碌碌，时不时还会甩回来几个问题要求澄清，等到最后拿到回复，还有可能根本不是自己期望的结果！这时候，坐在办事窗口外的我们，抓狂之余，往往会产生一个念头，如果我们能够看到，甚至参与到过程中，应该能够大大地提高办事效率。
没错，你应该能想到，下面要介绍的集算器，和sql相比对于我们这些过程控来说，就是一个可以轻松把玩和控制的计算（不止是查询）工具。
我们要做的，就是“照猫画虎”地把习惯中的sql操作迁移到集算器中，用小小的习惯改变，换来大大的效益提升。
首先，我们需要把数据从传统的数据源中“搬迁”到集算器中，这样后续的操作就可以完全在集算器中进行了。
我们最常用的数据源一般就是关系数据库rdb。这里使用的样例数据，就是数据库中的两个数据表：
订单信息表（order，主键orderid），包括订单编号orderid、客户代码customerid、雇员编号employeeid、订单日期orderdate、发送日期senddate以及金额money：
| orderid | customerid | employeeid | orderdate | senddate | money |
| 10248 | vinet | 2 |
2011-02-04 |
2011-02-16 |
440 |
| 10249 | tomsp | 9 |
2011-02-05 |
2011-02-10 |
1863 |
| 10250 | hanar | 9 |
2011-02-08 |
2011-02-12 |
1813 |
| 10251 | victe | 9 |
2011-02-08 |
2011-02-15 |
670 |
订单明细表（orderdetail，主键orderid，productid），包括订单编号orderid、产品编号productid、价格price、数量amount、折扣discount：
| orderid | productid | price | amount | discount |
| 11059 | 17 | 39 | 12 | 0.85 |
| 11059 | 60 | 34 | 35 | 0.9 |
| 11060 | 60 | 34 | 4 | 1 |
| 11060 | 77 | 13 | 10 | 0.95 |
“搬迁”，或者说导入数据的过程非常简单，如下图所示：
| 集算器 | a |
| 1 | =connect(“hsqldb”) |
| 2 | =a1.query(“select * from order”) |
| 3 | >a1.close() |
首先建立数据库连接（网格a1），然后直接通过单表全量查询的sql语句从数据库中读取数据（网格a2），最后清理现场，关闭数据库连接（网格a3）。
在执行了脚本后，我们可以选中网格a2，在结果区中看看搬过来的数据，同时，order表在集算器中也换了个身份，我们称之为“序表”，用网格名称a2代表。序表是集算器中一个非常重要的概念，现在我们可以简单地把它理解成对应数据库中的一张表：
其实，在集算器中，任何一个有计算结果的网格（一般是用等号“=”开始），都可以在执行脚本后，随时选中并查看结果，并通过网格名称a7、b8等随时引用，从而满足我们随时监控的欲望……
接下来，我们以sql中select语句的各个子句为线索，看看集算器中是如何操作的：
第一个：select子句
用来选择需要查询的字段，也可以通过表达式来对字段进行计算，或者重命名字段。与之对应的，集算器里有new、derive、run三个函数。
例如：只选出订单表里的订单编号、雇员编号、订单日期以及金额字段，同时把金额乘以100后使它的单位从元变成分，把相应的结果字段重命名为centmoney。
sql的做法如下：
| sql |
| select orderid,employeeid,orderdate,money*100 centmoney from order |
集算器对应的做法是下表中的a3：
| 集算器 | a |
| 1 | =connect(“hsqldb”) |
| 2 | =a1.query(“select * from order”) |
| 3 | =a2.new(orderid,employeeid,orderdate,money*100:centmoney) |
| 4 | =a3.derive(year(orderdate):orderyear) |
| 5 | =a4.run(string(orderyear) ”年”:orderyear) |
| 6 | >a1.close() |
a3利用a2的数据新建了一个序表，包含了要求的字段，包括把金额乘以100后用centmoney命名：
我们继续看下a4的结果，在序表a3的原有字段后增加了新字段orderyear，这也就是说derive(新计算字段) 相当于new(所有老字段, 新计算字段)，可以看做是new函数的一种简单的写法，免去了把老字段再抄写一遍。
a5使用了run函数，直接作用是修改老字段orderyear的值，但是再看a4的结果，也变成和a5一样了。这是因为run函数并没有像new、derive函数那样生成新的序表对象，而是在原来对象上直接做修改。
总结一下，在集算器中，new、derive、run函数都会产生序表结果，但是new、derive函数会生成一个新的序表对象，像是把数据复制出来（这个行为有个专有名词immutable），而run则直接修改被操作对象（行为属于mutable）。
【延伸阅读】之所以提出mutable这样的行为，有两个因素：首先是减少内存占用，从而提高性能；其次，有些实际业务的需求就需要改变原有对象，一系列的动作直接作用在一个对象上，到最后一步就得到正确结果了，而不是得到一堆中间结果，再做最后合并的动作。当然immutable也有它的适用场景，两种行为本身没有优劣之分。
第二个：where子句
用来对数据表按条件进行过滤。与之对应的，集算器通过select函数对一个序表的记录进行过滤。效果如下图所示：
针对前面的示例数据，我们希望查询指定时段（2012年1月期间）的订单数据，可以对比一下sql和集算器（网格a3）的做法。
| sql |
| select * from order where orderdate>=’2012-01-01′ and orderdate<‘2012-02-01’ |
| 集算器 | a |
| 1 | =connect(“hsqldb”) |
| 2 | =a1.query(“select * from order”) |
| 3 | =a2.select(orderdate>=date(“2012-01-01”) && orderdate |
| 4 | >a1.close() |
需要注意一下集算器表达式中有两个细节：一是用了date函数把字符串转换成日期类型，二是and/or在集算器里的写法是&&/||。
a3的结果如下：
看起来和a2结构一致，只是数据量小了。但是我们可以做一个实验，在网格b3中输入“=a2.select(orderid=10555).run(money*10:money)”，修改a2序表中某些记录的字段值，可以看到a3序表里这些对应记录的值也跟着变化了。这就说明两个序表里的记录就是同一个东西（叫做对象会显得更专业点），也就是说集算器里的select函数属于我们前面所说的mutable行为。
第三个：group by子句
groupy by经常和聚合函数sum、count等一起出现，用来将查询结果按照某些字段进行归类分组，并汇总统计。严格来说，这是两个独立的动作，但在sql中总是一起出现，从而给人一种两者必须同时使用的假象。事实上，这种组合是对分组操作的一种局限，或者说分组之后，能够进行的计算远不止sql中的几种聚合函数。在集算器中，与group by子句对应的是 group函数，它可以只对数据纯粹分组，每组形成一个小集合，在后面的计算中可以针对这些小集合进行简单的聚合，也可以进行更加复杂的计算。下图是sql中利用group by进行分组求和的示意：
同样还是前面的示例数据，我们希望计算2012年1月期间每个雇员的销售总额，也就是按照雇员编号分组后求和。针对这个分组求和的计算，我们对比一下sql和集算器的做法：
| sql |
| select employeeid, sum(money) salesamount from order where orderdate>=’2012-01-01′ and orderdate<‘2012-02-01’ group by employeeid |
| 集算器 | a |
| 1 | =connect(“hsqldb”) |
| 2 | =a1.query(“select * from order”) |
| 3 | =a2.select(orderdate>=date(“2012-01-01”) && orderdate |
| 4 | =a3.group(employeeid;~.sum(money):salesamount) |
| 5 | >a1.close() |
a4的结果如下：
集算器把查询分解成了三步：
首先，是a2取出订单表中的所有记录；
然后，a3过滤得到指定时段（2012年1月期间）的订单记录
最后，a4把过滤得到的记录按照雇员编号进行分组（由函数参数中分号前的部分定义，可以同时指定多个字段），同时对每个分组（用“~”符号表示）进行求和（sum）计算，并把计算结果的字段命名为salesamount（分号后的部分）
看起来和sql分组没什么不用，这只是因为我们这个例子只演示了和sql相同的分组查询。实际上a4里group函数的后半部分不是必须的，也可能有的业务仅仅是想得到分组后的结果，而不在这里进行求和、计数运算；也可能针对特定值的分组有不同的聚合运算，那就针对分组后的小集合“~”写更复杂的表达式就可以了。
同时，在其他教程中，我们还可以了解到，分组字段不局限于原始字段，可以是一个表达式，这个和sql里一样。
单纯的分组属于mutable行为，是把一个大集合的记录拆分到多个小集合里。而加上聚合运算后，因为产生了新的数据结构，就不再是简单的把原有记录挪挪地方的操作了。
第四个：having子句
用来对group by后的聚合结果再进行过滤。在集算器里，就没有专门对应having的函数了，对任何序表按照条件过滤都用select函数，因为计算是分步的，操作指向的对象总是明确的。而sql要求一句话把所有事情表达出来，遇到复杂查询，就难免出现对同一性质的操作增加概念，以表达作用到不同的对象上。再深想一下，having概念在sql里也不是必须的，它是对第一层子查询的简化写法：
select f1, sum(f2) f2 from t group by f1 having sum(f2)>100
等价于
select * from
(select f1, sum(f2) f2sum from t group by f1) t2
where f2sum >100
对更多层子查询做同类简化，估计会出现having2、having3…类似的关键字，但having2之后的简化性价比不高，sql也就没有提供了。这里又体现出分步计算的一个优势，只需要描述计算本质需要的概念，having、子查询这些因为技术手段被迫产生的概念就可以弃用了。减少非必要概念是降低学习成本的重要手段。
我们具体看一下sql和集算器的做法的对比，找到2012年1月期间销售额超过5000的雇员编号和他的销售总额：
| sql |
| select employeeid, sum(money) salesamount from order where orderdate>=’2012-01-01′ and orderdate<‘2012-02-01’ group by employeeid having sum(money)>5000 |
| 集算器 | a |
| 1 | =connect(“hsqldb”) |
| 2 | =a1.query(“select * from order”) |
| 3 | =a2.select(orderdate>=date(“2012-01-01”) && orderdate |
| 4 | =a3.group(employeeid;~.sum(money):salesamount) |
| 5 | =a4.select(salesamount>5000) |
| 6 | >a1.close() |
a5结果
随着查询复杂度逐步提升，集算器语句容易阅读，步骤流程清晰的特点也就凸显出来了。每一步都可以观察结果，根据结果随意控制计算流程，用最精简的概念描述每个计算步骤。这还只是一个最简单的单表查询例子，下一篇我们会继续了解在多表连接和联合的情况下，集算器会有怎样更加优秀的表现。