hive全套语法总结以及个别函数剖析

陆离a

已于 2022-11-18 13:51:59 修改

阅读量1.8k

点赞数 3

文章标签： hive sql 数据库

于 2020-11-12 16:00:45 首次发布

本文链接：https://blog.csdn.net/qq_43712538/article/details/109646006

版权

如何编写复杂sql

推荐此下网址:
https://cloud.tencent.com/developer/article/1033405
https://blog.csdn.net/zpn78163com/article/details/51450665
https://blog.csdn.net/u014132947/article/details/80530371
https://zhuanlan.zhihu.com/p/47528345

https://www.cnblogs.com/wucongzhou/p/12496294.html#1%E3%80%81%E5%8D%95%E8%A1%8C%E5%87%BD%E6%95%B0			--Oracle常用函数

Hive语法总结

1.nvl（【空值处理）

如果expr1为NULL，返回值为 expr2，否则返回expr1

格式:NVL(expr1,expr2)

2.GROUP BY

姓名相同的显示一次

SELECT name from test GROUP BY name

3.case when

如果字段名满足条件一,输出结果一,否则输出结果二

case when 字段名 in (‘条件一’…) then ‘结果一’ else　'结果二’end as ‘别名’

4.decode（case when then）

decode(条件,值1,返回值1,值2,返回值2,…,值n,返回值n,缺省值)

decode(value,if1,then1,if2,then2,if3,then3,…,else)

5.to_date将字符类型转换为日期类型

转成日期

to_date(b.STATIS_DAY,‘yyyymmdd’) 把b.STASTIS_DAY转化成yyyymmdd日期

6.to_char将日期和数字类型转换成字符类型

转成字符串

to_char(‘2020-02-03’,‘yyyymmdd’) 把2020-02-03转化成20200203格式

7.to_char和to_data组合使用

把2020-09-12转化成20200912格式,然后将20200912转化成字符串格式,20200912,+n代表几天前,-n代表几天后

to_char(to_date(‘2020-09-12’,‘yyyymmdd’) +n,‘yyyymmdd’)

567都可以在其基础上进行计算

8.add_months日期的按月增加

据查询的过程中进行日期的按月增加

add_months(date,int);其中第一个参数为日期，第二个为按月增加的幅度
- select add_months(‘20200902’,-1); --代表昨天的数据20200901

9.union（两表合并数据，不显示重复）

剔除重复字段

SELECT name from user UNION SELECT name FROM student

10.union all（两个或两个以上的表合并数据，显示重复）

可以将2个或者多个表进行合并，每一个union 子查询都必须有相同的列，而且对应的每个字段的字段类型都是一致的。例如一表中的第一个字段是int型，那么union二表中的第一个字段也必须是int型

即使有重复数据也会显示

SELECT name from user UNION ALL SELECT name FROM student

# Union All 和Union 区别

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

11.grouping sets（分组多字段）

类似于分组

SELECT 字段一,字段二,字段三,字段… FROM 表名 where 条件

GROUPby 分组字段一,分组字段二,分组字段N…
grouping SET (分组字段一,分组字段二,分组字段N…)

order by 排序字段;

12.with…as…(临时表)

子查询部分,临时表.

以下这句SQL的意思相当于

select * from (select * from xxx);

-- with table_name as(子查询语句) 其他sql 
with temp as (
    select * from xxx
)
select * from temp;

13.算术运算符

14.聚合函数

在这里插入图片描述

15.CASE…WHEN…THEN（if…else）

CASE	
		WHEN 条件 THEN
		"显示值" 
		WHEN 条件1 AND 条件2 THEN
		"显示值"  
		ELSE "显示值" 
	END AS 列别名 FROM 表名;

16.Like和RLike模糊查询

一般适用于模糊查询

select * from 表名 where name Like ‘%liu_’

解释%代表多个字符,_代表一个字符

17.Having（Group by后的条件，类似where）

一般和Group by,他是Group by 后用来查询的

SELECT name from test GROUP BY name Having age = 18;

