Hive函数篇_hive greatest

目录

引言

函数

时间函数

排序函数

字符串操作函数

UDTF函数

UDAF函数

多维分析函数

逻辑判断函数

引言

Hive是基于hadoop的数据仓库工具，能够将一张结构化的数据文件映射为一张数据库表。以便于在Hive中通过类似于传统sql（Hive中我们叫hql）做数据分析等工作。Hive默认计算模型是MapperReduce，将hql转换成MR任务进行计算；在Hive中还有Hive on Spark的模式，这里仅做了解。本文主要是在工作中使用Hive的时候，对一些用到的函数进行记录，以便后续查阅，也供其他同学们参考及互相交流知识。

函数

时间函数

 1、unix_timestamp()、unix_timestamp(STRING date)、unix_timestamp(STRING date,STRING pattern)
      返回本地时间当前时间搓，date默认格式 yyyy-MM-dd hh:mm:ss，可通过pattern设置时间格式。

示例1：
select unix_timestamp();

示例2：
select unix_timestamp('2019-06-11 10:10:10');

示例3：
select unix_timestamp('20190611','yyyyMMdd');

2、from_unixtime(BIGINT unixtime[,STRING format]) 将时间搓秒数转换成UTC时间

示例：
select from_unixtime(unix_timestamp('20190611' ,'yyyymmdd'), 'yyyy-mm-dd');

3、to_date(string date) ：返回时间字符串日期部分

示例：
select to_date("1993-01-01 00:12:12");

 4、datediff(string enddate,string startdate)  返回endDate和startDate相差的天数


初步验证了下，datediff函数只是简单天相减，与小时无关。

排序函数

1、first_value()：取分组排序后，当前行的第一个值。
     常用于：首次消费门店，首次消费类字段
     last_value()：取分组排序后，当前行的最后一个值
     常用于：最近消费门店，最近消费类字段

• 数据准备
  shop_order.txt
  001,耐克,2019-06-01
  002,安踏,2019-06-01
  003,李宁,2019-06-02
  001,阿迪达斯,2019-06-03
  001,李宁,2019-06-05
  002,耐克,2019-06-05
  003,耐克,2019-06-05
  004,乔丹,2019-06-05
• hive建表
  create table shop_order(
      zvip string,
      shopname string,
      orderdate string
  ) row format delimited fields terminated by ',';
  我这里在测试的时候，将orderdate的数据类型设置为date，但在做查询的时候报错：Underlying error: Primitve type DATE not supported in Value Boundary expression；暂把orderdate类型设置为string类型，仅做了个简单的建表。
• 导入数据
  hive -e "load data local inpath 'shop_order.txt' into table shop_order"
• hql执行函数示例

  ---

  执行语句1：
  select zvip, --会员编号
  orderdate,--订单时间
  shopname,--门店名称
  row_number() over(partition by zvip order by orderdate asc), --开窗函数对分组进行排序
  first_value(shopname) over(partition by zvip order by orderdate),-- 首次消费门店，取第一个值
  last_value(shopname) over(partition by zvip order by orderdate) -- 最近消费门店，取最后一个值
  from shop_order;
  结果：
  
  结果分析：可以看到last_value()中的值都不相同，通过查度娘发现last_value()默认统计范围是 rows between unbounded preceding and  current row；对语句进行改造。
  
  执行语句2：
  select zvip, --会员编号
  orderdate,--订单时间
  shopname,--门店名称
  row_number() over(partition by zvip order by orderdate asc), --开窗函数对分组进行排序
  first_value(shopname) over(partition by zvip order by orderdate),-- 首次消费门店，取第一个值
  last_value(shopname) over(partition by zvip order by orderdate rows between unbounded preceding and unbounded following) -- 最近消费门店，取最后一个值
  from shop_order;
  结果：
  
  去掉时间：
  
  结果分析：可以看到添加rows between unbounded preceding and unbounded following后，返回结果接近需要的结果（会员的首次消费门店、最近消费门店），但是此处存在重复记录；可通过group by 语句去重。改造结果如下：
  执行语句3：
  select zvip,fv,lv from(
  select zvip, --会员编号
  orderdate,--订单时间
  shopname,--门店名称
  row_number() over(partition by zvip order by orderdate asc), --开窗函数对分组进行排序
  first_value(shopname) over(partition by zvip order by orderdate) fv,-- 首次消费门店，取第一个值
  last_value(shopname) over(partition by zvip order by orderdate rows between unbounded preceding and unbounded following) lv -- 最近消费门店，取最后一个值
  from shop_order) x
  group by zvip,fv,lv;
  结果：
  
  结果分析：通过group by去重得出预期结果，但其中会生成2个job，执行时间也相对增加了。

