Re: Flattening a kind of 'dynamic' table

On Thu, 27 Jan 2005 13:02:48 -0500, Merlin Moncure
<merlin(dot)moncure(at)rcsonline(dot)com> wrote:
> Alexandre wrote:
> > On Thu, 27 Jan 2005 10:44:45 -0500, Merlin Moncure
> > <merlin(dot)moncure(at)rcsonline(dot)com> wrote:
> > > Alexandre wrote:
> > > Let's start with the normalized result set.
> > >
> > > product_id | department_id | sum
> > > 924 a 6000
> > > 924 c 1575
> > > 907 a 1500
> > > [...]
> >
> Right. I expanding departments into columns is basically a dead end.
> First of all, SQL is not really designed to do this, and second of all
> (comments continued below)

Ok, I got it. The basic message is to avoid making columns out of rows
like I'm doing right now, that "de-normalizing" in an array is the way
to go. So I should query and get the results in an array then after my
application will parse the array into the good columns. (I'm
developping a software.)

If I still got it wrong, this is because the 'geek' section of my
brain is in vacation: leave a message and when it'll come back, it'll
explain all this to me! :)

So I found the array_accum function in the doc, so I did create it.

CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);

Then I created this new select:
SELECT
product_id,
array_accum(department_id) as a_department_id,
array_accum(req_time) as a_req_time
FROM (SELECT * FROM design.product_department_time) AS tmp
GROUP BY product_id;

It gives:
product_id | a_department_id | a_req_time
------------+-----------------+------------------
924 | {A,C} | {6000,1575}
907 | {A,C,D} | {1500,1500,4575}
906 | {A,C,D} | {3000,3000,1935}

So, the performance should be much better using this agregate approach?

No I thing I'll merge the results in my software, unless you think
that at this point doing a LEFT JOIN with my jobs table is the way to
go, beacuse the performance will be good. (Personally I don't know the
answer of this one.)

> If parsing an array string is a pain I happen to have a C++ class handy
> that can compose/decompose a postgresql array string if:
> a: no more than 1 dimension and
> b: array bounds are known
>
> Let me know if you need it and I'll send it over.

Thank you for your offer. I think parsing an array is the easiest
thing to do for me in all this. :) If I encounter any problem, I'll
drop you a mail.

Regards.

--
Alexandre Leclerc