Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Median function in SQL
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;
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;
/* 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*/
median := val1;
else
dummy := dbms_sql.fetch_rows(C0); dbms_sql.column_value(C0,1,val2); median := (val1 + val2)/2;
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 CST