11：Hive从0到1系列学习：函数之常用的内置函数

最新推荐文章于 2022-04-06 15:15:43 发布
最新推荐文章于 2022-04-06 15:15:43 发布
阅读量498 收藏 1
点赞数 2
分类专栏： hive hadoop 文章标签： hive hadoop 数据仓库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46429290/article/details/122090916
版权
hive 同时被 2 个专栏收录
61 篇文章 4 订阅
订阅专栏
hadoop
46 篇文章 2 订阅
订阅专栏

内容目录

常用的内置函数

①空字段赋值：NVL

NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。

功能：如果value为NULL，则NVL函数返回default_value的值，否则返回value的值

如果两个参数都为NULL ，则返回NULL。

用法1：赋予一个常量

比如在员工表中就有很多空值，现在将奖金为空的员工赋值为0

select ename, sal, nvl(comm, 0) from emp;

nvl(comm, 0):当comm为null时，用0代替
在这里插入图片描述

用法2：赋予一个变量

当员工奖金为null时，用mgr代替

select ename, sal, nvl(comm, mgr) from emp;

在这里插入图片描述

②CASE WHEN THEN ELSE END

用法：
在这里插入图片描述

CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END 
---> When a = b, returns c; when a = d, return e; else return f

当a=b，返回c；当a=d，返回e；否则返回f；

③行转列

在这里插入图片描述

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

Example:

 >>> SELECT concat('abc', 'def') FROM src LIMIT 1;

 >>> 'abcdef'

将前后两个字符串连接起来

2）CONCAT_WS(separator, str1, str2,…)：

Example:
>>> SELECT concat_ws('.', 'www', array('facebook', 'com')) FROM src LIMIT 1;
>>> 'www.facebook.com'

根据某个指定字符连接起来，支持连接array和字符串

3）COLLECT_SET(col)：

它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

4）示例：

需要达成的效果把星座和血型一样的人归类到一起。结果如下：

射手座,A            猪八戒|小白龙
白羊座,A            孙悟空|唐僧
白羊座,B            黑熊怪|沙和尚

（1）准备数据

vim /opt/module/hive/datas/concat.txt

孙悟空,白羊座,A
猪八戒,射手座,A
沙和尚,白羊座,B
唐僧,白羊座,A
小白龙,射手座,A
黑熊怪,白羊座,B

（2）创建person_info表并导入数据

create table person_info(
name string, 
constellation string, 
blood_type string
) 
row format delimited 
fields terminated by ",";

在这里插入图片描述

导入数据

load data local inpath '/opt/module/hive/datas/concat.txt' into table person_info;

在这里插入图片描述

（3）按照要求查询数据

SELECT
	NAME,
	CONCAT_WS(',',constellation,blood_type) c_b
FROM person_info;

在这里插入图片描述

select 
	t.c_b,
	concat_ws('|', collect_set(t.name))
from(
    SELECT
    NAME,
    CONCAT_WS(',',constellation,blood_type) c_b
    FROM person_info
    ) t
group by t.c_b;

在这里插入图片描述

除了上述的写法，还可以在一个selct语句中实现

SELECT
concat_ws('|',COllect_set(concat_ws(',',constellation,blood_type))),
concat_ws('|',collect_set(name))
FROM person_info
group by constellation,blood_type;

在这里插入图片描述

④列转行

在这里插入图片描述

1）EXPLODE(col)：

将hive一列中复杂的array或者map结构拆分成多行
在这里插入图片描述

2）SPLIT(string str, string regex):

按照regex字符串分割str，会返回分割后的字符串数组
在这里插入图片描述

3）LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，再将多行结果组合成一个支持别名的虚拟表

4）示例

需求：将电影分类中的categroy列的数据分开

  movie	        category
《疑犯追踪》	 悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	  战争,动作,灾难

  ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
  
《疑犯追踪》      悬疑    
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》   悬疑
《Lie to me》   警匪
《Lie to me》   动作
《Lie to me》   心理
《Lie to me》   剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难

（1）数据准备

vim /opt/module/hive/datas/movie_info.txt

《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难

（2）创建hive表并导入数据

