Re: Need help on a query with analytics

Thank you for your help

Vincent

andrewst_at_onetel.net.uk (Tony) wrote in message news:<c0e3f26e.0402230520.246b0f98_at_posting.google.com>...
> fritschvince_at_yahoo.fr (FRITSCH Vincent) wrote in message news:<b220bc5e.0402222134.3ac12a71_at_posting.google.com>...
> > Hi,
> >
> > i need help on a query.
> >
> > I've this table :
> >
> > create table t (
> > customer number(5),
> > time date,
> > status varchar2(1)
> > )
> > /
> >
> > insert into t values (1,to_date('20030101','yyyymmdd'),1);
> > insert into t values (1,to_date('20030121','yyyymmdd'),0);
> > insert into t values (1,to_date('20030803','yyyymmdd'),1);
> > insert into t values (1,to_date('20030909','yyyymmdd'),1);
> > insert into t values (1,to_date('20031201','yyyymmdd'),0);
> >
> >
> > What i want is this output, representing periods (with a begin and an
> > end date) of successive status.
> >
> > customer status begin_ end_
> > 1 1 01/01/03 21/01/03
> > 1 0 21/01/03 03/08/03
> > 1 1 03/08/03 01/12/03
> > 1 0 01/12/03
> >
> >
> > On a windows 2000 platform EE 8.1.7, i write this query :
> >
> > select customer,
> > status,
> > time begin_,
> > lead(time) over (partition by customer order by time) end_
> > from t
> > order by customer, time
> > /
> >
> > CUSTOMER S BEGIN_ END_
> > --------- - -------- --------
> > 1 1 01/01/03 21/01/03
> > 1 0 21/01/03 03/08/03
> > 1 1 03/08/03 09/09/03
> > 1 1 09/09/03 01/12/03
> > 1 0 01/12/03
> >
> > but this is not exactly what i search (lines 3 and 4 need to be merge)
> >
> > Analytics functions can be useful here ?
> > Can someone help me ?
> >
> > Thanks in advance
>
> I think this does what you need - basically, remove "non-events" like
> row 4 from the data first:
>
> 1 select customer,
> 2 status,
> 3 time begin_,
> 4 lead(time) over (partition by customer order by time) end_
> 5 from ( select customer,
> 6 status,
> 7 time,
> 8 lag(status) over (partition by customer order by
> time) prev_status
> 9 from t
> 10 )
> 11 where status != nvl(prev_status,-1)
> 12* order by customer, time
> SQL> /
>
> CUSTOMER S BEGIN_ END_
> ---------- - ----------- -----------
> 1 1 01-JAN-2003 21-JAN-2003
> 1 0 21-JAN-2003 03-AUG-2003
> 1 1 03-AUG-2003 01-DEC-2003
> 1 0 01-DEC-2003
Received on Tue Feb 24 2004 - 08:59:46 CET