Home » SQL & PL/SQL » SQL & PL/SQL » Grouping Question
Grouping Question [message #199250] Sun, 22 October 2006 21:19 Go to next message
astaylor
Messages: 6
Registered: October 2006
Junior Member
Here is the issue:

I have a data-set, similar to something like:

Name Date Start_Time End_Time

John Nov.2 2:00 4:00
John Nov.2 4:00 5:30
John Nov.2 5:20 6:45
John Nov.2 7:00 9:00
John Nov.2 9:00 10:00
Mike Nov.2 1:00 3:00
Mike Nov.2 2:45 4:00
Mike Nov.2 5:00 6:00
Pete Nov.2 1:00 2:00
Pete Nov.2 2:00 4:00

I need to be able to create data grouping based on Name, Date and touching or overlapping time segments.
In the example below, numbers in the left column will represent the grouping. Values themselves are not important as long as they are distinct.

1 John Nov.2 2:00 4:00
1 John Nov.2 4:00 5:30
1 John Nov.2 5:20 6:45
2 John Nov.2 7:00 9:00
2 John Nov.2 9:00 10:00
3 Mike Nov.2 1:00 3:00
3 Mike Nov.2 2:45 4:00
4 Mike Nov.2 5:00 6:00
5 Pete Nov.2 1:00 2:00
5 Pete Nov.2 2:00 4:00

Grouping is required for further aggregation of some related data.

Can not use PL/SQL (can not touch the DB),so as this should be part of SQL query.

Oracle 9i.
Any help will be greatly appreciated.
Re: Grouping Question [message #199251 is a reply to message #199250] Sun, 22 October 2006 22:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT dense_rank()
       OVER (PARTITION BY name ORDER BY delta) AS grp
,      c.*
FROM ( 
  SELECT rn -
         SUM(
           CASE
             WHEN max_end >= start_time
             THEN 1
             ELSE 0
           END
         )
         OVER (PARTITION BY name ORDER BY start_time) AS delta
  ,      b.*
  FROM (
    SELECT a.*
    , MAX(end_time) 
      OVER (PARTITION BY name ORDER BY start_time ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS max_end
    , ROW_NUMBER()
      OVER (PARTITION BY name ORDER BY start_time) AS rn
    FROM ross_test a
  ) b
) c


Ross Leishman
Re: Grouping Question [message #199258 is a reply to message #199251] Sun, 22 October 2006 23:26 Go to previous messageGo to next message
astaylor
Messages: 6
Registered: October 2006
Junior Member
Ross,

Thank you very much for the prompt reply.

Here are the results of the query (on the sample dataset)

GRP DELTA NAME START_TIM END_TIME MAX_END RN

1 1 John 02-NOV-06 02-NOV-06 1
1 1 John 02-NOV-06 02-NOV-06 02-NOV-06 2
1 1 John 02-NOV-06 02-NOV-06 02-NOV-06 3
2 2 John 02-NOV-06 02-NOV-06 02-NOV-06 4
2 2 John 02-NOV-06 02-NOV-06 02-NOV-06 5
1 1 Mike 02-NOV-06 02-NOV-06 1
1 1 Mike 02-NOV-06 02-NOV-06 02-NOV-06 2
2 2 Mike 02-NOV-06 02-NOV-06 02-NOV-06 3
1 1 Pete 02-NOV-06 02-NOV-06 1
1 1 Pete 02-NOV-06 02-NOV-06 02-NOV-06 2

10 rows selected.

Thanks again for looking into it.
Re: Grouping Question [message #199259 is a reply to message #199258] Sun, 22 October 2006 23:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Didn't test this one, but it should give you the name-independent groups you are after.
SELECT dense_rank()
       OVER (ORDER BY name, delta) AS grp
,      c.*
FROM ( 
  SELECT rn -
         SUM(
           CASE
             WHEN max_end >= start_time
             THEN 1
             ELSE 0
           END
         )
         OVER (PARTITION BY name ORDER BY start_time) AS delta
  ,      b.*
  FROM (
    SELECT a.*
    , MAX(end_time) 
      OVER (PARTITION BY name ORDER BY start_time ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS max_end
    , ROW_NUMBER()
      OVER (PARTITION BY name ORDER BY start_time) AS rn
    FROM ross_test a
  ) b
) c


Ross Leishman
Re: Grouping Question [message #199264 is a reply to message #199259] Sun, 22 October 2006 23:49 Go to previous messageGo to next message
astaylor
Messages: 6
Registered: October 2006
Junior Member
This is it !

GRP DELTA NAME START_TIM END_TIME MAX_END RN
---------- --------- --------- --------- ----------
1 1 John 02-NOV-06 02-NOV-06 1
1 1 John 02-NOV-06 02-NOV-06 02-NOV-06 2
1 1 John 02-NOV-06 02-NOV-06 02-NOV-06 3
2 2 John 02-NOV-06 02-NOV-06 02-NOV-06 4
2 2 John 02-NOV-06 02-NOV-06 02-NOV-06 5
3 1 Mike 02-NOV-06 02-NOV-06 1
3 1 Mike 02-NOV-06 02-NOV-06 02-NOV-06 2
4 2 Mike 02-NOV-06 02-NOV-06 02-NOV-06 3
5 1 Pete 02-NOV-06 02-NOV-06 1
5 1 Pete 02-NOV-06 02-NOV-06 02-NOV-06 2

Thank you very, very much.

Arkady
Re: Grouping Question [message #199265 is a reply to message #199264] Mon, 23 October 2006 00:16 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You're welcome.

This one was for free. Next time supply CREATE TABLE and INSERT statements that we can cut and paste into SQL*Plus to build test data.

Ross Leishman
Re: Grouping Question [message #199334 is a reply to message #199265] Mon, 23 October 2006 11:30 Go to previous message
astaylor
Messages: 6
Registered: October 2006
Junior Member
Will do.

Thanks.
Previous Topic: constraint deferred
Next Topic: Error handling in PL\SQL
Goto Forum:
  


Current Time: Sun Dec 11 00:26:42 CST 2016

Total time taken to generate the page: 0.04236 seconds