(五)Hive常用函数示例 - HelloWorld开发者社区

1 CASE WHEN

示例我们有这样一组数据

data1.txt

id    name    dept    project
1001    zhangsan    sale    A
1002    lisi    sale    A
1003    wangwu    IT    B
1004    zhaoliu    IT    A
1004    zhaosi    sale    B

求出每个部门参加a、b项目各有多少人？

创建表

create table emp_pro(
id int,
name string,
dept string,
project string)
row format delimited fields terminated by "\t";

导入数据

load data local inpath into '/home/v2admin/demo/demo1.txt' into table emp_pro;

查看下我们的表的内容

+-------------+---------------+---------------+------------------+
| emp_pro.id  | emp_pro.name  | emp_pro.dept  | emp_pro.project  |
+-------------+---------------+---------------+------------------+
| 1001        | zhangsan      | sale          | A                |
| 1002        | lisi          | sale          | A                |
| 1003        | wangwu        | IT            | B                |
| 1004        | zhaoliu       | IT            | A                |
| 1004        | zhaosi        | sale          | B                |
+-------------+---------------+---------------+------------------+

创建表和导入数据没有问题，接下来开始实现我们的需求

select 
dept,
sum(case project when "A" then 1 else 0 end)  A_Count,
sum(case project when "B"  then 1 else 0 end)  B_Count
from emp_pro
group by dept;

结果

+-------+----------+----------+
| dept  | a_count  | b_count  |
+-------+----------+----------+
| IT    | 1        | 1        |
| sale  | 2        | 1        |
+-------+----------+----------+

2.CONCAT、CONCAT_WS、COLLECT_SET(col)

函数	说明
CONCAT(string A/col, string B/col…)	返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...)	它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col)	函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

示例如下
还是这个数据

data1.txt

id    name    dept    project
1001    zhangsan    sale    A
1002    lisi    sale    A
1003    wangwu    IT    B
1004    zhaoliu    IT    A
1004    zhaosi    sale    B

我们要得到如下结果

sale,A    zhangsan,lisi
sale,B    zhaosi
IT,A    zhaoliu
IT,B    wangwu

查询语句

select 
t1.bs,
concat_ws("|",collect_set(t1.name)) name
from(
select name,concat(dept,",",project) bs from emp_pro)  t1
group by t1.bs;

结果

+---------+----------------+
|  t1.bs  |      name      |
+---------+----------------+
| IT,A    | zhaoliu        |
| IT,B    | wangwu         |
| sale,A  | zhangsan|lisi  |
| sale,B  | zhaosi         |
+---------+----------------+

3 EXPOLODE(col)和LATERAL VIEW

函数	说明
EXPLODE(col)	将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW	用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合，例如 LATERAL VIEW udtf(expression) tableAlias AS columnAlias

示例
有这样一组数据

书名            标签
Python核心编程    开发、后台、编程、python
Java编程与进阶    开发、编程、Java
MYSQL实战        数据库、SQL

我们想得到展开的数据

Python核心编程    开发
Python核心编程    后台
Python核心编程    编程
Python核心编程    python
Java编程与进阶    开发
Java编程与进阶    编程
Java编程与进阶    Java
MYSQL实战    数据库
MYSQL实战    SQL

首先准备数据data2.txt

Python核心编程    开发、后台、编程、python
Java编程与进阶    开发、编程、Java
MYSQL实战    数据库、SQL

创建表并导入数据

-- 创建表
create table book_info(
book_name string,
category string)
row format delimited fields terminated by "\t";

-- 导入数据
load data local inpath "/home/v2admin/demo/data2.txt" into table book_info;

查看下表的数据

+----------------------+---------------------+
| book_info.book_name  | book_info.category  |
+----------------------+---------------------+
| Python核心编程           | 开发、后台、编程、python     |
| Java编程与进阶            | 开发、编程、Java          |
| MYSQL实战              | 数据库、SQL             |
+----------------------+---------------------+

Ok，接下来实现我们的需求

select
book_info.book_name,
tb.cate
from book_info
lateral view
explode(split(category, "、")) tb as cate;

结果

+----------------------+-----------+
| book_info.book_name  | tbl.cate  |
+----------------------+-----------+
| Python核心编程           | 开发        |
| Python核心编程           | 后台        |
| Python核心编程           | 编程        |
| Python核心编程           | python    |
| Java编程与进阶            | 开发        |
| Java编程与进阶            | 编程        |
| Java编程与进阶            | Java      |
| MYSQL实战              | 数据库       |
| MYSQL实战              | SQL       |
+----------------------+-----------+

