FAQ
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 ?

mvg/regards

Jo

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
-----------------------------------------------------------------

Search Discussions

  • Jacques Kilchoer at Mar 2, 2004 at 1:57 pm
    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
    -----------------------------------------------------------------
  • Jo_holvoet_at_amis.com at Mar 3, 2004 at 3:44 am
    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

    Jo

    Jacques Kilchoer
    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
    -----------------------------------------------------------------

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 2, '04 at 10:49a
activeMar 3, '04 at 3:44a
posts3
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase