赞
踩
SQL SERVER 2005 提供了行转列方法pivot(),以及列转行unpivot()方法;
但hive 里面没有自带pivot函数,以下为自己实现:
实现将原始表
转为目标表
- 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
- 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;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。