[Oracle] MEDIAN()

오라클 MEDIAN

 

MEDIAN()

   - 분포된 값들에서 중간값 정도를 return 해주는 것 같다.

   - 처음보는 함수이다.

 

MEDIAN(expr) 

MEDIAN(expr) OVER (query_partition_clause)

의 형태

 

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 data type or any nonnumeric data type that can be implicitly converted to a numeric data type. If you specify only expr, then the function returns the same data type as the numeric data type 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 data type, and returns that data type.

 

The 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 number CRN =CEILING(RN) and FRN = FLOOR(RN).

 

The final result will be :

 

      if(CRN = FRN =RN) then

         (value of expression form row at RN)

      else

         (CRN-RN) * (value of expression for row at RFN) +

         (RN-FRN) * (value of expression for row at CRN)

 

You can use MEDIAN as an analytic function. You can sepcify only the query_partition_cluase 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 function;

• PERCENTILE_CONT , which returns, for a given percentile, the value that correspondes 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.

 

오라클 OE 계정으로 테스트 해볼 수 있다.

 

SQL> SELECT
    department_id,
    MEDIAN(salary),
    AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY department_id ;

 

MEDIAN(salary) 는 department_id 별로 salary 를 정렬해서 중간에 있는 값을 return 해주는 것 같다.

 

SQL>

SELECT 
    manager_id,
    employee_id,
    salary,
    MEDIAN(salary) OVER (PARTITION BY manager_id) "Median by Mgr"
FROM employees
WHERE department_id > 60
ORDER BY manager_id, employee_id;