PostgreSQL中合并重叠的时间范围

Question

PostgreSQL中合并重叠的时间范围

postgresqlrangerecursive-querydate-rangerecursive-cte

3

我有一个包含时间戳范围和用户ID的PostgreSQL（9.4）表格，我需要将任何重叠的范围（具有相同的用户ID）合并为单个记录。我尝试了一组复杂的基本表达式（CTE）来实现这一点，但我们真实表格中的一些边缘情况使事情变得复杂。我得出结论，我可能需要使用递归CTE，但我没有写成功过。以下是创建测试表并填充数据的代码。这不是我们表格的确切布局，但对于示例来说足够接近。

CREATE TABLE public.test
(
  id serial,
  sessionrange tstzrange,
  fk_user_id integer
);

insert into test (sessionrange, fk_user_id)
values 
('[2016-01-14 11:57:01-05,2016-01-14 12:06:59-05]', 1)
,('[2016-01-14 12:06:53-05,2016-01-14 12:17:28-05]', 1)
,('[2016-01-14 12:17:24-05,2016-01-14 12:21:56-05]', 1)
,('[2016-01-14 18:18:00-05,2016-01-14 18:42:09-05]', 2)
,('[2016-01-14 18:18:08-05,2016-01-14 18:18:15-05]', 1)
,('[2016-01-14 18:38:12-05,2016-01-14 18:48:20-05]', 1)
,('[2016-01-14 18:18:16-05,2016-01-14 18:18:26-05]', 1)
,('[2016-01-14 18:18:24-05,2016-01-14 18:18:31-05]', 1)
,('[2016-01-14 18:18:12-05,2016-01-14 18:18:20-05]', 3)
,('[2016-01-14 19:32:12-05,2016-01-14 23:18:20-05]', 3)
,('[2016-01-14 18:18:16-05,2016-01-14 18:18:26-05]', 4)
,('[2016-01-14 18:18:24-05,2016-01-14 18:18:31-05]', 2);

我发现可以通过以下方法按照开始时间对会话进行排序：

select * from test order by fk_user_id, sessionrange

我可以使用窗口函数来确定单个记录是否与之前的记录重叠，如下所示：

SELECT *, sessionrange && lag(sessionrange) OVER (PARTITION BY fk_user_id ORDER BY sessionrange)
FROM test
ORDER BY fk_user_id, sessionrange

但这只检测单个先前记录是否与当前记录重叠（请参阅id = 6处的记录）。我需要检测到分区开头的所有记录。

在此之后，我需要将任何重叠的记录分组在一起，以找到最早会话的开始和最后会话的结束以终止。

我确信有一种方法可以做到这一点，但我可能忽略了什么。如何折叠这些重叠的记录？

- Ben Wyatt

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- klin · Accepted Answer

将重叠的范围合并为数组元素相对容易。为简单起见，以下函数返回 tstzrange 集合:

create or replace function merge_ranges(tstzrange[])
returns setof tstzrange language plpgsql as $$
declare
    t tstzrange;
    r tstzrange;
begin
    foreach t in array $1 loop
        if r && t then r:= r + t;
        else
            if r notnull then return next r;
            end if;
            r:= t;
        end if;
    end loop;
    if r notnull then return next r;
    end if;
end $$;

只需为用户汇总范围并使用函数：

select fk_user_id, merge_ranges(array_agg(sessionrange))
from test 
group by 1
order by 1, 2

 fk_user_id |                    merge_ranges                     
------------+-----------------------------------------------------
          1 | ["2016-01-14 17:57:01+01","2016-01-14 18:21:56+01"]
          1 | ["2016-01-15 00:18:08+01","2016-01-15 00:18:15+01"]
          1 | ["2016-01-15 00:18:16+01","2016-01-15 00:18:31+01"]
          1 | ["2016-01-15 00:38:12+01","2016-01-15 00:48:20+01"]
          2 | ["2016-01-15 00:18:00+01","2016-01-15 00:42:09+01"]
          3 | ["2016-01-15 00:18:12+01","2016-01-15 00:18:20+01"]
          3 | ["2016-01-15 01:32:12+01","2016-01-15 05:18:20+01"]
          4 | ["2016-01-15 00:18:16+01","2016-01-15 00:18:26+01"]
