Big Data/Analytics Zone is brought to you in partnership with:

Jim King is the visionary expert in the field of BI, database, Java development and programming. He has more than 10 years of experience in BI and development of Java applications, like esProc and esCalc. He is passionate about applying simpler methods to solve the complex problems of users. He is also an active blogger and has released a serial of original ideas on BI and Java development, such as BI, database application development, report development with help of Java, SQL, etc. Jim has posted 23 posts at DZone. You can read more from them at their website. View Full User Profile

# Integrating Dynamic Calculation Script for Set Operations in Java

07.17.2013
| 3218 views |

In Java development, we may encounter the complex set operations. Java alone is not powerful enough to save programmers’ efforts in implementing the computation details, which is time-consuming and poor in code reuse. In view of this, programmers usually resort to dynamic calculation script for set operation.

SQL is surely the first kind of script that comes into most programmers’ mind. However, to their disappointments, SQL does not support the explicit set, and is unable to represent the sets of a set, ordered set, generic set, and only the result set can be recognized as a set. Therefore, it is only the subset of the true set. Many operations on sets are hard to implement through SQL. Moreover, the computation is not limited on database, such as the data from Excel and even there is no database in the application environment. In this case, the usage of SQL database is further narrowed.

As the true dynamic calculation script for set operations, both R and esProc are more suitable for such computation, considering its support for generic set, ordered set, and Java- embedding. In addition, they not only empower users to retrieve data from one or more databases, Excel sheet, and txt file, but also enable users to compute step by step, and ultimately solve many complex computations in a much more convenient way.

Let’s check it out with this example: a sales department needs to make statistics on the outstanding salesmen who account for half of the total sales based on their sales records.

esProc scripts:

R scripts:

01 library(RODBC)

02 odbcDataSources()

03 conn<-odbcConnect("sqlsvr")

04 originalData<-sqlQuery(conn,'select * from salesOrder')

05 odbcClose(conn)

06 nameSum<-aggregate(originalData\$sales,list(originalData\$name),sum)

07 names(nameSum)<-c('name','salesSum')

08 orderData<-nameSum[rev(order(nameSum\$salesSum)),]

09 halfSum<-sum(orderData\$salesSum)/2