create table if not exists movie_info(
    movie string,
    category string
)
row format delimited
fields terminated by '\t';

（3）导入数据

load data local inpath '/opt/module/hive/datas/movie_info.txt' into table test.movie_info;

在这里插入图片描述

（4）按需求查询数据

select movie, category_name
from movie_info
lateral view
explode(split(category, ',')) movie_temp
as category_name;

在这里插入图片描述

⑤窗口函数

1）相关函数说明

函数名描述用法
over()指定分析函数工作的数据窗口大小，
这个数据窗口的大小可能会随着行的改变而改变
Current row: 当前行 n
preceding：往前n行数据 n
following：往后n行数据
unbounded preceding：表示从前面的起点
unbounded following：表示从后面的起点		over(partition by name)
over(order by orderdate)
over(rows between and )
over(rows between unbounding preceding and unbounded following )
over(partition by name order by orderdate
rows between n preceding and current)
lag(col,n,default_val)往前第n行数据
lead(col,n,default_val)往后第n行数据
Ntile(n)把有序窗口的行分发到指定数据的组中，各个组的编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号 注意：n必须为int类型

注意：

当有Order by ，没有WINDOW时， WINDOW的范围是，unbounded preceding and current row

select count(*) over(order by orderdate) from business where month(orderdate)=4;

在这里插入图片描述

当Order by和 WINDOW都没有时， WINDOW的范围是，unbounded preceding and unbounded following

select count(*) over() from business where month(orderdate)=4;

在这里插入图片描述

2）示例

（1）数据准备

vim /opt/module/hive/datas/business.txt

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

（2）创建hive表

create table if not exists business(
    name string,
    orderdate string,
    cost int
)
row format delimited
fields terminated by ',';

（3）导入数据

load data local inpath '/opt/module/hive/datas/business.txt' into table test.business;

在这里插入图片描述

（4）需求

  1. 查询在2017年4月份购买过的顾客及总人数
  2. 查询顾客的购买明细及月购买总额
  3. 上述的场景, 将每个顾客的cost按照日期进行累加
  4. 查询每个顾客上次的购买时间
  5. 查询前20%时间的订单信息

（5）需求实现

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

首先，如何查询出4月份购买过的顾客名单

select name from businee where month(orderdata)=4;

在这里插入图片描述

然后求出购买的总人数

select count(*) from business where month(orderdate)=4;

在这里插入图片描述

需要把所有的名字后面都添加一个总人数，使用窗口函数

select count(*) over() from business where month(orderdate)=4;

在这里插入图片描述

这种求得的总人数会把重复的也计算上去，所以分组求解

select count(*) over() from business where month(orderdate)=4;

在这里插入图片描述

将其组合在一起

select name, count(*) over() from business where month(orderdate)=4 group by name;

在这里插入图片描述

注意：如果日期并不是规则的格式，就不可以使用month进行月份判断，就可以使用分割字符substring进行处理

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

在这里插入图片描述

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

首先，如何查询顾客的购买明细

select name, count from business;

在这里插入图片描述

然后查询出月购买金额

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

在这里插入图片描述

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

将所有行进行累加

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

在这里插入图片描述

将本行和前所有行进行累加

select name, cost, sum(cost) 
over(
    partition by name 
    order by month(orderdate) 
    rows between unbounded preceding and current row) 
from business;

在这里插入图片描述

将本行只与前一行进行聚合

select name, cost, sum(cost)
over(
    partition by name
    order by month(orderdate)
    rows between 1 preceding and 1 following
)
from business;

在这里插入图片描述

将本行与后面所有行进行聚合

select name, cost, sum(cost)
over(
    partition by name
    order by month(orderdate)
    rows between current row and unbounded following
)
from business;

在这里插入图片描述

按照名字和月份分组，组内递增

select name, orderdate, cost, sum(cost)
over(
    partition by name, month(orderdate)
    order by day(orderdate)
    rows between unbounded preceding and current row
)
from business;

在这里插入图片描述

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

lag(col, n, default_val)：往前n行的数据col

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;

在这里插入图片描述

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

ntile(n)：把有序窗口的行分发到指定数据的组中，各个组的编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号

