ITPub博客

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

原创 数据治理 作者:raqsoft 时间:2018-08-23 16:13:06 0 删除 编辑

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

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

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

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


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

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

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

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

员工信息数据样本:

imagepng

州信息数据样本:

imagepng

好了,马上开始干活。首先,最简单的单表查询,看看员工中薪酬大于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后的表名,换成了文件名,别的和数据库查询一模一样!查询结果如下:

imagepng

注意,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中别名的用法,结果如下:

imagepng

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

配置方法如下图所示:

imagepng

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


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

查询结果如下:

imagepng


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

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”)

查询结果为:
imagepng 注意:集算器中的字段别名,不能和文件中的字段名重复。

 

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”)

查询结果为:

imagepng

 

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:
imagepng

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


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

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

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”)
3 return A2

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

imagepng  

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

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

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


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

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

imagepng

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

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

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


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

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

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31551377/viewspace-2212681/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-08-21

  • 博文量
    3
  • 访问量
    7395