給一句 SQL 就能做多維分析

《把 Excel 透視表搬到 WEB 上》我們以 Excel 文件數(shù)據(jù)集為例，介紹了如何把 Excel 的數(shù)據(jù)透視表嵌入到俺的應(yīng)用程序中。愛(ài)學(xué)習(xí)的同學(xué)會(huì)問(wèn)了，Excel 數(shù)據(jù)透視表可以使用 Excel 里取到的任何數(shù)據(jù)，比如數(shù)據(jù)庫(kù)取數(shù)，你們能直接從數(shù)據(jù)庫(kù)取數(shù)做分析嗎？Yes，I Can!

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

好了，言歸正傳，正文馬上開(kāi)始！

定義物理數(shù)據(jù)源

添加 SQL 數(shù)據(jù)集時(shí)首要任務(wù)就是先在 [web 應(yīng)用根目錄](méi)/WEB-INF/raqsoftConfig.xml 中定義好要使用的物理數(shù)據(jù)源。

<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 數(shù)據(jù)集

物理數(shù)據(jù)源創(chuàng)建好后，接下來(lái)最重要的一步就是添加 SQL 數(shù)據(jù)集了。只需選擇物理數(shù)據(jù)源，寫(xiě)上 SQL 語(yǔ)句，點(diǎn)擊查詢(xún)數(shù)據(jù)，緩存入文件保存就 ok 了！沒(méi)錯(cuò)，就是這么方便！

數(shù)據(jù)集創(chuàng)建完成后萬(wàn)事俱備只欠東風(fēng)，接下來(lái)就可以開(kāi)始多維分析的體驗(yàn)之旅了。

點(diǎn)擊【添加報(bào)表】，選擇數(shù)據(jù)集，填寫(xiě)報(bào)表名稱(chēng)【確定】，齊活！

數(shù)據(jù)集有了，分析指標(biāo)有了，現(xiàn)在就可以任性拖拽你想要查詢(xún)的指標(biāo)了，對(duì) SQL 數(shù)據(jù)集的分析也能像文件分析從單機(jī)一步躍升 WEB 環(huán)境了，是不是又找到了倍兒爽的感覺(jué)？哈哈

先拖拽幾個(gè)指標(biāo)找找手感：

咦？咋列名都是代碼，不是中文？這樣感覺(jué)不友好！同學(xué)，這個(gè)問(wèn)題提的好，其實(shí)答案很簡(jiǎn)單，分析出來(lái)的報(bào)表列名就是用的 SQL 數(shù)據(jù)集的字段名，所以只要使 SQL 數(shù)據(jù)集返回的字段名是中文就行！

利用 AS 返回中文字段名

修改數(shù)據(jù)集 SQL 后，點(diǎn)擊重新查詢(xún)數(shù)據(jù)，緩存入文件，再次添加報(bào)表，發(fā)現(xiàn)字段名變成中文了吧！

怎么樣，看著舒服了吧，一不小心又 get 到新技能！不過(guò)看了上面的結(jié)果愛(ài)提問(wèn)的同學(xué)可能又有想法了：部門(mén)怎么顯示的是編號(hào)，這 0123 代表啥俺又不知道，那咋做分析？哈哈，小編猜到你的想法，早有準(zhǔn)備，嘖嘖。。。

LEFT JOIN 搞定編號(hào)代碼顯示

把部門(mén)編號(hào)顯示成部門(mén)名稱(chēng)，百變不離其中，還是取決于 SQL 數(shù)據(jù)集的 SQL 語(yǔ)句。通過(guò) LEFT JOIN 做多表關(guān)聯(lián)就可以輕松搞定！

好了，通過(guò)上面的學(xué)習(xí)有沒(méi)有發(fā)現(xiàn)其實(shí)數(shù)據(jù)集全是由 SQL 語(yǔ)句決定的，這里的 SQL 語(yǔ)句只要符合 SQL 標(biāo)準(zhǔn)語(yǔ)法就行，比如可以通過(guò) AS 對(duì)字段重命名，LEFT JOIN 多表關(guān)聯(lián)實(shí)現(xiàn)名稱(chēng)的顯示等等，總之一句話(huà)：一切向 SQL 看齊！