字符串操作函数

 1、concat(str1,str2,str3...)：用于将多个函数连接成一个字符串，如果连接中有NULL值，则返回NULL。

示例1：
select concat(zvip,',',shopname) from shop_order;

示例2：

 select concat(zvip,',',shopname,NULL) from shop_order;

2、concat_ws(separator,str1,str2,…) ：concat()的特殊形式，第一个参数是其他参数的分隔符，分隔符的位置放在要连接的两个字符之间。分隔符可以是一个字符串。如果分隔符为NULL，则结果返回NULL。如果分隔字符有NULL值，则返回实际拼接结果。

示例1：

select concat_ws('-',shopname,'offline') from shop_order;

示例2：

select concat_ws('-',shopname,'offline',NULL) from shop_order;

示例3：

select concat_ws(null,shopname,'offline') from shop_order;

3、get_json_object(string jsonstr,String parseAtt) ：用来解析json字符串的一个字段。

示例：

select get_json_object('{"zvip":"001","shopname":"李宁","orderDate":"2019-06-11"}','$.zvip'),
get_json_object('{"zvip":"001","shopname":"李宁","orderDate":"2019-06-11"}','$.shopname'),
get_json_object('{"zvip":"001","shopname":"李宁","orderDate":"2019-06-11"}','$.orderDate');

4、json_tuple(string jsonstr,string str1,string str2,...)：用来解析Json字符串的多个字段。 

示例：

 select b.zvip,b.shopname,b.orderDate from t1 lateral view json_tuple('{"zvip":"001","shopname":"李宁","orderDate":"2019-06-11"}','zvip','shopname','orderDate') b as zvip,shopname,orderDate;

这里仅做了一个json字符串的示例，json_tuple()和get_json_object()这两个函数常用于解析埋点数据上，一般会将json格式的字符串数据存在表中，通过获取表的字段去解析。

 5、正则获取数据regexp_extract
CANKHive 正则匹配函数 regexp_extract - Suckseedeva - 博客园

UDTF函数

1、explode(列表数据)【posexplode(列表数据)】：行转列函数，单独使用的时候仅能返回一列的列表中的数据。

 Lateral View 用于和 UDTF 函数（explode、split）结合来使用。
首先通过 UDTF 函数拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
语法：
LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)
用法1：explode

select 
id,  -- id
tab.serviceId  -- 服务id
from 
(
    SELECT 'a001' as id,'IN02,IN67,IN41' as serviceIds
    union all
    SELECT 'a002' as id,'IN67,IN02' as serviceIds
) aa
    lateral view explode(split(serviceIds,',')) tab as serviceId
limit 100

​

用法2：获取对应列的index位置

select 
id,  -- id
tab.servIndex, -- 对应索引
tab.serviceId  -- 服务id
from 
(
    SELECT 'a001' as id,'IN02,IN67,IN41' as serviceIds
    union all
    SELECT 'a002' as id,'IN67,IN02' as serviceIds
) aa
    lateral view posexplode(split(serviceIds,',')) tab as servIndex,serviceId
limit 100

​
或者两个炸裂函数关联：
​

UDAF函数

1、collect_set()：列转行函数，将相同key，同一列不同value的值转成一行并去重，返回数组格式数据；
      collect_list()：列转行函数，将相同key，同一列不同value的值转成一行不去重，返回数组格式数据；

• 数据准备
  shop_order.txt
  001,耐克,2019-06-01
  002,安踏,2019-06-01
  003,李宁,2019-06-02
  001,阿迪达斯,2019-06-03
  001,李宁,2019-06-05
  002,耐克,2019-06-05
  002,安踏,2019-06-08    (新增一条记录)
  003,耐克,2019-06-05
  004,乔丹,2019-06-05
  【其他步骤同上】
• hql执行函数示例

  ---

  执行语句1：
  select zvip,collect_set(shopname) from shop_order group by zvip;
  结果：
  ​
  结果分析：通过collect_set()是对shopname值进行了去重的。
  执行语句2：
  select zvip,collect_list(shopname) from shop_order group by zvip;
  结果：
  ​
  结果分析：通过collect_list()可知在002会员中shopname的“安踏”出现了两次。如果有开发经验的人可以知道set和list的区别，就很容易区分这两个函数了。

2、相同类型多个字段求最值

在Hive中，可以使用内置函数GREATEST()来求多个字段的数值最大值。以下是使用示例：

SELECT GREATEST(col1, col2, col3) AS max_value
FROM your_table;

其中，col1、col2和col3是要比较的字段，your_table是包含这些字段的表。函数GREATEST()会返回给定字段中的最大值。

如果要求最小值，可以使用内置函数LEAST()。以下是使用示例：

SELECT LEAST(col1, col2, col3) AS min_value
FROM your_table;

