【오라클(Oracle) SQL 함수】 MEDIAN 함수

오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

o MEDIAN

 

---

문 법

median::=

---

목 적

MEDIAN 함수는 연속된 분산 모델을 가정한 역 분포 함수이다. 이 함수는 수치형 데이터 또는 일자(datetime)에 대한 중앙값 또는 값을 정렬한 후에 보간법을 사용하여 중앙값을 계산하여 반환한다. 계산 과정에서 Null 값은 무시된다.

이 함수는 수치 데이터 타입 또는 암묵적으로 수치 데이터 타입으로 변환 가능한 비수치 데이터 타입을 인수로 취한다. 만약 epr 인수만 지정하면, 이 함수는 인수의 수치형 데이터 타입과 동일한 데이터 타입을 반환한다. OVER 절을 지정하면, 오라클 데이터베이스는 수치형 우선 순위가 가장 높은 인수를 결정하고, 나머지 인수를 암묵적으로 해당 데이터 타입으로 변환한 다음 해당 데이터 타입을 반환한다.

MEDIAN 함수의 결과는 행을 먼저 정렬하여 행의 순서를 계산한다. 그룹 내에서 행의 개수를 N이라고 하면, Oracle은  수식 RN = (1 + (0.5*(N-1)) 을 사용하여 관심 있는 행 번호(RN)를 계산한다. 집계 함수의 최종 결과는 행 번호 CRN = CEILING(RN) 및 FRN = FLOOR(RN) 의 행 값 사이의 선형 보간을 통해 계산된다.

최종 결과는 다음과 같다.

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)

 

---

계산 과정 상세 설명

1. 행 정렬 : MEDIAN 함수의 값을 계산하기 위해서는 먼저 행들을 순서대로 정렬한다. 수치형 값의 경우 오름차순으로 정렬하고, 문자열 값의 경우 사전순으로 정렬한다.

2. 관심 행 번호(RN) 계산 : 그룹의 행 수를 N이라고 할 때, 관심 행 번호(RN)는 다음 공식으로 계산된다.

RN = 1 + (0.5 * (N-1))

이 공식은 중간값에 해당하는 행 번호를 찾기 위한 것이다. 예를 들어, N이 5이면 RN은 3이 된다. 즉, 5개의 행 중 세 번째 행이 중간값에 해당하는 행이라는 의미이다.

3. 선형 보간 : RN 값이 정수가 아닌 경우, 즉 소수점 값을 가지는 경우에는 선형 보간을 통해 최종 결과를 계산한다. 선형 보간은 두 개의 알려진 값 사이의 중간 값을 추정하는 방법이다. 이 경우, RN 바로 아래의 행 번호(FRN)와 RN 바로 위의 행 번호(CRN)에 해당하는 값들을 사용하여 중간값을 계산한다.

예를 들어, RN이 3.5이면, FRN은 3이고 CRN은 4가 된다. 이때, FRN에 해당하는 값과 CRN에 해당하는 값의 평균을 계산하여 중간값을 구한다.

이러한 과정을 통해 MEDIAN 함수는 그룹 내의 중간값을 계산하여 결과를 반환한다.

---

MEDIAN함수는 분석 함수로 사용할 수 있다. 이 경우 OVER 절에는 단지 query_partiton_clause 만 지정할 수 있다. 각 행에 대해 각 파티션 내의 데이터 값 집합 중 중간에 해당하는 중앙값을 반환한다.

이 함수를 다음 함수와 비교해보자.

• PERCENTILE_CONT 함수는 선형 보간 방법에 의해 지정한 백분위수에 상응하는 값을 반환한다.
• MEDIAN 함수는 백분위수 값이 기본적으로 0.5로 설정된 PERCENTILE_CONT 함수의 특수한 경우이다.
• PERCENTILE_DISC 함수는 보간 없이 주어진 백분위수에 상응하는 백분위수 값을 검색하는데 유용하다.

 

---

패키지 함수 비교 (Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table)

• MEDIAN Oracle Function 프로그래밍 비교

 

---

관련 함수

함수명	함수내용
AVG	지정된 칼럼에 대한 조건을 만족하는 데이터를 기준으로 Null을 제외한 평균값을 반환한다.
COUNT	쿼리에 의해 반환되는 관측치 행의 개수를 반환한다. 집계 함수 또는 분석함수로 이용할 수 있다.
MAX	지정한 인수를 기준으로 그룹 또는 전체 데이터에 대한 최댓값을 반환한다.
MIN	지정한 인수를 기준으로 그룹 또는 전체 데이터에 대한 인수의 최솟값을 반환한다.
MEDIAN	연속된 분산 모델을 가정한 역 분포 함수이다. 이 함수는 수치형 데이터 또는 일자(Date)에 대한 중앙값 또는 값의 정렬 후에 보간법을 사용하여 중앙값을 계산하여 반환한다.
PERCENTILE_CONT	연속된 분포 모델을 가정하여 인수로 지정한 백분위 값에 해당하는 값을 계산하는 역 분포 함수(inverse distribution function)이다.
PERCENTILE_DISC	이산 분포 모형을 가정하는 역 분포 함수이다. 사용자가 지정한 백분위수에 해당하는 데이터 값을 검색하고, 정확하게 일치하는 데이터 값이 없으면 지정한 백분위 값의 주변 데이터 정렬 옵션에 따라 우선순위가 빠른 바로 앞의 관측치 값을 반환한다.
STDDEV	수치형 인수 expr의 표본표준편차(sample standard deviation)를 반환한다.
SUM	그룹 또는 전체 데이터에 대한 수치형 인수 expr에 대한 값의 합을 반환한다.
VARIANCE	expr의 분산을 반환한다. 집계 함수 또는 분석함수로 이용된다.

 

---

예 제

 

집계 함수

다음 예제는 hr.employee 테이블에서 개별 부서에 근무하는 직원들의 급여 중앙값을 반환한다.

Oracle Program

SELECT department_id, MEDIAN(salary)
FROM   employees
GROUP BY department_id;

 

Results

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

 

---

분석 함수

다음 예제는 hr.employees 테이블의 특정 부서의 관리자별로 관리 중인 직원들의 급여 중앙값을 반환한다.

Oracle Program

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;

 

Results

MANAGER_ID EMPLOYEE_ID     SALARY Median by Mgr
---------- ----------- ---------- -------------
       100         101      17000         13500
       100         102      17000         13500
       100         145      14000         13500
       100         146      13500         13500
       100         147      12000         13500
       100         148      11000         13500
       100         149      10500         13500
       101         108      12008         12008
       101         204      10000         12008
       101         205      12008         12008
       108         109       9000          7800
       108         110       8200          7800
       108         111       7700          7800
       108         112       7800          7800
       108         113       6900          7800
       145         150      10000          8500
       145         151       9500          8500
       145         152       9000          8500
. . .

 

---

오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

 

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크

2024.01 수정