(8 rows)

另外，该算法可以在一个函数循环中应用于整个表格。对于大型数据集，我不确定但这种方法应该更快。

create or replace function merge_ranges_in_test()
returns setof test language plpgsql as $$
declare
    curr test;
    prev test;
begin
    for curr in
        select * 
        from test
        order by fk_user_id, sessionrange
    loop
        if prev notnull and prev.fk_user_id <> curr.fk_user_id then
            return next prev;
            prev:= null;
        end if;
        if prev.sessionrange && curr.sessionrange then 
            prev.sessionrange:= prev.sessionrange + curr.sessionrange;
        else
            if prev notnull then 
                return next prev;
            end if;
            prev:= curr;
        end if;
    end loop;
    return next prev;
end $$;

结果：

select *
from merge_ranges_in_test();

 id |                    sessionrange                     | fk_user_id 
----+-----------------------------------------------------+------------
  1 | ["2016-01-14 17:57:01+01","2016-01-14 18:21:56+01"] |          1
  5 | ["2016-01-15 00:18:08+01","2016-01-15 00:18:15+01"] |          1
  7 | ["2016-01-15 00:18:16+01","2016-01-15 00:18:31+01"] |          1
  6 | ["2016-01-15 00:38:12+01","2016-01-15 00:48:20+01"] |          1
  4 | ["2016-01-15 00:18:00+01","2016-01-15 00:42:09+01"] |          2
  9 | ["2016-01-15 00:18:12+01","2016-01-15 00:18:20+01"] |          3
 10 | ["2016-01-15 01:32:12+01","2016-01-15 05:18:20+01"] |          3
 11 | ["2016-01-15 00:18:16+01","2016-01-15 00:18:26+01"] |          4
(8 rows)

问题非常有趣。我尝试过找到递归解决方案，但似乎过程式尝试是最自然和高效的。

我最终找到了一个递归解决方案。该查询删除重叠行并插入它们的压缩等效行。

with recursive cte (user_id, ids, range) as (
    select t1.fk_user_id, array[t1.id, t2.id], t1.sessionrange + t2.sessionrange
    from test t1
    join test t2
        on t1.fk_user_id = t2.fk_user_id 
        and t1.id < t2.id
        and t1.sessionrange && t2.sessionrange
union all
    select user_id, ids || t.id, range + sessionrange
    from cte
    join test t
        on user_id = t.fk_user_id 
        and ids[cardinality(ids)] < t.id
        and range && t.sessionrange
    ),
list as (
    select distinct on(id) id, range, user_id
    from cte, unnest(ids) id
    order by id, upper(range)- lower(range) desc
    ),
deleted as (
    delete from test
    where id in (select id from list)
    )
insert into test
select distinct on (range) id, range, user_id
from list
order by range, id;

结果：

select *
from test
order by 3, 2;

 id |                    sessionrange                     | fk_user_id 
----+-----------------------------------------------------+------------
  1 | ["2016-01-14 17:57:01+01","2016-01-14 18:21:56+01"] |          1
  5 | ["2016-01-15 00:18:08+01","2016-01-15 00:18:15+01"] |          1
  7 | ["2016-01-15 00:18:16+01","2016-01-15 00:18:31+01"] |          1
  6 | ["2016-01-15 00:38:12+01","2016-01-15 00:48:20+01"] |          1
  4 | ["2016-01-15 00:18:00+01","2016-01-15 00:42:09+01"] |          2
  9 | ["2016-01-15 00:18:12+01","2016-01-15 00:18:20+01"] |          3
 10 | ["2016-01-15 01:32:12+01","2016-01-15 05:18:20+01"] |          3
 11 | ["2016-01-15 00:18:16+01","2016-01-15 00:18:26+01"] |          4
(8 rows)