[R] plz help. how to filter/group/sort data on mass data

bestbird bestbird7788 at gmail.com
Mon May 21 09:44:38 CEST 2012


hi, nice people
          I needs to compute the product whose annual sales values are all
among the top 100 using R ( version 2.15.0)
          data is stored in the MSSQL database.
          data structure( sales table's fields): productID, time, value
         I have found the SQL solution below:
SQL solution is as below:
--------------------------------------------------------------
WITH sales1 AS (
 SELECT productID, YEAR(time) AS year, SUM(value) AS value1
 FROM sales
 GROUP BY productID, YEAR(time)
)

SELECT productID
FROM (
 SELECT productID
 FROM (
 SELECT productID,RANK() OVER(PARTITION BY year ORDER BY value1 DESC)
rankorder 
FROM sales1 ) T1
 WHERE rankorder<=100) T2
GROUP BY productID
HAVING COUNT(*)=(SELECT COUNT(DISTINCT year ) FROM sales1)
--------------------------------------------------------------------------
now, I must solve it using R ( I need stepwise analysis capability in
further ).
I have retrive data from database as below:
----------------------------------------------------------------
 library(RODBC)
 odbcDataSources()
 conn=odbcConnect("sqlsvr")
 result=sqlQuery(conn,'select * from sales')
 odbcClose(conn)
 result
--------------------------------------------------------------
But I dont' know how to process next step, such as filter,sort,group
please give me some help.


--
View this message in context: http://r.789695.n4.nabble.com/plz-help-how-to-filter-group-sort-data-on-mass-data-tp4630714.html
Sent from the R help mailing list archive at Nabble.com.



More information about the R-help mailing list