The interrow computation is quite common, such as the aggregate, comparison with same period of any previous year, and link relative ratio in business statistics and analytics. Both R language and esProc provide a pretty good interrow computation ability with slight difference to each other. In the case below, the utilization of some basic inter-row computations is demonstrated to expound the differences between the two methods:
Since chrismas is coming, and the sales department of a company wants to make statistics on the outstanding sales persons after the chrismas big promotion, for example, the salesman who achieves half of the total sales amount of the company. The data are mainly from the order of table database: salesOrder. The main fields include the ID of order: ordered, Name of sales person: name, Sales amount: sales, and date of order: salesDate.
The straightforward solution is shown as below:
1. Group by sales person to calculate the sales amount of each sales person.
2. Sort by sales amount in reverse order on the basis of the data from the previous step.
3. According to the previous step, calculate the aggregate value of each record, and calculate the standard of comparison: the half of total sales of this company.
4. Of the aggregate values calculated in the previous step, select out the list of sales persons whose sales achievement meeting the below conditions: lower or equal to the standard of comparison; or although higher than the standard of comparison, the sales achievement of previous sales person is lower than the standard of comparison.
The detailed solution of R is shown as below:
01 library(RODBC)
02 odbcDataSources()
03 conn<-odbcConnect("sqlsvr")
04 originalData<-sqlQuery(conn,'select * from salesOrder')
05 odbcClose(conn)
06 nameSum<-gNameMonth<-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
10 orderData$addup<-cumsum(orderData$salesSum)
11 subset(orderData,addup<=halfSum | (addup>halfSum & c( 0, addup[- length (addup)]) <halfSum))
Please find the detailed solution of esProc (download) below:
Then, let us study on the differences between aggregate values:
The R uses cumsum to calculate the aggregate value in the line 10.
esProc uses cumulate in A4 to calculate the aggregate value.
Both writing styles are very convenient for users. However, the operation principle of esProc is aimed to each record: firstly, calculate the cumulate, then, get the aggregate value corresponding to this record according to the # row number. By comparison, R enjoys a higher efficiency than esProc on this respect since the calculation will be executed only once.
Dividing one statement of esProc into two statements can solve the efficiency issue, that is, firstly, calculate the list of aggregate value separately, and then insert it to the original dataset. However, such writing style is not as concise as the R that only requires one line of code.
Then, let us check the qualified sales person and the differences:
R completes the computation at the Line 11, mainly by moving the line, and using c( 0, addup[- length (addup)]) to construct a column for the new data. Compared with the column addup, the new column just moves down one column, and the last entry of data is removed and filled with 0 of the first entry. Then, you can compare whether the aggregate value is lower than the standard of comparison, or although it is higher than the standard of comparison, its previous record is lower than the standard.
R does not provide the ability to access the data at the relative position. Therefore, the method of “move the data in the relative position to the current position” is adopted. Though the result is still the same, the style of writing is not intuitive enough, and it requires the analyst a relatively higher ability in logic thinking.
The writing style of esProc is select(addup<=B3 || (addup>B3 && addup[-1]
Unlike the fixed algorithm of aggregate value, the algorithm of this step is relatively much freer. You may find that the style of expression regarding the relative position of esProc is very agile with great advantages.
Compared with the fixed algorithms, this step of algorithm is much freer.
As we can see from the above case, the computations of relative position and interrow computations can solve many problems which are apparently complex. esProc is more flexible in expressing the relative positions. Therefore, esProc users can feel more relax when calculating the complex problems.
Regarding the R language, appending to the whole column/row and the fixed algorithm are relative more concise and very impressive.
For industries like marketing and sales, healthcare and pharmaceutical, educational, financial and telecommunication, statisticsl computing tools like R or esProc are usually helpful to ease working strength and improve efficiency.
BI technology consultant for Raqsoft
10 + years of experience on BI/OLAP application, statistical computing and analytics
Email: Contact@raqsoft.com
Website: www.raqsoft.com
No comments:
Post a Comment