[HIVE SQL] 코딩테스트 준비 1

-  사칙 연산시 NULL값 주의하기

- 또는 비율을 계산할 때, 정수로 나누거나 0으로 나누는 등의 실수를 할 수 있다. 

NULL은 문자와 결합해도, 숫자와 사칙연산을 해도 NULL이 된다. 따라서 원하는 결과 형태에 따라 처리 후 가공해야 한다.

예를 들면, (금액 - 할인금액) = 만 원-NULL = NULL 이러면 안되는 거잖아!

따라서 만 원 - COALESCE(할인금액, 0) = 만 원 이렇게 처리해야 한다.

또는 NULLIF 를 사용한다.

계산한 값이 소수점을 포함하려면 100.0 * 컬럼 이런 식으로 사용하자

 

- SIGN 함수는 매개변수가 양수면 1, 0이라면 0, 음수라면 -1을 리턴한다.

평균을 낼 때, NULL값을 같이 카운트할지, 빼고 카운트할지에 따라 활용하면 된다.

 

- 거리 계산

제곱은 POWER, 제곱근은 SQRT ,ABS 절댓값 → sqrt(power(x1-x2,2)) as rms

 

- 날짜, 시간 계산 함수

to_date() 타임스탬프 문자열을 날짜로 변환할 때

date_add(to_date(), 1) : after 1 day

add_months(to_date(),-1) : before 1 month

datediff( , ) : 두 날짜의 차이를 계산

 

- 문자열 다루기

split함수로 문자열을 분해하고 n번쨰 요소 추출하기

이때 피리어드(점)가 특수 문자이므로 역 슬래시로 이스케이프 처리해야 함

ip : 192.168.0.1 → cast(split( ip, '\\.')[0] as int) : 192

 

- 집약 함수를 적용한 값과 집약 전의 값을 동시에 다루기

윈도우 함수에서 over 구문에 매개변수를 지정하지 않으면 테이블 전체에 집약함수를 적용한 값이 리턴된다. 

partition by (컬럼) 을 지정하면 해당 컬럼 값을 기반으로 그룹화하고 집약 함수를 적용한다.

따라서 원래 레코드를 건드리지 않고 추가할 수 있다.

 

select user_id, product_id

, score : 개별 리뷰 점수

, avg(score) over() as avg_score  : 전체 평균 리뷰 점수

, avg(score) over(partition by user_id) as user_avg_score : 사용자의 평균 리뷰 점수

, score - avg(score) over(partition by user_id) as user_avg_score_diff : 개별 리뷰 점수와 사용자 평균 리뷰 점수 차이

 

- LAG, LEAD

lag(product_id, 2) over(order by score desc) : 현재 행보다 앞에 있는 행의 값 추출하기

lead(product_id, 2) over(order by score desc) : 현재 행보다 뒤에 있는 행의 값 추출하기

 

- FIRST_VALUE, LAST_VALUE 순위가 높은, 낮은 상품 id 찾기

order by 구문과  sum/avg 등의 집약함수를 조합하면, 집약 함수의 적용 범위를 유연하게 지정할 수 있음.

order by 구문에 이어지는 rows 구문은 이후에 설명할 윈도우 프레임 지정 구문임

 

select product_id, score

, row_number() over(order by score desc) as row

, sum(score) over(order by score desc rows between unbounded preceding and current row) as cum_score : 순위 상위에서 현재 행까지의 스코어를 모두 더한 값

, avg(score) over(order by score desc rows between 1 preceding and 1 following) as local_avg : 현재 행과 앞뒤의 행 하나씩, 전체 3개 행의 평균 스코어를 계산한 값

, first_value(product_id) over(order by score desc rows between unbounded preceding and unbounded following) as first_value : 윈도우 내부의 가장 첫 번째 레코드 추출

, last_value(product_id) over(order by score desc rows between unbounded preceding and unbounded following) as last_value : 윈도우 내부의 가장 마지막 레코드 추출

 

위처럼 윈도우 프레임 지정하는 법

가장 기본 : ROWS BETWEEN start AND end

start / end : CURRENT ROW(현재의 행), n PRECEDING(n행 앞), n FOLLOWING(n행 뒤), UNBOUNDED PRECEDING(이후 행 전부) 등

 

- 윈도우 프레임 지정별 상품ID 집약 배열하는 쿼리

select product_id

, row_number()  over(order by score desc) as row

, collect_list(product_id) over(order by score desc rows between unbounded preceding and unbounded following)

 : 가장 앞 순위부터 가장 뒷 순위까지의 번위를 대상으로 상품 ID 집약

, collect_list(product_id) over(order by score desc rows between unbounded preceding and current row)

 : 가장 앞 순위부터 현재 순위까지의 범위를 대상으로 상품 ID 집약

, collect_list(product_id) over(order by score desc rows between 1 preceding and 1 following)

 : 순위 하나 앞과 하나 뒤까지의 범위를 대상으로 상품 ID 집약