SQL-错题集锦

多行转多列-字符串拼接 -字符串拼接

set hive.exec.mode.local.auto = true;

/*
同一部门会有多个绩效，求多行转多列结果
由
2014 B 9
2015 A 8
2014 A 10
2015 B 7
2014 B 6
到
a col_A col_B
2014 10 6,9
2015 8 7

*/
//建表
create table if not exists text.multi_row
(
dat string,
name string,
performance double
)row format delimited fields terminated by "\t"
;
//思路
load data local inpath "/text_re/resour.txt" into table text.multi_row;

insert into text.multi_row values
(2014,'B',9),
(2015,"A",8),
(2014,'A',10),
(2015,'B',7),
(2014,'B',6);
/*
思路 先按照 年份和name 进行分组,分组的内容 使用 collect_list 将绩效多行转为一列 存放的内容是数组
然后 使用case when 对 name 内容进行判别
1_ collect_list将多行转为一列 但是是 double类型的数组 hive中存储没有数组类型
2.concat_ws 只能对字符串类型进行拼接 将 数组内的double类型转换
3.出现了新零售的问题 一列数据中有null 和字符串
思路 group by 后 使用max 函数 !!
*/

with tmp as ( SELECT dat,name , concat_ws(",", COLLECT_LIST(cast(performance as string) ) ) PER
FROM text.multi_row GROUP BY dat,name)
select dat,
max(case when name ='A' then PER end ) as co1,
max(case when name ='B' then PER end ) as co2
from tmp group by dat;

left join 时 where 和 on的区别

image.png

A 十行

• 在使用外连接的时候,hive 默认开启谓词下推,会先执行where中的过滤条件,on后边的过滤条件不一定生效,如果不写where 在读取的时候,默认不过滤 全读取

• 左连接 left join ,右连接 right join
  左边的过滤条件放在on后边不生效,结果中还会有,但不参与join

列: a left join b on a.id = b.id and a.id ! = 1  结果集中还有 id = 1

• 只有过滤条件放在where 后边才生效
  实际开发中先进行where过滤,然后再进行join 关联

hive sql 执行顺序

image.png

c
这条语句有两个mr 程序

MapReduce	sql	备注
input	from
map	where[select] \join
shuffle	group by\order by
reduce	having[select]\limit\join

HIVE底层MR. select 可以发生再Map端也可以发生再reduce端
JOIN 可以发生在map端 也可以发生在Reduce端