在应用hive在数据仓库中进行数据提取中经常会用户很多函数,为了记录自己常用的hive函数,特写这个系列的文章,记录下自己的使用心得,其中包括使用场景、用法等。
1、concat
concat是字符拼接函数的一种,在实际的数据提取中也是常用的,其主要用于将输出的字段与其他字符拼接在一起,比如数据仓库中的某字段金额存储的只是数字,你想要在后面加上对应的单位,这个时候concat函数就派上用途了。
用法:
concat(string s1,string s2,string s3)
注意:输入必须为字符串类型,如果是其他的数据类型需要应用cast(value as string)函数将相应的字段转换为字符类型。
例子:
select concat(money,'元')from table1;
2、concat_ws
既然有concat函数为什么又有concat_ws呢?其实concat_ws是针对concat中一种特殊情形引入的,就是如果想要输出的字段用相同的字符进行分隔,应用concat函数就显得很笨重,你需要这样写concat(s1,sep_str,s2,sep_str,s3.......),这样的sql显得臃肿重复,这个时候你就需要用concat_ws函数了。concat_ws使用场景:输出字段用相同字符分隔的情形。
用法:
concat_ws(sep_str,s1,s2,s3,.......)
注意:字符拼接函数的输入一般数字符类型,如果不是就需要转换为字符类型。
例子:
select concat_ws('|',name,age,sex,grade,telno) from table2;
3、row_number
大家一般都听说过分组的概念,但是你听说过分组排序的概念吗?
具体实例常用:
每个用户可能在一天中产生多条记录,想要看每个用户每天的第一条、第二条和最后一条记录。
这个实例如果你想用单纯的分组很难实现,这里就需要用到分组排序的思想,首先按照用户id字段进行分组,再按照时间字段进行排序,并得到一个新的字段,这个字段就是分组排序的结果,你想要的结果就是包含在这个结果中。
用法:
row_number() over(partition by 字段1 order by s3 字段2 asc)
例子:
SELECT *,row_number() over(partition BY idORDER BY in_times DESC) AS numFROM table3;
table3表的数据是这样的:
应用上面的分组排序的结果是这样的:
新增加了一列num,这列是分组排序的结果。
如果你想要第一条记录,就用升序排列,取第一条记录;
如果你想要最后一天记录,就用降序排序取第一条;
如果你想取指定的记录,前几条记录用升序,后几条记录用降序。
取每个用户每天的第一条记录
SELECT id,in_timesFROM(SELECT *,row_number() over(partition BY idORDER BY in_times ASC) AS numFROM table3)aWHERE a.num=1;
取每个用户的最后一条记录
SELECT id,in_timesFROM(SELECT *,row_number() over(partition BY idORDER BY in_times DESC) AS numFROM table3)aWHERE a.num=1;
取每个用户的前五条记录中的第五条
SELECT id,in_timesFROM(SELECT *,row_number() over(partition BY idORDER BY in_times ASC) AS numFROM table3)aWHERE a.num=5;
如果喜欢我,欢迎关注微信公众号:洞察数据挖掘价值。