Group data by non-unique keys by distinct time range

Question

I have data in an Oracle table like the following:

PROJ_NBR     STATUS    START_DT        
AL20         AC        1/14/2010 4:31        
AL20         AC        1/14/2010 4:32        
AL20         AC        1/14/2010 4:32        
AL20         DE        1/14/2010 4:34        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         AC        3/4/2010 4:31        
AL20         AC        3/4/2010 4:39        
AL20         AC        3/21/2010 13:24        
AL20         AC        2/4/2011 13:54        --year changes here
AL20         AC        2/4/2011 14:14        
AL20         AC        2/4/2011 14:27        
AL20         DE        2/11/2011 16:24

Our table has 2,091,348 rows.

I want data like following...

PROJ_NBR     STATUS    BEGIN                END
AL20         AC        1/14/2010 4:31       1/14/2010 4:32
AL20         DE        1/14/2010 4:34       1/14/2010 4:46
AL20         AC        3/4/2010 4:31        2/4/2011 14:27  --Second time range for AL20, AC
AL20         DE        2/11/2011 16:24      2/11/2011 16:24

It appears that the data is sorted by date/time and each change of proj_nbr/status should create a new section regardless of time range. Note that the third row in the results spans years yet the fourth is a point in time rather than a range. — Leigh Riffel, Feb 21 '11 at 13:56

bernd_k · Accepted Answer · 2011-02-19 12:36:51Z

Below a complete solution for sql-server.

I was trying analytical functions, but didn't succeed. I'm tempted to repost the question to SO requiring a solution using analytical functions. I think there must be one.

And here is my working solution. I'm proud not to have used cursors.

set language us_english
Set Nocount ON

create table #t( PROJ_NBR char(4),  STATUS char(2), START_DT datetime);

insert into #t values ('AL20',     'AC', '1/14/2010 4:31');  
insert into #t values ('AL20',     'AC', '1/14/2010 4:32');  
insert into #t values ('AL20',     'AC', '1/14/2010 4:32');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:34');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'AC', '3/4/2010 4:31');   
insert into #t values ('AL20',     'AC', '3/4/2010 4:39');   
insert into #t values ('AL20',     'AC', '3/21/2010 13:24'); 
insert into #t values ('AL20',     'AC', '2/4/2011 13:54');  
insert into #t values ('AL20',     'AC', '2/4/2011 14:14');  
insert into #t values ('AL20',     'AC', '2/4/2011 14:27');  
insert into #t values ('AL20',     'DE', '2/11/2011 16:24'); 


select
    distinct PROJ_NBR, STATUS, [start], [end]
from(
select 
*,
(select MIN(START_DT) 
    from #t t1 
    where t.STATUS = t1.STATUS 
    and t.START_DT >= t1.START_DT 
    and not exists ( select * from #t t2 
                    where t.STATUS <> t2.STATUS  
                    and  t.START_DT >= t2.START_DT
                    and t1.START_DT <= t2.START_DT)
) [start],
(select Max(START_DT) 
    from #t t1 
    where t.STATUS = t1.STATUS 
    and t.START_DT <= t1.START_DT 
    and not exists ( select * from #t t2 
                    where t.STATUS <> t2.STATUS  
                    and  t.START_DT <= t2.START_DT
                    and t1.START_DT >= t2.START_DT)
) [end]

from #t t
) t1
order by [start]

I think besides column and table names this can easily adapted to Oracle too.

This solution would require five full table scans. – Leigh Riffel Feb 21 '11 at 16:23 — Leigh Riffel, Feb 21 '11 at 16:23

Leigh Riffel · Accepted Answer · 2011-02-20 21:53:33Z

Oracle Analytics Solution:

