Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.soft-sys.math.mathematica > #10274

Re: how to compute mass data?

From bestbird7788@gmail.com
Newsgroups comp.soft-sys.math.mathematica
Subject Re: how to compute mass data?
Date 2012-05-30 08:12 +0000
Organization Steven M. Christensen and Associates, Inc and MathTensor, Inc.
Message-ID <jq4kpp$fu7$1@smc.vnet.net> (permalink)
References <jpnhgb$q9c$1@smc.vnet.net>

Show all headers | View raw


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 	

Back to comp.soft-sys.math.mathematica | Previous | Next — Previous in thread | Find similar | Unroll thread


Thread

how to compute mass data? best bird <bestbird7788@gmail.com> - 2012-05-25 08:56 +0000
  Re: how to compute mass data? bestbird7788@gmail.com - 2012-05-30 08:12 +0000

csiph-web