RE: OLAP question

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 2 Mar 2004 12:00:09 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F602621033DAF0D@irvmbxw02>

You should sign up for Oracle Magazine! This same question was answered in the Ask Tom column in the March / April 2004 issue. I'm going to skip his long explanation and go straight to the solution. Maybe there is a shorter way of writing this but I think that Tom Kyte's solution is the shortest one.

SQL> select

  2     equipment,
  3     to_char (date_time, 'DD-MON-YYYY HH24:MI:SS') as date_time,
  4     status,
  5     delta_secs

  6 from equipment_downtime ;

EQUIPMENT DATE_TIME STAT DELTA_SECS

------------ -------------------- ---- ----------
A1           01-JAN-2004 00:00:00 down         15
A1           01-JAN-2004 00:00:15 down         20
A1           01-JAN-2004 00:00:35 up          600
A1           01-JAN-2004 00:10:35 down         25
A1           01-JAN-2004 00:11:00 up         1500

SQL> select
  2     min (z.equipment) as equipment,
  3     to_char (min (z.date_time), 'DD-MON-YYYY HH24:MI:SS') as date_time,
  4     to_char (sum (z.delta_secs)) || ' secs' as downtime

  5 from
  6 (select

  7        y.equipment,
  8        y.date_time,
  9        y.status,
 10        y.delta_secs,
 11        max (y.rn) over (order by y.equipment, y.date_time) as max_rn
 12      from
 13        (select
 14            x.equipment,
 15            x.date_time,
 16            x.status,
 17            x.delta_secs,
 18            case
 19               when x.status != x.lstatus or x.lstatus is null
 20               then x.row_num
 21            end as rn
 22          from 
 23           (select
 24               w.equipment,
 25               w.date_time,
 26               w.status,
 27               w.delta_secs,
 28               lag (w.status) over (order by w.equipment, w.date_time) as lstatus,
 29               row_number () over (order by w.equipment, w.date_time) as row_num
 30             from equipment_downtime w
 31           ) x
 32        ) y

 33 ) z
 34 where z.status = 'down'
 35 group by z.max_rn
 36 order by 1, 2 ;

EQUIPMENT DATE_TIME DOWNTIME

------------ -------------------- ---------------------------------------------
A1           01-JAN-2004 00:00:00 35 secs
A1           01-JAN-2004 00:10:35 25 secs

SQL>
> -----Original Message-----
> jo_holvoet_at_amis.com
>
> Hi all,
>
> I have a dataset relating to equipment up/downtime. Example :
>
> equipment date_time status delta_secs
> --------------------------------------------------------------
> ----------------
> ...
> A1 01-JAN-2004 00:00:00 down 15
> A1 01-JAN-2004 00:00:15 down 20
> A1 01-JAN-2004 00:00:35 up 600
> A1 01-JAN-2004 00:10:35 down 25
> A1 01-JAN-2004 00:11:00 up 1500
> ...
>
> I would like to have an overview of downtimes like :
>
> ...
> A1 01-JAN-2004 00:00:00 35 secs
> A1 01-JAN-2004 00:10:35 25 secs
> ...
>
> Can I do this with analytic functions ? When I start with
> something like :
>
> select equipment,
> date_time,
> sum(delta_secs) over (partition by equipment, status order by
> equipment, date_time)
> from dataset
>
> then of course I get something like :
>
> A1 01-JAN-2004 00:00:00 35 secs
> A1 01-JAN-2004 00:10:35 60 secs
>
> (the second downtime is added to the first).
>
> Am I missing something obvious ?

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Mar 02 2004 - 13:57:42 CST