sql - 比较 postgres 中的数组

Question

上面的历史表捕获了当前存在标签的时间和区域。
我想知道标签何时进入或退出区域。
试过

    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;

我现在有

现在我想比较当前和上一个区域以识别进入/退出的区域

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

最后我想要一些类似的东西

试试 http://sqlfiddle.com/#!17/a7db2/3

Answer 1

似乎对未嵌套的数组元素进行操作比在整个数组上更容易:

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.
您可以将每个 zone_id 的时间聚合到数组中如果您更喜欢这种最终结果的格式。