[Oracle-l] 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>
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.

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