查询?还是计算?这不再是个问题!-欧洲杯在线开户

发布时间: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
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结果

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

 

网站地图