当前位置：首页 > news >正文

Hive的基本操作（查询）

news 来源：原创 2024/9/20 19:37:14

1、基础查询

基本语法

select 字段列表|表达式|子查询
from 表(子查询|视图|临时表|普通表)
where [not] 条件A and|or 条件B				--先：面向原始行进行筛选
group by 字段A[,字段B,...]					=> 分组【去重处理】
having 聚合条件(非原始字段条件)				--再：针对聚合后的字段进行二次筛选
order|sort|cluster by 字段A[,字段B,...]		--后：全局排序(非limit的最后一句)	走mapreduce
limit N(前N条记录) | M(行号偏移量),N(记录数)

1.where子句的条件格式

一：关系运算符

关系运算符：> , >= , < , <= , =【等值判断】 , <>【不等于】

延伸：between (>=)SMALL_VALUE and (<=)BIG_VALUE; 【面向于数值或日期】

二：逻辑运算符

逻辑运算符：not【非】 , and【与】 , or【或】

延伸：

--if函数：
if(BOOLEN_EXPR,VALUE_IF_TRUE,VALUE_IF_FALSE_OR_NULL)案例：select user_id,`if`(order_amount < 1000,'low','high') as consumptionfrom test1wwhere user_gender = '女'limit 100;结果展示：user_id	consumption652,high376,high537,high280,high23,high--空值判断：
1.nvl(VALUE_A,VALUE_B)	=>	VALUE_A为空值(null),则返回VALUE_B。否则返回VALUE_A
2.isnull(VAL)		=>	如果 VAL 为 null,则返回 1 。否则返回 0--case when函数：
case EXPR when V1 then VAL1 when V2 then VAL2 ... else VALN end <=> switch ... case
case when 条件1 then VAL1 when 条件2 then VAL2 ... else VALN end <=> if ... else if ...案例：select user_id,case when order_amount<1000 then '低消费人群' when order_amount<5000 then '中等消费人群' else '高消费人群' end as levelfrom test1wwhere user_gender = '女'limit 100;结果展示：user_id	level652,高消费人群376,高消费人群537,低消费人群280,中等消费人群...

三：通配符

模糊查询：

基本语法：like '% | _'	【模糊匹配】讲解：% => 任意个任意符号_ => 一个任意符号案例：select "张无极" like '张%';		=> trueselect "张无极" like '张_';		=> false

正则匹配：

基本语法：rlike '正则表达式'如：'^//d+$'案例：select "like" rlike '^[a-zA-Z]{2,4}$';	  =>true

2.排序

1、order by 表达式[field|func|case...when...]    		---【全局排序】：性能差优化：在order by B 之前，可以先对数据进行 distribute by A 与 sort by B=> 先部分排序，后全局排序2、sort by FIELD_N 								  --在【每一个reducer端】排序解释：当reducer 的数量为1时，等同于 order byFIELD_N 必须是select字段列表中的一员一般和 distribute by 配合使用3、cluster by 		--cluster by 字段A = distribute by 字段A + sort by 字段A

3.分组

1、group by 表达式(field|func|case...when) 	--为了聚合而分组，否则类似去重(代替distinct)目的：按照某些条件对数据进行分组并进行聚合操作，使用 group by多分组：1.group by A,B,C 		grouping sets(B,(A,C),(B,C))	✔  --指定多个【分组】为：B,(A,C),(B,C)2.group by cube(A,B,C) 		--排列组合后的所有分组：A,B,C,(A,B),(A,C),(B,C),(A,B,C)3.group by rollup(A,B,C)	--最左原则的所有分组：A,(A,B),(A,B,C)2、distribute by 表达式(field|func|case...when)目的：为了将数据分区，仅仅将数据分发到多个节点上并行处理，使用 distribute by解释：1.不改变原始行数2.类似于 hadoop job 中的 Partitioner。 【默认是采用hash算法】3.指定按哪个字段的hashcode分区，配合【预先设置reducer数量】注意：distribute by【决定进哪个reducer】与sort by【在reducer中排序】一般搭配使用的distribute by通常使用在SORT BY语句之前

小型案例：