18.cast类型转换

cast(expression AS Type); --例: cast(‘123’ AS unsigned);

type目标类型：

- 二进制，同带binary前缀的效果 : binary
- 字符型，可带参数 : char()
- 日期 : date
- 时间: time
- 日期时间型 : datetime
- 浮点数 : decimal
- 整数 : signed
- 无符号整数 : unsigned

19.binary字符转数值

在这里插入图片描述

20.rand抽样查询（类似于java的random）

21.创建索引

create index [index_studentid] on table student_3(studentid)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
with deferred rebuild
IN TABLE index_table_student_3;

org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler ：创建索引需要的实现类

index_studentid:索引名称

student_3:表名

index_table_student_3:创建索引后的表名

22.查看索引表（index_table_student_3）没有数据

select*from index_table_student_3;

23.删除索引

DROP INDEX index_studentid on student_3;

24.查看索引

SHOW INDEX on student_3;

25.拼接字符

通过||进行拼接

26.截取

substr

substr(‘字符串’,n,m)

将字符串从n位截取到m位

27.pivot行转列

28.unpivot列转行

29.for…in

30.查找字符串位置

select instr(‘abcfdgfdhd’,‘fd’) from 表名

31.结算字符串长度

select length(‘abcdef’) from 表名

32.initcap（首字母变大写） ,lower(变小写),upper(变大写)

select lower(‘ABC’) s1, upper(‘def’) s2, initcap(‘efg’) s3 from 表名;

显示:abc,DEF,Efg

33.取整函数（ceil 向上取整,floor 向下取整）

select ceil(66.6) N1,floor(66.6) N2 from 表名;

显示:67,66

34.求余

select mod(9,5) from 表名;

9/5=1…4

显示为:4

35.to_number() 转换为数字类型

select to_number(to_char(sysdate,‘hh12’)) from dual; //以数字显示的小时数

36.rank() over()

rank()over(order by 列名排序)
- order by 排序结果不连续比如说: 1,1,1,4
dense_rank()over(order by 列名排序)
- 排序结果连续比如:1,1,1,2
rank () OVER (PARTITION BY 列名 ORDER BY 列名排序) 使用分区方式获取最高值
- 将数据分组之后进行排序

问题：分区与分组有什么区别?
•分区只是将原始数据进行名次排列(记录数不变),
•分组是对原始数据进行聚合统计(记录数变少,每组返回一条)。

剖析窗口函数:

常见的窗口函数有三个:rank, dense_rank, row_number

谈谈三者:

rank() over(partition by 分组字段 order by 排序字段 desc)
- rank()这个函数使用来排序的,他排序下来的结果是原来是:1,2,3,4.当有三人成绩相同时,就会排列成1,1,1,4名次
dense_rank(partition by 分组字段 order by 排序字段 desc)
- 这个函数是不会占用下一名次的位置:比如说原来结果是:1,2,3,4.当有三人成绩相同时,就会排列成1,1,1,2
row_number(partition by 分组字段 order by 排序字段 desc)
- 这个函数是按正常排序下来的,比如说前三名成绩相同,他排序下来是:1,2,3,4

37.limit(分页)

SELECT * FROM emp LIMIT n,m 从第n+1条,取m条数据

38.大小写转换

Lower：转换大小写混合的字符串为小写字符串
Upper：转换大小写混合的字符串为大写字符串
Initcap:将每个单词的首字母转换为大写，其他字母为小写

40.拼接字符串(concat 和 ‘||’)

select concat('Hello ',‘World’) from dual;

–等价于
select 'Hello '||‘World’ from dual;

陆离a

关注

3
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
hive全套语法总结以及个别函数剖析

如何编写复杂sql推荐此下网址:https://cloud.tencent.com/developer/article/1033405https://blog.csdn.net/zpn78163com/article/details/51450665https://blog.csdn.net/u014132947/article/details/80530371https://zhuanlan.zhihu.com/p/47528345https://www.cnblogs.com/wucong
复制链接

扫一扫