하이브(hive) sql - 행(row)을 기준으로 최대값, 최소값 추출하는 방법!

SQL을 이용하면 보통 CREATE, SELECT, GROUP BY, INSERT, DROP 등의 문법을 많이 사용한다. 하지만 이런 문법들만으로는 어느 순간 한계에 부딪히게 된다. 이런 난해한 경우를 해결하기 위해 SQL에는 여러가지 함수들이 있다.

오늘은 하이브 SQL에서 여러 칼럼의 값 중에 최대값과 최소값을 가져오는 방법을 알아보도록 하겠다.

 

 

하이브 최대, 최소값

 

 

한 칼럼의 최대값을 가져오기 위해서는 MAX, 최소값을 가져오기 위해서는 MIN을 사용한다. 그런데 여러 칼럼을 비교하여 그 중에 최대인 값을 가져오려면 어떻게 해야 할까. 단순하게는 CASE,WHEN을 이용해도 될 것 같다.

 

SQL에는 이러한 작업을 편리하게 처리할 수 있는 함수가 있다. 바로 greatest와 least이다.

 

 

사용방법은 간단하다. greatest하고 함수 안에 칼럼값들을 넣어주면 된다. 그러면 그 중에서 가장 큰 값을 가져온다. 반대로 least 함수를 이용하면 함수 안에 여러 칼럼값들 중에서 가장 작은 값을 가져온다.

 

 

실제 예제를 살펴보자.

아래와 같은 테이블이 있다고 가정해보자.

grp1	grp2	grp3	var1	var2	var3
A	Z	가	1	6	9
B	Y	나	3	5	-1
C	X	다	9	4	2
A	X	가	3	1	-3
B	Y	나	2	2	1
C	X	다	4	3	0

 

 

가장 큰 칼럼의 값을 추출하기 위해 gratest를 사용했는데, 에러가 발생한다.

 

SELECT GREATEST(VAR1, VAR2, VAR3) AS VAR_GR 
FROM TEMP.TEST

 

위와 같이 사용하면 "Bad status for request TFetchResultReq(fetchType=0 ~ " 이라는 알 수 없는 에러가 발생한다. 더 확인해보니 GREATEST 함수 안에 변수타입을 지정해야줘야 한다고 한다. 이미 INT타입의 칼럼들인데 왜 지정해 줘야 하는지는 알 수 없지만. 아래와 같이 직접 지정해주고 나니 에러가 발생하지는 않는다.

 

SELECT GREATEST( INT(VAR1), INT(VAR2), INT(VAR3) ) AS VAR_GR, LEAST( INT(VAR1), INT(VAR2), INT(VAR3) ) AS VAR_LS
FROM TEMP.TEST

위와 같이 하면 아래와 같이 칼럼등 중에 최대값과 최소값을 추출할 수 있다.

VAR_GR	VAR_LS
9	1
5	-1
9	3
3	-3
2	1
4	0

 

 

혹시 문자열을 비교하고 싶다면 INT대신에  STRING함수를 사용하면 된다.

 

오늘은 이렇게 하이브 SQL에서 여러 칼럼값 중 최대값과 최소값을 가져오는 방법에 대해서 알아보았다. 함수를 알면 간단하게 할 수 있는 작업도 모르면 어려워보인다. greatest와 least함수를 통해 손쉽게 할 수 있었다.