Hive高阶函数

Hive高阶函数--列转行

列转行需要用到 explode()函数, 爆炸函数, 它属于UDTF(表生成函数), 即: 一进多出. 只能处理 数组或者字典.

select `array`(11, 22, 33);
select explode(array(11, 22, 33));      -- 一进多出,  列转行
select explode(map(11, 22, 33));        -- 报错, map是键值对类型, 元素个数必须成对出现.
select explode(map(11, 22, 33, 44));        -- 报错, map是键值对类型, 元素个数必须成对出现.

建表

NBA历年夺冠球队信息.
数据格式为: Chicago Bulls,1991|1992|1993|1996|1997|1998

create table the_nba_championyear(
    team_name string comment '队名',
    champion_year array<string> comment '夺冠年份'
) row format delimited
    fields terminated by ','                -- 切割后, 数据为: "Chicago Bulls", "1991|1992|1993|1996|1997|1998"
    collection items terminated by '|';     -- 切割后, 数据为: "Chicago Bulls", ["1991", "1992", "1993", "1996", "1997", "1998"]

上传源文件, 然后查看数据.

select * from the_nba_championyear;

 此时数据为

    team_name           champion_year
    Chicago Bulls         1991
    Chicago Bulls         1992
    Chicago Bulls         1993
    Chicago Bulls         1996
    Chicago Bulls         1997
    Chicago Bulls         1998
    San Antonio Spurs     1999
    San Antonio Spurs     2003

4.  把 champion_year字段用 explode()爆炸函数, 炸开即可.

select explode(champion_year) as champion_year from the_nba_championyear;

 5. 在上述查询基础上, 加入: 队名即可, 写法如下.

select a.team_name, b.champion_year from the_nba_championyear a
lateral view explode(champion_year) b as champion_year;

---

高阶函数--行转列

行转列涉及到的函数:
    采集函数:
        collect_list()     采集, 不去重, 有序(不是排序, 而是元素的存取顺序).
        collect_set()      采集, 去重, 无序.
    拼接函数:
        concat()           默认拼接符 ''
        concat_ws()        可以指定拼接符.

1. 建表.

create table row2col2(
     col1 string,
     col2 string,
     col3 int
)row format delimited fields terminated by '\t';

2. 上传源文件.

3. 查看表数据.

select * from row2col2;

4. HiveSQL语句--完成行转列代码实现.

select
    col1, col2,
    concat_ws('-', collect_list(cast(col3 as string))) as col3,
    concat_ws('-', collect_set(cast(col3 as string))) as col4
from
    row2col2
group by
    col1, col2;

---

Hive高阶函数--处理json字符串

json字符串介绍:
    概述:
        它是一种特殊规则的字符串, 由键值对组成. 只要用双引号包裹.
    格式:
        {"键":"值", "键":"值"}
    作用:
        一般是前后端传输数据的, 跨域传输, 例如: 前端把数据封装好 然后传入到 后台代码中.
    细节:
        json字符串也有数组形式:  [{"键":"值", "键":"值"}, {"键":"值", "键":"值"}...]
    Hive中处理Json字符串主要有两种思路:
        思路1: 以普通字符串的形式存储json字符串, 然后解析.          简单说: 先存储, 再解析.
            方式1: get_json_object()      逐个解析.
            方式2: json_tuple()           批量解析.
        思路2: 在加载(建表)json文件的时候, 直接解析json字符串.      简单说: 存储时, 直接解析.

演示 思路1

以普通字符串的形式存储json字符串, 然后解析.          简单说: 先存储, 再解析.

1. 建表.

create table t_json1(
    json string
);

2. 上传源文件.

3. 查看源数据.

select * from t_json1;

 4. 解析json字符串.

4.1 方式1: get_json_object()      逐个解析

select
    get_json_object(json, '$.device') as device,            -- 设备名
    get_json_object(json, '$.deviceType') as deviceType,     -- 设备类型
    get_json_object(json, '$.signal') as signal,    -- 信号强度
    get_json_object(json, '$.time') as `time`    -- 数据采集时间
from
    t_json1;

 4.2 方式2: json_tuple()           批量解析.

