|
[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. 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 |
