查询?还是计算?这不再是个问题!

发布时间:2018-04-27 分类:集算器 Tag:,

SQL到集算器的基本查询语法迁移(一)单表操作

作者:不讲理
长于自助查询,OLAP分析以及WEB系统前后端JAVA、JAVASCRIPT

cxyfqy

数据库和数据分析领域,有一个强大的存在,大名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<date(“2012-02-01”))
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<date(“2012-02-01”))
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<date(“2012-02-01”))
4 =A3.group(employeeId;~.sum(money):salesAmount)
5 =A4.select(salesAmount>5000)
6 >A1.close()

A5结果

随着查询复杂度逐步提升,集算器语句容易阅读,步骤流程清晰的特点也就凸显出来了。每一步都可以观察结果,根据结果随意控制计算流程,用最精简的概念描述每个计算步骤。这还只是一个最简单的单表查询例子,下一篇我们会继续了解在多表连接和联合的情况下,集算器会有怎样更加优秀的表现。