select
    -- 格式: json_tuple(json字符串, '键名1', '键名2', '键名3'...) as (别名1, 别名2, 别名3...)
    json_tuple(json, 'device', 'deviceType', 'signal', 'time') as (device, deviceType, signal, `time`)
from t_json1;

 演示 思路2

在加载(建表)json文件的时候, 直接解析json字符串.      简单说: 存储时, 直接解.

1. 建表, 直接解析 json字符串.

create table t_json2(
    device string,
    deviceType string,
    signal string,
    `time` string
)

 2. 上传源文件.

3. 查看源数据(看到的就是 解析好的内容)

select * from t_json2;

---

Hive高阶函数--窗口函数

窗口函数解释:
    概述:
        窗口函数相当于给表新增一列, 至于新增的内容是什么, 取决于窗口函数结合什么函数一起使用.
        窗口函数指的是 over()函数, 它可以限定操作数据的范围, 进行局部 或者 全局计算等...
    格式:
        可以结合窗口函数一起用的函数 over(partition by 分组字段 order by 排序字段 asc | desc rows between 起始行 and 结束行)
    可以结合窗口函数一起用的函数 分为3类:
        1. 聚合函数.
            count(), sum(), max(), min(), avg()
        2. 排序函数.
            row_number(), rank(), dense_rank(), ntile(n)
        3. 其它函数.
            lag(), lead(), first_value(), last_value()
    细节:
        1. 窗口函数相当于给表新增一列, 至于新增的内容是什么, 取决于窗口函数结合什么函数一起使用.
        2. 如果不写partition by, 默认是全局统计, 如果写了, 则只统计组内的数据(局部统计).
        3. 如果不写order by, 默认是统计组内所有数据, 如果写了, 则统计组内第一行至当前行的数据.
        4. 通过rows between可以限定操作数据的范围, 常用关键字如下:
            unbounded preceding         起始行(第一行)
            n preceding                 向上n行
            current row                 当前行
            n following                 向下n行
            unbounded following         结束行(最后1行)
        5. ntile(n)是几分之几的意思, 表示把数据分成几份, 优先参考最小分区.
           例如: 7条数据, 分成3份, 则是:   1,1,1      2,2     3,3
        6. 常用的 可以结合窗口函数一起用的函数 主要有: count(), sum(), row_number(), rank(), lag()

1.切换数据库

create database day09;
use day09;
show tables ;

 2.建表

--  员工表
CREATE TABLE employee (
    id     int,         --  编号
    name   string, --  姓名
    deg    string, --  职位
    salary int,         --  工资
    dept   string  --  部门
) row format delimited fields terminated by ',';

--  网站点击量表
create table website_pv_info
(
    cookieid   string, --  用户id
    createtime string, --  访问时间
    pv         int          --  页面浏览量
) row format delimited fields terminated by ',';


--  网站访问记录表
create table website_url_info
(
    cookieid   string, --  用户id
    createtime string, --  访问时间
    url        string  --  访问的url页面
) row format delimited fields terminated by ',';

3.需求: 统计所有员工的工资.

不使用窗口函数, 直接用传统的 聚合函数.

select sum(salary) from employee;

4.需求: 统计所有员工的工资, 并将其展示在每个员工数据的最后.

select *, sum(salary) over() from employee;

---

Hive高阶函数--窗口函数--结合聚合函数

演示 窗口函数 + 聚合函数一起使用.

1.求出网站总的pv数

所有用户所有访问加起来

如果over()里边什么都不写, 默认操作的是: 表中 该列所有的数据.

select *, sum(pv) over() as total_pv from website_pv_info;

2.求出每个用户总pv数

方式1: sum() + group by 一起使用.

select cookieid, sum(pv) as total_pv from website_pv_info group by cookieid;

方式2: 聚合函数 + 窗口函数一起使用.

如果写了partition by(表示分组): 则默认操作 组内所有的数据.

select *, sum(pv) over(partition by cookieid) as total_pv from website_pv_info;

如果写了order by(表示排序):     则默认操作 组内第一行 至 当前行的数据.

