如何编写复杂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.算术运算符
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(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 表名;
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;
org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler ：创建索引需要的实现类
index_studentid:索引名称
student_3:表名
index_table_student_3:创建索引后的表名
22.查看索引表（index_table_student_3）没有数据
- select
*from
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.月环比
月环比=本月/上月
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
6877
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
