Re: Need help on a query with analytics

From: Tony <andrewst_at_onetel.net.uk>
Date: 23 Feb 2004 05:20:29 -0800
Message-ID: <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 Mon Feb 23 2004 - 14:20:29 CET