Mathematica 9 is now available
Services & Resources / Wolfram Forums / MathGroup Archive
-----

MathGroup Archive 2012

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

Search the Archive

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