hive的DDL和DML操作(四)

最新推荐文章于 2024-08-16 11:28:57 发布
最新推荐文章于 2024-08-16 11:28:57 发布
阅读量100 收藏
点赞数
文章标签： hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45631932/article/details/120831394
版权

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

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 类型。

2.案例说明

（1）数据准备
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）创建表并加载数据

create table business(name string,orderdate string,cost int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath '/opt/moudle/hive/data/business.txt' into table business;

（3）按需求查询数据

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

substring(str, pos[,len]) 函数说明：

select substring(orderdate,0,2) from business;#取orderdate的前两个字

在这里插入图片描述

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

select name,count(*) over() from business group by name;
select name,count(*) from business group by name;
#有over()的情况下，是统计一共有多少个不同的name，name字段中一共有4个
#不同的人名，因此返回的值是4
#没有over()的情况下，是统计每个name出现的次数

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

#查询顾客的购买明细及购买总额
select name,orderdate,cost,sum(cost) 
over(partition by name) # partition by 只能在over中使用
from business; #partition by 相当于对name进行了分组，先分组在统计

#查询顾客的购买明细及顾客月购买总额
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 orderdate)#按name分组
#order by 对窗口内进行排序，并对每个日期进行累加
from business;
#还可以写成：
select name,orderdate,cost,sum(cost)
over(partition by name order by orderdate 
rows between unbounded preceding and current row)
from business;
#如果over内没有添加窗口指令，默认是从开始到当前行。

在这里插入图片描述
前一行cost和当前行的cost以及后一行的cost相加

select name,orderdate,cost,sum(cost)
over(partition by name order by orderdate 
rows between 1 PRECEDING and 1 FOLLOWING)
from business;
#第一行cost=10，前一行没有数据，后一行数据为46，则两个相加就是得到的结果
#第二行cost=46，前一行cost=10，后一行cost=55，则三个数相加就是最后结果

在这里插入图片描述

select name,orderdate,cost,
sum(cost) over() as sample1,
--所有行相加
sum(cost) over(partition by name) as sample2,
--按 name 分组，组内数据相加 
sum(cost) over(partition by name order by orderdate) as sample3,
--按 name 分组，组内数据累加
sum(cost) over(partition by name order by orderdate 
rows between UNBOUNDED PRECEDING and current row ) as sample4,
--和 sample3 一样,由起点到 当前行的聚合
sum(cost) over(partition by name order by orderdate 
rows between 1 PRECEDING and current row) as sample5, 
--当前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate 
rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,
--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate 
rows between current row and UNBOUNDED FOLLOWING ) as sample7 
--当前行及后面所有行
from business;

rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分 区中的数据行数量

4）查看顾客上次的购买时间
LAG(col,n,default_val)：往前第 n 行数据
default_val:默认值，如果是空值，将填充该数据

select name,orderdate,lag(orderdate,1)
over(partition by name order by orderdate)
from business;

在这里插入图片描述

select name,orderdate,lag(orderdate,1,orderdate)
over(partition by name order by orderdate)
from business;

在这里插入图片描述
LEAD(col,n, default_val)：往后第 n 行数据

select name,orderdate,lead(orderdate,1,orderdate)
over(partition by name order by orderdate)
from business;

在这里插入图片描述

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

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

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

在这里插入图片描述

3.RANK函数

（1）函数说明

RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

（2）案例说明

1)数据准备

孙悟空 语文 87
孙悟空 数学 95
孙悟空 英语 68
大海 语文 94
大海 数学 56
大海 英语 84
宋宋 语文 64
宋宋 数学 86
宋宋 英语 84
婷婷 语文 65
婷婷 数学 85
婷婷 英语 78
2）创建表并导入数据

create table score( name string, subject string, score int)
row format delimited fields terminated by "\t";

load data local inpath '/opt/moudle/hive/data/score.txt' into table score;

3)计算每门学科成绩排名

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

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

在这里插入图片描述
DENSE_RANK() 排序相同时会重复，总数会减少

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

在这里插入图片描述
ROW_NUMBER() 会根据顺序计算

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

在这里插入图片描述
4）求出每门学科前三名的学生

select name,subject,score
from(select *, 
rank() over(partition by subject order by score desc) rk
from score) t1
where rk<=3;

在这里插入图片描述

4.其他常用函数

日期函数

（1）获取当前时间current_timestamp()

select current_timestamp();

（2）获取当前时间戳unix_timestamp()

select unix_timestamp()

（3）时间戳转回日期格式from_unixtime()

select from_unixtime(1634560912);

（4）获取当前日期current_date()

select current_date();

（5）获取中间日期

select to_date('2021-10-18 20:48:21');

