[Date Index] [Thread Index] [Author Index]
Re: how to compute mass data?
On Friday, May 25, 2012 4:56:43 PM UTC+8, best bird wrote: > Hi > My problem is: I needs to compute the product whose annual sales > values are all among the top 100. > I heard that WolframeAlpha can solve this problem(or R,or SPSS, > SAS,SSAS, I'm chossing), but I don't know how to write the correct > code. plz help me. what I want is: > 1. retreive data from database. > 2. then, sort data > 3. then, filter data > 4. then, group data > 5. step 1-4 should be step by step, just like Excel( SQL lacks this > stepwise ability) > I had got a SQL sample to solve the same problem, just 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) Data is here, plz help me. productID time value 1 2009-01-01 802 1 2009-02-01 302 2 2009-01-01 404 2 2009-03-01 204 3 2009-01-01 606 1 2010-01-01 602 1 2010-06-01 902 2 2010-07-01 204 2 2010-07-01 304 3 2010-09-01 406 3 2010-10-01 906