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

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 列）进行关联。

员工信息数据样本：

州信息数据样本：

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

注意，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 中直接使用文件名或者相对路径了。这是不是更像指定了一个数据库，直接访问其中的表了？

配置方法如下图所示：

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

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

查询结果如下：

---

下面，进入一些细节内容：

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

查询结果为：

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

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

查询结果为：

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，并按顺序指定对应的网格参数名作为输入：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

---

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