Hive函数介绍:

    最初Hive函数分为 内置函数 和 用户自定义函数两大类, 其中用户自定义函数又被分为3类, 分别是:
        内置函数:   属于Hive自带的.
        用户自定义函数:
            UDF:  全称叫 User Defined Functions, 普通函数, 即: 一进一出.
                例如: select * from stu;
            UDAF: 全称叫 User Defined Aggregation Functions, 聚合函数, 即: 多进一出.
                例如: select count(id) from stu;
            UDTF: 全称叫  User Defined Table-Generating Functions, 表生成函数, 即: 一进多出.
                例如: select explode(array(11, 22, 33));
    后来发现用 UDF, UDAF, UDTF来划分Hive函数实在是太方便了, 于是提出了1个词, 叫: 函数标准扩大化, 即:
    UDF, UDAF, UDTF本来是形容用户自定义函数的, 现在, hive中的函数没有内置函数 和 自定义函数之分了, 取而代之的是: UDF, UDAF, UDTF

问题: 如果某个函数我不会用怎么办?

答案: 查看Hive针对于该函数的 说明文档.

-- 查看Hive所有的内置函数
show functions ;

-- 查看函数的说明信息

describe function 函数;  -- 简单信息

describe function extended 函数; -- 详细信息,包括 测试案例(有的没有)

相关函数:

字符串相关函数:

1. 字符串切割

-- spilt(参数1,参数2)     参数1: 要操作的参数.  参数2: 切割符
select spilt("aa,bb,cc",",");        -- ["aa","bb","cc"]

-- substr(参数1,参数2,参数3) 和 substring(参数1,参数2,参数3) 
-- 参1: 要操作的字符串.  参2: 起始索引, 参数3: 个数
-- 效果一样, 没有区别

select substr('2024-01-13 11:51:27', 1, 10);
select substring('2024-01-13 11:51:27', 1, 10);

2. 字符串拼接

-- concat() 拼接符默认为空
select concat('aa','bb','cc');   aabbcc

-- concat_ws(参数1,参数2) 可以指定拼接符  参数1:拼接符   参数2:要拼接的内容
select concat_ws('-','aa','bb','cc');   aa-bb-cc

3. 获取字符串长度

select length('abc');

4. 转大小写

select lower('ABC');        -- abc 转小写

select upper('abc');        -- ABC 转大写

5. 移除首尾空格

select trim('    aa   bb    ');     -- 'aa   bb'

6. 正则替换, 参数1:要被处理的字符串. 参数2:正则表达式. 参数3:用来替换的内容

select regexp_replace('100-200', '\\d+', '夯哥');

7. URL解析.
select parse_url('http://www.itcast.cn/path/p1.php?query=1', 'HOST');       -- www.itcast.cn,  主机名.
select parse_url('http://www.itcast.cn/path/p1.php?query=1', 'PATH');       -- /path/p1.php,   资源路径
select parse_url('http://www.itcast.cn/path/p1.php?username=admin01&password=pwd111', 'QUERY');         -- username=admin01&password=pwd111, 访问参数
select parse_url('http://www.itcast.cn/path/p1.php?username=admin01&password=pwd111', 'QUERY', 'username');      -- admin01, username参数值.

-- 8. 处理json字符串, 它是一种特定格式的字符串, 主要用于 传输数据的, 把数据封装成json字符串格式, 在各个框架中进行传输.

-- json解析函数:get_json_object(json_txt, path)
-- 注意: 整个json字符串用单引号'包裹, json字符串中的键, 值用双引号"包裹.
-- json字符串的格式: {键:值, 键: 值}
-- json数组的格式: [{键:值, 键: 值}, {键:值, 键: 值}, {键:值, 键: 值}]      -- 索引从 0 开始.
select get_json_object('{"name":"杨过", "age":"18"}', '$.name');      -- 杨过,   $表示json对象
select get_json_object('[{"name":"杨过", "age":"18"}, {"name":"小龙女", "age":"26"}]', '$.[1].name');

日期相关:

select current_date();      -- 获取当前时间, 年月日格式

