Hive 常用函数

一、常用内置函数

查看系统自带的函数
 show functions;

显示自带的函数的用法
desc function upper;

详细显示自带的函数的用法
desc function extended upper;

1、空字段赋值

NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。如果value为NULL，则返回default_value的值，否则返回value的值。

2、CASE WHEN THEN ELSE END

求出不同部门男女各多少人。结果如下：

select 
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male_count,
  sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex
group by dept_id;

3、行转列

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。separator为参数间的分隔符。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。
注意: CONCAT_WS must be "string or array

COLLECT_SET(col)：，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。COLLECT_SET函数只接受基本数据类型。

collect_list(): 汇总

select 分类字段,concat_ws(’,’,collect_set(合并字段)) as 别名 from table_name group 分类字段;

/* 原始数据
name       gender          times
张三         男            唐
李四         男            唐
王五         男            明
赵六         男            明
*/
-- 先用collect_set将列拼接在一起，然后再通过concat_ws进行展开拼接
SELECT a.gender_times,
       concat_ws(';',collect_set(a.name)) name
FROM
  (SELECT name,
          concat(gender, '_','times') gender_times
   FROM hero_info) t
GROUP BY t.gender_times;
/*查询结果
gender_times    name    
男_唐     张三;李四
男_明     王五;赵六

4、列转行

EXPLODE(col)：将array或者map结构拆分成多行。
LATERAL VIEW : 侧写表(虚拟表)

LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

示例：

SELECT movie,category_name 
FROM movie_info 
lateral VIEW
explode(split(category,",")) movie_info_tmp  AS category_name ;

5、窗口函数（开窗函数）

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的改变而变化。

CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据

UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING表示到后面的终点

LAG(col,n,default_val)：往前第n行数据
LEAD(col,n, default_val)：往后第n行数据

NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

（1） 查询在2017年4月份购买过的顾客及总人数

select name,count(*) over () 
from business 
where substring(orderdate,1,7) = '2017-04' 
group by name;

（2） 查询顾客的购买明细及月购买总额

select name,orderdate,cost,
sum(cost) over(partition by month(orderdate)) 
from business;

（3） 将每个顾客的cost按照日期进行累加

select name,orderdate,cost, 
sum(cost) over() as sample1,--所有行相加 
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加 
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加 
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 
from business;

注意：
rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量

（4） 查看顾客上次的购买时间

select name,orderdate,cost, 
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2 
from business;

含义：LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

（5） 查询前20%时间的订单信息

select * from (
    select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
    from business
) t
where sorted = 1;

总结:

• over(): 会为每条数据都开启一个窗口. 默认的窗口大小就是当前数据集的大小.

• over(partition by …) : 会按照指定的字段进行分区， 将分区字段的值相同的数据划分到相同的区。
  每个区中的每条数据都会开启一个窗口.每条数据的窗口大小默认为当前分区数据集的大小.

• over(order by …) : 会在窗口中按照指定的字段对数据进行排序.
  会为每条数据都开启一个窗口,默认的窗口大小为从数据集开始到当前行.

• over(partition by … order by …) :会按照指定的字段进行分区，
  将分区字段的值相同的数据划分到相同的区,
  在每个区中会按照指定的字段进行排序.
  会为每条数据都开启一个窗口,默认的窗口大小为当前分区中从数据集开始到当前行.

• over(partition by … order by … rows between … and …) :
  指定每条数据的窗口大小.

关键字:

  order by :       全局排序 或者 窗口函数中排序.
  partition by :   窗口函数中分区
  distribute by :  分区 
  sort by :        区内排序
  cluster by :     分区排序, 等价于 distribute by + sort by
  partitioned by : 建表指定分区字段
  clustered by :   建表指定分桶字段

6、Rank

RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;

二、常用日期函数

1. unix_timestamp:返回当前或指定时间的时间戳

select unix_timestamp();
select unix_timestamp("2020-10-28",'yyyy-MM-dd');

2. from_unixtime：将时间戳转为日期格式

select from_unixtime(1603843200);

3. current_date：当前日期

select current_date;

4. current_timestamp：当前的日期加时间

select current_timestamp;

5. to_date：抽取日期部分

select to_date('2020-10-28 12:12:12');

6. year：获取年

select year('2020-10-28 12:12:12');

7. month：获取月

select month('2020-10-28 12:12:12');

8. day：获取日

select day('2020-10-28 12:12:12');

9. hour：获取时

select hour('2020-10-28 12:13:14');

10. minute：获取分

select minute('2020-10-28 12:13:14');

11. second：获取秒

select second('2020-10-28 12:13:14');

12. weekofyear：当前时间是一年中的第几周

select weekofyear('2020-10-28 12:12:12');

13. dayofmonth：当前时间是一个月中的第几天

select dayofmonth('2020-10-28 12:12:12');

14. months_between： 两个日期间的月份

select months_between('2020-04-01','2020-10-28');

15. add_months：日期加减月

select add_months('2020-10-28',-3);

16. datediff：两个日期相差的天数

select datediff('2020-11-04','2020-10-28');

17. date_add：日期加天数

select date_add('2020-10-28',4);

18. date_sub：日期减天数

select date_sub('2020-10-28',-4);

19. last_day：日期的当月的最后一天

select last_day('2020-02-30');

20. date_format(): 格式化日期

select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');

三、常用取整函数

round： 四舍五入

select round(3.14);
select round(3.54);

ceil： 向上取整

select ceil(3.14);
select ceil(3.54);

floor： 向下取整

select floor(3.14);
select floor(3.54);

四、常用字符串操作函数

.upper： 转大写

select upper('low');

lower： 转小写

select lower('low');

length： 长度

select length("atguigu");

trim： 前后去空格

select trim(" atguigu ");

lpad： 向左补齐，到指定长度

select lpad('atguigu',9,'g');

rpad： 向右补齐，到指定长度

select rpad('atguigu',9,'g');

regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！

SELECT regexp_replace('2020/10/25', '/', '-');

五、集合操作

size： 集合中元素的个数

select size(friends) from test3;

map_keys： 返回map中的key

select map_keys(children) from test3;

map_values: 返回map中的value

select map_values(children) from test3;

array_contains: 判断array中是否包含某个元素

select array_contains(friends,'bingbing') from test3;

sort_array： 将array中的元素排序

select sort_array(friends) from test3;