Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
Syntax
See Also:
"Analytic Functions" for information on syntax, semantics, and restrictionsPurpose
MEDIAN
is an inverse distribution function that assumes a continuous distribution model. It takes a numeric or datetime value and returns the middle value or an interpolated value that would be the middle value once the values are sorted. Nulls are ignored in the calculation.
This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If you specify only expr
, then the function returns the same datatype as the numeric datatype of the argument. if you specify the OVER
clause, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.
See Also:
Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence" for information on numeric precedenceThe result of MEDIAN
is computed by first ordering the rows. Using N
as the number of rows in the group, Oracle calculates the row number (RN
) of interest with the formula RN
= (1
+ (0.5
*(N
-1
)). The final result of the aggregate function is computed by linear interpolation between the values from rows at row numbers CRN
= CEILING
(RN
) and FRN
= FLOOR
(RN
).
The final result will be:
if (CRN = FRN = RN) then (value of expression from row at RN) else (CRN - RN) * (value of expression for row at FRN) + (RN - FRN) * (value of expression for row at CRN)
You can use MEDIAN
as an analytic function. You can specify only the query_partition_clause
in its OVER
clause. It returns, for each row, the value that would fall in the middle among a set of values within each partition.
Compare this function with these functions:
PERCENTILE_CONT, which returns, for a given percentile, the value that corresponds to that percentile by way of interpolation. MEDIAN
is the specific case of PERCENTILE_CONT
where the percentile value defaults to 0.5.
PERCENTILE_DISC, which is useful for finding values for a given percentile without interpolation.
Aggregate Example
The following query returns the median salary for each department in the hr.employees
table:
SELECT department_id, MEDIAN(salary) FROM employees GROUP BY department_id; DEPARTMENT_ID MEDIAN(SALARY) ------------- -------------- 10 4400 20 9500 30 2850 40 6500 50 3100 60 4800 70 10000 80 8900 90 17000 100 8000 110 10150 7000
Analytic Example
The following query returns the median salary for each manager in a subset of departments in the hr.employees
table:
SELECT manager_id, employee_id, salary, MEDIAN(salary) OVER (PARTITION BY manager_id) "Median by Mgr" FROM employees WHERE department_id > 60; MANAGER_ID EMPLOYEE_ID SALARY Median by Mgr ---------- ----------- ---------- ------------- 100 149 10500 13500 100 148 11000 13500 100 147 12000 13500 100 146 13500 13500 100 145 14000 13500 100 101 17000 13500 100 102 17000 13500 101 204 10000 12000 101 108 12000 12000 101 205 12000 12000 108 113 6900 7800 108 111 7700 7800 108 112 7800 7800 108 110 8200 7800 108 109 9000 7800 145 155 7000 8500 145 154 7500 8500 . . .