select unix_timestamp();    -- 获取当前时间, unix格式, 即: 从时间原点(1970-01-01 00:00:00)截止到当前时间的 秒值.

select unix_timestamp('2024/01/12', 'YYYY/mm/dd');  -- 根据指点的时间, 获取其unix时间.

-- 根据秒值(unix时间) 获取其对应的 日期.
select from_unixtime(1684477644);       -- 2023-05-19 06:27:24

-- 获取年月日.
select year('2023-05-19 14:31:02');     -- 2023
select month('2023-05-19 14:31:02');     -- 5
select day('2023-05-19 14:31:02');     -- 19

-- 时间相关操作, 增加, 减少, 比较.
select date_add('2023-05-19', 2);       -- 2023-05-21
select date_add('2023-05-19', -2);       -- 2023-05-17
select datediff('2023-05-19', '2023-05-20');    -- 前 - 后,  -1

-- 扩展, 计算某年的2月有多少天.
-- 思路: 把时间设置为该年的3月1日, 然后往前推1天, 判断该天是月中的第几天, 即为结果.
select day(date_add('2023-03-01', -1));
select dayofmonth(date_add('2024-03-01', -1));

数字相关函数:

select rand();      -- 0.0 ~ 1.0, 包左不包右.
select round(4.3);  -- 四舍五入, 原理: +0.5, 然后求地板数.

select abs(-10);    -- 求绝对值
select ceil(5.3);   -- 天花板数, 比该数大的所有数字中, 最小的那个整数(包括本身)      6
select ceil(5.0);   -- 天花板数, 比该数大的所有数字中, 最小的那个整数(包括本身)      5
select floor(5.6);  -- 地板数, 比该数小的所有数字中, 最大的那个整数(包括本身)        5

-- 自己实现 四舍五入.
select `floor`(5.3 + 0.5);  -- 5

非空校验 相关函数:

-- if函数(条件表达式, 值1, 值2),  先执行条件表达式, 看起结果是否成立, 成立执行值1, 否则执行值2
select if(5 > 3, '郑州', '新乡');   -- 郑州

-- 非空校验.
select isnull('zz');        -- false
select isnull(null);         -- true

