有了金刚钻,不惧瓷器活|在数据文件上轻松使用SQL

SQL,在数据处理和分析领域基本上类似“普通话”的地位,几乎是一项必备的能力,但是要使用 SQL,又离不开关系数据库系统,也就是 RDBMS,这就好比普通话主要还是得在中国说才管用。当然,如果为了去英国美国,学个英语还算值,这就像学个 Python、Hadoop,出去找工作也算是个技能。但是如果要在一般的文本数据,或者 Excel 表格上作分析,就像是去个基里巴斯之类的小国家,为了能够愉快的购物,难道还要卷起舌头,从背单词、学语法开始?这种时候,恐怕第一时间想到的,就是上某宝,淘个好用的翻译器吧。

创新互联建站专注为客户提供全方位的互联网综合服务,包含不限于做网站、成都做网站、江陵网络推广、成都微信小程序、江陵网络营销、江陵企业策划、江陵品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等,从售前售中售后,我们都将竭诚为您服务,您的肯定,是我们最大的嘉奖;创新互联建站为所有大学生创业者提供江陵建站搭建服务,24小时服务热线:13518219792,官方网址:www.cdcxhl.com

集算器,在这个问题上,可以说是一款居家旅行的必备神器了!

事实上,用 SQL 处理结构规整的文本或者 Excel 表格数据,除了是一种偷懒的想法外,也是一个很自然的思路。一个文件或者表格由若干数据行构成,而每行数据要么由确定的分隔符(空格、逗号、制表符……巴拉巴拉)分隔项目,要么就是规定了每个项目的固定长度。这种表示方式,和关系数据库中的表(Table)几乎是如出一辙,连变长字段和定长字段也都似乎有模有样。不同之处,是文件上没有主键、数据类型、是否可空这些概念。另外,就是文件之间关系的说明也没有像数据库那样明确,往往只是作为业务规则或者经验,存在于用户的脑袋里或者一些给人看的文件里面。

集算器的思路,也是如此,通过自动解析结构化文本或者 Excel 文件,将文件映射为 “表”,并在此基础上,充分支持 SQL 的语法和功能。


好了,闲言少叙,进入正题。我们以两个有关联的文件作为样例,看看如何在不“安装数据库 -> 建数据库表 -> 导入数据”的情况下,轻轻松松地进行查询分析:

首先,看一下样例数据,一共是两个文件:员工信息(employee.txt)和州的基本信息(state.xlsx),注意!这里我们使用了两种文件,一个是格式化的 TXT 文本,另一个是 Excel 电子表格,也就是说,集算器可以同时连接不同类型的数据源,神不神奇?意不意外?

更神奇的是,集算器可以根据文件后缀,自动识别和读取四种文件类型!分别是:文本(txt)、Excel(xls、xlsx)和 csv 文件。

下面两张图分别是员工信息和州信息的样本数据,两个文件之间通过员工信息中的 STATE 项(第 5 列)和州信息中的 STATEID 项(第 1 列)进行关联。

员工信息数据样本:

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL

州信息数据样本:

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL

好了,马上开始干活。首先,最简单的单表查询,看看员工中薪酬大于 10000(SALARY>10000)的女(GENDER=’F’)员工,输出结果按照员工编号(EID)排序,集算器代码如下:


A
1=connect()
2=A1.query(“select * from c:/sql/employee.txt where gender=’F’ and salary>10000 order by eid”)

没错,就这么简单,就这么熟悉!第 1 步,连接数据库……呃,这里没有指定参数,所以直接连接的就是文件系统,第 2 步,使用 query() 函数执行 SQL 查询,而这里的 SQL,除了把 from 后的表名,换成了文件名,别的和数据库查询一模一样!查询结果如下:

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL

注意,windows 环境下,集算器里的文件路径用斜杠“/”而不是反斜杠“\”,这和 Java 语言一致。

好吧,这也太像了,下面我们来个不太像的,查询不早于 1980 年 01 月 01 日出生的,薪酬大于 10000 的员工:


A
1$()select * from c:/sql/employee.txt where BIRTHDAY>=date(‘1980-01-01’) and SALARY>10000

很简单,使用 $()相当于 connect() 函数,后面直接写 SQL 即可。事实上,括号中可以写不同的数据源名称,从而同时连接多个数据源。

另外,这个例子使用了 SQL 中的字符串转日期的函数 date()。


接下来,是 SQL 数据库有别于单个文件的关键,关联查询。对于薪酬大于 10000 的女员工,还想再看看她们都在哪个州:


A
1=connect()
2=A1.query(“select t1.eid eid,t1.name name,t1.gender gender,t2.name state,t2.population population,t1.salary salary from c:/sql/employee.txt t1 left join c:/sql/state.xlsx t2 on t1.state=t2.stateid where t1.gender=’F’ and t1.salary>10000 “)