利用 Tag 標(biāo)簽添加 SQL 數(shù)據(jù)集

除了上述這種在界面上添加 SQL 數(shù)據(jù)集的方式外，其實(shí)還可以通過(guò) Tag 標(biāo)簽添加。

SQL 數(shù)據(jù)集和文件數(shù)據(jù)集在 Tag 標(biāo)簽的使用大同小異，只是屬性略有不同。

Tag 標(biāo)簽添加 SQL 數(shù)據(jù)集：

<raqsoft:analysev2

dataSource="dqldemo"  //指定SQL需要使用的物理數(shù)據(jù)源

sql="select * from EMPLOYEE" //SQL語(yǔ)句

… …

>

在 Tag 標(biāo)簽中添加 SQL 數(shù)據(jù)集，訪(fǎng)問(wèn)多維分析頁(yè)面時(shí)會(huì)將該數(shù)據(jù)集默認(rèn)作為初始數(shù)據(jù)集，并直接將結(jié)果集以明細(xì)的形式展現(xiàn)在頁(yè)面中。

注重細(xì)節(jié)的同學(xué)是不是又發(fā)現(xiàn)了端倪？怎么使用 Tag 標(biāo)簽添加數(shù)據(jù)集時(shí)沒(méi)有點(diǎn)擊【查詢(xún)數(shù)據(jù)，緩存入文件】結(jié)果也能正常展現(xiàn)呢？難道這兩種添加數(shù)據(jù)集的方式還有什么不同嗎？

哈哈，迷糊了吧，×××扔的真奏效

其實(shí)這兩種方式的底層對(duì)數(shù)據(jù)的處理機(jī)制是一樣的，唯一的不同點(diǎn)就是 Tag 標(biāo)簽添加數(shù)據(jù)集可以理解為是直接為分析頁(yè)面設(shè)置了初始數(shù)據(jù)集，這個(gè)時(shí)候程序會(huì)自動(dòng)生成臨時(shí)文件，然后將計(jì)算結(jié)果存入，這樣一來(lái)就不用勞您大駕再動(dòng)手緩存了。不過(guò)如果您還需要繼續(xù)在頁(yè)面上對(duì)初始數(shù)據(jù)集修改或者添加新的數(shù)據(jù)集，那就還得按最開(kāi)始講的那樣兒，每次修改 SQL 語(yǔ)句后，都需要點(diǎn)擊 【查詢(xún)數(shù)據(jù)，緩存入文件】 重新生成了臨時(shí)文件了。默認(rèn)臨時(shí)文件命名規(guī)則是以時(shí)間毫秒數(shù)組合而成。

怎么樣，SQL 數(shù)據(jù)集在俺自己的應(yīng)用程序里用起來(lái)是不是也超便捷？不論是在頁(yè)面數(shù)據(jù)集菜單中添加還是在 Tag 標(biāo)簽里添加，其實(shí)都是先讓集算器通過(guò) SQL 取數(shù)，然后將返回的結(jié)果集緩存入臨時(shí)文件，之后在頁(yè)面上的多維分析就都是基于這個(gè)臨時(shí)數(shù)據(jù)文件取數(shù)了。這樣一來(lái)，借用集算器的計(jì)算能力，在界面上就能做拖拽分組、聚合、過(guò)濾等等數(shù)據(jù)分析動(dòng)作了。

關(guān)于緩存文件的生命周期小編還是要多嘮叨幾句，簡(jiǎn)單分為如下三點(diǎn)：

1、當(dāng)緩存文件的父目錄路徑中包含”temp”字樣時(shí)，如：/Raqsofttemp/.txt 或 /temp/.txt，程序會(huì)在 Session 超時(shí)時(shí)自動(dòng)清理該緩存文件