select isnotnull('zz);     -- true
select isnotnull(null);      -- false

-- 空值转换, 判断第1个值是否为null, 如果不是就获取第1个值, 否则就用第2个值(类似于 默认值)
select nvl('zz', '张三');     -- zz
select nvl(null, '张三');      -- 张三

-- 非空查找 coalesce(值1, 值2....), 获取参数列表中, 第1个非null值
select coalesce(null, null, null);  -- null
select coalesce(null, 11, 22);      -- 11

-- case when转换.
select
    case
        when 5 > 3 then '大于'
        when 5 < 3 then '小于'
        else '等于'
    end as eq ;

-- 如果只做等于判断, 可以变形为:
select
    case 5
        when 1 then '周1'
        when 3 then '周3'
        when 5 then '周5'
        else '不知道周几, 睡迷糊了!'
    end as week;

杂项函数 相关函数:

-- 类型转换相关.
select cast(10.3 as int);       -- 10
select cast(10 as string);      -- '10'
select cast('12.3' as double);  -- 12.3
select cast('12.3a' as double);  -- null

-- 获取哈希值.
select hash('张三');          -- -838675700
select mask_hash('张三');     -- 1d841bc0ee98309cb7916670b7f0fdef5f4c35150711a41405ef3633b56322cf

-- mask()函数, 脱敏函数, 顺序是: 大写字母, 小写字母, 数字
select mask('abc123ABC');                   -- xxxnnnXXX, 默认: 大写字母X, 小写字母x, 数字n
select mask('abc123ABC', '大','小', '*');     -- xxxnnn***, 指定: 大写字母 大, 小写字母 小, 数字*

-- 对数组元素排序, 默认: 升序.
select sort_array(array(11, 33, 55, 22));       -- [11,22,33,55]

-- 加密函数.
select md5('pwd111');       -- 130353326a7bfab601f57757033b5b4a

行列转换入门:

-- 1. 建表, 添加表数据, 源数据格式为: "Chicago Bulls,1991|1992|1993|1996|1997|1998"
-- 处理后, 格式为: "Chicago Bulls", ""
create table the_nba_championship(
    team_name string,               -- 队名
    champion_year array<string>     -- 夺冠年份
) 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"]

-- 2. 查看表数据.
select * from the_nba_championship;

-- 3. 需求, 把上述的数据, 按照 队名, 年份(只有1个值)的方式做打印, 例如:
-- 实际开发中: 侧视图 lateral view() 一般会和 explode() 结合使用.
/*
    列名:         team_name       champion_year
    第1行:        Chicago Bulls     1991
    第2行:        Chicago Bulls     1992
    第3行:        Chicago Bulls     1993
    ...
*/
-- 3.1 想办法对数据进行炸开, 这里用到炸裂函数 explode();
select explode(array('aa', 'bb', 'cc'));

-- 3.2 对夺冠年份进行 炸裂操作.
select explode(champion_year) from the_nba_championship;

-- 3.3 到这, 我们发现数据(夺冠年份)已经炸开了, 那么直接和原表一起查询即可. 如下:
select team_name, explode(champion_year) from the_nba_championship;     -- 报错

-- 4. 最终版, 通过侧视图存储 炸裂后的内容, 然后和原表数据一起查询即可.
-- 侧视图解释: lateral view, 理解为: 临时的存储炸裂的内容.
-- 侧视图格式: lateral view 炸裂函数 侧视图名 as 炸裂后的列名.
select a.team_name, b.champion_year from the_nba_championship a
lateral view explode(champion_year) b as champion_year;

行列转换之: 行转列:

-- 1. 建表, 上传源文件.
create table row2col2(
    col1 string,
    col2 string,
    col3 int
)row format delimited fields terminated by '\t';

-- 2. 查询表数据.
select * from row2col2;

-- 3. 实现: 行转列.
/*
行转列涉及到的函数:
    数据采集函数:
        collect_list()      采集数据, 可重复, 有序
        collect_set()       采集数据, 唯一, 无序.
    数据拼接函数:
        concat()        拼接符: 默认为空.
        concat_ws()     拼接符: 可以自定义.
*/
select concat(1, 2, 3);
select concat_ws('-', 1, 2, 3);     -- 报错, concat_ws()拼接的内容必须是字符串形式.
select concat_ws('-', cast(1 as string), cast(2 as string), cast(3 as string));     -- 报错, concat_ws()拼接的内容必须是字符串形式.

-- 最终写法: 行转列代码实现.
select
    col1, col2,
    --  cast(col3 as string)  把 col3内容转成 字符串, 方便后续 concat_ws()拼接.
    --  collect_list(cast(col3 as string)) 把 col3列的内容, 全部采集到, 有序, 可重复.
    --  concat_ws('-', collect_list(cast(col3 as string)))  把 上述的内容, 按照-, 拼接到一起.
    concat_ws('-', collect_list(cast(col3 as string))) as col3
from row2col2 group by col1, col2;

行列转换之: 列转行:

-- 1. 建表, 上传源文件.
create table col2row2(
    col1 string,
    col2 string,
    col3 string
)row format delimited fields terminated by '\t';

-- 2. 查询表数据.
select * from col2row2;

-- 3. 实现: 列转行.
-- 3.1 把 col3列给炸开.
select explode(split(col3, ',')) from col2row2;     -- '1,2,3' => ['1', '2', '3']

-- 3.2 侧视图 lateral view + explode()炸裂函数实现  列转行.
select t1.col1, t1.col2, t2.col3 from col2row2 t1
lateral view explode(split(col3, ',')) t2 as col3;

处理json字符串:

-- 思路1: 先用字符串把json内容存储下来, 然后对其进行处理.
-- 源数据格式: '{"device":"device_30","deviceType":"kafka","signal":98.0,"time":1616817201390}'
-- 1. 建表, 上传表数据.
create table test1_json(
    json string
);
-- 2. 查询表数据.
select * from test1_json;

-- 3. 处理json.
-- 3.1 get_json_object()函数处理, 只能逐个处理, 属于 UDF函数, 一进一出.
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 test1_json;

-- 3.2 json_tuple()函数处理, 批量处理, 属于 UDTF函数, 一进多出.
select
    json_tuple(json, 'device', 'deviceType', 'signal', 'time')  as (device, deviceType, signal, `time`)
from test1_json;

-- 3.3 上述json_tuple()的变形写法, 可以用侧视图(lateral view)来存储处理后的数据, 进行查询.
select device, deviceType, signal, `time` from test1_json
-- 侧视图格式       侧视图存储的内容                                     侧视图名字       侧视图中每一项(列)的别名
lateral view json_tuple(json, 'device', 'deviceType', 'signal', 'time') lv as device, deviceType, signal, `time`;


-- 思路2: 在建表的时候, 直接对 json字符串进行处理.
-- 1. 建表(直接对json进行处理, 采用指定的SerDe类, 即: JsonSerDe), 上传表数据.
create table test2_json(
    device string,
    deviceType string,
    signal string,
    `time` string
) row format serde 'org.apache.hive.hcatalog.data.JsonSerDe';     -- 如果不用默认的LazySimpleSerDe类, 采用指定SerDe类处理.

-- 2. 查询表数据.
select * from test2_json;

cte表达式

概述:
    全称叫 Common Table Expression, 公共表表达式, 用来(临时)存储表结果的, 后续可以重复使用.
格式:
    with CTE表达式的别名 as (
        被CTE所存储的内容, 即: SQL查询语句
    )
    select ... from  cte表达式别名;

-- 1. CTE表达式入门.
with t1 as (
    select * from stu
)
select * from t1;

-- 2. from风格.
with t1 as (
    select * from stu
)
from t1 select name, age;

-- 3. 链式写法(链式编程)
with t1 as (select * from stu),
     t2 as (select * from t1 where id > 95010),
     t3 as (select id, name, gender,age from t2 where id > 95010)
select name, gender from t3;

-- 4. CTE表达式结合 union 使用.
with t1 as (
    select * from stu
)
select * from t1
union  all      -- 合并, 不去重.
select * from t1 limit 3;       -- 44条 => 3条

-- 5. 用表 把 CTE的结果 永久存储.
create table hg1 as
with t1 as (
    select * from stu
)
select id, name, age from t1;

select * from hg1;

-- 6. 用视图 把 CTE的结果 "永久"存储.
create view hg2 as
with t1 as (
    select * from stu
)
select id, name, age from t1;

select * from hg2;

窗口函数相关

概述:
    窗口函数指的是 over()函数, 它可以结合特定的函数一起使用, 完成不同的功能.
目的/作用:
    窗口函数 = 给表新增一列, 至于新增的内容是什么, 取决于窗口函数和什么函数一起使用.
格式:
    能和窗口函数一起使用的函数 over(partition by 分组字段 order by 排序字段 rows between 起始行 and 结束行)
能和窗口函数一起使用的函数解释:
    聚合函数: count(), sum(), max(), min(), avg()
    排序函数: row_number(), rank(), dense_rank(), ntile()
    其它函数: lag(), lead(), first_value(), last_value()
注意:
    1. 窗口函数相当于给表新增一列, 至于新增的内容是什么, 取决于窗口函数和什么函数一起使用.
    2. 如果不写partition by, 表示: 统计表中所有的数据, 如果写了 表示统计组内所有的数据.
    3. 如果不写order by, 表示: 统计组内所有的数据, 如果写了, 表示统计组内第一行截止到当前行的数据.
    4. rows between表示统计的范围, 它可以写的关键字如下:
        unbounded preceding   第一行
        unbounded following   最后一行
        n preceding           向上几行
        n following           向下几行
        current row           当前行
    5. ntile(数字)表示几分之几, 里边的数字表示把数据分成几份, 如果不够分, 优先参考最小分区.
       例如: 7条数据分成3份, 则最终结果为:  1, 1, 1    2, 2    3, 3

--  ------------------------------------- 以下是窗口函数案例 窗口 + 聚合 --------------------------------
--  补充概述: PV: Page View 页面浏览量, UV: user view, 用户访问数 IP: ip(独立访客)访问数
--  例如: 我通过我电脑的谷歌浏览器访问了京东的10个页面, 通过Edge浏览器访问了京东的7个页面, 请问: PV, UV, IP分别是多少?
--  答案: IP: 1个, UV: 2个, PV: 17个

--  案例2: 演示 窗口函数 + 聚合函数一起使用.
--  需求:求出网站总的pv数 所有用户所有访问加起来
--  如果over()里边什么都不写, 默认操作的是: 表中 该列所有的数据.
select *, sum(pv) over() as total_pv from website_pv_info;      -- 不写partition by 统计表中所有的数据.

--  需求: 求出每个用户总pv数
--  方式1: sum() + group by 一起使用.
select cookieid, sum(pv) 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;      -- 写partition by 统计组内所有的数据.

--  细节: 如果写了order by(表示排序):     则默认操作 组内第一行 至 当前行的数据.
select *, sum(pv) over(partition by cookieid order by createtime) as total_pv from website_pv_info;      -- 写order by 统计组内 第一行 截止到 当前行的数据..

--  上述的代码, 等价于如下的内容:
select *, 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 * from website_pv_info;
select *, sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following ) as total_pv from website_pv_info;

