Hive : percentile (백분률 구하기, 백분위 구하기) / 백분위로 중간값(median) 구하기

 

 

 

데이터를 다루다 보면 전체에 대한 백분위를 구할 때가 있습니다.

 

이럴 때에는 percentile이라는 유용한 함수를 사용할 수 있습니다.

 

 

Syntax

percentile(column, percent)

 

- column

백분위를 구할 데이터가 있는 대상 column

 

- percent

상위 몇%를 의미

 

 

 

 

 

 

 

Table = test_table

col1	col2
a	1
a	2
a	3
b	1
b	6
b	7
c	1
c	2
d	2
d	2
d	4
d	1

 

위같은 table이 있다고 가정합시다.

 

 

select  col1
        , percentile(col2, 0.5) as result_col
from test_table
--
group by col1
;

 

col1	result_col
a	2
b	6
c	1.5
d	2

 

쿼리와 결과입니다.

 

결과에 대한 해석이 필요합니다.

 

select  col1
        , percentile(col2, 0.5) as result_col
from test_table
--
group by col1
;

 

일단 쿼리를 보면 위와 같습니다.

col2를 기준으로 percentile() 함수를 적용했습니다.

 

percentile() 함수를 보면 col2, 0.5가 parameter로서 주어졌는데 col1에 존재하는 값 별로 col2에 있는 값 기준 50%에 있는 값을 구하라는 것입니다.

 

col1	col2
a	1
a	2
a	3

 

col1	result_col
a	2

 

 

먼저 col1 = a인 값들만 보겠습니다.

col1 = a인 값들에 대해 col2값은 1, 2, 3 총 3개가 있습니다. 이 중 상위 50%의 값은 2입니다.

 

 

 

 

 

col1	col2
b	1
b	6
b	7

 

col1	result_col
b	6

 

col1 = b인 값입니다.

col1 = b인 행에 대해 col2에는 1, 6, 7이라는 데이터가 있습니다.

이 중 50% 위치에 있는 값은 바로 중간값 6입니다.

 

 

 

 

col1	col2
c	1
c	2

 

col1	result_col
c	1.5

 

col1 = c인 값에 대해서는 col2 = 1, 2 가 있습니다.

이 경우 50% 위치에 있는 값이 없습니다.

따라서 1과 2의 사이인 1.5가 됩니다.

 

 

 

 

 

 

col1	col2
d	2
d	2
d	4
d	1

 

col1	result_col
d	2

 

col1 = d인 행에 대해서는 col2 = 1, 2, 2, 4 가 있습니다.

중복은 무시하고 여기서 50% 위치에 있는 값은 2입니다.

따라서 2가 return됩니다.

 

 

 

 

 

 

 

with
temp_median as (
    select  col1
            , percentile(col2, 0.5) as median
    from test_table
)
--
select  tt.*
        , m.median
from test_table as tt
--
left join temp_median as m
    on m.col1 = tt.col1
;

 

이를 이용하면 위처럼 원본 데이터에 중간값을 구하여 하나의 컬럼으로 만들 수도 있습니다.