[Date Index] [Thread Index] [Author Index]

how to compute mass data?

• To: mathgroup at smc.vnet.net
• Subject: [mg126609] how to compute mass data?
• From: best bird <bestbird7788 at gmail.com>
• Date: Fri, 25 May 2012 04:55:27 -0400 (EDT)
• Delivered-to: l-mathgroup@mail-archive0.wolfram.com

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)