2、生成緩存文件時(shí)，如果發(fā)現(xiàn)緩存文件不存在，則會(huì)自動(dòng)根據(jù)數(shù)據(jù)集的配置重新生成緩存文件

3、如果緩存文件路徑中沒(méi)有 temp 字樣，則不會(huì)對(duì)緩存文件做任何刪除處理，由客戶(hù)的系統(tǒng)自己管理這些緩存文件的生命周期

緩存文件的后綴是.txt，聰明的你肯定就能猜潤(rùn)乾報(bào)表是用 TXT 文件作為中介的，但 TXT 文件的性能有點(diǎn)差，而且數(shù)據(jù)類(lèi)型也不那么精確，個(gè)別情況會(huì)出點(diǎn)小錯(cuò)。其實(shí)從 SQL 中讀出來(lái)的數(shù)據(jù)已經(jīng)是二進(jìn)制格式，再轉(zhuǎn)成文本保存有點(diǎn)費(fèi)時(shí)費(fèi)力不討好。

那么，還能怎么弄？

潤(rùn)乾報(bào)表提供了二進(jìn)制格式的緩存文件，不過(guò)，這時(shí)候我得說(shuō)一句不過(guò)了，這個(gè)功能需要收費(fèi)的集成集算器才能支持了。如果已經(jīng)有了這個(gè)功能組件，那么就簡(jiǎn)單了。

二進(jìn)制緩存文件提升性能

打開(kāi) [WEB 應(yīng)用根目錄](méi)/raqsoft/guide/jsp/olap.jsp 添加如下 JS API 腳本：

<script>

guideConf.dataFileType = 'binary'; //binary是二進(jìn)制文件，會(huì)以游標(biāo)方式讀取，能支持超出內(nèi)存的數(shù)據(jù)集；該屬性的默認(rèn)為text文本文件類(lèi)型。

</script>

好了，設(shè)置完了，再緩存時(shí)就是以二進(jìn)制文件的類(lèi)型保存臨時(shí)文件了。

咋樣，是不是超簡(jiǎn)單？超便利？超喜歡？

不過(guò)話(huà)又說(shuō)回來(lái)，上面這種緩存入文件的方式對(duì)于數(shù)據(jù)量不大的情況沒(méi)問(wèn)題，但有時(shí)候我們希望利用數(shù)據(jù)庫(kù)的計(jì)算能力（畢竟數(shù)據(jù)庫(kù)還可以集群分布等），那又該怎么辦呢？

不要慌，潤(rùn)乾報(bào)表幫你忙！

非緩存 SQL 數(shù)據(jù)集

在 JSP 腳本中添加不需要緩存數(shù)據(jù)的 SQL 數(shù)據(jù)集。操作起來(lái)也不難，只先敲上一個(gè)做原始查詢(xún)的 SQL 語(yǔ)句就行。

將 SQL 語(yǔ)句傳遞給數(shù)據(jù)庫(kù)做查詢(xún)，查詢(xún)后分析界面會(huì)直接將結(jié)果集中的字段列出，進(jìn)行拖拽就可以了。

下面以一個(gè)多表查詢(xún)做舉例，在 [demo 應(yīng)用根目錄](méi)/raqsoft/guide/jsp/olap.jsp 添加 JS API 進(jìn)行設(shè)置。

<script>

guideConf.sqlId="<%=sqlId%>";//指定sqlid

var sqlDatasets = [

{

sqlId:"sqlId1" //指定數(shù)據(jù)集名稱(chēng)，不可重復(fù)

,dataSource:"dqldemo" //指定SQL語(yǔ)句所使用的數(shù)據(jù)源

,sql:"SELECT EMPLOYEE.Name as 雇員姓名,DEPARTMENT.NAME as 部門(mén),EMPLOYEE.COUNTRY as 國(guó)家 FROM EMPLOYEE LEFT JOIN DEPARTMENT ON EMPLOYEE.DEPTID=DEPARTMENT.ID" //指定數(shù)據(jù)集SQL語(yǔ)句

,fields:null

},

… …

]