--  ------------------------------------- 以下是窗口函数案例 窗口 + 排序 --------------------------------
--  需求: 根据点击量(pv)做排名, 组内排名.
--  这里的排序函数指的是: 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) rn,
       rank() over (partition by cookieid order by pv desc) rk,
       dense_rank() over (partition by cookieid order by pv desc) drk
from website_pv_info;

--  需求: 根据cookieID进行分组, 获取每组点击量最高的前4名数据, 这个就是经典的案例: 分组求TopN
--  Step1: 根据cookieID进行分组, 根据点击量进行排名.
select
       *,
       dense_rank() over (partition by cookieid order by pv desc) drk
from website_pv_info where drk <= 4;        -- 报错.
--  细节: where只能筛选表中已经有的列(数据)
--  Step2: 把上述的查询结果当做一张表, 然后从中获取我们要的数据即可.
with t1 as (
    select
       *,
       dense_rank() over (partition by cookieid order by pv desc) drk
    from website_pv_info
)
select * from t1 where drk <= 4;

--  ntile(数字,表示分成几份)  采用均分策略, 每份之间的差值不超过1, 优先参考最小的那个部分, 即: 7分成3份, 则是: 3, 2, 2
select
       *,
       ntile(3) over (partition by cookieid order by pv desc) nt
