SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Rated R (A Teaser)

How many lines of TSQL codes and Window Functions you need to come up with this?


With R, it takes about 2 commands:

> order <- as.matrix(sqlFetch(ch,"SalesDetails")
> summary(order)

To give you an idea, I prepared the dataset into a view like this:

CREATE VIEW SalesDetails
 soh.OrderDate AS [Date],
 ppc.Name AS Category,
 pps.Name AS Subcat,
 pp.Name as Product,
 SUM(sd.OrderQty) AS Qty,
 SUM(sd.LineTotal) AS LineTotal
FROM Sales.SalesPerson sp
 INNER JOIN Sales.SalesOrderHeader AS soh
 ON sp.BusinessEntityID = soh.SalesPersonID
 INNER JOIN Sales.SalesOrderDetail AS sd
 ON sd.SalesOrderID = soh.SalesOrderID
 INNER JOIN Production.Product AS pp
 ON sd.ProductID = pp.ProductID
 INNER JOIN Production.ProductSubcategory AS pps
 ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
 INNER JOIN Production.ProductCategory AS ppc
 ON ppc.ProductCategoryID = pps.ProductCategoryID
GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderID, ppc.name, pps.Name, pp.Name,

And, that’s it. I just fed that view to the R engine and it summarized the dataset just by using those two commands.

This is not the perfect dataset to test with R.

I’d love to dig deeper into R because of its powerful analytical features.

What is R?

“R is a language and environment for statistical computing and graphics.”

If you’re into statistics, linear/non-linear modelling, or simply want to try another tool to analyze your data warehouse, give R a shot.

If you want to start digging with R, Ted Malone ( b | t ) has a nice introduction to using R with SQL Server 2012. Get more information about R on the R Project website.

Marlon Ribunal - SQL, Code, Coffee, etc.

I'm passionate about SQL Server. But I feel like I haven't reached my full potential yet. So, this is my mission: My purpose is to help people in their pursuit of growth and development; and, thereby, help myself realize my full potential as a professional, husband, father, christian, and human being.
My online presences include: Tech Blog: Marlon Ribunal - SQL, Code, Coffee, etc. Productivity & GTD Hack Blog: Productivity Bits Twitter: @MarlonRibunal


Leave a comment on the original post [marlonribunal.com, opens in a new window]

Loading comments...