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

- 행을 집약해서 쉼표로 구분된 문자열 변환하기

(최종 출력에서 데이터를 열로 전개해야 가독성이 높은 경우가 많음, 직감적으로 이해가능)

collect_list를 사용해서 하나로 묶고 concat_ws를 사용해서 구분자로 구분하기

concat_ws(',', collect_list(product_id)) 

 

- 가로 기반 데이터를 세로 기반으로 변환하기 (p110)

우선 가로 기반 데이터의 수가 고정되어 있으면, 즉 행으로 전개할 데이터의 수가 고정되었다면, 데이터 수와 같은 수의 일련 번호를 가진 피벗 테이블을 만들고 CROSS JOIN 하기

select q.year

, case when p.idx = 1 then 'q1'

          when p.idx = 2 then 'q2'

          when p.idx = 3 then 'q3'

          when p.idx = 4 then 'q4' end as quarter

, case when p.idx = 1 then q.q1

          when p.idx = 2 then q.q2

          when p.idx = 3 then q.q3

          when p.idx = 4 then q.q4 end as sales

from quarterly_sales as q

cross join (

               --행으로 전개하고 싶은 열의 수만큼 순번 테이블 만들기

                                  select 1 as idx

                   union all select 2 as idx

                   union all select 3 as idx

                   union all select 4 as idx

                ) as p

 

- 임의의 길이를 가진 배열을 행으로 전개하려면?

select purchase_id, product_id

from purchase_log as p

LATERAL VIEW explode(split(product_ids, ',')) as product_id

 

- 날짜별 매출과 당월 누계 매출을 집계하는 쿼리 (p147)

select dt

, substring(dt,1,7) as year_month

, sum(purchase_amount) as total_amount

, sum(sum(purchase_amount)) over(partition by substring(dt,1,7) order by dt rows unbounded preceding) as agg_amount

from purchase_log

group by 1,2

order by 1,2

 

 

- NTILE

ntile(10) over(order by purchase_amount desc) as decile

 

- 키가 중복되는 레코드 한번에 확인하기

select id, count(*) as record_num

, concat_ws(',', collect_list(name)) as name_list

from 테이블 이름

group by 1

having count(*) > 1

또는 

count(1) over(partition by id) as key_num

   : id별 중복을 카운트 하여 key_num > 1인 레코드 찾기 (그럼 원래 레코드 형식 그대로 출력할 수 있음)