samedi 28 février 2015

extract state changes from snapshot-based archive table


I have a table snapshot, storing snapshots every time an object stored in another live changed one of its attributes. I need to find all objects that changed a specific variable to a specific value in a given time frame.


For example, assume the following layout:



CREATE TABLE snapshot
(
"timestamp" timestamp with time zone NOT NULL,
person text NOT NULL,
haircolor text NOT NULL,
city text NOT NULL,
CONSTRAINT snapshot_pkey PRIMARY KEY (person, "timestamp")
)


Here, I want to find all persons that moved (from any other city) to NY between 2006-02-01 and 2006-02-14. I want all of them, even the ones that moved in on 2006-02-03 and moved out on 2006-02-05 again. (And of course I do not want to list people who only changed their haircolor in the given time frame).


What would an efficient query look like?





Aucun commentaire:

Enregistrer un commentaire