嗯,用文件名代替表名确实有点长,所以我们用了 SQL 中别名的用法,结果如下:

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL

除了使用别名代替文件的绝对路径,对于特别长的路径或者文件很多的情况,为了方便书写和清晰阅读,还可以在集算器 - 菜单 - 工具 - 选项中配置主目录,这样就可以在 SQL 中直接使用文件名或者相对路径了。这是不是更像指定了一个数据库,直接访问其中的表了?

配置方法如下图所示:

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL

配置了主目录后的查询是这个样子,查询工资总额大于 100000 的部门对应的人数和工资总额:


A
1=connect()
2=A1.query(“select dept,count(1) c,sum(salary) s from employee.txt group by dept having s>100000”)

查询结果如下:

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL


下面,进入一些细节内容:

1)集算器支持逻辑运算 and、or 和 not,例如:查询员工姓 Smith 或者 Robinson,并且是 Sales 部门之外的男员工:


A
1=connect()
2=A1.query(“select * from employee.txt where (surname=’Smith’ or surname=’Robinson’) and gender=’M’ and not dept=’Sales’ “)

2)集算器中,支持用 is null 来判断是否为空,用 is not null 判断非空,例如:找出 surname 为空的员工:


A
1=connect()
2=A1.query(“select EID,NAME,SURNAME from employee.txt where surname is null”)

同时支持用 coalesce 函数处理空值,例如:员工 surname 字段为空时在结果中显示为“UNKNOWN”:


A
1=connect()
2=A1.query(“select EID,NAME,SURNAME,coalesce(SURNAME,’UNKNOWN’) as SURNAME1 from employee.txt”)

查询结果为:

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL

注意:集算器中的字段别名,不能和文件中的字段名重复。

3)集算器支持 Case when,例如:性别字段为“F”的要显示为“female”,为“M”的要显示为“male”。


A
1=connect()
2=A1.query(“select EID,NAME,GENDER,(case gender when ‘F’ then ‘female’ else ‘male’ end) as GENDER1 from employee.txt”)

查询结果为:

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL

4)集算器支持 like 关键字进行模糊查询,例如:在员工中,查询 surname 字段包含“son”的员工。


A
1=connect()
2=A1.query(“select * from employee.txt where surname like ‘%son%’”)

其中的“%”为通配符,表示一个或者多个字符。另外,“_”表示一个字符。如果要查询以“son”结尾,并且前面有三个字符的情况,可以写成 surname like ‘___son’;“[WJ]”表示包含“W”和“J”的字符列表。surname like ‘[WJ]%’表示 surname 是以“W”或者“J”开头。surname like ‘[!WJ]%’表示 surname 不是以“W”或者“J”开头。

5)集算器支持通过 In 关键字在多个值中查询数据。例如:查询“Finance、Sales、R&D”三个部门的员工。


A
1=connect()
2=A1.query(“select * from employee.txt where dept in (‘Finance’,’Sales’,’R&D’) “)

6)集算器支持通过 with T as (x) 的方式定义一个外部表。例如:employee.txt 中的 state 字段和另一个数据源 demo 数据库的 state 表的 stateid 字段左连接,查出每个员工所在州的名字和人口:


A
1=connect()
2=A1.query(“with t2 as  (connect(\”demo\”).query(\”select * from states\”)) select t1.eid eid,t1.name name,t1.gender gender,t2.name state,t2.population population,t1.salary salary  from employee.txt t1 left join t2 on t1.STATE=t2.STATEID”)

在这个 SQL 中:

with t2 as  (connect(\”demo\”).query(\”select * from states\”)) 定义了一个外部表 t2,连接 demo 数据源(实际上是集算器自带的 hsql 演示数据库),用 query 函数执行 SQL“select * from states”。(其中,\”是在字符串中使用双引号的转义写法)

后边的“select t1.eid … left join t2 on t1.STATE=t2.STATEID”则利用定义好的 t2 和 employee.txt 左连接,查出每个员工所在州的名字和人口。

这个查询是典型的数据库和文本文件的联合查询。实际上,with 关键字可以定义各种数据源查出的数据,从而非常灵活的实现跨异构数据源的联合查询。

7)集算器支持通过 into to 将查询结果输出的文件中。例如:查询工资总额大于 100000 的部门对应的人数和工资总额,结果写入 deptResult.xlsx。这里,新的文件就类似关系数据数据库里的一个新表。


A
1=connect()
2=A1.query(“select dept,count(1) c,sum(salary) s into deptResult.xlsx from employee.txt group by dept having s>100000”)

