RE: OLAP question

From: <jo_holvoet_at_amis.com>
Date: Wed, 03 Mar 2004 10:46:37 +0100
Message-id: <OFF2D4935B.E9767066-ONC1256E4C.00351ABF@eu.amis.com>

Thanks Jacques !

What it boils down to I guess is having to simulate the 'partition by ...' analytical clause yourself (via the lag function). I had a solution with one subselect less, but yours runs approximately 25% faster.

Oh, and I was signed up for oramag, but I haven't received the last couple of issues :(
Guess I'll have to sign up again.

Thanks again,

mvg/regards

Jacques Kilchoer <Jacques.Kilchoer_at_quest.com> 03/02/2004 21:00

        To:     oracle-l_at_freelists.org
        cc:     Jo HOLVOET/AMIEU_at_AMIEU
        Subject:        RE: OLAP question

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 Wed Mar 03 2004 - 03:44:06 CST