SELECT Proj_Nbr, Status, MIN(Start_Dt) AS "BEGIN", MAX(Start_Dt) AS "END"
FROM (
    SELECT Proj_Nbr, Status, Start_Dt, 
       MAX(SectionID) OVER (ORDER BY Start_Dt ROWS UNBOUNDED PRECEDING) SectionID
    FROM
    (
        SELECT Proj_Nbr, Status, Start_Dt, 
           CASE WHEN Proj_Nbr = Lag(Proj_Nbr) OVER (ORDER BY start_dt)
              AND Status = Lag(Status) OVER (ORDER BY start_dt)
                 THEN null
              ELSE rowid
           END SectionID  
        FROM t1
    )
)
GROUP BY Proj_Nbr, Status, SectionID;

Data Generation:

CREATE TABLE t1 (PROJ_NBR VARCHAR2(4),  STATUS VARCHAR2(2), START_DT DATE);

INSERT INTO t1 VALUES ('AL20', 'AC', to_date('01/14/2010 04:31', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('01/14/2010 04:32', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('01/14/2010 04:32', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:34', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('03/04/2010 04:31', 'MM/DD/YYYY HH24:MI')); 
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('03/04/2010 04:39', 'MM/DD/YYYY HH24:MI')); 
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('03/21/2010 13:24', 'MM/DD/YYYY HH24:MI')); 
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('02/04/2011 13:54', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('02/04/2011 14:14', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('02/04/2011 14:27', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('02/11/2011 16:24', 'MM/DD/YYYY HH24:MI'));

Paul White 9 · Accepted Answer · 2018-10-29 08:45:50Z

Here's my take: (Thanks to Leigh for providing the data generation script)

select proj_nbr, status, min(start_dt) start_dt, end_dt 
from (
    select a.*, (
        select max(p.start_dt) 
        from t1 p 
        where P.PROJ_NBR = a.proj_nbr and p.status = a.status 
        and p.start_dt >= a.start_dt
        and not exists (
            select null from t1 q where q.PROJ_NBR = a.proj_nbr 
            and q.status <> p.status 
            and q.start_dt < p.start_dt and q.start_dt > a.start_dt)
    ) end_dt 
    from t1 a
)
group by proj_nbr, status, end_dt

I think adding the following index will improve performance:

create index on t1 (proj_nbr, status, start_date)

Your query does return the correct results. The results are out of order for more recent versions of Oracle, but a simple order by would solve that. — Leigh Riffel, Feb 21 '11 at 12:57

Paul White 9 · Accepted Answer · 2018-10-29 08:46:55Z

0

SELECT PROJ_NBR, STATUS, MIN(START_DT) AS _BEGIN, MAX(START_DT) AS _END 
FROM MYTABLE GROUP BY PROJ_NBR, STATUS;

Why the _? Because BEGIN and END are reserved words.

edited Oct 29 '18 at 8:46

Paul White 9♦

55.9k16 gold badges342 silver badges521 bronze badges

answered Feb 18 '11 at 22:34

Gaius

10.7k3 gold badges27 silver badges63 bronze badges

3

That was my initial thought, but I don't believe that's sufficient. The desired output has two different rows with a PROJ_NBR of AL20 and a STATUS of AC. The simple GROUP BY would return a single such row with a start date of 1/14/2010 and an end date of 2/4/2011. I believe we're going to need analytics to detect the changes from one status to another. – Justin Cave Feb 18 '11 at 23:09
2

Justin's right, I believe that he needs a sequence column that will be reset when the status changes, and take the group data by that particular sequence, not only grouped by proj_nbr and status. – Marian Feb 19 '11 at 11:12

add a comment |

Stack Exchange Network

current community

your communities

more stack exchange communities

Group data by non-unique keys by distinct time range

4 Answers 4

Your Answer

Not the answer you're looking for? Browse other questions tagged oracle group-by gaps-and-islands or ask your own question.

Linked

Hot Network Questions

Group data by non-unique keys by distinct time range

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged oracle group-by gaps-and-islands or ask your own question.

Linked

Related

Hot Network Questions