… …

</script>

腳本添加完成后，訪(fǎng)問(wèn)分析頁(yè)面時(shí)只需要在 URL 里為 sqlId 參數(shù)傳入數(shù)據(jù)集名稱(chēng)，如下圖所示，使用名稱(chēng)為 sqlId1 的數(shù)據(jù)集：

將 SQL 數(shù)據(jù)集作為數(shù)據(jù)來(lái)源設(shè)置后，就能針對(duì)這個(gè)數(shù)據(jù)集分析了，但分析過(guò)程不會(huì)像緩存入文件類(lèi)型的數(shù)據(jù)集那樣預(yù)先加載這個(gè)數(shù)據(jù)集的數(shù)據(jù)，而是通過(guò)在這個(gè) SQL 上追加更多的查詢(xún)條件、分組、聚合子句方式實(shí)時(shí)在數(shù)據(jù)庫(kù)中查詢(xún)的，如：
select f1 , sum(f2) from (${ 原始 sql}) t where … group by …. having ….

簡(jiǎn)單 SQL 提升性能

基于上面這種做法，對(duì)性能有高要求的同學(xué)估計(jì)會(huì)有情緒了，很多 DB 不會(huì)優(yōu)化，這樣做就會(huì)搞的很慢，體驗(yàn)并不好。

哈哈，莫捉急，為了提高性能我們針對(duì)簡(jiǎn)單 SQL 還有更智能的自動(dòng)改善性能的手段，所謂簡(jiǎn)單 SQL 就是單表無(wú)分組的 SQL 語(yǔ)句。SQL 會(huì)在被解析后再重組 SQL，而不是用比較慢的子查詢(xún)語(yǔ)句。不過(guò)當(dāng) SQL 中包含 JOIN、分組、子查詢(xún)、UNION 等復(fù)雜查詢(xún)功能時(shí)，由于無(wú)法進(jìn)行拆解，因此程序還是會(huì)自動(dòng)選用子查詢(xún)方式進(jìn)行處理。

設(shè)置單表無(wú)分組的簡(jiǎn)單 SQL：

<script>

guideConf.sqlId="<%=sqlId%>";//指定sqlid

var sqlDatasets = [

{

sqlId:"sqlId1" //指定數(shù)據(jù)集名稱(chēng)，不可重復(fù)

,dataSource:"dqldemo" //指定SQL語(yǔ)句所使用的數(shù)據(jù)源

,sql:"SELECT * from 客戶(hù)" //指定數(shù)據(jù)集SQL語(yǔ)句

,fields:null

},

… …

]

… …

</script>

訪(fǎng)問(wèn)分析頁(yè)面，拖拽指標(biāo)，如下圖所示：

像上面例子這樣最終拖拽后基于原始 SQL"select * from 客戶(hù)" 生成的 SQL 語(yǔ)句為：SELECT 地區(qū) 地區(qū), 城市 城市,count(客戶(hù) ID) 客戶(hù) ID 計(jì)數(shù) FROM 客戶(hù) t GROUP BY 地區(qū), 城市。

好了，對(duì) SQL 數(shù)據(jù)集多維分析的介紹就告于段落了，現(xiàn)在我們來(lái)回顧一下心路歷程：

可以看到我們從可以緩存的少量數(shù)據(jù)，到利用數(shù)據(jù)庫(kù)計(jì)算能力的大量數(shù)據(jù)都有很好的解決方案，同時(shí)還提供了相應(yīng)的性能提升手段。不過(guò)，俺們 WEB 多維分析的超能力可不僅僅如此，比如還能隨意修改報(bào)表分析的表格樣式、通過(guò)建模來(lái)讀取維度和指標(biāo)信息等等，想要學(xué)習(xí)潤(rùn)乾報(bào)表多維分析更多知識(shí)，歡迎訪(fǎng)問(wèn)乾學(xué)院（c.raqsoft.com.cn）。