如果序列未中断,则从多行获取总时间间隔 [英] Get total time interval from multiple rows if sequence not broken
问题描述
我有个工作
和人
表(这些只是了解问题的示例)。
I have Work
and Person
tables (these are just examples to understand problem).
id INTEGER
person_id INTEGER
dt_from DATETIME
dt_to DATETIME
人员
表
Person
table
person_id INTEGER
name VARCHAR(50)
数据
工作
表
Data
Work
table
id | person_id | dt_from | dt_to
-------------------------------------------------
1 | 1 | 2011-01-01 | 2011-02-02
2 | 1 | 2011-02-02 | 2011-04-04
3 | 1 | 2011-06-06 | 2011-09-09
4 | 2 | 2011-01-01 | 2011-02-02
5 | 2 | 2011-02-02 | 2011-03-03
....etc.
人员
表
带有人名的人的名字
Person
table
Just person names with person id
Person 1 : 2011-01-01 - 2011-04-04
Person 1 : 2011-06-06 - 2011-09-09
Person 2 : 2011-01-01 - 2011-03-03
时间间隔必须按顺序进行。它不能在中间的某个地方被破坏。这就是为什么第一个人有两个时间间隔的原因。
Interval must be in sequence. It cannot be broken somewhere in middle. Thats why Person 1 have two intervals.
我正在使用postgres,如果它有所更改。你有什么想法吗?
我想在一个查询中执行此操作,但是如果没有这样的解决方案,我将在php中进行一些间隔合并。
I'm using postgres if it changes something. Have you any thougths? I wanted do it in one query, but if there is no such solution i will do some interval merge in php.
推荐答案
在一个SQL选择中可能有一种方法可以执行此操作,但这使我无所适从。我设法做到了一个存储功能。这是我所做的测试:
There may be a way to do this in one SQL select, but it escapes me. I managed to do it with one stored function though. Here's what I did for my testing:
create table work
(id integer, start_date date, end_date date);
insert into work values (1, '2011-01-01','2011-02-02');
insert into work values (1, '2011-02-02','2011-04-04');
insert into work values (1, '2011-06-06','2011-09-09');
insert into work values (2, '2011-01-01','2011-02-02');
insert into work values (2, '2011-02-02','2011-03-03');
create or replace function get_data() returns setof work as
$body$
declare
res work%rowtype;
sd date := null;
begin
for res in
select
w1.id,
case when exists (select 1 from work w2 where w1.id=w2.id and w2.end_date=w1.start_date) then null else w1.start_date end,
case when exists (select 1 from work w2 where w1.id=w2.id and w2.start_date=w1.end_date) then null else w1.end_date end
from
work w1
order by
id, start_date, end_date
loop
if res.start_date is not null and res.end_date is not null then
return next res;
elsif res.start_date is not null then
sd := res.start_date;
elsif res.end_date is not null then
res.start_date := sd;
return next res;
end if;
end loop;
return;
end;$body$
language 'plpgsql';
然后
select * from get_data() order by id, start_date;
返回以下结果:
id | start_date | end_date
----+------------+------------
1 | 2011-01-01 | 2011-04-04
1 | 2011-06-06 | 2011-09-09
2 | 2011-01-01 | 2011-03-03
(3 rows)
我想这就是您回来。
这篇关于如果序列未中断,则从多行获取总时间间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!