（6）获取年、月、日、时、分、秒

select year('2021-10-18 20:48:21');
select month('2021-10-18 20:48:21');
select day('2021-10-18 20:48:21');
select hour('2021-10-18 20:48:21');
select minute('2021-10-18 20:48:21');
select second('2021-10-18 20:48:21');

（7）两个日期之间的月份 months_between

select months_between('2021-08-21','2021-03-21');

（8）日期加减月add_months

select add_months('2021-02-22',3);

（9）计算两个日期相差的天数datediff

select datediff('2021-10-1','2021-10-8');

（10）日期加天数date_add

select date_add('2021-10-01',3);

（11）日期减天数date_sub

select date_sub('2021-10-01',3);

（12）日期当月的最后一天last_day

select last_day('2021-10-2');

（13）日期格式化

select date_format('2021-10-2','yyyy/mm/dd');

常用取整函数
（1）round四舍五入

select round(3.14159);

在这里插入图片描述

（2）ceil向上取整

select ceil(3.14);

在这里插入图片描述

（3）floor向下取整

select floor(3.14);

在这里插入图片描述
常用字符串操作函数
（1）upper转大写

select upper("abc");

（2）lower转小写

select upper("ABC");

（3）length长度

select length("abc");

（4）trim前后去空格

select trim("   abc  ");

（5）lpad向左补齐，到指定长度

select lpad('de',5,'abc');

在这里插入图片描述

（6）rpad向右补齐，到指定长度

select rpad('ab',5,'cde');

在这里插入图片描述

（7）regexp_replace使用正则表达式匹配目标字符串，匹配成功后替换

select regexp_replace('2021/10/19','/','-');

在这里插入图片描述
集合函数
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

