hive 列转行与行专列

1. 行转列

1.1 现状

SQL SERVER 2005 提供了行转列方法pivot()，以及列转行unpivot()方法；

但hive 里面没有自带pivot函数，以下为自己实现：

实现将原始表

转为目标表

1.2 简单实现

select name,
sum(if(course='math', score, null)) as math,
sum(if(course='english', score, null)) as english,
sum(if(course='chinese', score, null)) as chinese
from table_name
group by name

1.3 按日期对group by 字段 整合成一行

select a.dt,
cnt1, concat(round(cnt1/total_cnt*100,2), '%') as rate1,
cnt2, concat(round(cnt2/total_cnt*100,2), '%') as rate2
from (
    select dt,
    sum(if(code='101', cnt, null)) as cnt1,
    sum(if(code='102', cnt, null)) as cnt2
    from (
        select dt, code, count(1) as cnt
        from test.table_1
        group by dt, code
    ) a1
    group by dt
) a
left join (
    select dt, count(1) as total_cnt
    from test.table_1
    group by dt
) b
on a.dt=b.dt;