Hive语法总结_hive to_char

如何编写复杂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常用函数
1
2
3
4
5
6
7

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
1

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

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

即使有重复数据也会显示

SELECT name from user UNION ALL SELECT name FROM student
1

# 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;
1
2
3
4
5

13.算术运算符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YQHirwWG-1612752574834)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1600868378976.png)]

14.聚合函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SGsMX5yd-1612752574836)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1600869140101.png)]

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

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-d8p6904v-1612752574838)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1600911704989.png)]

19.binary字符转数值

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k3sZ1PFD-1612752574840)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1600911734024.png)]

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;
1
2
3
4

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;

二.实例总结

1.截取上周时间

where  dt >= to_char(to_date(?{f_day} ,'yyyymmdd')-7,'yyyymmdd')
and dt <=to_char(to_date(?{t_day} ,'yyyymmdd')-7,'yyyymmdd')	
1
2

三.计算公式

1.日环比

日环比 = 今天/昨天

2.月环比

月环比=本月/上月

3.年环比

年环比=本年/去年

4.日同比

日同比=今年的今天/去年的今天

5.月同比

月同比=今天的本月/去年的本月

6.上周用户本周留存率

上周用户本周留存率 = 上周活跃本周留存用户数/活跃用户数

7.播放转化率

播放转化率= 播放用户数/活跃用户数

8.上周活跃留存率

上周活跃留存率=上周活跃本周留存用户数/上周活跃用户数

9.同比增长率

同比增长率=（本期数-同期数）/|同期数|×100%

10.环比增长率

环比增长率=（本期数-上期数）/上期数×100%

11.完播率

完播率 = 播放完成度达60%及以上的播放用户(>=3s) / 播放用户数(>=3s)

注释：play_reach_standard_num / play_num

播放完成度/用户数

四.注意点

case when和decode使用区别

case when…then…else…end…as…一般用于多个字段进行判断

decode一般用于一个字段进行判断

SQL的执行顺序:

• 判断是从什么地方来的表(from)
• 判断是否有外联,判断外联条件(on)
• 判断连接形式(left right inner)
• 判断SQL语句的条件(where)
• 判断分组(group by)
• WITH CUBE or WITH ROLLUP
• Having 具有group by的情况下才可以使用
• 判别查询字段(select)
• 去重(distinct)
• 排序(order by)
• top