如何透過一條資料庫語句做資料分析

定義物理資料來源

新增 SQL 資料集時首要任務就是先在 [web 應用根目錄]/WEB-INF/raqsoftConfig.xml 中定義好要使用的物理資料來源。

<DBList>
<DB name="dqldemo">
<property name="url" value="jdbc:hsqldb:hsql://127.0.0.1/demo" ></property>
<property name="driver" value="org.hsqldb.jdbcDriver" ></property>
<property name="type" value="13" ></property>
<property name="user" value="sa" ></property>
<property name="password" ></property>
… …
</DB>
… …
</DBList>

介面新增 SQL 資料集

物理資料來源建立好後，接下來最重要的一步就是新增 SQL 資料集了。只需選擇物理資料來源，寫上 SQL 語句，點選 查詢資料，快取入檔案儲存就 ok 了！沒錯，就是這麼方便！

資料集建立完成後萬事俱備只欠東風，接下來就可以開始多維分析的體驗之旅了。

點選【新增報表】，選擇資料集，填寫報表名稱【確定】，齊活！

資料集有了，分析指標有了，現在就可以任性拖拽你想要查詢的指標了，對 SQL 資料集的分析也能像檔案分析從單機一步躍升 WEB 環境了，是不是又找到了倍兒爽的感覺？哈哈

先拖拽幾個指標找找手感：

咦？咋列名都是程式碼，不是中文？這樣感覺不友好！同學，這個問題提的好，其實答案很簡單，分析出來的報表列名就是用的 SQL 資料集的欄位名，所以只要使 SQL 資料集返回的欄位名是中文就行！

利用 AS 返回中文欄位名

修改資料集 SQL 後，點選 重新查詢資料， 快取入檔案，再次新增報表，發現欄位名變成中文了吧！

怎麼樣，看著舒服了吧，一不小心又 get 到新技能！不過看了上面的結果愛提問的同學可能又有想法了：部門怎麼顯示的是編號，這 0123 代表啥俺又不知道，那咋做分析？哈哈，小編猜到你的想法，早有準備，嘖嘖。。。

LEFT JOIN 搞定編號程式碼顯示

把部門編號顯示成部門名稱，百變不離其中，還是取決於 SQL 資料集的 SQL 語句。透過 LEFT JOIN 做多表關聯就可以輕鬆搞定！

好了，透過上面的學習有沒有發現其實資料集全是由 SQL 語句決定的，這裡的 SQL 語句只要符合 SQL 標準語法就行，比如可以透過 AS 對欄位重新命名，LEFT JOIN 多表關聯實現名稱的顯示等等，總之一句話：一切向 SQL 看齊！

利用 Tag 標籤新增 SQL 資料集

除了上述這種在介面上新增 SQL 資料集的方式外，其實還可以透過 Tag 標籤新增。

SQL 資料集和檔案資料集在 Tag 標籤的使用大同小異，只是屬性略有不同。

Tag 標籤新增 SQL 資料集：

<raqsoft:analysev2
dataSource="dqldemo"  //指定SQL需要使用的物理資料來源
sql="select * from EMPLOYEE" //SQL語句
… …
>

在 Tag 標籤中新增 SQL 資料集，訪問多維分析頁面時會將該資料集預設作為初始資料集，並直接將結果集以明細的形式展現在頁面中。

注重細節的同學是不是又發現了端倪？怎麼使用 Tag 標籤新增資料集時沒有點選【 查詢資料，快取入檔案】結果也能正常展現呢？難道這兩種新增資料集的方式還有什麼不同嗎？

其實這兩種方式的底層對資料的處理機制是一樣的，唯一的不同點就是 Tag 標籤新增資料集可以理解為是直接為分析頁面設定了初始資料集，這個時候程式會自動生成臨時檔案，然後將計算結果存入，這樣一來就不用勞您大駕再動手快取了。不過如果您還需要繼續在頁面上對初始資料集修改或者新增新的資料集，那就還得按最開始講的那樣兒，每次修改 SQL 語句後，都需要點選 【查詢資料，快取入檔案】 重新生成了臨時檔案了。預設臨時檔案命名規則是以時間毫秒陣列合而成。

怎麼樣，SQL 資料集在俺自己的應用程式裡用起來是不是也超便捷？不論是在頁面資料集選單中新增還是在 Tag 標籤裡新增，其實都是先讓集算器透過 SQL 取數，然後將返回的結果集快取入臨時檔案，之後在頁面上的多維分析就都是基於這個臨時資料檔案取數了。這樣一來，借用集算器的計算能力，在介面上就能做拖拽分組、聚合、過濾等等資料分析動作了。

關於快取檔案的生命週期小編還是要多嘮叨幾句，簡單分為如下三點：

1、當快取檔案的父目錄路徑中包含”temp”字樣時，如：/Raqsofttemp/ .txt 或 /temp/.txt，程式會在 Session 超時時自動清理該快取檔案

