Postgresql – extract state changes from snapshot-based archive table

postgresqlwindow functions

I have a table snapshot, storing snapshots (of the new state) 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?

Best Answer

I came up with the following query:

    with city_before as (
        select person, city
        from (
            select 
                person, 
                timestamp, 
                first_value(timestamp) over (partition by person order by timestamp desc) as latest_timestamp, 
                first_value(city) over (partition by person order by timestamp desc) as city
            from snapshot
            where timestamp < '2006-02-01'
        ) _
        where timestamp = latest_timestamp
    ),
    city_during as (
        select person, city
        from snapshot
        where timestamp between '2006-02-01' and '2006-02-15'
        group by person, city
    )
    select distinct person
    from (
        select s.person, b.city as city_before, s.city as city
        from city_during s
        left outer join city_before b on b.person = s.person
        where b.city <> s.city and s.city = 'NY'
    ) _;
Related Question