給一句 SQL 就能做多維分析

《把 Excel 透視表搬到 WEB 上》我們以 Excel 文件數據集為例，介紹了如何把 Excel 的數據透視表嵌入到俺的應用程序中。愛學習的同學會問了，Excel 數據透視表可以使用 Excel 里取到的任何數據，比如數據庫取數，你們能直接從數據庫取數做分析嗎？Yes，I Can!

俺的程序支持對任何數據庫做 SQL 數據集多維分析，只需先把數據源配置好，然后程序猿啪啪幾下敲下需要進行多維分析的 SQL 語句就可以，靈活便捷！

好了，言歸正傳，正文馬上開始！

定義物理數據源

添加 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 數據集多維分析的介紹就告于段落了，現在我們來回顧一下心路歷程：

可以看到我們從可以緩存的少量數據，到利用數據庫計算能力的大量數據都有很好的解決方案，同時還提供了相應的性能提升手段。不過，俺們 WEB 多維分析的超能力可不僅僅如此，比如還能隨意修改報表分析的表格樣式、通過建模來讀取維度和指標信息等等，想要學習潤乾報表多維分析更多知識，歡迎訪問乾學院（c.raqsoft.com.cn）。