with product_total as ( select order_item_product_id product_id,sum(order_item_subtotal) totalfrom cb_order_itemsgroup by order_item_product_id
)
select product_id,total
from product_total
distribute by product_id
sort by total desc;

多分组案例

1.grouping sets 案例：✔create temporary table tmp_cb_order_ymbsc_sets asselect year,month,dept_id,cate_id,prod_idgrouping__id,sum(quantity) as quantity,round(sum(amount)) as amountfrom tmp_cb_order_ymbscgroup by year,month,dept_id,cate_id,prod_idgrouping sets(prod_id,(dept_id,cate_id),(year,month),(year,month,prod_id))order by grouping__id;-------------------------------------寻找哪几组【去重】：select grouping__idfrom tmp_cb_order_ymbsc_setsgroup by grouping__id;--------------------------------------- grouping__id:6 :	year,month,prod_id7 :	year,month25 : dept_id,cate_id 	30 : prod_id2.cube 案例：【不常用】selectyear(order_date) as year,month(order_date) as month,day(order_date) as day,count(*) as count,grouping__idfrom cb_ordersgroup by cube (year(order_date),month(order_date),day(order_date))order by grouping__id;3.rollup 案例：【不常用】selectyear(order_date) as year,month(order_date) as month,day(order_date) as day,count(*) as count,grouping__idfrom cb_ordersgroup by rollup (year(order_date),month(order_date),day(order_date))order by grouping__id;

2、子查询

基本语法

select 			可以出现子查询(查某个字段值,与主查询存在逻辑主外键关系)
from 			可以出现子查询(数据表的子集 select F1,...,FN from T where ... group by ...)
where 			可以出现子查询(FIELD in|=|>= (select ONLY_ONE_FIELD_IN ...))
group by FIELD|substr(FIELD,0,4),...
having 			可以出现子查询(FIELD in|=|>= (select ONLY_ONE_FIELD_IN ...))
order by FIELD|substr(FIELD,0,4),...

常用语法【from子查询】

select 字段列表|表达式|子查询
from(select 字段列表|表达式|子查询					 ---先进行内部的查询from TABLEwhere [not] 条件A and|or 条件B...
)												---后进行外部的查询
where [not] 条件A and|or 条件B						--后=>先：面向原始行进行筛选
group by 字段A[,字段B,...]	
order by 字段A[,字段B,...]							--后=>再：针对聚合后的字段进行二次筛选
limit N(前N条记录) | M(行号偏移量),N(记录数)		--后=>后：全局排序(非limit的最后一句)

3、CTE

基本语法

with 
SUB_ALIA as(...),
SUB_ALTER as(select...from SUB_ALIA...)
select...

小型案例

withtotal_amount as(select sum(order_amount) totalfrom hive_internal_par_regex_test1wwhere year>=2016group by user_gender, user_idhaving total>=20000),level_amount as(select round(total/10000) as levelfrom total_amount)
select level,count(*) as level_count
from level_amount
group by level;结果展示：level level_count2,1623,1254,265,5

4、联合查询

数据准备

Class表：
+-------+---------+
|classId|className|
+-------+---------+
|      1|  yb12211|
|      2|  yb12309|
|      3|  yb12401|
+-------+---------+Student表：
+-----+-------+
| name|classId|
+-----+-------+
|henry|      1|
|ariel|      2|
| jack|      1|
| rose|      4|
|jerry|      2|
| mary|      1|
+-----+-------+

三种主要形式

一：内连接【inner join】

两集合取交集：

select A.内容,....,B.内容,...                              =>字段别名：提高筛选的性能
from TABLE_A as A												
inner join TABLE_B as B
on A.主键=B.外键 (and A.fa = VALUE...)  多表√ 两表√        	=>表进行合并时进行【连接条件】
where A.fa = VALUE;                     两表√             =>合并后进行【条件筛选】
group by ...
having ...
order by ...
limit ...

小型案例：

select * from Student S
inner join Class C
on S.classId = C.classId结果展示：+-----+-------+-------+---------+| name|classId|classId|className|+-----+-------+-------+---------+|henry|      1|      1|  yb12211||ariel|      2|      2|  yb12309|| jack|      1|      1|  yb12211||jerry|      2|      2|  yb12309|| mary|      1|      1|  yb12211|+-----+-------+-------+---------+

