接最近写HIVE SQL的一点笔记【1】

多行数据字段连接成一个字段的方法，需配合group by 使用

select concat_ws(',',collect_list(oid)) as oid_list,tid 
from HIVE_TABLE_NAME_XXXX 
group by tid

执行结果效果如下

使用`collect_list`获取相同tid行的oid字段，并使用 `concat_ws` 函数拼接这个集合，中间用此处指定的字符串“，”逗号拼接。

collect_list会忽略集合中null值，如果集合内的所有值均为null，那么此时结果是一个空数组。

如果要对结果去重，可以使用collect_set替换collect_list方法

再有就是，只有group by的字段可以直接在select后面使用，其他字段可以使用MAX函数对字段取值，这里和Mysql的 any_value函数有点相似。

如果我们需要得到结果是一个JSON字符串，同样的可以这样操作

select concat('[',concat_ws(',',collect_list(info)),']') as order_json,tid 
from
	(
      select tid,concat('{"tid":',tid,',"oid":',oid,'}') as info
		from HIVE_TABLE_NAME_XXXX 
	)
group by tid

可以得到结果如下，order_json即为所需的json字符串

若需要对同一个tid内的oid按照指定顺序排序，则需要借助

DISTRIBUTE BY  column1 SORT BY column1,column2 desc

语句，我们试下改写下上面的sql

select concat('[',concat_ws(',',collect_list(info)),']') as order_json,tid 
from
	(
      select tid,concat('{"tid":',tid,',"oid":',oid,'}') as info
		from HIVE_TABLE_NAME_XXXX 
      DISTRIBUTE BY tid SORT BY tid,oid DESC
	)
group by tid

查询结果如下

可以看到确实按照oid降序排序了，这里DISTRIBUTE BY的作用是按照指定的字段分区，并根据后面的SORT BY排序

可以参见https://blog.csdn.net/fantasticqiang/article/details/80769316

如果排序是升序的话，可以用cluster by语句替代

1．DISTRIBUTE BY的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。

2．Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。