create table map_test( 
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ',' 
collection items terminated by '_'
map keys terminated by ':' lines terminated by '\n';

（1）size 集合中元素的个数

select size(friends) from map_test;

在这里插入图片描述

（2）map_keys 返回map中的key

select map_keys(children) from map_test;

在这里插入图片描述

（3）map_values 返回map中的values

select map_values(children) from map_test;

在这里插入图片描述

（4）array_contains 判断array中是否包含某个元素

select array_contains(friends,'bingbing') from map_test;

在这里插入图片描述

（5）sort_array 将array中的元素排序

select sort_array(friends) from map_test;

在这里插入图片描述
hive实现wordcount

hello,world
hello,hive,spark,hadoop
hello,zookeeper,spark,flink
hive,spark,hadoop

create table input(words string);

load data local inpath '/opt/moudle/hive/data/input.txt' into table input;

hive实现

select word,count(*) from
(select explode(split(words,',')) word from input) t1
group by word;

在这里插入图片描述

5.自定义函数

根据用户自定义函数类别分为以下三种：
（1）UDF（User-Defined-Function）一进一出
（2）UDAF（User-Defined Aggregation Function）聚集函数，多进一出 类似于：count/max/min
（3）UDTF（User-Defined Table-Generating Functions） 一进多出

可以参考尚硅谷的课程
https://www.bilibili.com/video/BV1EZ4y1G7iL?p=87&spm_id_from=pageDriver

weixin_45631932
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Hive之DML数据操作
01-07
Hive简介：https://blog.csdn.net/weixin_45102492/article/details/104418259 Hive安装部署 ： ...Hive之DDL数据定义： https://blog.csdn.net/weixin_45102492/article/details/91349323 Hive
Hive DDL 之 表
01-07
创建表 语法： CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], …)] [COMMENT table_comment] [PARTITIONED BY (col_name data_type [COMMENT col_comment], …)...
大数据之Hive:DML数据操作(四)
weixin_43597208的博客
01-13 132
1.查询(查) 1-1.全表查询 hive (default)> select * from emp; 1-2.选择特定列查询 hive (default)> select empno, ename from emp; 2.删除和更改 删除 delete from stu where id=1; 修改 update stu set name=zhangsan where id = 1; 特别注意： 如果一个表要实现update和delete功能，该表就必须支持ACID，而支持ACID，就
Hive DDL
qq_41953856的博客
11-16 1145
hive 4.0.0 修改表/分区/列 修改表 修改表名 ALTER TABLE old_table_name RENAME TO new_table_name; 从0.6版本开始，对内部表的重命名将移动其在HDFS的位置。从Hive 2.2.0 开始重命名已更改。 只有在没有 LOCATION 子句且在其数据库目录下创建表时，才会移动内部表的 HDFS 位置。 0.6 之前的 Hive 版本只是在不移动 HDFS 位置的情况下重命名了 Metastore 中的表。 修改表Properties ALT
Hive的 DDL和 DML操作总结
夜雨寒霜
12-27 5744
目录 1、Hive的DDL（data define language） 1.1、库的操作 1.2、表的操作 1.2.1、创建表 1.2.2、查看表的描述信息 1.2.3、查看表的列表 1.2.4、表的修改 1.2.5、表/分区 数据的清空 1.2.6、删除表 1.2.7、查看详细建表语句 2、Hive的DML（data managed language）操作 2.1、表的数...
Hive 表 DML 操作
qq_71139242的博客
10-25 3850
Hive 表 DML 操作；load
Hive DDL及DML操作
Transkai
03-24 100
一.修改表 增加/删除分区 语法结构 ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ... partition_spec: : PARTITION (partition_col = partiti...
Hive DDL和DML
zhongmingyu
04-10 597
在关系型数据库管理系统（RDBMS）中，（Data Definition Language）和（Data Manipulation Language）是两类不同类型的SQL语句，用于对数据库进行不同的操作。DML（Data Manipulation Language）：DML语句用于对数据库中的数据进行操作，包括查询、插入、更新和删除数据。需要注意的是，DDL语句会对数据库的结构进行修改，可能会对数据库的完整性和安全性产生重大影响，因此通常需要具有足够权限的用户来执行。
Hive之DDL、DML数据操作
lslslslslss的博客
12-22 910
目录 一、DDL(Data Definition Language 数据库定义语言) 1.创建数据库 2.查询数据库 3.修改数据库 4.删除数据库 5.创建表 管理表（内部表） 外部表 两个表的相互转换 修改表 删除表 清除表 二、DML数据操作 1.数据导入 向表中装载数据（Load） 通过查询语句向表中插入数据（Insert） 查询语句中创建表并加载数据（As Select） 创建表时通过Location指定加载数据路径 Import数据到指定Hive表中 2.
Hive数据操作DDL、DML及语法
a1007904449的博客
08-12 320
除了distribute by 的功能外，还会对该字段进行排序，所以cluster by = distribute by + sort by。比如按照name属性分为3个桶，就是对name属性值的hash值对3取摸，按照取模结果对数据分桶。==注意：==连接 n个表，至少需要n-1个连接条件。内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。一起使用，按照一个或者多个列对结果进行分组，然后对每个组执行聚合操作。Hive支持通常的SQL JOIN语句，但是只支持等值连接，...
Hive 的DDL和DML
AceCream的博客
10-30 426
本文是跟着这个学习的 https://www.bilibili.com/video/av65556024?p=1 DDL 1.库 建库： > create database if not exists 库名; 还有一个方式： > create database if not exists 库名 location 路径; 指定hdfs路径 查看数据库： > show d...
Hive DML
01-07
Hive不支持update的操作。数据一旦导入，则不可修改。要么drop掉整个表，要么建立新的表，导入新的数据。 load：加载数据到表 load data [local] inpath ‘filepath’ overwrite | into table 表名 [partition ...
hive基本操作
05-16
hive 基本操作的命令和hive 实用命令等，有利与学习hive和mysql
Hive语法详解
02-11
Hive SQL支持大多数传统关系型数据库所支持的SQL语句类型，包括DDL（Data Definition Language）、DML（Data Manipulation Language）和DQL（Data Query Language）等。此外，Hive还支持一系列内置函数和用户自定义...
spring注册DispatcherServlet的过程
weixin_42145727的博客
08-13 377
spring注册DispatcherServlet的过程
Servlet的三种写法
daibadetianshi的博客
08-16 340
Java Servlet（服务端小应用程序）是一种基于 Java 的技术，用于处理来自 Web 客户端的 HTTP 请求，并生成相应的响应。Servlet 可以看作是服务器端的 Java 应用程序组件，它们扩展了 Web 服务器的功能，使其能够处理复杂的业务逻辑和动态内容生成。
VB+ACCESS宾馆客房管理系统(系统+论文+封面).zip
最新发布
08-16
计算机毕业设计资源包含（项目部署视频+源码+LW+开题报告等等），所有项目经过助教老师跑通，有问题可以私信博主解决，可以免费帮部署。
自密实混凝土的研究进展.doc
08-16
自密实混凝土的研究进展.doc
基于Andriod条码识别的商品价格查询软件APP设计与实现.docx
08-16
基于Andriod条码识别的商品价格查询软件APP设计与实现.docx
列举出hive所有的ddl、dml语句例子
06-02
以下是一些常见的Hive DDL（数据定义语言）和DML（数据操作语言）语句的例子： DDL语句： 1. 创建数据库 ``` CREATE DATABASE IF NOT EXISTS my_database; ``` 2. 删除数据库 ``` DROP DATABASE IF EXISTS my_...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值