二：外连接

左外连接【left join】

两个集合取左全集，右交集

select A.内容,....,B.内容,...                              	     =>字段别名：提高筛选的性能
from TABLE_A as A                 									【A为主表】
left [outer] join TABLE_B as B		    							【B为从表】
on A.主键|外键=B.外键|主键 (and A.fa = VALUE...)    多表√ 两表√     =>表进行合并时进行【连接条件】
where A.fa = VALUE;                                 两表√        =>合并后进行【条件筛选】
group by ...
having ...
order by ...
limit ...

小型案例：

select * from Student S
left join Class C
on S.classId = C.classId结果展示：+-----+-------+-------+---------+| name|classId|classId|className|+-----+-------+-------+---------+|henry|      1|      1|  yb12211||ariel|      2|      2|  yb12309|| jack|      1|      1|  yb12211|| rose|      4|   null|     null||jerry|      2|      2|  yb12309|| mary|      1|      1|  yb12211|+-----+-------+-------+---------+

右外连接【right join】

两集合取右全集，左交集

select A.内容,....,B.内容,...                              		=>字段别名：提高筛选的性能
from TABLE_A as A                 										【A为主表】
right [outer] join TABLE_B as B		    								【B为从表】
on A.主键|外键=B.外键|主键 (and A.fa = VALUE;)    多表√ 两表√      =>表进行合并时进行【连接条件】
where A.fa = VALUE;                               两表√         =>合并后进行【条件筛选】
group by ...
having ...
order by ...
limit ...

小型案例：

select * from Student S
right join Class C
on S.classId = C.classId结果展示：+-----+-------+-------+---------+| name|classId|classId|className|+-----+-------+-------+---------+| mary|      1|      1|  yb12211|| jack|      1|      1|  yb12211||henry|      1|      1|  yb12211||jerry|      2|      2|  yb12309||ariel|      2|      2|  yb12309|| null|   null|      3|  yb12401|+-----+-------+-------+---------+

全外连接【full join】

两集合取左右全集

select A.内容,....,B.内容,...                              		 =>字段别名：提高筛选的性能
from TABLE_A as A                 										【A为主表】
full [outer] join TABLE_B as B		    								【B为从表】
on A.主键|外键=B.外键|主键 (and A.fa = VALUE;)    多表√ 两表√       =>表进行合并时进行【连接条件】
where A.fa = VALUE;                               两表√          =>合并后进行【条件筛选】
group by ...
having ...
order by ...
limit ...

小型案例：

select * from Student S
full join Class C
on S.classId = C.classId结果展示：+-----+-------+-------+---------+| name|classId|classId|className|+-----+-------+-------+---------+|henry|      1|      1|  yb12211|| jack|      1|      1|  yb12211|| mary|      1|      1|  yb12211|| null|   null|      3|  yb12401|| rose|      4|   null|     null||ariel|      2|      2|  yb12309||jerry|      2|      2|  yb12309|+-----+-------+-------+---------+

三：交叉连接【cross join】

两集合取笛卡尔积

select A.内容,....,B.内容,...                              		 =>字段别名：提高筛选的性能
from TABLE_A as A                 										【A为主表】
cross join TABLE_B as B		    										【B为从表】
on A.主键|外键=B.外键|主键 (and A.fa = VALUE;)    多表√ 两表√       =>表进行合并时进行【连接条件】
where A.fa = VALUE;                               两表√          =>合并后进行【条件筛选】
group by ...
having ...
order by ...
limit ...

小型案例：

select * from Student S
cross join Class C
on S.classId = C.classId结果展示：+-----+-------+-------+---------+| name|classId|classId|className|+-----+-------+-------+---------+|henry|      1|      1|  yb12211||henry|      1|      2|  yb12309||henry|      1|      3|  yb12401||ariel|      2|      1|  yb12211||ariel|      2|      2|  yb12309||ariel|      2|      3|  yb12401|| jack|      1|      1|  yb12211|| jack|      1|      2|  yb12309|| jack|      1|      3|  yb12401|| rose|      4|      1|  yb12211|| rose|      4|      2|  yb12309|| rose|      4|      3|  yb12401||jerry|      2|      1|  yb12211||jerry|      2|      2|  yb12309||jerry|      2|      3|  yb12401|| mary|      1|      1|  yb12211|| mary|      1|      2|  yb12309|| mary|      1|      3|  yb12401|+-----+-------+-------+---------+

