Hive系统函数

https://www.ibm.com/support/knowledgecenter/SSPT3X_4.2.0/com.ibm.swg.im.infosphere.biginsights.admin.doc/doc/admin_HA_HiveS2.html
https://cwiki.apache.org/confluence/display/Hive/Hive+on+Spark%3A+Getting+Started

1 Hive系统函数

• 1.1 Hive concat 函数

concat 函数在连接字符串的时候，只要其中一个是 NULL，那么将返回 NULL

hive> select concat('a','b');
ab
hive> select concat('a','b',null);
NULL

• 1.2 concat_ws 函数
  concat_ws函数在连接字符串的时候，只要有一个字符串不是NULL，就不会返回NULL。
  concat_ws 函数需要指定分隔符。

hive> select concat_ws('-','a','b');
a-b
hive> select concat_ws('-','a','b',null);
a-b
ive> select concat_ws('','a','b',null);
ab

• 1.4 collect_set 函数
• 1.4.1 创建原数据表

hive >
drop table if exists stud;
create table stud (name string, area string, course string, score
int);

• 1.4.2 向原数据表中插入数据

hive>
insert into table stud values('zhang3','bj','math',88);
insert into table stud values('li4','bj','math',99);
insert into table stud values('wang5','sh','chinese',92);
insert into table stud values('zhao6','sh','chinese',54);
insert into table stud values('tian7','bj','chinese',91);

• 1.4.3 查询表中数据

hive > select * from stud;
stud.name stud.area stud.course stud.score
zhang3 bj math 88
li4 bj math 99
wang5 sh chinese 92
zhao6 sh chinese 54
tian7 bj chinese 91

• 1.4.4 把同一分组的不同行的数据聚合成一个集合

hive > select course, collect_set(area), avg(score) from
stud group by course;
chinese ["sh","bj"] 79.0
math ["bj"] 93.5

• 1.4.5 用下标可以取某一个

hive > select course, collect_set(area)[0], avg(score) from
stud group by course;
chinese sh 79.0
math bj 93.5

• 1.5 STR_TO_MAP 函数
  （1）语法描述
  STR_TO_MAP(VARCHAR text, VARCHAR listDelimiter, VARCHAR keyValueDelimiter)
  （2）功能描述
  使用 listDelimiter 将 text 分隔成 K-V 对，然后使用 keyValueDelimiter 分隔每个 K-V 对，
  组装成 MAP 返回。默认 listDelimiter 为（ ，），keyValueDelimiter 为（=）。
  （3）案例

str_to_map('1001=2020-06-14,1002=2020-06-14', ',' , '=')
输出
{"1001":"2020-06-14","1002":"2020-06-14"}

• 1.6 nvl 函数
  1）基本语法
  NVL（表达式 1，表达式 2）
  如果表达式 1 为空值，NVL 返回值为表达式 2 的值，否则返回表达式 1 的值。
  该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、
  字符型和日期型。但是表达式 1 和表达式 2 的数据类型必须为同一个类型

hive > select nvl(1,0);
1
hive > select nvl(null,"hello");
hello

• 1.7 coalesce 函数

COALESCE(expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。

hive> select coalesce(null,null,3);
3

select coalesce('','1')  -- ''   ,'' 不是null

2 日期处理函数

• 2.1 date_format 函数（根据格式整理日期）

hive (gmall)> select date_format('2020-06-14','yyyy-MM');
2020-06

• 2.2 date_add 函数（加减日期）

hive> select date_add('2020-06-14',-1);
2020-06-13
hive > select date_add('2020-06-14',1);
2020-06-15

• 2.3 next_day 函数
  （1）取当前天的下一个周一

hive > select next_day('2020-06-14','MO');
2020-06-15

说明：星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）
（2）取当前周的周一

hive > select date_add(next_day('2020-06-14','MO'),-7);
2020-06-8

（2）取当前周的周日

hive> select date_add(next_day('2020-06-14','MO'),-1);
2020-06-14

• 2.4 last_day 函数（求当月最后一天日期）

hive > select last_day('2020-06-14');
2020-06-30

• 2.5 date_sub函数 和trunc函数（求当月月初时间）

 方法一 hive > select trunc('2020-06-14','MM');
2020-06-01

方法二： hive> select date_sub('2020-06-14',dayofmonth('2020-06-14')-1);
2020-06-01

求上个月月初时间

hive > 
select add_months( date_sub('2020-08-14',dayofmonth('2020-08-14')-1),-1);
2020-07-01

• 2.6 add_months函数（获取上个月的今天）

hive> select add_months('2020-06-14',-1);
2020-05-14

• 2.7 datediff函数 日期之间的差值天数

hive> select datediff('2020-06-18','2020-06-10');
8

对于扫描整个月的数据可以采用如下简便方案：

date_format(login_last,'yyyy-MM')=date_format('2020-06-10','yyyy-MM')

对于分组之后再次进行分组的需求,采用开窗函数解决

select
uid,
sum(if(date_format(createtime,'yyyy-MM-dd')='2020-10-16',1,0)) ,
sum(if(date_format(createtime,'yyyy-MM-dd')='2020-10-16' and rk=1,1,0)) ,
sum(if(date_format(createtime,'yyyy-MM-dd')='2020-10-16' and dn >0,1,0)) ,
sum(if(date_format(createtime,'yyyy-MM-dd')='2020-10-16' and dn >0 and rk=1,1,0)) ,
sum(if(date_format(createtime,'yyyy-MM-dd')='2020-10-16' and dn>30,1,0)) ,
sum(if(date_format(createtime,'yyyy-MM-dd')='2020-10-16' and dn>0 and dn<=30,1,0)) ,
sum(if(rk1=1,1,0)) 
from(
select
uid,
dn,
oid,
createtime,
row_number() over(PARTITION BY oid ORDER BY createtime DESC) rk,  --先对oid进行分组，由于数据库设计问题，可能会存在多个oid情况，所以在计算某型指标时需要对其进行去重
row_number() over(PARTITION BY uid,date_format(createtime,'yyyy-MM-dd') order by date_format(createtime,'yyyy-MM-dd') ASC) rk1   --uid在一天内会出现多次，如果出现多次就只计算一次，所以需要对uid和今天日期 分组 去重 计算某些指标
from employee
WHERE dt='2020-11-14' 
and date_format(createtime,'yyyy-MM-dd')>=trunc('2020-10-16','MM')
and date_format(createtime,'yyyy-MM-dd')<='2020-10-16'
) tel group by uid