On Mar 14, 2013, at 10:27 AM, Kevin Grittner <[email protected]> wrote:
>>
>
> I didn't understand your description of what you are trying to do,
> and the example has so many columns and cases that it would take a
> long time to understand it. Can you distill this down to just a
> few columns and cases so that it is easier to understand what you
> are trying to accomplish? Even better would be a self-contained
> test case with just a few rows so people can see "before" and
> "after" data. What you have already posted will help give context
> on how it needs to scale, which is important, too; but if you make
> the issue easier to understand, the odds improve that someone will
> volunteer the time needed to make a suggestion.
Here is a simplified version of the query approach I am attempting. First of
all, this query works fine with a limited number of columns. There are some
colums that I am leaving alone (those are the var1, var2, var3 variables) and a
limited number of variables that I am trying to "unstack" (those are unstack1,
unstack2, unstack3…. variables).
The problem lies in that the real table I am working with is vary large. There
are 30 plus var1, var2… columns I am not unstacking, and 30 plus variables
(unstack1, unstack2…) that I am unstacking, from a 25 million row table.
I have looked at the tablefunc approach, and I am wondering if it is any more
efficient than using the CASE approach I am trying here. I let the full version
of the below query run for 2 days before killing it when it threatened to fill
the entire hard drive (250 Gigs).
CREATE TABLE unstacked_table AS (
SELECT
var1,
var2,
var3,
MAX (
CASE
WHEN variable_name = 'unstack1' THEN
VALUE
END
) AS unstack1,
MAX (
CASE
WHEN variable_name = 'unstack2' THEN
VALUE
END
) AS unstack2,
MAX (
CASE
WHEN variable_name = 'unstack3' THEN
VALUE
END
) AS unstack3,
MAX (
CASE
WHEN variable_name = 'unstack4' THEN
VALUE
END
) AS unstack4
FROM
stacked_table
GROUP BY
variable1,
variable2,
variable3
)
;
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
