Compare arrays in postgres

user_1081060 0
Viewed 70

enter image description here

Above history table captures time and zones where a tag is present at the moment. I want to find out when a tag enters or exits a zone. tried with

    more_data as (
         select tag_id,
                update_time,
                zone_ids as current_zones,
                lag(zone_ids, 1) over (partition by tag_id order by update_time asc) as prev_zones
         from tag_hist
         order by update_time asc
     )
select *
from more_data
order by tag_id, update_time asc;

Now I have enter image description here

Now I want to compare current and prev zones to identify entered/exited zones

with entered zones = current_zones - prev_zones
with exited zones = prev_zones - current_zones

And finally I want to have something like enter image description here

try it http://sqlfiddle.com/#!17/a7db2/3

common-table-expression postgresql arrays sql
2 Answers

Could you try with below if it resolves what you need,

with more_data as (
         select tag_id,
                update_time,
                zone_ids as current_zones,
                lag(zone_ids, 1) over (partition by tag_id order by update_time asc) as prev_zones
         from tag_hist
         order by update_time asc
     )
select t.*,
   (select array(select unnest(current_zones::int[]) except select unnest(prev_zones::int[]))) as "entered zones",
   (select array(select unnest(prev_zones::int[]) except select unnest(current_zones::int[]))) as "exited zones"
from more_data t
order by tag_id, update_time asc;

db<>fiddle

user_138780230

It seems that operating on unnested array elements is easier than on whole arrays:

with tag_hist_row_numbers as (
-- add row numbers 
    select *, row_number() over (order by tag_id, update_time) as rn
    from tag_hist
),
tag_hist_enter_exit as (
-- unnest zone_ids and find enters/exits
    select 
        tag_id, zone_id, update_time, rn,
        rn - 1 is distinct from lag(rn) over w as enter,
        rn + 1 is distinct from lead(rn) over w as exit
    from tag_hist_row_numbers
    cross join unnest(zone_ids) as zone_id
    window w as (partition by tag_id, zone_id order by update_time)
),
tag_hist_times as (
-- assign enter/exit times
    select 
        tag_id, 
        zone_id, 
        enter,
        update_time as enter_time,
        case when exit then update_time else lead(update_time) over w end as exit_time
    from tag_hist_enter_exit
    where enter or exit
    window w as (order by tag_id, zone_id, update_time)
)
select
-- remove redundant rows
-- rows with exit are useless now
    tag_id, 
    zone_id, 
    enter_time,
    exit_time
from tag_hist_times
where enter
order by tag_id, zone_id

SQLFiddle.

You can aggregate times into arrays for each zone_id if you prefer this format of the final results.

user_19957380
Related