2、生成快取檔案時，如果發現快取檔案不存在，則會自動根據資料集的配置重新生成快取檔案

3、如果快取檔案路徑中沒有 temp 字樣，則不會對快取檔案做任何刪除處理，由客戶的系統自己管理這些快取檔案的生命週期

快取檔案的字尾是.txt，聰明的你肯定就能猜潤乾報表是用 TXT 檔案作為中介的，但 TXT 檔案的效能有點差，而且資料型別也不那麼精確，個別情況會出點小錯。其實從 SQL 中讀出來的資料已經是二進位制格式，再轉成文字儲存有點費時費力不討好。

那麼，還能怎麼弄？

潤乾報表提供了二進位制格式的快取檔案，不過，這時候我得說一句不過了，這個功能需要收費的整合集算器才能支援了。如果已經有了這個功能元件，那麼就簡單了。

二進位制快取檔案提升效能

開啟 [WEB 應用根目錄]/raqsoft/guide/jsp/olap.jsp 新增如下 JS API 指令碼：

<script>
guideConf.dataFileType = 'binary'; //binary是二進位制檔案，會以遊標方式讀取，能支援超出記憶體的資料集；該屬性的預設為text文字檔案型別。
</script>

好了，設定完了，再快取時就是以二進位制檔案的型別儲存臨時檔案了。

咋樣，是不是超簡單？超便利？超喜歡？

不過話又說回來，上面這種快取入檔案的方式對於資料量不大的情況沒問題，但有時候我們希望利用資料庫的計算能力（畢竟資料庫還可以叢集分佈等），那又該怎麼辦呢？

不要慌，潤乾報表幫你忙！

非快取 SQL 資料集

在 JSP 指令碼中新增不需要快取資料的 SQL 資料集。操作起來也不難，只先敲上一個做原始查詢的 SQL 語句就行。

將 SQL 語句傳遞給資料庫做查詢，查詢後分析介面會直接將結果集中的欄位列出，進行拖拽就可以了。

下面以一個多表查詢做舉例，在 [demo 應用根目錄]/raqsoft/guide/jsp/olap.jsp 新增 JS API 進行設定。

<script>
guideConf.sqlId="<%=sqlId%>";//指定sqlid
var sqlDatasets = [
{
sqlId:"sqlId1" //指定資料集名稱，不可重複
,dataSource:"dqldemo" //指定SQL語句所使用的資料來源
,sql:"SELECT EMPLOYEE.Name as 僱員姓名,DEPARTMENT.NAME as 部門,EMPLOYEE.COUNTRY as 國家 FROM EMPLOYEE LEFT JOIN DEPARTMENT ON EMPLOYEE.DEPTID=DEPARTMENT.ID" //指定資料集SQL語句
,fields:null
},
… …
]
… …
</script>

指令碼新增完成後，訪問分析頁面時只需要在 URL 裡為 sqlId 引數傳入資料集名稱，如下圖所示，使用名稱為 sqlId1 的資料集：

將 SQL 資料集作為資料來源設定後，就能針對這個資料集分析了，但分析過程不會像快取入檔案型別的資料集那樣預先載入這個資料集的資料，而是透過在這個 SQL 上追加更多的查詢條件、分組、聚合子句方式實時在資料庫中查詢的，如：
select f1 , sum(f2) from (${ 原始 sql}) t where … group by …. having ….

簡單 SQL 提升效能

基於上面這種做法，對效能有高要求的同學估計會有情緒了，很多 DB 不會最佳化，這樣做就會搞的很慢，體驗並不好。

哈哈，莫捉急，為了提高效能我們針對簡單 SQL 還有更智慧的自動改善效能的手段，所謂簡單 SQL 就是單表無分組的 SQL 語句。SQL 會在被解析後再重組 SQL，而不是用比較慢的子查詢語句。不過當 SQL 中包含 JOIN、分組、子查詢、UNION 等複雜查詢功能時，由於無法進行拆解，因此程式還是會自動選用子查詢方式進行處理。

設定單表無分組的簡單 SQL：

<script>
guideConf.sqlId="<%=sqlId%>";//指定sqlid
var sqlDatasets = [
{
sqlId:"sqlId1" //指定資料集名稱，不可重複
,dataSource:"dqldemo" //指定SQL語句所使用的資料來源
,sql:"SELECT * from 客戶" //指定資料集SQL語句
,fields:null
},
… …
]
… …
</script>

訪問分析頁面，拖拽指標，如下圖所示：

像上面例子這樣最終拖拽後基於原始 SQL" select * from 客戶" 生成的 SQL 語句為： SELECT 地區 地區, 城市 城市,count(客戶 ID) 客戶 ID 計數 FROM 客戶 t GROUP BY 地區, 城市。

好了，對 SQL 資料集多維分析的介紹就告於段落了，現在我們來回顧一下心路歷程：

可以看到我們從可以快取的少量資料，到利用資料庫計算能力的大量資料都有很好的解決方案，同時還提供了相應的效能提升手段。