有了金剛鉆，不懼瓷器活 | 在數(shù)據(jù)文件上輕松使用 SQL

SQL，在數(shù)據(jù)處理和分析領(lǐng)域基本上類似“普通話”的地位，幾乎是一項必備的能力，但是要使用 SQL，又離不開關(guān)系數(shù)據(jù)庫系統(tǒng)，也就是 RDBMS，這就好比普通話主要還是得在中國說才管用。當然，如果為了去英國美國，學個英語還算值，這就像學個 Python、Hadoop，出去找工作也算是個技能。但是如果要在一般的文本數(shù)據(jù)，或者 Excel 表格上作分析，就像是去個基里巴斯之類的小國家，為了能夠愉快的購物，難道還要卷起舌頭，從背單詞、學語法開始？這種時候，恐怕第一時間想到的，就是上某寶，淘個好用的翻譯器吧。

集算器，在這個問題上，可以說是一款居家旅行的必備神器了！

事實上，用 SQL 處理結(jié)構(gòu)規(guī)整的文本或者 Excel 表格數(shù)據(jù)，除了是一種偷懶的想法外，也是一個很自然的思路。一個文件或者表格由若干數(shù)據(jù)行構(gòu)成，而每行數(shù)據(jù)要么由確定的分隔符（空格、逗號、制表符……巴拉巴拉）分隔項目，要么就是規(guī)定了每個項目的固定長度。這種表示方式，和關(guān)系數(shù)據(jù)庫中的表（Table）幾乎是如出一轍，連變長字段和定長字段也都似乎有模有樣。不同之處，是文件上沒有主鍵、數(shù)據(jù)類型、是否可空這些概念。另外，就是文件之間關(guān)系的說明也沒有像數(shù)據(jù)庫那樣明確，往往只是作為業(yè)務(wù)規(guī)則或者經(jīng)驗，存在于用戶的腦袋里或者一些給人看的文件里面。

集算器的思路，也是如此，通過自動解析結(jié)構(gòu)化文本或者 Excel 文件，將文件映射為 “表”，并在此基礎(chǔ)上，充分支持 SQL 的語法和功能。

好了，閑言少敘，進入正題。我們以兩個有關(guān)聯(lián)的文件作為樣例，看看如何在不“安裝數(shù)據(jù)庫 -> 建數(shù)據(jù)庫表 -> 導入數(shù)據(jù)”的情況下，輕輕松松地進行查詢分析：

首先，看一下樣例數(shù)據(jù)，一共是兩個文件：員工信息（employee.txt）和州的基本信息（state.xlsx），注意！這里我們使用了兩種文件，一個是格式化的 TXT 文本，另一個是 Excel 電子表格，也就是說，集算器可以同時連接不同類型的數(shù)據(jù)源，神不神奇？意不意外？

更神奇的是，集算器可以根據(jù)文件后綴，自動識別和讀取四種文件類型！分別是：文本（txt）、Excel（xls、xlsx）和 csv 文件。

下面兩張圖分別是員工信息和州信息的樣本數(shù)據(jù)，兩個文件之間通過員工信息中的 STATE 項（第 5 列）和州信息中的 STATEID 項（第 1 列）進行關(guān)聯(lián)。

員工信息數(shù)據(jù)樣本：

州信息數(shù)據(jù)樣本：

好了，馬上開始干活。首先，最簡單的單表查詢，看看員工中薪酬大于 10000（SALARY>10000）的女（GENDER=’F’）員工，輸出結(jié)果按照員工編號（EID）排序，集算器代碼如下：

沒錯，就這么簡單，就這么熟悉！第 1 步，連接數(shù)據(jù)庫……呃，這里沒有指定參數(shù)，所以直接連接的就是文件系統(tǒng)，第 2 步，使用 query() 函數(shù)執(zhí)行 SQL 查詢，而這里的 SQL，除了把 from 后的表名，換成了文件名，別的和數(shù)據(jù)庫查詢一模一樣！查詢結(jié)果如下：

注意，windows 環(huán)境下，集算器里的文件路徑用斜杠“/”而不是反斜杠“\”，這和 Java 語言一致。

好吧，這也太像了，下面我們來個不太像的，查詢不早于 1980 年 01 月 01 日出生的，薪酬大于 10000 的員工：

很簡單，使用 $()相當于 connect() 函數(shù)，后面直接寫 SQL 即可。事實上，括號中可以寫不同的數(shù)據(jù)源名稱，從而同時連接多個數(shù)據(jù)源。

