Re: Newbie? or just lack of resources and training? PL/SQL Question

dispatcher wrote:
>
> I've been placed in charge (I think because I was the only one not
> afraid of it) Intergraph Computer Aided Dispatch System Using Clix (read
> Intergraph's unix)
> and these Oracle products:
>
> ORAKRNL SN01040 24-FEB-1993 06.00.33.05 ORACLE Base product (kernel)
> ORAKRNLRT SN01161 24-FEB-1993 06.00.33.05 ORACLE Base product (kernel)
> ORAPLUS SN01045 24-FEB-1993 03.00.09.06 ORACLE SQL*Plus Utility
> ORAPLUSRT SN01162 24-FEB-1993 03.00.09.06 ORACLE SQL*Plus Utility - Run
> ORATPO SN01110 24-FEB-1993 06.00.33.05 ORACLE Transaction Processing
>
> I have been trying to find the median of the difference between two
> timestamps.
> (The bean counters are demanding a print out of what we could tell them
> from
> experience) I can not use the true average because there are enough
> aberations
> to skew the results way to the high side. So the question is....
>
> Is anyone familar with a method or command to derive the median of a set
> of numbers using PL/SQL? (I'm hoping for a command, because I've looked
> at a Statistics book
> and I'm not sure I have the time to learn the formula)
>
> For reference this is what I used to find the average:
>
> SQL> select (avg(ds_ts - sdts))/60 from aeven
> 2 where curent='T' and PRIORITY < '4' and
> 3 cdts between '19960601000000' AND '19960630240000';
>
> (AVG(DS_TS-SDTS))/60
> --------------------
> 8.0189
>
> All help or pointers greatfully accepted.
>
> Thom Woolverton ( dispatcher_at_tntonline.com )
> FIRE/EMS Emergency Dispatch
> Lee County, FL
>
> http://www.tntonline.com/personal/911.htm

Unfortunately SQL and PL/SQL don't have a MEDIAN function so the way to do it, it seems to me would be to create a stored procedure where you gave it the beginning and ending cdts.

Create or replace
function median(b_cdts date, e_cdts date) return number is /* I'm assuming cdts is a date */

   cursor c_aeven is select (ds_ts - sdts) dtsdiff from aeven where

      curent='T' and PRIORITY < '4' and cdts between b_cdts and e_cdts
      order by (ds_ts - sdts);
   r_aeven     c_aeven%rowtype;
   stop_pt     number;
   w_cnt       number;
   pr_val      number;
   even_n      boolean;

   select count(*) into w_cnt from aeven where curent='T' and

      PRIORITY < '4' and cdts between b_cdts and e_cdts;    stop_pt:=floor(w_cnt/2)+1;
   even_n:=(mod(w_cnt,2)=0); /* An even number? */    w_cnt:=0;
   for r_aeven in c_aeven loop

      w_cnt:=w_cnt+1;
      if w_cnt < stop_pt then
         pr_val:=r_aeven.dtsdiff;
      else
         exit;
      end if;

      return (pr_val+r_aeven.dtsdiff)/2;    else

      return r_aeven.dtsdiff;
   end if;
end;
/

I believe that should do it. If the number of rows that match the where clause are not too terribly large it should be fairly quick. You may want to parameterize the "curent='T' and priority<'4'" rather than hard-code them in.

I hope this helps
Ken Denny
Insight Industries, Inc.
RTP, NC
kdenny_at_interpath.com Received on Wed Nov 20 1996 - 00:00:00 CET