Re: how to compute mass data?

• To: mathgroup at smc.vnet.net
• Subject: [mg126671] Re: how to compute mass data?
• From: bestbird7788 at gmail.com
• Date: Wed, 30 May 2012 04:10:23 -0400 (EDT)
• Delivered-to: l-mathgroup@mail-archive0.wolfram.com
• References: <jpnhgb\$q9c\$1@smc.vnet.net>

```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

```

• Prev by Date: Re: Sqrt of complex number
• Next by Date: Re: Notebook Format History?
• Previous by thread: how to compute mass data?
• Next by thread: Re: Simultaneous Nonlinear Data Fits