另外，這個例子使用了 SQL 中的字符串轉(zhuǎn)日期的函數(shù) date()。

接下來，是 SQL 數(shù)據(jù)庫有別于單個文件的關(guān)鍵，關(guān)聯(lián)查詢。對于薪酬大于 10000 的女員工，還想再看看她們都在哪個州：

嗯，用文件名代替表名確實有點長，所以我們用了 SQL 中別名的用法，結(jié)果如下：

除了使用別名代替文件的絕對路徑，對于特別長的路徑或者文件很多的情況，為了方便書寫和清晰閱讀，還可以在集算器 - 菜單 - 工具 - 選項中配置主目錄，這樣就可以在 SQL 中直接使用文件名或者相對路徑了。這是不是更像指定了一個數(shù)據(jù)庫，直接訪問其中的表了？

配置方法如下圖所示：

配置了主目錄后的查詢是這個樣子，查詢工資總額大于 100000 的部門對應的人數(shù)和工資總額：

查詢結(jié)果如下：

下面，進入一些細節(jié)內(nèi)容：

1）集算器支持邏輯運算 and、or 和 not，例如：查詢員工姓 Smith 或者 Robinson，并且是 Sales 部門之外的男員工：

2）集算器中，支持用 is null 來判斷是否為空，用 is not null 判斷非空，例如：找出 surname 為空的員工：

同時支持用 coalesce 函數(shù)處理空值，例如：員工 surname 字段為空時在結(jié)果中顯示為“UNKNOWN”：

查詢結(jié)果為：

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

3）集算器支持 Case when，例如：性別字段為“F”的要顯示為“female”，為“M”的要顯示為“male”。

查詢結(jié)果為：

4）集算器支持 like 關(guān)鍵字進行模糊查詢，例如：在員工中，查詢 surname 字段包含“son”的員工。

其中的“%”為通配符，表示一個或者多個字符。另外，“_”表示一個字符。如果要查詢以“son”結(jié)尾，并且前面有三個字符的情況，可以寫成 surname like ‘___son’；“[WJ]”表示包含“W”和“J”的字符列表。surname like ‘[WJ]%’表示 surname 是以“W”或者“J”開頭。surname like ‘[!WJ]%’表示 surname 不是以“W”或者“J”開頭。

5）集算器支持通過 In 關(guān)鍵字在多個值中查詢數(shù)據(jù)。例如：查詢“Finance、Sales、R&D”三個部門的員工。

6）集算器支持通過 with T as (x) 的方式定義一個外部表。例如：employee.txt 中的 state 字段和另一個數(shù)據(jù)源 demo 數(shù)據(jù)庫的 state 表的 stateid 字段左連接，查出每個員工所在州的名字和人口：

在這個 SQL 中：

with t2 as  (connect(\”demo\”).query(\”select * from states\”)) 定義了一個外部表 t2，連接 demo 數(shù)據(jù)源（實際上是集算器自帶的 hsql 演示數(shù)據(jù)庫），用 query 函數(shù)執(zhí)行 SQL“select * from states”。（其中，\”是在字符串中使用雙引號的轉(zhuǎn)義寫法）

后邊的“select t1.eid … left join t2 on t1.STATE=t2.STATEID”則利用定義好的 t2 和 employee.txt 左連接，查出每個員工所在州的名字和人口。

這個查詢是典型的數(shù)據(jù)庫和文本文件的聯(lián)合查詢。實際上，with 關(guān)鍵字可以定義各種數(shù)據(jù)源查出的數(shù)據(jù)，從而非常靈活的實現(xiàn)跨異構(gòu)數(shù)據(jù)源的聯(lián)合查詢。

7）集算器支持通過 into to 將查詢結(jié)果輸出的文件中。例如：查詢工資總額大于 100000 的部門對應的人數(shù)和工資總額，結(jié)果寫入 deptResult.xlsx。這里，新的文件就類似關(guān)系數(shù)據(jù)數(shù)據(jù)庫里的一個新表。

說了這么多，可以看出，通過集算器，我們就能夠基本實現(xiàn)在結(jié)構(gòu)化的文本數(shù)據(jù)（txt、csv 等）和 Excel 文件（xls、xlsx）上輕松、直接地使用 SQL。

