arrays - pg 删除数组中的公共(public)部分

Question

假设一个 postgresql 查询输出 2 列的行，每行都是 int[][2] 的数组

            track0                          track1
{{1,2},{5847,5848},{5845,5846}......}   {{1,2},{5847,5848},{10716,10715}........}

 {{13,14},{1,2},{5847,5848},{284,285}........}   {{13,14},{1,2},{5847,5848},{1284,1285}................}

除最后一列外，我们如何删除两列共有的前导数组？第一行 {1,2} 应该从两列中删除。在第二行 {13,14},{1,2} 应该从两列中删除。

可以用sql完成还是必须用plpgsql？

我可以管理 plpgsql，但想要 sql 解决方案。

Answer 1

给定你的样本，在相等的对序列中没有间隙，

t=# with d(t0,t1) as (values
      ('{{1,2},{5847,5848},{5845,5846}}'::int[][2],'{{1,2},{5847,5848},{10716,10715}}'::int[][2])
    , ('{{13,14},{1,2},{5847,5848},{284,285}}'::int[][2],'{{13,14},{1,2},{5847,5848},{1284,1285}}'::int[][2])
    )
    , u as (
      select *
      from d
      join unnest(t0) with ordinality t(e0,o0) on true
      left outer join unnest(t1) with ordinality t1(e1,o1) on o0=o1
    )
    , p as (
    select *
    , case when (
        not lead(e0=e1) over w
        or not lead(e0=e1,2) over w
        or e0!=e1
      ) AND (o1%2) = 1
      then ARRAY[e0,lead(e0) over w] end r0
    , case when (
        not lead(e0=e1) over w
        or not lead(e0=e1,2) over w
        or e0!=e1
      ) AND (o1%2) = 1
      then ARRAY[e1,lead(e1) over w] end r1
    from u
    window w as (partition by t0,t1 order by o0)
    )
    select t0,t1,array_agg(r0),array_agg(r1)
    from p
    where r0 is not null or r1 is not null
    group by t0,t1
    ;
                  t0                   |                   t1                    |         array_agg         |          array_agg
---------------------------------------+-----------------------------------------+---------------------------+-----------------------------
 {{13,14},{1,2},{5847,5848},{284,285}} | {{13,14},{1,2},{5847,5848},{1284,1285}} | {{5847,5848},{284,285}}   | {{5847,5848},{1284,1285}}
 {{1,2},{5847,5848},{5845,5846}}       | {{1,2},{5847,5848},{10716,10715}}       | {{5847,5848},{5845,5846}} | {{5847,5848},{10716,10715}}
(2 rows)

如果你为多维数组添加一些技巧，你可以跳过部分并发症，看here和 here