一、group by操作后将其他字段串接
select id,concat_ws('|', collect_set(name)) from tmp_test group by id;
collect_set 是 Hive 内置的一个聚合函数, 它返回一个消除了重复元素的对象集合, 其返回值类型是 array，HIve group by操作后将其它字段拼接，mysql中使用的是group_concat(字段，‘分隔符’)
//第一种是正确用法
select id,group_concat(字段 separator ';') from table_name group by id;
//该种方法放回的分隔符会带上,分隔符，最终分隔符是;
select id,group_concat(字段,';') from aa group by id;
--- 默认的分隔符是","
select id,group_concat(name separator ';') from table_name group by id;
--- 如果要对name去重
select id,group_concat(distinct name) from table_name group by id;
--- 如果还想对name排序
select id,group_concat(name order by name desc) from table_name group by id;
presto中使用的是array_distinct或者array_join(array_agg(字段), ‘,’)；
array_join(array_agg(name), ',', 'null') as names;
二、group by 操作时ParseException line 7:22 missing ) at ‘,’ near ‘’
HIve自身的bug，需要在每个子查询后面加上别名并且 group by 后面多个字段时，第一个字段不可以在表名，建议去掉括号；
select a,b,c
from (select a,b,c
from (select a,b,c
from table) a
where no = 1) b
group by a;
SELECT table1.a,
b, d
SUM(tab1.c)
FROM table1 join table2 on tab1.a = tab2.b
GROUP BY table1.a,b, d
GROUPING SETS ((b, tab1.a, d))
三、hive和presto的一些对比
处理json数据时，presto用的是json_extract_scalar，hive用的是get_json_object
二者的取交集都是intersect；
四、hive和presto的转换时间戳对比
标准时间格式——时间戳
hive：select unix_timestamp(cast (‘2017-08-30 10:36:15’ as timestamp));
presto：select to_unixtime(cast (‘2017-08-30 10:36:15’ as timestamp));
时间戳——标准时间格式
presto：select format_datetime(from_unixtime(1510284058),‘yyyy-MM-dd HH:mm:ss’);
hive：select from_unixtime(1323308943123,‘yyyy-MM-dd HH:mm:ss’);
其中会遇到将字符串的时间戳转换为数值类型，hive和presto中都提供了cast转换，cast(value as type) type显式转换一个值的类型。可以将varchar类型的值转为数字类型，反过来转换也可以。 try_cast(value as type) type与cast类似，不过，如果转换失败会返回null，这个只有presto有，另外需要注意的是，hive中的int类型是就是int，而presto中是包装类型Integer，如果cast的type写错也会报错；
五、hive随机取数
distribute by rand()sort by rand() limit 100;
六、hive中的like语句无法匹配下划线
1、当无法匹配下划线的字段时，应该使用“`”对字段进行引用；
2、无法匹配like子句内容时，此时需要对like后面的字符串添加转义字符，比如like ‘%_abc_%’，于是sql判断的是包含_abc_内容的数据；
七、海量数据取差集
比较优化的方法是类似如下，取join之后，取右表中的值为空的结果数据，速度提升N倍
select count(*)
from a
left join b on a.id=b.id
where a.is_del=1
and b.id is null
;
八、truncate partition、drop partition 区别
功能：两者都用于删除数据，即将对应的partition的数据文件删除；
不同点：
truncate partition 只删除数据文件，保存在mysql中的metadata不会被删除；
drop partition 只删除数据文件且删除在mysql中的metadata；
TRUNCATE TABLE table_name PARTITION (dt='2022-05-01');
ALTER TABLE table_name DROP PARTITION (partition_date='2022-05-01');
九、hive中去重，并保留唯一值
使用row_number()函数：
row_number()over (partition by id order by time desc) num 取num=1 的
意思是先根据id进行分组，并在分组内部按time降序排序，row_number()函数计算的值就表示某个id组内部排序后的顺序编号（该编号在一个组内是连续并且唯一的) ，所以最后直接取每个分组内的第一个（num=1）即可；
十、identifiers must not start with a digit； surround the identifier with double quotes
或许你尝试了很多遍也没有解决这个问题，甚至没有看懂这句话，没关系，现在你懂了，因为你在命名的时候用的是类似“7days“这样数字开头英文结尾的，sql里面可以别名里含有下划线和数字，但一定不能用数字开头取别名，改个名字就好。
十一、sql做left join 操作时，on两边都是null值时，所有数据被过滤
当做join操作时，如果出现null值，可能会导致没有数据，此时需要将null字段设置为默认值
比如 presto中coalesce(字段,‘null’)。
十二、hive中的行转列和列转行
列转行用 lateral view explode：
select id,name,project_ids
from table_name a
lateral view explode(split(a.project_id,',')) b AS project_ids
;
行转列用以下函数：
concat(string1,string,...) //连接括号内字符串，数量不限。
concat_ws(separator,string1,string2,...) //连接括号内字符串，数量不限，连接符为separator。
collect_set(col) //此函数只接受基本类型，主要是将字段的值进行去重汇总，产生array类型字段。