當然，集算器并不是完全“平移”復制了 SQL 的能力，對于 SQL 中的子查詢，集算器目前并不能直接支持，而是會以更加靈活、方便、直觀的分步式計算方式加以解決。同時，對于有些特殊的 join 計算，集算器和傳統(tǒng)數(shù)據(jù)庫相比會慢一點。

最后，我們再來看看通過集算器進行 SQL 計算，還能額外獲得哪些福利：

1）根據(jù)輸入?yún)?shù)動態(tài)計算：

在進行數(shù)據(jù)查詢時，常常需要根據(jù)不同的條件進行計算，也就是我們說的動態(tài)執(zhí)行。這時，我們可以定義“網(wǎng)格參數(shù)”，為可能發(fā)生變化的條件預留位置。例如：想要找出公司里較高薪水的年輕員工有哪些，但是年齡段和薪酬起始線還不確定，我們就可以在集算器 IDE 的菜單“程序 / 網(wǎng)格參數(shù)”中，定義兩個參數(shù)：birthday 和 salary：

然后在查詢語句中用占位符“？”寫出 SQL，并按順序指定對應的網(wǎng)格參數(shù)名作為輸入：

如果在定義網(wǎng)格參數(shù)的時候指定了具體的數(shù)值，并且沒有勾選“每次運行前設(shè)置參數(shù)”那么運行腳步會直接指定的數(shù)值。如果勾選了“每次運行前設(shè)置參數(shù)”，那么每次運行腳本的時候，都會彈出“設(shè)置參數(shù)值”窗口。這樣，我們就可以隨時輸入我們需要的參數(shù)值了，相應地，查詢結(jié)果也會隨之改變了：

2）在命令行中使用 SQL 查詢文件

在 windows 或者 linux 系統(tǒng)中，我們還可以通過命令行中調(diào)用編寫好的集算器腳本，直接對文件數(shù)據(jù)進行查詢。如果結(jié)合操作系統(tǒng)的定時任務(wù)機制，就可以在指定時間完成批量數(shù)據(jù)計算了。

我們先看一個不返回結(jié)果集的例子。定期為財務(wù)部門提供工資總額大于 100000 的部門對應的人數(shù)和工資總額，結(jié)果寫入 deptResult.xlsx（然后可以通過郵件或其他方式發(fā)送給相關(guān)人員）。

首先，編寫集算器腳本，并保存為 deptResult.dfx。

然后，在命令行執(zhí)行 esprocx.exe 命令，（在集算器安裝目錄的 bin 文件夾中），執(zhí)行結(jié)果：

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

其中，第二行是 Output 函數(shù)輸出的提示信息，可以用于監(jiān)控程序執(zhí)行和調(diào)試。

我們再看一個返回結(jié)果集的例子，同樣的查詢需求，但是不要求輸出到文件中，而是直接查看結(jié)果。這次我們把編寫的集算器腳本換個名字存為 deptQuery.dfx。

在命令行中的執(zhí)行并查看結(jié)果：

更進一步，集算器也可以做到直接在命令行寫完整的 SQL 語句，直接從文件中返回需要查詢的結(jié)果。是不是和數(shù)據(jù)庫命令行查詢工具一樣方便？

先定義一個參數(shù) sql，用來傳入需要查詢的 SQL 語句。

然后編寫如下集算器腳本，保存為 query.dfx，

執(zhí)行命令時，在命令行中直接寫 SQL 語句，結(jié)果如下：

結(jié)合前面說的根據(jù)參數(shù)動態(tài)計算的方法，也可以在使用命令行計算時實現(xiàn)一定的交互。還是以前面說過的查詢公司里薪酬較高的年輕員工為例：

在集算器 IDE 菜單“程序 / 網(wǎng)格參數(shù)”中，定義兩個參數(shù)：birthday 和 salary。

編寫如下集算器腳本，保存為 empQueryParam.dfx，

執(zhí)行命令時，按照順序為兩個參數(shù)提供數(shù)值，結(jié)果如下：

至此，我們已經(jīng)充分了解了利用集算器，就可以用 SQL 這把“金剛鉆”來攬數(shù)據(jù)文件這些“瓷器活兒”了。其實，這個故事里，集算器才是真正的“金剛鉆”！除了本文描述的將數(shù)據(jù)文件直接作為“表”來處理的方式，集算器真正有力的武器庫遠不止此。通過這款輕量級的數(shù)據(jù)分析工具，無論是數(shù)據(jù)庫還是文件系統(tǒng)中的數(shù)據(jù)，都可以被輕松處理，快刀斬亂麻！