It is known to us that SQL users have to finish composing all codes and then run them all at one time, resulting in a poor ability for interactctive data analytics. However, the simple and easy-to-understand query syntax of SQL is always welcomed by programmers. As powerful computation and analysis tools, R language and esProc are surely need to offer the similar query syntax. In this article, let's discuss how to implement the basic functions of SQL through R language and esProc with some examples.
The example data is from 2 tables of the classical Northwind database:
Orders table with the main fields: OrderID, EmployeeID, OrderDate, Freight, CustomerID
Customer table with the main fields: CustomerID, CompanyName
Retrieve data of the entire table
SQL solution: select * from Orders
R solution: A1<-sqlQuery(conn,'select * from Orders')
esProc solution: $select * from Orders
Comments: The word count of esProc and SQL codes only differs by one word. The R language has a strong flavor of programming.
Where: Search for the order which has a freightage higher than 100 and is placed before the year of 1998
SQL solution: SELECT * FROM Orders WHERE Freight > 100 AND OrderDate < '1998-01-01'
R solution: subset(A1,Freight > 100 & OrderDate < as.POSIXlt('1998-01-01'))
esProc solution: =A1.select(Freight > 100 && OrderDate < date("1998-01-01"))
Comments: Both SQL and R solutions are close to SQL to some extent. R is of the typical function style, and esProc is of the typical object style. The programmers prefer the former, while the business experts or analysts prefer the latter esProc, for esProc is easier for them to understand.
Order: Sort by employees correctly, and then sort by freightage in reverse order
SQL solution: SELECT * FROM Orders ORDER BY EmployeeID ,Freight DESC
R solution: A1[order(A1$ EmployeeID,-A1$Freight),]
esProc solution: =A1.sort(EmployeeID,Freight:-1)
Comments:
The R solution is to retrieve 2 vectors from A1 at first, pass them to the function order to group them together for sorting, and then export the serial numbers. At last, data will be rearranged according to the serial number. The computation process of R language is rather winding, not as straightforward as SQL. It is because that R is good at handling vector, the access to structured data will take column as the basic unit, and the parameter usually takes up a whole column. By comparison, SQL takes the record (column) as the basic unit, with parameter as the column name.
esProc solution resembles that of SQL because esProc takes the record (row) as the basic unit .
Group & Sum: Summarize by employee, sum up the freightage, and count the orders:
SQL solution: SELECT EmployeeID, COUNT(OrderID), SUM(Freight) FROM Orders GROUP BY EmployeeID
esProc solution: =A1.group(EmployeeID; EmployeeID, ~.count(), ~.sum(Freight))
R solution:
A4<-aggregate(A1$Freight,list(A1$EmployeeID),sum)
A4$count<-tapply(A1$Freight,A1$EmployeeID,length)
Comments: In this case, it is obvious that R and SQL differ greatly. The algorithms available in R may be clearer for mathematicians, and means more learning efforts for users accustomed to SQL.
SQL: http://www.sql.org/
R: http://en.wikipedia.org/wiki/R_%28programming_language%29
esProc: http://www.raqsoft.com/products
From the above functions, when come to SQL data computing and complex data calculation, SQL is replaceable in some aspects actually. We can see from the above comparison, esProc has a coding style more close to that of SQL since esProc supports the data structure of “Record” by the infrastructure, making it more suitable for users that are familiar with SQL. In addition, compared with R language, esProc provides the representation style of “object + functions” that is much easier for business experts to accept for financial data analytics, marketing and sales data analysis.
R is more resourceful in details, ideal for the programmers and mathematicians. In addition, supporting the regular expressions and other functions makes R more open as a preferred analytics tool for programmers.
No comments:
Post a Comment