Compare arrays in postgres

Question

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

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

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

klin · Accepted Answer · 2020-09-18 08:34:53Z

1

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.

answered Sep 18, 2020 at 8:34

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jebil Over a year ago

Thanks a lot @klin it works Is it possible to return exit time as null or empty when a zone is not exited yet. To identify that tag is currently in that zones.

klin Over a year ago

Yes, see SQLFiddle. Compare the queries (two changes) to see how it works.

Sujitmohanty30 · Accepted Answer · 2020-09-18 06:47:50Z

1

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

answered Sep 18, 2020 at 6:47

Sujitmohanty30

3,3362 gold badges9 silver badges24 bronze badges

4 Comments

Jebil Over a year ago

it solves half of my problem, is there any way we can we capture entry exit times also

Sujitmohanty30 Over a year ago

@Jebil, I must admit I didn't get the part how the last final output can be derived from the array of "entered zones" and "exited zones" ? Could you explain ?

Jebil Over a year ago

when an id comes in 'entered zones' its entered_at is populated, and when it comes in 'exited zones' its exited_at is populated

Sujitmohanty30 Over a year ago

@Jebil, thanks for clarification. I got it. However I got new learnings from this post.

Collectives™ on Stack Overflow

Compare arrays in postgres

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related