说了这么多,可以看出,通过集算器,我们就能够基本实现在结构化的文本数据(txt、csv 等)和 Excel 文件(xls、xlsx)上轻松、直接地使用 SQL。

当然,集算器并不是完全“平移”复制了 SQL 的能力,对于 SQL 中的子查询,集算器目前并不能直接支持,而是会以更加灵活、方便、直观的分步式计算方式加以解决。同时,对于有些特殊的 join 计算,集算器和传统数据库相比会慢一点。


最后,我们再来看看通过集算器进行 SQL 计算,还能额外获得哪些福利:

1)根据输入参数动态计算:

在进行数据查询时,常常需要根据不同的条件进行计算,也就是我们说的动态执行。这时,我们可以定义“网格参数”,为可能发生变化的条件预留位置。例如:想要找出公司里较高薪水的年轻员工有哪些,但是年龄段和薪酬起始线还不确定,我们就可以在集算器 IDE 的菜单“程序 / 网格参数”中,定义两个参数:birthday 和 salary:

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL

然后在查询语句中用占位符“?”写出 SQL,并按顺序指定对应的网格参数名作为输入:


A
1=connect()
2=A1.query(“select * from employee.txt where BIRTHDAY>=? and SALARY>?”,birthday,salary)

如果在定义网格参数的时候指定了具体的数值,并且没有勾选“每次运行前设置参数”那么运行脚步会直接指定的数值。如果勾选了“每次运行前设置参数”,那么每次运行脚本的时候,都会弹出“设置参数值”窗口。这样,我们就可以随时输入我们需要的参数值了,相应地,查询结果也会随之改变了:

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL

2)在命令行中使用 SQL 查询文件

在 windows 或者 linux 系统中,我们还可以通过命令行中调用编写好的集算器脚本,直接对文件数据进行查询。如果结合操作系统的定时任务机制,就可以在指定时间完成批量数据计算了。

我们先看一个不返回结果集的例子。定期为财务部门提供工资总额大于 100000 的部门对应的人数和工资总额,结果写入 deptResult.xlsx(然后可以通过邮件或其他方式发送给相关人员)。

首先,编写集算器脚本,并保存为 deptResult.dfx。


A
1=connect()
2=A1.query(“select dept,count(1) c,sum(salary) s into deptResult.xlsx from employee.txt group by dept having s>100000”)
3>output(“create deptResult.xlsx successfully!”)

然后,在命令行执行 esprocx.exe 命令,(在集算器安装目录的 bin 文件夹中),执行结果:

| C:\Program Files\raqsoft\esProc\bin>esprocx.exe deptResult.dfxcreate deptResult.xlsx successfully! |

其中,第二行是 Output 函数输出的提示信息,可以用于监控程序执行和调试。


我们再看一个返回结果集的例子,同样的查询需求,但是不要求输出到文件中,而是直接查看结果。这次我们把编写的集算器脚本换个名字存为 deptQuery.dfx。


A
1=connect()
2=A1.query(“select dept,count(1) c,sum(salary) s from employee.txt group by dept having s>100000”)
3return A2

在命令行中的执行并查看结果:

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL

更进一步,集算器也可以做到直接在命令行写完整的 SQL 语句,直接从文件中返回需要查询的结果。是不是和数据库命令行查询工具一样方便?

先定义一个参数 sql,用来传入需要查询的 SQL 语句。

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL

然后编写如下集算器脚本,保存为 query.dfx,


A
1=connect()
2=A1.query(sql)
3return A2

执行命令时,在命令行中直接写 SQL 语句,结果如下:

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL

结合前面说的根据参数动态计算的方法,也可以在使用命令行计算时实现一定的交互。还是以前面说过的查询公司里薪酬较高的年轻员工为例:

在集算器 IDE 菜单“程序 / 网格参数”中,定义两个参数:birthday 和 salary。

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL

编写如下集算器脚本,保存为 empQueryParam.dfx,


A
1=connect()
2=A1.query(“select * from employee.txt where BIRTHDAY>=? and SALARY>?”,birthday,salary)
3return A2

执行命令时,按照顺序为两个参数提供数值,结果如下:

有了金刚钻,不惧瓷器活 | 在数据文件上轻松使用 SQL


至此,我们已经充分了解了利用集算器,就可以用 SQL 这把“金刚钻”来揽数据文件这些“瓷器活儿”了。其实,这个故事里,集算器才是真正的“金刚钻”!除了本文描述的将数据文件直接作为“表”来处理的方式,集算器真正有力的武器库远不止此。通过这款轻量级的数据分析工具,无论是数据库还是文件系统中的数据,都可以被轻松处理,快刀斩乱麻!


网站栏目:有了金刚钻,不惧瓷器活|在数据文件上轻松使用SQL
转载源于:http://pwwzsj.com/article/jhsecg.html