1.distinct 用于返回唯一不同的值。 去重
1.1.作用于单列
select distinct name from A
1.2作用于多列
select distinct name, id from A
实际上是根据name和id两个字段来去重的,这种方式Access和SQL Server同时支持。
2.collect_list和collect_set 它们都是将分组中的某列转为一个数组返回,不同的是collect_list不去重而collect_set去重。
把一列数据转换成一行数据
select username, collect_list(video_name) from t_visit_video group by username ;
3.explode
lateral view用于和split、explode等UDTF一起使用的,能将一行数据拆分成多行数据,在此基础上可以对拆分的数据进行聚合,
lateral view首先为原始表的每行调用UDTF,UDTF会把一行拆分成一行或者多行,lateral view再把结果组合,产生一个支持别名表的虚拟表。
其中explode还可以用于将hive一列中复杂的array或者map结构拆分成多行
SELECT explode(children) AS myChild FROM t3;
SELECT explode(address) AS (myMapKey, myMapValue) FROM t3;
使用explode拆分Array
select explode(split(goods_id,',')) as goods_id from explode_lateral_view;
使用explode拆解Map
select explode(split(area,',')) as area from explode_lateral_view;
配合LATERAL VIEW使用
select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,','))goods as goods_id2;
Lateral View通常和UDTF一起出现,为了解决UDTF不允许在select字段的问题。
Multiple Lateral View可以实现类似笛卡尔乘积。
Outer关键字可以把不输出的UDTF的空结果,输出成NULL,防止丢失数据。
CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果,支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...):它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
reflect函数可以支持在sql中调用java中的自带函数,秒杀一切udf函数。
使用java.lang.Math当中的Max求两列当中的最大值
select reflect("java.lang.Math","max",col1,col2) from test_udf;
判断是否为数字
使用apache commons中的函数,commons下的jar已经包含在hadoop的classpath中,所以可以直接使用。
select reflect("org.apache.commons.lang.math.NumberUtils","isNumber","123");
Row_number函数
语法:row_number() over(parations by order by )
row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开时排序).
rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内).
dense_rank()是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 .
版权说明
文章采用: 《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》许可协议授权。版权声明:未标注转载均为本站原创,转载时请以链接形式注明文章出处。如有侵权、不妥之处,请联系站长删除。敬请谅解!