将数据按指定顺序分为n组

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

在这里插入图片描述

order by还可以指定正序或降序

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

在这里插入图片描述

⑥Rank

函数说明：

RANK() 排序相同时会重复，总数不会变

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

ROW_NUMBER() 会根据顺序计算

有这么一张表的数据，按需要进行处理
在这里插入图片描述

1）按照学科对分数进行排序，重复时顺序相同，总数不变

select 
name, 
subject, 
score,
rank() over(partition by subject order by score desc) sorts
from score;

在这里插入图片描述

2）按照学科对分数进行排序，重复时顺序相同，总数减少

select 
name, 
subject, 
score,
dense_rank() over(partition by subject order by score desc) sorts
from score;

在这里插入图片描述

3）按照学科对分数进行排序，重复时顺序排列，总数不变

select 
name, 
subject, 
score,
row_number() over(partition by subject order by score desc) sorts
from score;

在这里插入图片描述

牧码文
关注
  • 2
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 3
    评论
专栏目录
大数据学习：Hive函数.pdf
12-07
Hive函数学习指南 Hive是一个基于Hadoop的数据仓库工具，用于存储、查询和分析大规模数据。Hive提供了丰富的内置函数，方便用户对数据进行处理和分析。本文将详细介绍Hive函数的应用，包括聚合函数、数学函数、集合...
hive Sql列转行使用explode的注意事项-null值处理
最新发布
Tate的博客
09-16 1263
ive Lateral View explode字段值为空时，导致数据异常丢失解决方案
sql窗口函数
yatou50的专栏
07-01 646
与聚集函数一样，窗口函数也针对定义的行集（组）执行聚集，但它不像聚集函数那样每组之返回一个值，窗口函数可以为每组返回多个值。实际上，DB2中称这种函数为联机分析处理OLAP函数，而Oracle把它们称为解析函数，但ISO SQL标准把它们称为窗口函数。窗口函数一般在OLAP分析、制作报表过程中会使用到。窗口函数：聚合函数 over()聚合函数 over(partition by 字段）—分区聚合函数 over(order by 字段)--框架字句本文以Oracle11g中HR模式下的Employees表为例
Hive Lateral View explode列为空时导致数据异常丢失
雾岛与鲸的博客
04-06 1691
1.问题描述 日常工作中我们经常会遇到一些非结构化数据，因此常常会将Lateral View 结合explode使用，达到将非结构化数据转化成结构化数据的目的，但是该方法对应explode的内容是有非null限制的，否则就有可能造成数据缺失。 SELECT name,info FROM ( SELECT name, split(info_list,',') as info_arrary FROM ( select '张三' as name,'1,
Hive: 函数大全
mapplei的博客
07-28 748
一、关系运算: -- 1. 等值比较:= 语法:A=B 操作类型:所有基本类型 描述: 如果表达式 A 与表达式 B 相等，则为 TRUE;否则为 FALSE 举例: hive> select 1 from lxw_dual where 1=1; -- 2. 不等值比较:<> 语法: A <> B 操作类型: 所有基本类型 描述: 如果表达式 A 为 NULL，或者表达式 B 为 NULL，返回 NULL;如果表达式 A
hive函数参考手册，包含常用的hive内置函数.doc
09-24
Hive 函数是 Hive 中的内置函数，用于对数据进行处理和分析。这些函数可以分为四大类：关系运算符、算术运算符、逻辑运算符和复杂类型函数。 关系运算符用于比较两个值是否相等、不等、或是否包含于某个范围内等。...
快速学习-Hive函数
01-20
Hive函数的学习是理解和运用Hive的关键部分。熟练掌握内置函数的使用，并学会创建自定义函数，能够使你在数据处理中游刃有余。无论是处理大数据集还是进行复杂的数据分析，Hive的函数库都能提供强大的支持。通过不断...
第5章：Hive函数重要应用案例1
08-08
【Hive函数重要应用案例1】本章主要探讨在Hive中处理特殊数据格式的技巧，特别是涉及多字节分隔符和字段内包含分隔符的问题。在Hive中，通常使用单字节分隔符（如逗号、制表符）来加载文本数据，但在实际场景中，...
hive函数大全.doc
09-23
Hive 是一个基于 Hadoop 的数据仓库工具，提供了许多内置函数来帮助用户快速处理和分析数据。下面是 Hive 中的一些常用函数，可以与实际应用结合使用。 关系运算 关系运算是 Hive 中最基本的操作之一，用于比较两...
Hive列转行函数
weixin_44516261的博客
10-03 2857
Hive列转行函数 select split("a,b,c,d",','); 效果: select explode(split("a,b,c,d",',')); 效果: 案例: 原始数据 movie category 《疑犯追踪》 悬疑,动作,科幻,剧情 《Lie to me》 悬疑,警匪,动作,心理,剧情 《战狼2》 战争,动作,灾难 需求 《疑犯追踪》 悬疑 《疑犯追踪》 动作 《疑犯追踪》 科幻 《疑犯追踪》 剧情 《Lie
hive列转多行LATERAL VIEW explode
weixin_38842096的博客
11-29 1405
lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表. 数据：以制表符分隔 movie category 《疑犯追踪》 悬疑,动作,科幻,剧情 《Lie ...
hiveql列转行使用explode的注意事项-null值处理
u013200776的专栏
05-22 1211
一、订单表如下： order_id product_name 1 铅笔，苹果，抽纸 2 3 苹果，草莓，猕猴桃 二、统计每种商品被购买次数，需要将product_name列转行，其中order_id等于2的是异常数据product_name为null。 如果写成下面这样，一条结果都没有： -- explode里面的字段不能为null，否则一条数据结果记录都没有了 select product_name_element -- 产品名称 ...
Hive Lateral View explode字段值为空时，导致数据异常丢失解决方案
qq_35684452的博客
03-08 3169
1.问题描述 日常工作中，我们经常会用Lateral View 结合explode把非结构化数据转化成结构化数据，但是该方法对应explode的内容是有非null限制的，否则就有可能造成数据缺失。 现有数据 目标结果 name info name info 韩立 1，2，3 韩立 1 厉飞雨 null 韩立 2 韩立 3 厉飞雨 nu
hivesql之lateral view explode遇到记录为空情况
C_time的博客
11-29 2784
在正常解析一个有值的字符串时，用lateral view explode是完全ok的，但是，当遇到该字符串为空时，如果在使用该函数，就会导致该条记录消失。这时候需要加个outer lateral view outer explode ...
hive load data inpath 空目录_Hive基础学习
weixin_39968128的博客
11-21 1554
本节我们主要来学习一些hive的命令操作，同时探究一下Hive，HDFS，MySQL之间的联系，从而更好的理解其内部原理。常用的基础命令#此处的命令都是指在hive命令行下执行的命令，所有的命令别忘记以分号结尾。 hive> show databases;#查看当前数据库列表 hive> create databases dbname;#创建一个名称为dbname的数据库 hive&g...
[Hive基础]-- hive空值判断
欢迎来到我的博客，一起探索代码里的世界！
07-18 1万+
hive中空值判断基本分两种： 1、NULL 与 \Nhive在底层数据中如何保存和标识NULL，是由            alter table name SET SERDEPROPERTIES('serialization.null.format' = '\N'); 参数控制的比如：         (1).设置 alter table name SET SERDEPROPERTIES...
Hive条件判断
热门推荐
u012378570的专栏
03-15 2万+
Hive条件判断 if,coalesce,case…when
记录查询HIVE表的条目数为0，select* 却可以看到数据的异常
越疆
01-10 936
一个现象： SELECT * FROM &lt;tbl_name&gt; 是有数据的 但是 SELECT COUNT(*) FROM &lt;tbl_name&gt; 结果为 0 原因是字段不匹配。需要检查字段名称或者字段个数。
Hive内置函数详解：从关系到日期运算
"这篇文档详尽地介绍了Hive的内置函数，涵盖了关系运算、数学运算、逻辑运算以及数值计算和日期函数等多个方面，是Hive用户进行数据分析时的重要参考资料。" 在Hive中，内置函数提供了丰富的数据处理能力，使得在大...
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

牧码文

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值