如何编写复杂sql

推荐此下网址:
https://cloud.tencent.com/developer/article/1033405


https://zhuanlan.zhihu.com/p/47528345

			--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.算术运算符

hive nvl用法 hive的nvl_sql

14.聚合函数

hive nvl用法 hive的nvl_数据库_02

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字符转数值

hive nvl用法 hive的nvl_hive nvl用法_03

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*fromindex_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;