PERCENTILE_CONT 창 함수
PERCENTILE_CONT는 연속 분포 모델을 가정하는 역분포 함수입니다. 백분위 값과 정렬 명세를 가지며, 정렬 명세와 관련하여 지정된 백분위 값에 해당하는 보간 값을 반환합니다.
PERCENTILE_CONT는 순서가 지정된 값 사이의 선형 보간을 계산합니다. 이 함수는 집계 그룹에서 백분위 값(P)과 NULL을 제외한 행들의 번호(N)를 사용하여 정렬 명세에 따라 행의 순서를 지정한 후 행 번호를 계산합니다. 행 번호(RN)를 계산하는 공식은 RN = (1+ (P*(N-1))입니다. 이 집계 함수의 최종 결과는 행 번호가 CRN = CEILING(RN)과 FRN = FLOOR(RN)인 행의 값 사이 선형 보간을 통해 계산됩니다.
최종 결과는 다음과 같습니다.
(CRN = FRN = RN)일 때 결과는 (value of expression from
row at RN)입니다.
그렇지 않다면 다음 결과가 표시됩니다.
(CRN - RN) * (value of expression for row at FRN) + (RN - FRN) * (value of
expression for row at CRN).
PARTITION 절은 OVER 절에서만 지정할 수 있습니다. 각 행마다 PARTITION을 지정하면 PERCENTILE_CONT가 임의의 파티션에 속한 값 집합 중에서 지정한 백분위에 해당하는 값을 반환합니다.
PERCENTILE_CONT는 컴퓨팅 노드 전용 함수입니다. 쿼리에서 사용자 정의 테이블이나 Amazon Redshift 시스템 테이블을 참조하지 않는 경우 이 함수는 오류를 반환합니다.
구문
PERCENTILE_CONT ( percentile ) WITHIN GROUP (ORDER BY expr) OVER ( [ PARTITION BY expr_list ] )
인수
- Percentile
-
0과 1 사이의 숫자 상수입니다. 이 계산에서 Null 값은 무시됩니다.
- WITHIN GROUP (ORDER BY expr)
-
숫자 또는 날짜/시간 값을 지정하여 백분위를 정렬 및 계산합니다.
- OVER
-
창 파티션을 지정합니다. OVER 절에는 창 순서 또는 창 프레임 명세가 포함될 수 없습니다.
- PARTITION BY expr
-
OVER 절에서 각 그룹의 레코드 범위를 설정하는 인수(옵션)입니다.
반환 값
반환 형식은 WITHIN GROUP 절에서 ORDER BY 표현식의 데이터 형식에 따라 결정됩니다. 다음 표는 ORDER BY 표현식의 데이터 형식에 따른 반환 형식을 나타낸 것입니다.
| 입력 형식 | 반환 유형 |
|---|---|
| INT2, INT4, INT8, NUMERIC, DECIMAL | DECIMAL |
| FLOAT, DOUBLE | DOUBLE |
| 날짜 | 날짜 |
| TIMESTAMP | TIMESTAMP |
사용 노트
ORDER BY 표현식이 최대 정밀도가 38자리로 정의된 DECIMAL 데이터 형식인 경우에는 PERCENTILE_CONT가 부정확한 결과 또는 오류를 반환합니다. PERCENTILE_CONT 함수의 반환 값이 38자리를 초과하면 정밀도가 손실될 수도 있기 때문에 알맞은 자리 수로 결과가 잘립니다. 보간 도중 중간 결과가 최대 정밀도를 초과하면 수치 오버플로우가 발생하고 함수는 오류를 반환합니다. 이러한 상황을 방지하려면 정밀도가 낮은 데이터 형식을 사용하거나, 혹은 ORDER BY 표현식을 낮은 정밀도로 변환합니다.
예를 들어 DECIMAL 인수가 포함된 SUM 함수는 38자리의 기본 정밀도를 반환합니다. 함수 결과의 비율은 인수 비율과 동일합니다. 따라서 예를 들어 DECIMAL(5,2) 열의 SUM은 DECIMAL(38,2) 데이터 형식을 반환합니다.
다음은 PERCENTILE_CONT 함수의 ORDER BY 절에서 SUM 함수를 사용한 예입니다. PRICEPAID 열의 데이터 형식이 DECIMAL(8,2)이므로 SUM 함수는 DECIMAL(38,2)을 반환합니다.
select salesid, sum(pricepaid), percentile_cont(0.6) within group (order by sum(pricepaid) desc) over() from sales where salesid < 10 group by salesid;
잠재적 정밀도 손실이나 오버플로우 오류를 방지하려면 다음 예와 같이 함수 결과를 정밀도가 낮은 DECIMAL 데이터 형식으로 변환하는 것이 좋습니다.
select salesid, sum(pricepaid), percentile_cont(0.6) within group (order by sum(pricepaid)::decimal(30,2) desc) over() from sales where salesid < 10 group by salesid;
예제
아래 예들에서는 WINSALES 테이블을 사용합니다. 요청 데이터에 대한 설명은 창 함수 예제를 위한 샘플 테이블 섹션을 참조하세요.
select sellerid, qty, percentile_cont(0.5) within group (order by qty) over() as median from winsales; sellerid | qty | median ----------+-----+-------- 1 | 10 | 20.0 1 | 10 | 20.0 3 | 10 | 20.0 4 | 10 | 20.0 3 | 15 | 20.0 2 | 20 | 20.0 3 | 20 | 20.0 2 | 20 | 20.0 3 | 30 | 20.0 1 | 30 | 20.0 4 | 40 | 20.0 (11 rows)
select sellerid, qty, percentile_cont(0.5) within group (order by qty) over(partition by sellerid) as median from winsales; sellerid | qty | median ----------+-----+-------- 2 | 20 | 20.0 2 | 20 | 20.0 4 | 10 | 25.0 4 | 40 | 25.0 1 | 10 | 10.0 1 | 10 | 10.0 1 | 30 | 10.0 3 | 10 | 17.5 3 | 15 | 17.5 3 | 20 | 17.5 3 | 30 | 17.5 (11 rows)
다음은 Washington 주에 거주하는 판매자의 티켓 판매에 대한 PERCENTILE_CONT와 PERCENTILE_DISC를 계산하는 예입니다.
SELECT sellerid, state, sum(qtysold*pricepaid) sales, percentile_cont(0.6) within group (order by sum(qtysold*pricepaid::decimal(14,2) ) desc) over(), percentile_disc(0.6) within group (order by sum(qtysold*pricepaid::decimal(14,2) ) desc) over() from sales s, users u where s.sellerid = u.userid and state = 'WA' and sellerid < 1000 group by sellerid, state; sellerid | state | sales | percentile_cont | percentile_disc ----------+-------+---------+-----------------+----------------- 127 | WA | 6076.00 | 2044.20 | 1531.00 787 | WA | 6035.00 | 2044.20 | 1531.00 381 | WA | 5881.00 | 2044.20 | 1531.00 777 | WA | 2814.00 | 2044.20 | 1531.00 33 | WA | 1531.00 | 2044.20 | 1531.00 800 | WA | 1476.00 | 2044.20 | 1531.00 1 | WA | 1177.00 | 2044.20 | 1531.00 (7 rows)