4.开窗函数(也叫窗口函数)

4.1 函数说明

函数	说明
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类型。

过一下即可，等看示例的时候，再对照着看

4.2 示例

1) 老套路，先上数据
姓名日期消费

# 姓名    日期    消费
zhangsan    2017-01-01    10
zhangsan    2017-01-02    15
wangwu    2017-02-03    23
wangwu    2017-01-04    29
zhaoliu    2017-01-05    46
zhaoliu    2017-04-06    42
wangwu    2017-01-07    50
zhangsan    2017-01-08    55
zhangsan    2017-04-08    62
wangwu    2017-04-09    68
zaholiu    2017-05-10    12
zhouzhou    2017-04-11    75
zhouzhou    2017-04-13    94

保存文件data3.txt

2)创建表并导入数据

-- 创建表
create table log_info(
name string,
log_date string,
cost int)
row format delimited fields terminated by "\t";

-- 导入数据
load data local inpath "/home/v2admin/demo/data3.txt" into table log_info;

3)需求一: 查询2017年4月份消费过的用户和用户总数

-- over() 不加参数，表示整张表开个窗口
select
name,
count(*) over() as count
from log_info
where substring(log_date,1,7) = '2017-04'
group by name;

结果

+-----------+--------+
|   name    | count  |
+-----------+--------+
| wangwu    | 4      |
| zhangsan  | 4      |
| zhaoliu   | 4      |
| zhouzhou  | 4      |
+-----------+--------+

4）需求2 查询用户的消费明细和每月消费总额

select
name,
log_date,
cost,
sum(cost) over(partition by month(log_date))
from log_info;

结果

+-----------+-------------+-------+---------------+
|   name    |  log_date   | cost  | sum_window_0  |
+-----------+-------------+-------+---------------+
| zhangsan  | 2017-01-01  | 10    | 205           |
| zhangsan  | 2017-01-02  | 15    | 205           |
| wangwu    | 2017-01-04  | 29    | 205           |
| zhaoliu   | 2017-01-05  | 46    | 205           |
| wangwu    | 2017-01-07  | 50    | 205           |
| zhangsan  | 2017-01-08  | 55    | 205           |
| wangwu    | 2017-02-03  | 23    | 23            |
| zhouzhou  | 2017-04-11  | 75    | 341           |
| zhouzhou  | 2017-04-13  | 94    | 341           |
| zhangsan  | 2017-04-08  | 62    | 341           |
| wangwu    | 2017-04-09  | 68    | 341           |
| zhaoliu   | 2017-04-06  | 42    | 341           |
| zaholiu   | 2017-05-10  | 12    | 12            |
+-----------+-------------+-------+---------------+

5）需求3 查询用户上次的消费日期

select
name,
log_date,
cost,
lag(log_date,2) over (partition by name order by log_date) as last_time
from log_info;

结果

+-----------+-------------+-------+-------------+
|   name    |  log_date   | cost  |  last_time  |
+-----------+-------------+-------+-------------+
| wangwu    | 2017-01-04  | 29    | NULL        |
| wangwu    | 2017-01-07  | 50    | NULL        |
| wangwu    | 2017-02-03  | 23    | 2017-01-04  |
| wangwu    | 2017-04-09  | 68    | 2017-01-07  |
| zaholiu   | 2017-05-10  | 12    | NULL        |
| zhangsan  | 2017-01-01  | 10    | NULL        |
| zhangsan  | 2017-01-02  | 15    | NULL        |
| zhangsan  | 2017-01-08  | 55    | 2017-01-01  |
| zhangsan  | 2017-04-08  | 62    | 2017-01-02  |
| zhaoliu   | 2017-01-05  | 46    | NULL        |
| zhaoliu   | 2017-04-06  | 42    | NULL        |
| zhouzhou  | 2017-04-11  | 75    | NULL        |
| zhouzhou  | 2017-04-13  | 94    | NULL        |
+-----------+-------------+-------+-------------+

4.日期函数

1)current_date返回当前日期

0: jdbc:hive2://hadoop10:10000> select current_date();
+-------------+
|     _c0     |
+-------------+
| 2020-01-15  |
+-------------+

2）date_add, date_sub,datediff()日期的加减

-- 从今天开始，100天以后的日期
select date_add(current_date(), 100);

-- 从今天开始，100天以前的日期；
select date_sub(current_date(), 100);

-- 两个日期之间的差
select datediff(current_date(), "2019-01-03");