interesting SQL, consolidate time period
it's post on usenet, intersting problem, let think about it. Siege had given the answers.
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')
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.
1 comments:
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;
发表评论 Add/View Comments