[Date Index] [Thread Index] [Author Index]
how to compute mass data?
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)