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