一.Hive UDTF之explode函数

1.UDTF-----explode(ARRAY a)

select explode(array("a","b","c")) as item;

2.UDTF-----explode(Map m)

select explode(map("a",1,"b",2,"c",3)) as (key,value)

3.UDTF--posexplode(ARRAY a):返回元素在数组中的索引位置

select posexplode(array("a","b","c")) as (pos,item);

4.UDTF--inline(ARRAY>a)

select inline(array(named_struct("id",1,"name","zs"),
    named_struct("id",2,"name","sao"),
    named_struct("id",3,"name","ou"))) as (id,name);

功能介绍

一般情况下，explode函数可以直接单独使用即可；也可以根据业务需要结合lateral view侧视图一起使用。 explode(array) 将array里的每个元素生成一行； explode(map) 将map里的每一对元素作为一行，其中key为一列，value为一列；

二、Hive Lateral View 侧视图

Lateral View是一种特殊的语法，主要搭配UDTF类型函数一起使用，用于解决UDTF函数的一些查询限制的问题。一般只要使用UDTF，就会固定搭配lateral view使用。

将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。
使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by 、limit等语句中，不需要再单独嵌套一层子查询。

id	name	hobbies
1	zs	[reading,coding]
2	ls	[coding,running]

变为

id	name	hobbies	hobby
1	zs	[reading,coding]	reading
1	zs	[reading,coding]	coding
2	ls	[reading,ssss]	reading
2	ls	[reading,ssss]	ssss

--lateral view侧视图基本语法如下select …… from tabelA lateral view UDTF(xxx) 别名 as col1,col2,col3……;

select id,name,hobbies,hobby
from person lateral view explode(hobbies) tmp as hobby;

案例演示1

1.数据准备

1）表结构

2）建表语句

create table movie_info(
    movie string,     --电影名称
    category string   --电影分类
) 
row format delimited fields terminated by "\t";

3）装载语句

insert overwrite table movie_info
values ("《疑犯追踪》", "悬疑,动作,科幻,剧情"),
       ("《Lie to me》", "悬疑,警匪,动作,心理,剧情"),
       ("《战狼2》", "战争,动作,灾难");

2. 需求

第一步，把category字符串变成数组

select
    movie,
       split(category,",") a
from movie_info;

第二步，因为later view 中的explode字段后面跟上表已经存在的字段，而第一步的是起的别名，所以子查询下

select *from (
             select movie,split(category,",") category
    from movie_info
                 ) t1 lateral view explode(category) tmp as cate;

得到三列十一行的表

第三步分组聚合

select cate,count(*) from (
             select movie,split(category,",") category
    from movie_info
                 ) t1 lateral view explode(category) tmp as cate
group by cate;

案例演示2

有一份数据《The_NBA_Championship.txt》，关于部分年份的NBA总冠军球队名单；第一个字段表示球队名称，第二个字段是获取总冠军的年份；字段之间以，分割；总冠军年份之间以|进行分割。需求：使用Hive建表映射成功数据，对数据拆分，要求拆分之后数据如下所示：

create table the_nba_championship(
    team_name string,
    champion_year array<string>

) row format delimited fields terminated by ","
collection items terminated by '|';

执行错误

1.在select条件中，如果只有explode函数表达式，程序执行是没有任何问题的；

2.但是如果在select条件中，包含explode和其他字段，就会报错；

3.如何理解这个错误？为什么在select的时候，explode的旁边不支持其他字段的同时

UDTF's are not supported outside the SELECT clause, nor nested in expressions
不支持在 SELECT 子句之外使用 UDTF，也不支持嵌套在表达式中

UDTF语法限制

UDTF语法限制解决

从SQL层面上来说上述问题的解决方案是：对两张表进行join关联查询;

Hive专门提供了语法lateral View侧视图，专门用于搭配explode这样的UDTF函数，以满足上述需要。

select a.team_name,tmp.year
from the_nba_championship
a lateral view explode(champion_year) tmp as year;

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

窗口函数（Window functions）也叫做开窗函数、OLAP函数，其最大特点是：输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。如果函数具有OVER子句，则它是窗口函数。窗口函数可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行，窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中。

绝大多数的聚合函数都可以配合窗口函数使用，列如max(),min(),sum(),count(),avg()