5、联合查询

何为联合查询？

纵向拼接表，高变大
查询字段的【数量】与【类型】必须相同，字段名是以【第一张表为准】。

union与union all的区分

union：合并后删除重复项（去重）
union all：合并后保留重复项 ✔

小型案例

数据准备：

语句：

select age,job from bank_client_info_3
union all
select age,job from bank_client_info_3;

北京网站建设多少钱？

辽宁网页制作哪家好_网站建设

高端品牌网站建设_汉中网站制作

盘古信息MOM系统：赋能企业打造高效数字化车间的关键

【绘唐阿祖】Ai小说推文一键生成工具功能预览极速版

AI智能名片在Web 3.0技术栈中的应用与前景研究

FastAPI 学习之路（五十）WebSockets（六）聊天室完善

近源渗透简介

5. 基于Embedding实现超越elasticsearch高级搜索

python数据可视化（6）——绘制散点图

【人工智能】Transformers之Pipeline（一）：音频分类（audio-classification）

huggingface 数据集和模型加速下载, hfdata 输出为json

实验二：图像灰度修正

【vue深入学习第1章】Vue.js 中的 Ajax 处理：vue-resource 库的深度解析

聊聊自动驾驶中的路径和轨迹

算法力扣刷题记录四十八【513.找树左下角的值】

Oralce笔记-解决Oracle18c中ORA-28001: 口令已经失效

【持续集成_05课_Linux部署SonarQube及结合开发项目部署】

----------

【391天】每日项目总结系列128（2018.03.03）

〔开发系列〕一次关于小程序开发的深度总结

30天自制操作系统-2

Android优雅地处理按钮重复点击

Angularjs之国际化

HTTP 简介

HTTP中GET与POST的区别 99%的错误认识

js对象的深浅拷贝

Node项目之评分系统（二）- 数据库设计

php ci框架整合银盛支付

Spring-boot 启动时碰到的错误

use Google search engine

vue 配置sass、scss全局变量

包装类对象

从零开始学习部署

得到一个数组中任意X个元素的所有组合即C(n,m)

多线程 start 和 run 方法到底有什么区别？

规范化安全开发 KOA 手脚架

类orAPI - 收藏集 - 掘金

-- 数据结构顺序表 --Java

腾讯视频格式如何转换成mp4 将下载的qlv文件转换成mp4的方法

新版博客前端前瞻

一些css基础学习笔记

用element的upload组件实现多图片上传和压缩

原生Ajax

在electron中实现跨域请求，无需更改服务器端设置

LevelDB 入门 —— 全面了解 LevelDB 的功能特性

好程序员web前端教程分享CSS不同元素margin的计算 ...

【数据结构与算法】冒泡排序：简单易懂的排序算法解析

#70结构体案例1（导师，学生，成绩）

$Django python中使用redis, django中使用(封装了),redis开启事务(管道)

（5）STL算法之复制

(6) 深入探索Python-Pandas库的核心数据结构：DataFrame全面解析

（附源码）springboot工单管理系统毕业设计 964158

（附源码）springboot美食分享系统毕业设计 612231

（附源码）springboot青少年公共卫生教育平台毕业设计 643214

（附源码）SSM环卫人员管理平台计算机毕设36412

（力扣记录）1448. 统计二叉树中好节点的数目

（算法）前K大的和

1、基础查询

基本语法

1.where子句的条件格式

一：关系运算符

二：逻辑运算符

三：通配符

2.排序

3.分组

多分组案例

2、子查询

基本语法

常用语法【from子查询】

3、CTE

基本语法

小型案例

4、联合查询

数据准备

三种主要形式

一：内连接【inner join】

二：外连接

左外连接【left join】

右外连接【right join】

全外连接【full join】

三：交叉连接【cross join】

5、联合查询

何为联合查询？

union与union all的区分

小型案例

相关文章：