From oracle-l-bounce@freelists.org Wed Mar 3 03:44:06 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i239i6c08711 for ; Wed, 3 Mar 2004 03:44:06 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i239i4o08696 for ; Wed, 3 Mar 2004 03:44:05 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 52550394981; Wed, 3 Mar 2004 04:44:19 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 03 Mar 2004 04:43:06 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from amis-colo-mail1.vanco.nl (amis-colo-mail1.vanco.nl [194.7.240.241]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3E2B2394E1F for ; Wed, 3 Mar 2004 04:43:03 -0500 (EST) Received: from amis-colo-mail1.vanco.nl (localhost [127.0.0.1]) by amis-colo-mail1.vanco.nl (8.11.7+Sun/8.11.6) with ESMTP id i239kpP24161 for ; Wed, 3 Mar 2004 10:46:51 +0100 (MET) Received: from miov11 (miov11.eu.amis.com [138.203.104.77]) by amis-colo-mail1.vanco.nl (8.11.7+Sun/8.11.6) with ESMTP id i239kdf24151; Wed, 3 Mar 2004 10:46:45 +0100 (MET) Received: from noteseu01.eu.amis.com ([138.203.105.217]) by mail.eu.amis.com (PMDF V5.2-32 #40601) with ESMTP id <01L7ADASWTAK961ON9@mail.eu.amis.com>; Wed, 3 Mar 2004 10:46:39 +0100 (MET) Date: Wed, 03 Mar 2004 10:46:37 +0100 From: jo_holvoet@amis.com Subject: RE: OLAP question To: Jacques Kilchoer Cc: oracle-l@freelists.org Message-id: MIME-version: 1.0 X-Mailer: Lotus Notes Release 5.0.8 June 18, 2001 Content-Type: text/plain; charset="us-ascii" X-MIMETrack: Serialize by Router on NotesEU01/SRV/AMIEU(Release 5.0.12 |February 13, 2003) at 03/03/2004 10:46:39, Serialize complete at 03/03/2004 10:46:39 X-archive-position: 135 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jo_holvoet@amis.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l 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@freelists.org cc: Jo HOLVOET/AMIEU@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@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@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 -----------------------------------------------------------------