select order_id,order_date,amount,
sum(amount) over(窗口范围） total_amount
from order_info;

范围有两种：一种是基于行的，一种是基于值的

基于行的：要求每行数据的窗口为上一行到当前行

基于值的：要求每行数据的窗口为值位于当前值-1,到当前值

语法--------窗口---------基于行

sum(amount)over()

row between and

其实：UNBOUNDED PRECEDING 表示从前面的起点，

n PRECEDING：往前n行数据，

CURRENT ROW：当前行

n FOLLOWING：往后n行数据

1.前面两种情况的终点：n PRECEDING：往前n行数据，

CURRENT ROW：当前行

n FOLLOWING：往后n行数据

UNBOUNDED FOLLOWING表示到后面的终点

2.CURRENT ROW的终点：

CURRENT ROW

n FOLLOWING

UNBOUNDED FOLLOWING

3.n FOLLOWING的终点：

n FOLLOWING

UNBOUNDED FOLLOWING

语法--------窗口---------基于值

sum(amount)over()

order by[column] range between and

其实：UNBOUNDED PRECEDING 表示从前面的起点，

n PRECEDING：往前n行数据，

CURRENT ROW：当前行

n FOLLOWING：往后n行数据

1.前面两种情况的终点：n PRECEDING：往前n行数据，

CURRENT ROW：当前行

n FOLLOWING：往后n行数据

UNBOUNDED FOLLOWING表示到后面的终点

2.CURRENT ROW的终点：

CURRENT ROW

n FOLLOWING

UNBOUNDED FOLLOWING

3.n FOLLOWING的终点：

n FOLLOWING

UNBOUNDED FOLLOWING

order_date<=2022-01-03

语法------窗口-----分区

应用：每个用户截止下单时间总额

跨行取值函数

（1）lead和lag

应用：上一次下单时间和上一次下单时间

first_value(order_data,false)是否跳过null

RANK() 排序相同时会重复，总数不会变，可以有并列

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

比如rank 1 1 3

dense_rank 11 2

row_number 1 2 3

注：rank 、dense_rank、row_number不支持自定义窗口。

案例演示

1.数据准备

1）表结构

2）建表语句

create table order_info
(
    order_id     string, --订单id
    user_id      string, -- 用户id
    user_name    string, -- 用户姓名
    order_date   string, -- 下单日期
    order_amount int     -- 订单金额
);

3）装载语句

insert overwrite table order_info
values ('1', '1001', '小元', '2022-01-01', '10'),
       ('2', '1002', '小海', '2022-01-02', '15'),
       ('3', '1001', '小元', '2022-02-03', '23'),
       ('4', '1002', '小海', '2022-01-04', '29'),
       ('5', '1001', '小元', '2022-01-05', '46'),
       ('6', '1001', '小元', '2022-04-06', '42'),
       ('7', '1002', '小海', '2022-01-07', '50'),
       ('8', '1001', '小元', '2022-01-08', '50'),
       ('9', '1003', '小辉', '2022-04-08', '62'),
       ('10', '1003', '小辉', '2022-04-09', '62'),
       ('11', '1004', '小猛', '2022-05-10', '12'),
       ('12', '1003', '小辉', '2022-04-11', '75'),
       ('13', '1004', '小猛', '2022-06-12', '80'),
       ('14', '1003', '小辉', '2022-04-13', '94');

2. 需求

1）统计每个用户截至每次下单的累积下单总额

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    sum(order_amount) over(partition by user_id order by order_date rows between unbounded preceding and current row) sum_so_far
from order_info;

2）统计每个用户截至每次下单的当月累积下单总额

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    sum(order_amount) over(partition by user_id,substring(order_date,1,7) order by order_date rows between unbounded preceding and current row) sum_so_far
from order_info;

3）统计每个用户每次下单距离上次下单相隔的天数（首次下单按0天算）

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    nvl(datediff(order_date,last_order_date),0) diff
from
(
    select
        order_id,
        user_id,
        user_name,
        order_date,
        order_amount,
        lag(order_date,1,null) over(partition by user_id order by order_date) last_order_date
    from order_info
)t1

4）查询所有下单记录以及每个用户的每个下单记录所在月份的首/末次下单日期

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    first_value(order_date) over(partition by user_id,substring(order_date,1,7) order by order_date) first_date,
    last_value(order_date) over(partition by user_id,substring(order_date,1,7) order by order_date rows between unbounded preceding and unbounded following) last_date
from order_info;

5）为每个用户的所有下单记录按照订单金额进行排名

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    rank() over(partition by user_id order by order_amount desc) rk,
    dense_rank() over(partition by user_id order by order_amount desc) drk,
    row_number() over(partition by user_id order by order_amount desc) rn
from order_info;