RE: OLAP question
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Mar 2004 12:00:09 -0800
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@xxxxxxxx
>
> 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
