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.
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
