2

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

Improve this question
0

2 Answers 2

Reset to default
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.

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

2 Comments

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.
Yes, see SQLFiddle. Compare the queries (two changes) to see how it works.
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

Improve this answer

4 Comments

it solves half of my problem, is there any way we can we capture entry exit times also
@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 ?
when an id comes in 'entered zones' its entered_at is populated, and when it comes in 'exited zones' its exited_at is populated
@Jebil, thanks for clarification. I got it. However I got new learnings from this post.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.