Re: Median function in SQL

From: Chrysalis <cellis_at_iol.ie>
Date: 1998/03/02
Message-ID: <34FB3643.6A67_at_iol.ie>#1/1

Kevin Merritt wrote:
>
> Does anyone have any idea how to implement a MEDIAN function in Oracle? For
> example, I would like to perform the following SQL SELECT statement:
>
> SELECT MEDIAN(revenue) FROM company_financials WHERE revenue > 1000000
>
> I can write a stored procedure for each table/column I want to perform this
> for, but I would like to somehow extend the built-in environment to have
> the ultimate flexiblity.
>
> Thanks in advance

You may find the following procedure useful as a generic method of obtaining the median of any domain from any relation:

set serverout on size 4096;
create or replace procedure MEDIAN_VALUE

   (table_name varchar2, column_name varchar2, median OUT number) as    /*

      Note that this cannot be made a function callable from SQL,
      since it necessarily involves a SQL statement with an ORDER BY

   */

   S0 varchar2 (120) := 'select count('||column_name||') from ' ||table_name;

   S1  varchar2 (180) := 'select '|| column_name ||
                         ' from ' || table_name  ||
                         ' where '|| column_name || ' is not null'||
                         ' order by '||column_name;

   dummy integer;
   C0    integer;
   X0    integer;
   odd   boolean;

   cnt integer;
   skip integer;
   val1 number;
   val2 number;

begin
/* Define a cursor to count (non-null) values of target column */

   C0 := dbms_sql.open_cursor;
   dbms_sql.parse(C0,S0,dbms_sql.v7);
   dbms_sql.define_column(C0,1,cnt);    

/* Execute it */

   X0 := dbms_sql.execute(C0);

   dummy := dbms_sql.fetch_rows(C0);	-- fetch the (only) row;
   dbms_sql.column_value(C0,1,cnt);		-- count in variable cnt
   dbms_sql.close_cursor(C0);			-- so that we can re-use the reference;
   

/* Check whether the number of values is odd or even */

   if mod(cnt,2) = 1 then

      ODD := TRUE;
      skip := (cnt-1)/2;
   else
      ODD := FALSE;
      skip := cnt/2-1;

   end if;    

/* Re-use the cursor to select rows in order of target column */

   C0 := dbms_sql.open_cursor;
   dbms_sql.parse(C0,S1,dbms_sql.v7);
   dbms_sql.define_column(C0,1,val1);    

/* Execute it */

   X0 := dbms_sql.execute(C0);

/* Process rows */

   for i in 1 .. skip -- skip computed number of rows    loop

      dummy := dbms_sql.fetch_rows(C0);
   end loop;
   /*

     The next value fetched is:
     a)  the median if rows are ODD or
     b) the first of two values to be averaged if not

   */
   dummy := dbms_sql.fetch_rows(C0);
   dbms_sql.column_value(C0,1,val1);
   if ODD then

      median := val1;
   else

      dummy := dbms_sql.fetch_rows(C0);
      dbms_sql.column_value(C0,1,val2);
      median := (val1 + val2)/2;

   end if;
   dbms_sql.close_cursor(C0);
end MEDIAN;
/

show errors;

Rem Example of usage (note that "table_name" can be a view) Rem "variable median number;"
Rem "execute median_value('user_tab_columns','data_length',:median);" Rem
Rem Then:
Rem "print median;" OR (for example)
Rem "select min(data_length),max(data_length),:median "median(data_length)"
Rem from user_tab_columns;"

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards, Guards"

Received on Mon Mar 02 1998 - 00:00:00 CET