巩固时间段 [英] 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屋！
