巩固时间段 [英] consolidate time periods
问题描述
我能得到一些帮助吗?给出一个非常简单的事件表,
他们的持续时间,我想巩固一个事件的时间段
(1)相互接触或(2)重叠每个事件其他。
CREATE TABLE事件
(event_id VARCHAR(25)NOT NULL,
start_date DATE NOT NULL,>
end_date DATE NOT NULL,
CHECK(start_date< = end_date),
PRIMARY KEY(event_id,start_date,end_date));
插入活动
价值(''馅饼吃',''2009-01-01'',''2009-01-02''),< br $>
(''馅饼吃',''2009-01-03'',''2009-01-05''),
(''馅饼吃'' ','''2009-01-04'',''2009-01-07''),
(''馅饼吃'',''2009-02-01'',' '2009-02-07'');
目标是将这三行减少到更少的行,以显示我们吃馅饼的好几天
。
(''馅饼吃'',' 2009-01-01'','''2009-01-07'')
INSERT INTO语句中的第一行和第二行触摸每个
其他可以替换为:
(''Pie Eating'',''2009-01-01'',''2009-01-05'')>
第三行将与这个新行重叠,并且可以用它合并
,如前所示。但是,第四行的数据是
出现在前三个月之后的一个月,它与所有
其他行不相交。
我想要的是:
1)便携,简单的光标解决方案
2)递归CTE解决方案
我将在书中使用它,所以你会得到信用。
Can I get a little help? Given a very simple table of events, with
their durations, I want to consolidate time periods for an event that
(1) touch each other or (2) overlap each other.
CREATE TABLE Events
(event_id VARCHAR(25) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CHECK (start_date <= end_date),
PRIMARY KEY (event_id, start_date, end_date));
INSERT INTO Events
VALUES (''Pie Eating'', ''2009-01-01'', ''2009-01-02''),
(''Pie Eating'', ''2009-01-03'', ''2009-01-05''),
(''Pie Eating'', ''2009-01-04'', ''2009-01-07''),
(''Pie Eating'', ''2009-02-01'', ''2009-02-07'');
The goal is to reduce these three rows into fewer rows that show how
many days we were eating pies.
(''Pie Eating'', ''2009-01-01'', ''2009-01-07'')
The first and second rows in the INSERT INTO statement touch each
other and can be replaced with:
(''Pie Eating'', ''2009-01-01'', ''2009-01-05'')
The third row will overlap with this new row and can be consolidated
with it, as shown before. However, the fourth row has data that
occurs a month after the first three and it is disjoint from all the
other rows.
What I want is:
1) A portable, simple cursor solution
2) A Recursive CTE solution
I am going to use this in a book, so you will get credit.
推荐答案
- CELKO--写道:
--CELKO-- wrote:
我能得到一点帮助吗?给出一个非常简单的事件表,
他们的持续时间,我想巩固一个事件的时间段
(1)相互接触或(2)重叠每个事件其他。
CREATE TABLE事件
(event_id VARCHAR(25)NOT NULL,
start_date DATE NOT NULL,>
end_date DATE NOT NULL,
CHECK(start_date< = end_date),
PRIMARY KEY(event_id,start_date,end_date));
插入活动
价值(''馅饼吃',''2009-01-01'',''2009-01-02''),< br $>
(''馅饼吃',''2009-01-03'',''2009-01-05''),
(''馅饼吃'' ','''2009-01-04'',''2009-01-07''),
(''馅饼吃'',''2009-02-01'',' '2009-02-07'');
目标是将这三行减少到更少的行,以显示我们吃馅饼的好几天
。
(''馅饼吃'',''2009-01-01'',''2009-01-07'')
INSERT INTO语句中的第一行和第二行触及每个
其他可以替换为:
(''Pie Eating'',''2009-01-01'',''2009-01-05 '')
第三行将与这个新行重叠,并且可以用它合并
,如前所示。但是,第四行的数据是
出现在前三个月之后的一个月,它与所有
其他行不相交。
我想要的是:
1)便携,简单的光标解决方案
2)递归CTE解决方案
我将在一本书中使用它,所以你会得到信用。
Can I get a little help? Given a very simple table of events, with
their durations, I want to consolidate time periods for an event that
(1) touch each other or (2) overlap each other.
CREATE TABLE Events
(event_id VARCHAR(25) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CHECK (start_date <= end_date),
PRIMARY KEY (event_id, start_date, end_date));
INSERT INTO Events
VALUES (''Pie Eating'', ''2009-01-01'', ''2009-01-02''),
(''Pie Eating'', ''2009-01-03'', ''2009-01-05''),
(''Pie Eating'', ''2009-01-04'', ''2009-01-07''),
(''Pie Eating'', ''2009-02-01'', ''2009-02-07'');
The goal is to reduce these three rows into fewer rows that show how
many days we were eating pies.
(''Pie Eating'', ''2009-01-01'', ''2009-01-07'')
The first and second rows in the INSERT INTO statement touch each
other and can be replaced with:
(''Pie Eating'', ''2009-01-01'', ''2009-01-05'')
The third row will overlap with this new row and can be consolidated
with it, as shown before. However, the fourth row has data that
occurs a month after the first three and it is disjoint from all the
other rows.
What I want is:
1) A portable, simple cursor solution
2) A Recursive CTE solution
I am going to use this in a book, so you will get credit.
1)太多的工作和SQL Server支持递归CTE我声称
便携性;-)
2)WITH rec(event_id,start_date,end_date)
AS(SELECT event_id,start_date,end_date FROM events
UNION ALL
SELECT rec.event_id,rec.start_date,events.end_date
FROM events,
rec
WHERE events.start_date
BETWEEN rec.start_date AND rec.end_date + 1天
AND events.end_date rec.end_date)
SELECT event_id,MIN(start_date)as start_date,end_date
FROM(SELECT event_id,start_date,MAX(end_date)AS end_d吃了
来自rec
GROUP BY event_id,start_date)AS rtrunc
GROUP BY event_id,end_date;
EVENT_ID START_DATE END_DATE
------------------------- ---------- - ---------
SQL0347W递归公用表表达式SRIELAU.REC可能包含一个
无限循环。 SQLSTATE = 01605
馅饼吃01/01/2009 01/07/2009
馅饼吃02/01/2009 02/07/2009
2条记录被选中时打印出1条警告信息。
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
1) Too much work and with SQL Server supporting recursive CTE I claim
portability ;-)
2) WITH rec(event_id, start_date, end_date)
AS (SELECT event_id, start_date, end_date FROM events
UNION ALL
SELECT rec.event_id, rec.start_date, events.end_date
FROM events,
rec
WHERE events.start_date
BETWEEN rec.start_date AND rec.end_date + 1 day
AND events.end_date rec.end_date)
SELECT event_id, MIN(start_date) as start_date, end_date
FROM (SELECT event_id, start_date, MAX(end_date) AS end_date
FROM rec
GROUP BY event_id, start_date) AS rtrunc
GROUP BY event_id, end_date;
EVENT_ID START_DATE END_DATE
------------------------- ---------- ----------
SQL0347W The recursive common table expression "SRIELAU.REC" may contain an
infinite loop. SQLSTATE=01605
Pie Eating 01/01/2009 01/07/2009
Pie Eating 02/01/2009 02/07/2009
2 record(s) selected with 1 warning messages printed.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
PS:我怀疑这可以用OLAP表达式编写,也可以使用
窗口
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
PS: I suspect this can be written with OLAP expressions as well using
windowing
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge Rielau写道:
Serge Rielau wrote:
PS:我怀疑这可以用OLAP表达式编写
窗口
PS: I suspect this can be written with OLAP expressions as well using
windowing
我想的是:
SELECT min_start_date,MAX(end_date)
FROM(
SELECT
start_date,end_date,
MAX(例如,当start_date< = max_end_date + 1天
THEN NULL ELSE start_date END)
OVER(ORDER BY start_date,end_date
ROWS UNBOUNDED PRECEDING)
FROM(
SELECT
start_date,end_date,
MAX(end_date)OVER(
ORDER BY start_date,end_date
无限制前期和前期之间的行程
)
来自事件
)T1(start_date,end_date,max_end_date)
)T2(start_date,end_date,min_start_date)
GROUP BY min_start_date
ORDER BY min_start_date;
应该工作
/ Lennart
I guess something like:
SELECT min_start_date, MAX(end_date)
FROM (
SELECT
start_date, end_date,
MAX(CASE WHEN start_date <= max_end_date + 1 day
THEN NULL ELSE start_date END)
OVER (ORDER BY start_date, end_date
ROWS UNBOUNDED PRECEDING)
FROM (
SELECT
start_date, end_date,
MAX(end_date) OVER (
ORDER BY start_date, end_date
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
)
FROM events
) T1 (start_date, end_date, max_end_date)
) T2 (start_date, end_date, min_start_date)
GROUP BY min_start_date
ORDER BY min_start_date;
should work
/Lennart
这篇关于巩固时间段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!