select *, sum(pv) over(partition by cookieid order by createtime) as total_pv from website_pv_info;

上述的代码, 等价于如下的内容:

select *,                                                                 --   第1行             至    当前行
       sum(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row ) as total_pv
from
    website_pv_info;

需求: 统计每个cookieID的pv(访问量)

只统计: 当前行及 向前3行 向后1行

select *,                                                                 --   向前3行    至    向后1行
       sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following ) as total_pv
from
    website_pv_info;

---

Hive高阶函数--窗口函数--结合排序函数

这里的排序函数指的是: row_number(), rank(), dense_rank(), 它们都可以做排名, 不同的是, 对相同值的处理结果.

例如: 数据是100, 90, 90, 60, 则: row_number是: 1, 2, 3, 4,  rank: 1, 2, 2, 4,  dense_rank: 1, 2, 2, 3

select
    *,
    row_number() over (partition by cookieid order by pv desc ) as rn,      -- 根据用户id分组, 根据点击量, 降序排列.
    rank() over (partition by cookieid order by pv desc ) as rk,
    dense_rank() over (partition by cookieid order by pv desc ) as dr
from website_pv_info;

分组求TOPN

Step1: 根据cookieID进行分组, 根据点击量进行排名.

select
    *,
    dense_rank() over (partition by cookieid order by pv desc ) as dr
from website_pv_info
where dr <= 4;          -- 报错, 思路没有问题, 但是: where只能筛选表中已经有的列(数据)

细节: where只能筛选表中已经有的列(数据)

Step2: 把上述的查询结果当做一张表, 然后从中获取我们要的数据即可.

with t1 as (
    select
        *,
        dense_rank() over (partition by cookieid order by pv desc ) as dr
    from website_pv_info
)
select * from t1 where dr <= 4;

ntile(数字,表示分成几份)

采用均分策略, 每份之间的差值不超过1, 优先参考最小的那个部分, 即: 7分成3份, 则是: 3, 2, 2

select
    *,
    ntile(3) over (partition by cookieid order by pv desc ) as nt      -- 根据用户id分组, 根据点击量, 降序排列. 分成3份
from website_pv_info;

需求: 按照cookieid分组, 按照点击量降序排列, 只要每组前三分之一的数据.

with t1 as (
    select
        *,
        ntile(3) over (partition by cookieid order by pv desc ) as nt      -- 根据用户id分组, 根据点击量, 降序排列. 分成3份
    from website_pv_info
)
select * from t1 where nt = 1;

---

Hive高阶函数--窗口函数--结合其它函数

1. LAG 用于统计窗口内往上第n行值

需求: 显示用户上一次的访问时间.

select
    *,
    -- 获取上1个createtime列的值,  根据cookieid分组   根据createtime升序排列
    lag(createtime) over(partition by cookieid order by createtime) lag_time
from
    website_url_info;

 根据cookieID分组

根据cookieID分组, createtime升序排序, 获取当前行 向上2行的createtime列的值, 找不到就用默认值(李四)填充.

select
    *,
    -- 获取createtime列向上第2个值, 如果没有就用 夯哥 作为默认值进行填充.
    -- 参1: 要操作的列,   参2: 向上第几个.  参3: 默认值.
    lag(createtime, 2, '李四') over(partition by cookieid order by createtime) lag_time2
from
    website_url_info;

 2. LEAD 用于统计窗口内往下第n行值

select
    *,
    -- 获取createtime列向上第2个值, 如果没有就用 夯哥 作为默认值进行填充.
    -- 参1: 要操作的列,   参2: 向下第几个.  参3: 默认值.
    lead(createtime, 2, '夯哥') over(partition by cookieid order by createtime) lead_time
from
    website_url_info;

 3. FIRST_VALUE 取分组内排序后，截止到当前行，第一个值

select
    *,
    first_value(createtime) over(partition by cookieid order by createtime) first_time
from
    website_url_info;

4. LAST_VALUE  取分组内排序后，截止到当前行，最后一个值

select
    *,
    last_value(createtime) over(partition by cookieid order by createtime) last_time
from
    website_url_info;