DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type:content-transfer-encoding;
	b=Syn1TK37BNZLRPVnYc4LjDd5sHcspz9NJWM9lj9I1YbHDqeeZCshb5iYbtyPe0JcxK
	et2RI+1eO5SNLmMTXmeA0FX27pfz2Ow39FJFfUSCeRdegm7qkEQiQuyqE4eYws5pNRZG
	c+j1lp+zAxUATNJ/17iytULWeiFvGMrYu6Sx4=
Message-ID: <4A439E6E.3050202@gmail.com>
Date: Thu, 25 Jun 2009 09:57:34 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.21 (X11/20090318)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Bucketing Row Data in columns
References: <1245861555.4420.30.camel@devil>
	<020601c9f580$f02b7a50$ec5a3d0a@marktestcr.marktest.pt>
In-Reply-To: <020601c9f580$f02b7a50$ec5a3d0a@marktestcr.marktest.pt>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I would be suspicious of this sort of solution of turning rows into 
columns by mean of a series of correlated sub-selects.  Once the data 
set gets large and the number of columns goes over 2 or 3 this will in 
all likelihood not perform well. 
I had the pleasure of re-writing a "report" which was based on count() 
(similar to sum()) per user_id with the counts going into various 
columns per user.  18000 users, a dozen columns from table of 2 million 
rows, report took >1,000,000 seconds (yes almost 12 days) to complete.  
Re-write runs in 5-10 minutes (now at 10M rows) by getting the counts as 
rows (user, item, count) into a temp table and making the columns from 
the temp table (pl/psql)  Getting the counts takes half the time, making 
the flattened report takes half the time.


Oliveiros Cristina wrote:
> Hello, Sandeep,
>  
> I am not sure if this is what you want.
>  
> I came up with this query
>  
> SELECT *
> FROM (SELECT user_id,SUM(amount) as day1 FROM t1 WHERE 
> create_timestamp = '2009-1-1' GROUP BY "user_id") a
> NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day2 FROM t1 WHERE 
> create_timestamp = '2009-1-2' GROUP BY "user_id") b
> NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day3 FROM t1 WHERE 
> create_timestamp = '2009-1-3' GROUP BY "user_id") c
>  
> The solution is not totally correct because it returns NULL in the 
> places you return 0.
> It seems the SUM() returns NULL when it gets an all NULL column...
> Is it problematic for your application ?
>  
> Also, i 'm not sure if I fully understand your last sentence
> /lets assume the buckets are fixed i.e 3 only. but I wish to get them 
> unlimited i.e day 1 to day 20./
>  
> You say that the buckets are fixed at 3. So, you mean the table output 
> will always have 4 columns? 3 days plus one for user_id ?
> If you want 20 buckets it must be a different query...
>  
> Could you please clarify what you mean when you say that you want to 
> get a bucket unlimited ?
>  
> Best,
> Oliveiros
>
>     ----- Original Message -----
>      
>     *From:* Sandeep <mailto:gibsosmat@gmail.com>
>     *To:* pgsql-sql@postgresql.org <mailto:pgsql-sql@postgresql.org>
>     *Sent:* Wednesday, June 24, 2009 5:39 PM
>     *Subject:* [SQL] Bucketing Row Data in columns
>
>     Hi all,
>     I need help on creating a sql, not a problem even if its pl/sql
>
>     I have orders table schema is as follow
>
>     orders(order_id,user_id, create_timestamp, amount)
>
>     and I want to generate a report like
>     for the past 3 days bucketing purchases i.e SUM(amount) every day
>     in columns
>     i.e result will be having these columns.
>
>     (user_id, amount_day1, amount_day2, amount_day3)
>
>     ex:
>     am leaving order_id assume they are auto incrementing and unique,
>     date format dd/mm/yyyy
>     (user_id, create_timestamp, amount)
>     (user1, 01/01/2009,100)
>     (user1, 01/01/2009,100)
>     (user2, 01/01/2009,100)
>     (user2, 02/01/2009,100)
>     (user2, 02/01/2009,100)
>     (user1, 02/01/2009,100)
>     (user2, 03/01/2009,100)
>     (user2, 03/01/2009,100)
>     (user3, 03/01/2009,100)
>
>
>     result
>
>     (user_id, amount_day1, amount_day2, amount_day3)
>     (user1, 200, 200, 0)
>     (user2, 100, 200, 200)
>     (user3, 0, 0, 100)
>
>
>     hope you guys got what I am trying to generate through sql.
>
>     I could get this data in each row, but I want it in columns.
>     Can anyone help me on this? lets assume the buckets are fixed i.e
>     3 only. but I wish to get them unlimited i.e day 1 to day 20.
>
>     Regards
>     Sandeep Bandela
>