MSNG (and my other Tek-Tips buddies),
This has been a fascinating activity for me. MEDIAN is a
function that has been glaringly absent from Oracle's repertoire
of built-in functions since Oracle's beginnings. Until now,
I've summarily dismissed MEDIAN as impossible without some
higher horsepower in Oracle than it has. I'm
not aware that even in Oracle 9i's (or "10g's"
"Analytical"
features there is a reliable, non-estimating-based MEDIAN
function.
So, I was rather surprised when a reliable MEDIAN function
(generalised for character, number, and date median values)
jumped off the screen at me in only 7 lines of PL/SQL procedure
code. Since I haven't before seen such a procedure in Oracle,
I took the time to "productise" it (below) and offer it to
my Tek-Tips buddies first.
In return, I encourage your comments, observations,
criticisms, and suggestions.
Code:
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
REM About this script/file:
REM
REM NAME: "Median.sql" - PL/SQL code to create a function that
REM determines the MEDIAN (i.e., middle) value among an ordered
REM set of values. MEDIAN is the value in the ordered set at
REM which we find as many values "above" that point as we find
REM below that point within the ordered set. This function can
REM find the MEDIAN among any datatypes (NUMBER, VARCHAR2, DATE,
REM et cetera) except LONG datatypes.
REM
REM AUTHOR: Dave Hunt
REM Co-principal, Dasages, LLC
REM dave@dasages.com
REM 1-801-733-5333
REM
REM VERSION: 1.0
REM
REM USAGE PRIVILEGES:
REM The author, Dave Hunt, reserves all rights to this script,
REM including common copyrights. Under his reserved rights,
REM the author grants usage privileges to the public. Anyone
REM may use or distribute this script freely provided 1) there
REM is no fee associated with its use and 2) All REMARKS in
REM this script remain intact. Any publication of this
REM script or inclusion in another publication or body of work
REM occurs only by express, written permission from the author.
REM
REM MAINTENANCE HISTORY:
REM 06-NOV-2003: Original Code
REM
REM SYNTAX:
REM
REM MEDIAN (table_list,expr,WHERE_condition | NULL)
REM
REM USAGE NOTES:
REM The MEDIAN function may appear in any location in a SQL
REM statement or a PL/SQL block where any other single-row or
REM or group function may appear. Since MEDIAN includes a
REM source-table list as its first argument, any existing
REM table (example: DUAL) may drive use of MEDIAN.
REM
REM Although MEDIAN's arguments may originate from any SQL or
REM bind variable, usually the arguments take the form of
REM quoted literals. Example:
REM
REM MEDIAN ('S_EMP','SALARY','WHERE dept_id in(10,20,35)')
REM
REM Argument notes:
REM
REM table_list one or more tables, views, or synonyms (of
REM a table or view) necessary to derive the
REM ordered set of values. If there is more
REM than one table, then use comma separators:
REM MEDIAN('s_emp,s_dept',...).
REM
REM expr any valid Oracle character, number, or date
REM expression which ordered set of values
REM represents the pool from which the MEDIAN
REM function derives its "middle" RETURN value.
REM
REM WHERE_condition an optional WHERE-clause specification that
REM | NULL specifies which rows from the table_list
REM tables shall contribute values to the
REM ordered set. If argument-2 expression values
REM come from all table_list table rows, then
REM there is no need for the WHERE_condition and
REM an un-quoted named literal, NULL, must
REM appear as the MEDIAN function's third argument:
REM
REM SELECT MEDIAN('s_emp','salary',NULL) from DUAL;
REM
REM ...results in the MEDIAN salary from the entire
REM s_emp table.
REM
REM Dealing with quoted values inside MEDIAN's quoted arguments:
REM Oracle is notorious for its difficulty working with
REM single quotes inside of single-quoted literal strings.
REM Although MEDIAN can handle Oracle's standard
REM "two quotes result in one quote" methodology within
REM single-quoted literals, MEDIAN makes it much easier:
REM Within a single-quoted MEDIAN-argument, when you want
REM a quoted literal, you may instead use a "back-quote"
REM (`) (usually above the <left-tab> of most PC keyoards).
REM MEDIAN translates the back-quote to a regular single-
REM quote, as needed, as part of its internal processing.
REM
REM RETURN value: To accommodate character, numeric, and date
REM median values, MEDIAN returns a VARCHAR2 representation
REM of argument-2. MEDIAN can then be an argument itself
REM to a data-type-conversion function such as
REM TO_NUMBER(MEDIAN...), TO_DATE(MEDIAN...), et cetera.
REM
REM EXAMPLES:
REM
REM select median ('s_emp','last_name',null) name from dual;
REM
REM Median Name
REM -----------
REM Newman
REM
REM select median ('s_emp','last_name',
REM 'where last_name between `A` and `H`') name from dual;
REM
REM Median Name
REM -----------
REM Chang
REM
REM select median('s_emp','start_date',null) HDate from dual;
REM
REM Median Hire Date
REM ----------------
REM 27-FEB-91
REM
REM select dept_id,
REM to_number(median('s_emp','salary','where dept_id = '||dept_id)) sal
REM from s_emp
REM group by dept_id;
REM
REM Median
REM DEPT_ID Salary
REM ---------- -----------
REM 10 1,450.00
REM 31 1,400.00
REM 32 1,490.00
REM 33 1,515.00
REM 34 795.00
REM 35 1,450.00
REM 41 1,200.00
REM 42 1,200.00
REM 43 850.00
REM 44 800.00
REM 45 1,100.00
REM 50 1,550.00
REM
REM Important notes for above example:
REM 1) TO_NUMBER converts MEDIAN's return value from varchar2.
REM 2) Since the "GROUP BY" clause considers salaries
REM BY DEPARTMENT, MEDIAN's WHERE_condition must
REM also limit its ordered-set of salaries to
REM just those in the current department.
REM Therefore, MEDIAN's argument-3 WHERE clause is a
REM concatenation of 'where dept_id = ' to whatever
REM is the current value of DEPT_ID for the current
REM grouped row.
REM **************************************************************
REM MEDIAN function definition:
REM **************************************************************
Code:
create or replace function median
( tab_name varchar2
, target_exp varchar2
, where_cond varchar2
) return varchar2
is
rec_cnt number;
sql_stmt varchar2(4000);
mid_point number;
median_value varchar2(4000);
begin
sql_stmt := 'select count(*) from '||tab_name||' '||where_cond;
sql_stmt := translate(sql_stmt,chr(96),chr(39)); /* This command
turns back-quotes (`) into single-quotes ('). */
execute immediate sql_stmt into rec_cnt;
mid_point := ceil(rec_cnt/2);
sql_stmt :=
'select xxx from (select rownum rn, xxx'||
' from (select '||target_exp||' xxx from '||tab_name||
' '||where_cond||' order by xxx)) where rn = '||
mid_point;
sql_stmt := translate(sql_stmt,chr(96),chr(39)); /* This command
turns back-quotes (`) into single-quotes ('). */
execute immediate sql_stmt into median_value;
return median_value;
end;
/
REM **************************************************************
REM End of MEDIAN function definition
REM **************************************************************
Here are the pertinent table values with which I tested the code (First, in SALARY order to confirm MEDIAN(...SALARY...):
select start_date, last_name, salary from s_emp order by salary;
Code:
Start
Date LAST_NAME SALARY
--------- --------------- -----------
21-JUL-91 Newman 750.00
06-AUG-91 Patel 795.00
17-OCT-90 Patel 795.00
30-NOV-90 Chang 800.00
26-MAY-91 Markarian 850.00
17-MAR-91 Dancs 860.00
08-MAR-90 Smith 940.00
07-APR-90 Biri 1,100.00
09-MAY-91 Schwartz 1,100.00
18-JAN-91 Urguhart 1,200.00
09-FEB-91 Nozaki 1,200.00
14-MAY-90 Menchu 1,250.00
09-FEB-92 Catchpole 1,300.00 <-- Median Salary
27-FEB-91 Havel 1,307.00
17-JUN-91 Nagayama 1,400.00
07-FEB-92 Maduro 1,400.00
14-MAY-90 Magee 1,400.00
08-MAR-90 Ngao 1,450.00
09-OCT-91 Dumas 1,450.00
07-APR-90 Quick-To-See 1,450.00
18-JAN-92 Giljum 1,490.00
18-FEB-91 Sedeghi 1,515.00
22-JAN-92 Nguyen 1,525.00
04-MAR-90 Ropeburn 1,550.00
03-MAR-90 Velasquez 11,110.00
25 rows selected.
select median('s_emp','salary',null) from dual;
1300
1 row selected.
Second, in LAST_NAME order to confirm MEDIAN(...LAST_NAME...):
Code:
select start_date, last_name, salary from s_emp order by last_name;
Start
Date LAST_NAME SALARY
--------- --------------- -----------
07-APR-90 Biri 1,100.00
09-FEB-92 Catchpole 1,300.00
30-NOV-90 Chang 800.00
17-MAR-91 Dancs 860.00
09-OCT-91 Dumas 1,450.00
18-JAN-92 Giljum 1,490.00
27-FEB-91 Havel 1,307.00
07-FEB-92 Maduro 1,400.00
14-MAY-90 Magee 1,400.00
26-MAY-91 Markarian 850.00
14-MAY-90 Menchu 1,250.00
17-JUN-91 Nagayama 1,400.00
21-JUL-91 Newman 750.00 <-- Median Name
08-MAR-90 Ngao 1,450.00
22-JAN-92 Nguyen 1,525.00
09-FEB-91 Nozaki 1,200.00
06-AUG-91 Patel 795.00
17-OCT-90 Patel 795.00
07-APR-90 Quick-To-See 1,450.00
04-MAR-90 Ropeburn 1,550.00
09-MAY-91 Schwartz 1,100.00
18-FEB-91 Sedeghi 1,515.00
08-MAR-90 Smith 940.00
18-JAN-91 Urguhart 1,200.00
03-MAR-90 Velasquez 11,110.00
25 rows selected.
select median('s_emp','last_name',null) from dual;
Newman
1 row selected.
Lastly, in START_DATE order to confirm MEDIAN(...START_DATE...):
Code:
select start_date, last_name, salary from s_emp order by start_date;
Start
Date LAST_NAME SALARY
--------- --------------- -----------
04-MAR-90 Ropeburn 1,550.00
08-MAR-90 Ngao 1,450.00
08-MAR-90 Smith 940.00
07-APR-90 Quick-To-See 1,450.00
07-APR-90 Biri 1,100.00
14-MAY-90 Menchu 1,250.00
14-MAY-90 Magee 1,400.00
17-OCT-90 Patel 795.00
30-NOV-90 Chang 800.00
18-JAN-91 Urguhart 1,200.00
09-FEB-91 Nozaki 1,200.00
18-FEB-91 Sedeghi 1,515.00
27-FEB-91 Havel 1,307.00 <-- Median START_DATE
17-MAR-91 Dancs 860.00
09-MAY-91 Schwartz 1,100.00
26-MAY-91 Markarian 850.00
17-JUN-91 Nagayama 1,400.00
21-JUL-91 Newman 750.00
06-AUG-91 Patel 795.00
09-OCT-91 Dumas 1,450.00
18-JAN-92 Giljum 1,490.00
22-JAN-92 Nguyen 1,525.00
07-FEB-92 Maduro 1,400.00
09-FEB-92 Catchpole 1,300.00
03-MAR-90 Velasquez 11,110.00
25 rows selected.
select median('s_emp','start_date',null) from dual;
27-FEB-91
1 row selected.
Now, MSNG, Following is my "hand-coded" (but not Oracle-checked)
revision of your original code with the addition of my MEDIAN
function. (Since I don't have your A, B, and C tables, their
definitions, or their data, I really could not do any more than
a "desk check" of the code. Since you have a moderately complex
combination of "WHERE" conditions and "GROUP BY" clauses that
significantly affect the pool of "ordered-set" TOTAL_HOURS for
each employee, and must, therefore, translate into identical
filtering for the MEDIAN function, I've done the best I can
with my argument-3 WHERE_condition formulation. As a result,
you may need to "tweak" argument-3. Once the WHERE
condition is correct, the MEDIAN return value will be correct:
Code:
SELECT A.HRID
,LTRIM(RTRIM(A.LAST_NAME)) ||
', '||LTRIM(RTRIM(A.FIRST_NAME)) || ' '||
LTRIM(RTRIM(A.MIDDLE_NAME)) AS EMPLOYEE_NAME
,NVL(SUM(B.TOTAL_HOURS),0) AS TOTAL_HOURS
,TO_NUMBER(MEDIAN('A,B,E','B.TOTAL_HOURS',
'WHERE A.HRID = B.HRID(+) '||
'AND A.HRID = E.HRID '||
'AND B.ATTENDANCE_START_DATE >= TO_DATE(FROM_DATE,`MM/DD/YYYY`) '||
'AND B.ATTENDANCE_START_DATE <= TO_DATE(T_DATE,`MM/DD/YYYY`) '||
'AND LTRIM(RTRIM(A.LAST_NAME)) = `'||LTRIM(RTRIM(A.LAST_NAME))||
'` AND LTRIM(RTRIM(A.FIRST_NAME)) = `'||LTRIM(RTRIM(A.FIRST_NAME))||
'` AND LTRIM(RTRIM(A.MIDDLE_NAME)) = `'||LTRIM(RTRIM(A.MIDDLE_NAME))||
'` AND E.SUPERVISOR_HRID = '||E.SUPERVISOR_HRID||
'AND E.DISTRICT_HRID = '||E.DISTRICT_HRID||
'AND E.DIVISION_HRID = '||E.DIVISION_HRID||'`')) Middle
,COUNT(B.HRID) AS TOTAL_COUNT
,E.SUPERVISOR_HRID
,A.MRG
,E.MANAGER_HRID
,E.DISTRICT_HRID
,E.DIVISION_HRID
FROM A,B,E
WHERE A.HRID = B.HRID(+)
AND A.HRID = E.HRID
AND B.ATTENDANCE_START_DATE >= TO_DATE(FROM_DATE,'MM/DD/YYYY')
AND B.ATTENDANCE_START_DATE <= TO_DATE(T_DATE,'MM/DD/YYYY')
GROUP BY A.HRID
,LTRIM(RTRIM(A.LAST_NAME)) || ', '||LTRIM(RTRIM(A.FIRST_NAME)) || ' '||LTRIM(RTRIM(A.MIDDLE_NAME)),
,E.SUPERVISOR_HRID
,A.MRG
,E.MANAGER_HRID
,E.DISTRICT_HRID
,E.DIVISION_HRID
ORDER BY EMPLOYEE_NAME
/
I know that the MEDIAN function works fine when the WHERE_condition is well done.
So, cross your fingers and say your prayers that I've properly constructed the
WHERE_condition from your out WHERE and GROUP BY clauses.
Let me know how it works for you, (I believe I'll go to bed for a couple of hours before tomorrow/today starts all over again.)
Cheers,
Dave
Sandy, Utah, USA @ 13:53 GMT, 06:53 Mountain Time
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