以上语句会返回给定字段中的最小值。

多维分析函数

1、with cube多维数据集

1.1 测试数据使用本地模式会节省很多时间。

SET hive.exec.mode.local.auto=true;
SET hive.exec.mode.local.auto.inputbytes.max=50000000;
SET hive.exec.mode.local.auto.input.files.max=5 ;

1.2 、创建测试表

create table tmp.tmp_test(
   f1 string ,
   f2 string,
   f3 string,
   cnt int 
);

1.3 造数

'A','A','B',1
'B','B','A',1
'A','A','A',2
'A','B','A',2

INSERT OVERWRITE table tmp.tmp_test
select
"A",
"A",
"B",
1
UNION ALL
select
"B",
"B",
"A",
1
UNION ALL
select
"A",
"A",
"A",
2
UNION ALL
select
"A",
"B",
"A",
2;

1.4 测试with_cube

select 
f1,
f2,
f3,
sum(cnt),
grouping__id,
rpad(reverse(bin(cast(grouping__id AS bigint))),3,'0')
from tmp.tmp_test
group by f1,
f2,
f3 with cube;

结果数据：

​

1.5 多维数据集

CUBE 运算符可用于生成 n 维的多维数据集，即具有任意数目维度的多维数据集。只有一个维度的多维数据集可用于生成合计，例如：

select 
nvl(f1,'all') f1,
sum(cnt)
from tmp.tmp_test
group by f1 with cube;

结果数据：

​

1、 with rollup

select 
f1,
f2,
f3,
sum(cnt),
grouping__id,
rpad(reverse(bin(cast(grouping__id AS bigint))),3,'0')
from tmp.tmp_test
group by f1,
f2,
f3 with rollup;

数据结果：

​

2、 grouping sets

select 
f1,
f2,
f3,
sum(cnt),
grouping__id,
rpad(reverse(bin(cast(grouping__id AS bigint))),3,'0')
from tmp.tmp_test
group by f1,
f2,
f3 grouping sets((f1),(f1,f2));

数据结果：

hivesql和sparksql执行引擎运行结果还会不一样，如下图

HiveSQL：按grouping_id的二进制进行反转。grouping_id算法是有值的为1，空值为0，再反转。

​

SparkSQL：grouping_id算法是有值的为0，空值为1

​

总结

1、cube的分组组合最全，是各个维度值得笛卡尔（包含null）组合。

2、rollup的各维度组合应满足：前一维度为null则后一位维度必须为null；前一维度取非null时，下一维度随意。

3、grouping sets则为自定义维度，根据需要分组即可。通过grouping sets的使用可以简化SQL，比group by 单维度进行union性能更好。

3、left semi join与inner join区别

LEFT SEMI JOIN (左半连接) 是IN/EXISTS子查询的一种更高效的实现。

示例：

select A.key,A.value from A where A.key in (select B.key from B) 

等同于：

select A.key,A.value FROM A left semi join B on (A.key = B.key)

left semi join 与 inner join 代码关联示例 ：

​​

 特点：

1、left semi join 是只传递表的join key给map阶段，所以最终的select结果数据只有左表数据。

2、left semi join是in(keySet)的关系，遇到右表重复记录会跳过，而inner join会发散。所以在右表有重复key的情况下，left semi join只产生一条，而inner join 会产生多条。17、炸裂函数laterial view

逻辑函数

1、CAST(<expr> as <type>) 数据类型转换
      cast (null as string)

2、 COALESCE(T v1, T v2, …)  ：返回第一个非NULL值，如果全部为NULL，则返回NULL。  
​
窗口函数

1、sum() over(order by row between n preceding and current row)

over(
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)

<PARTITION BY clause> ::=  PARTITION BY value_expression , ... [ n ]  
<ORDER BY clause> ::=  ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ]  
<ROW or RANGE clause> ::=  { ROWS | RANGE } <window frame extent>  
<window frame extent> ::=  { <window frame preceding>  | <window frame between> }  
<window frame between> ::=  BETWEEN <window frame bound> AND <window frame bound>  
<window frame bound> ::=  { <window frame preceding> | <window frame following> }  
<window frame preceding> ::= { UNBOUNDED PRECEDING | <unsigned_value_specification> PRECEDING | CURRENT ROW }  
<window frame following> ::= { UNBOUNDED FOLLOWING | <unsigned_value_specification> FOLLOWING | CURRENT ROW }  
<unsigned value specification> ::= { <unsigned integer literal> }

参考：
Hive中使用sum over()实现累积求和和滑动求和_xuehuagongzi000的博客-CSDN博客_hive 求和
SQL Server 开窗函数over()选项RANGE/ROWS详解 - 五维思考 - 博客园