複雜多資料來源報表join後再計算的簡便方法，join簡便方法

複雜多資料來源報表join後再計算的簡便方法，join簡便方法

         複雜資料來源是報表開發的常見問題，比如不同資料庫表先進行join運算，再進行後續的過濾分組排序等運算。JasperReport/Birt等報表工具有virtual data source或table join，可以一定程度地實現多資料來源join後計算，但掌握起來並不容易。

集算器具有結構化強計算引擎，支援多樣性資料來源，整合簡單，可以協助報表工具方便地實現此類需求，下面通過一個例子來說明多資料來源join後計算的過程。

         Sales是mysql資料庫中的表，儲存著多名銷售人員每天的多個訂單，其中欄位SellerId是銷售人員編號。emp是mssql資料庫中的表，儲存著銷售人員資訊，其中欄位EId是銷售人員編號，Name是銷售人員名字，Dept是部門名稱。現在需要在報表中展現：訂單編號、日期、金額、銷售人員名字、部門名稱，條件是：訂單日期在最近N天（比如30天）或者訂單屬於某幾個受關注的部門（比如Markeding和Finance）。

         由於訂單編號、日期、金額來自於表sales，而銷售人員名字、部門名稱來自於表emp，因此要進行不同資料庫之間join運算，join之後還要進行條件過濾。部分來源資料如下：

         表sales

         表emp

         集算器代碼：

         A1=myDB1.query("select * fromsales")

         這句代碼從資料來源myDB1查詢出sales表的記錄，myDB1指向mysql資料庫。函數query用來執行SQL查詢，可以接收外部參數。A1的計算結果如下：

         A2=myDB2.query("select * fromemp")

         這句代碼從資料來源myDB2查詢出emp表的記錄，myDB2指向mssql資料庫。

         A3=A1.switch(SellerId,A2:EId)

         上述代碼將A1中的SellerId欄位替換成A2中對應的記錄，關聯欄位為EId。A3的計算結果如下（藍色字型表示該資料項目包含下級成員）：

         當A2中找不到對應的記錄時，函數switch預設保留A1中記錄，對應的SellerId顯示為空白，效果類似於左串連。如果想進行內串連，應當使用選項@i，形如：A1.switch@i(SellerId,A2:EId)

        

         A4=A3.select(OrderDate>=after(date(now()),days*-1)||depts.array().pos(SellerId.Dept))

         上述代碼可對關連接果進行過濾，條件有2個，第1個條件是：訂單日期在最近N天（對應參數days），運算式為OrderDate>=after(date(now()),days*-1)。第2個條件是：訂單屬於某幾個受關注的部門（對應參數depts），運算式是depts.array().pos(SellerId.Dept)。運算子||表示邏輯關係“或”。

         函數now可以取目前時間，函數date將目前時間轉為日期，函數after可以算出相對時間，比如after("2015-01-30",-30)表示將時間後退30天，即2015-01-01。使用不同的選項，函數after還能以年、季、月、秒為單位計算相對時間。

         函數array可以按分隔字元將字串變成集合，比如"Marketing,Finance".array()等於["Marketing","Finance"]。函數array的預設分隔符號是逗號，也可以指定其他分隔字元。函數pos可以找出成員在集合中的位置，比如["Marketing ","Finance"].pos("Finance")等於2，在邏輯關係中等於true。如果成員不在集合中，則返回null，在邏輯關係中等於false。

         值得注意的是SellerId.Dept這種用法，這表示SellerId欄位對應的記錄的Dept欄位。可以看到，用switch替換欄位後，表之間的關聯關係就可以用對象的方式來訪問，這種方式直觀簡單，進行多表多層關聯時會體現得更明顯。

         Days和depts都是來自於報表的參數，如果分別輸入30、"Marketing,Finance"，則A4的結果如下：

         A5=A4.new(OrderID,OrderDate,Amount,SellerId.Name:Name,SellerId.Dept:Dept)

         上述代碼從A4中取得報表需要的欄位，其中SellerId.Name和SellerId.Dept分別表示emp表中的員工名字和部門名稱，運算子“:”表示重新命名。A5的計算結果如下：

        到此為止，報表需要的資料就全部計算出來了。最後只需用result A5將A5中的二維表返回報表工具。集算器對外提供JDBC介面，報表工具會將集算器識別為普通資料庫，整合方案請參考相關文檔。

 

        接下來以JasperReport為例設計一張簡單報表，表樣如下：

         需要定義兩個報表參數pdays、pdeps，分別對應集算器中的兩個參數。預覽後可以看到報表結果：

         報表調用集算器的方法和調用預存程序一樣，比如將本指令碼儲存為afterjoin1.dfx，則在JasperReport的SQL設計器中可以用afterJoin1 $P{pdays},$P{pdepts}來調用。

 

         有了esProc的協助，報表工具還可以處理更複雜的多資料來源join後計算。比如：找到某日期之後每個銷售人員的銷售額增長最快的三天，展現銷售人員名字、這三天的日期、銷售額、增長率。

         集算器代碼：

        A1=myDB1.query("select * from sales whereOrderDate>=?",beginDate)

        上述代碼用來查詢表sales中某日期之後的訂單，其中beginDate為報表傳來的參數，假設該值為”2015-01-01”，則A1的計算結果如下：

        A2=myDB2.query("select * from emp")

        上述代碼查詢emp表，結果如下：

        A3=A1.switch(SellerId,A2:EId)

        上述代碼將A1中的SellerId欄位替換成A2中對應的記錄，結果如下：

        A4=A3.group(SellerId)

        上述代碼將訂單按SellerId分組。左側是A4的計算結果，右側是其中兩個SellerId的訂單。

        A5=A4.(~.groups(OrderDate,SellerId;sum(Amount):subtotal))

        上述代碼將每個SellerId的訂單再按照OrderDate和SellerId分組，並匯總各組的訂單金額，即：每個銷售人員每天的銷售額。如：

        上述代碼中“A4.()”表示對A4的每個成員進行迴圈計算，括弧中的“~”是成員變數，即某SellerId對應的訂單記錄。“~.groups()”表示對每個成員應用函數groups。函數groups可對資料分組並進行簡單的匯總，函數group可以只分組不匯總。

 

        A6=A5.(~.derive((subtotal-subtotal[-1])/subtotal[-1]:rate))

        上述代碼計算出每個銷售人員銷售額的日增長率，計算結果如下：

         上述代碼中，函數derive用來增加新欄位，欄位名為rate，演算法為“（當日銷售額-上一日的銷售額）/上一日的銷售額”。可以看到，集算器用subtotal[-1]來表示上一日的銷售額，可以輕鬆進行相對位置的計算。

         值得注意的是，由於第一條記錄沒有對應的“上一日的銷售額”，因此銷售額增長率為Null。

 

        A7=A6.(~.select(#!=1))

        這句代碼在A6的基礎上去掉每組資料的第一條記錄（因為第一條記錄的增長率為無意義的Null）。

        代碼中的函數select可以進行查詢，“#”表示迴圈序號，“#!=1”即序號不等於1。同樣的功能也可以用函數delete來實現，但效能稍低，這是因為函數select只返回引用，而delete需要改變實際資料。

 

        A8=A7.(~.top(-rate;3))

        上述代碼可以計算出每個銷售人員銷售額增長率最大的三天對應的記錄。函數Top可以根據某欄位（或某幾個欄位的運算式）取出前N條記錄。計算結果如下：

        A9=A8.union()

        上述代碼將A8中的各組資料合併起來，形成新的二維表A9，如下：

        A10=A9.new(SellerId.Name:Name,OrderDate,subtotal,rate)

        上述代碼從A9中取出需要的欄位，即本案例最終計算結果。

        result A10

        上述代碼將A10中的二維表返回報表工具。報表的設計可以參考前一個案例，這裡不再贅述。