Re: Noob Q collapsing multiple rows into one via case and max

From: Kenneth Tilton <ktilton(at)mcna(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Noob Q collapsing multiple rows into one via case and max
Date: 2011-01-06 23:12:57
Message-ID: 4D264C79.7030700@mcna.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 1/6/2011 5:50 PM, Kenneth Tilton wrote:
> [a meta-question for all the below is "what's a good link for hairy SQL"?]
>
> A while ago I worked on a project where we had some hairy SQL
> collapsing multiple rows of pseudo-rdf triples (columns
> subject,predicate, and object) into one flattened row in which a
> hard-coded case/max (I forget the exact syntax) plucked out a value
> for a column "X" by looking for a row with the predicate "X". So we'd
> find all the rows for one subject (a logical row ID) and collapse it
> into a single row of conventional columns.
>
> I now want to do something similar, but combining a conventional XY
> table with columns X and Y and a separate change-history table with
> columns row-id, column-name, value, and last-time-in-effect, to which
> old values get pushed by an update trigger on XY when X or Y change.
>
> The goal is to specify a time in the past and recreate a row of XY as
> of that time, by starting from the current row and replacing X and Y
> if necessary by the value that would have been current then, ie with
> the max last-time-in-effect <= the specified time.
>
> My thought was to read XY for a desired row-id with a left join to the
> change-history table where last-time-in-effect <= the specified time
> (pretty obvious) but the stumper is the clause for the x-then and
> y-then columns. A useless start:
>
> case when column_name = "X" then change_history.value else XY.X
> end case as x-then
>
> I had some luck with that, but I also want to pick out the most recent
> change up to the target time where there were multiple changes.

btw, I have this (actual names with a "states" table and changes held in
a "triple" table modelled after rdf where s=row-id, p=column-name, and
ostr = the prior value):

select max (case when p='name' then ostr else 'n' end) as xx
from states left outer join triple on states.id=s
where s=1 and created < '2011-01-06 15:52:30';

That happens to produce the right result but only because the rows are
coming back in order created...hmm. Can I rely on that? Meaning the left
join on the "triple" table will always return them in the order created?

kt

>
> kt
>
> ps. Is this a crazy way to achieve our requirement, which is to see
> the DB as it was at a point in the past? Our data is incredibly
> stable, btw. kt

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Benjie Buluran 2011-01-07 01:56:24 Inserting data from one database to another using stored functions
Previous Message Kenneth Tilton 2011-01-06 22:50:26 Noob Q collapsing multiple rows into one via case and max