from website_pv_info;


-- 需求: 按照cookieid分组, 按照点击量降序排列, 只要每组前三分之一的数据.
with t1 as (
    select
           *,
           ntile(3) over (partition by cookieid order by pv desc) nt
    from website_pv_info
)
select * from t1 where nt = 1;

select * from website_pv_info;


--  ------------------------------------- 以下是窗口函数案例 窗口 + 其它 --------------------------------
--  1. LAG 用于统计窗口内往上第n行值
--  需求: 显示用户上一次的访问时间, 格式:  lag(字段, n, 默认值)  向上获取字段的第n个值, 如果没有写写默认值, 找不到就是null, 如果写了默认值, 找不到就用默认值.
select
       *,
       -- 向上1个, 找到就显示, 找不到就显示为 null
       lag(createtime) over(partition by cookieid order by createtime) `lag1`,
       -- 向上2个, 找到就显示, 找不到就用默认值: '2023-05-20 10:52:05'
       lag(createtime, 2, '2023-05-20 10:52:05') over(partition by cookieid order by createtime) `lag2`
from website_url_info;

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

--  2. LEAD 用于统计窗口内往下第n行值
select
       *,
       lead(createtime) over(partition by cookieid order by createtime) `lead1`,
       lead(createtime, 2, '夯哥') over(partition by cookieid order by createtime) `lead2`
from website_url_info;

--  3. FIRST_VALUE 取分组内排序后,截止到当前行,第一个值
select
       *,
       first_value(createtime) over(partition by cookieid order by createtime) `first_value`
from website_url_info;

--  4. LAST_VALUE  取分组内排序后,截止到当前行,最后一个值
select
       *,
       last_value(createtime) over(partition by cookieid order by createtime) `last_value`
from website_url_info;