时间戳

首页 > 技术文章 > Oracle-查询结果的处理

Oracle-查询结果的处理

Hedger-Lee 2020-10-07 17:03 原文

函数相关

统计上卷函数

rollup()

专门做最终的汇总统计

先对每个组分别做计算，然后最终对整个表做计算

集合运算

union all

将两个查询语句的所有结果进行拼接

union

先将结果拼接，再去除重复值

并集

intersect

取两个查询结果的交集

minus

取第一个结果有的但是第二个结果没有的数据

逻辑处理

空值处理

nvl()

专门处理空值的方法，当列的值为空的时候，设置一个默认值

nvl(列名,如果列为空时的默认值)

nvl2()

专门处理空值的方法，当列为空或者不为空的时候，都单独设置显示结果

nvl2(列名,不为空的处理,为空的处理)

逻辑判断

decode()

逻辑判断函数

decode(列名,判断条件1,条件1为真的结果,判断条件2,条件2为真的结果...,所有条件为假的结果)

sign()

计算一个数学表达式

sign(数字计算)
计算结果为正数则等于1   负数则等于-1  零则等于0

decode()+sign()逻辑判断

如果sign(数字-1000)   
如果结果=0，说明数字是1000，如果是1，数字大于1000，-1数字则小于1000

case...when...

逻辑判断语句

case 
  when 条件判断1  then  结果显示
  when 条件判断2  then  结果显示
  ...
  else  结果显示
end

伪列

rowid

每一行数据，都有存在于oracle数据库里面的唯一编号。

使用rowid进行去重

将要去重的数据进行分组，

找到每个组最小或者最大的rowid，

将除了最小或者最大rowid的行数据都删除掉，

剩下的就是去重之后的数据了

delete from dept where rowid not in
(select min(rowid) from dept group by deptno,dname,loc);

rownum

根据表的顺序，生成的一组从小到大的序号

使用rownum进行分页查询

--分页查询的应用，先将rownum那一行固定下来
select * from
(select emp.*,rownum r from emp)
 where r>=5 and r<=10;

分析函数

核心：开窗函数over()

over(partition by 分组字段 order by 排序字段)

聚合计算

使用聚合函数加上开窗函数，计算出一列新的数据

排名函数

row_number()

排名不存在相同并列的名次

即使两个的结果相同，排名也分前后

rank()

排名存在并列的名次

当两个结果相同时，名次也相同，然后会下个名次会跳过

dense_rank()

排名存在并列的名次

当两个结果相同时，名次也相同，然后会下个名次会继续被使用，不会跳过

数据平移

将数据向上或者向下挪动一行

lag(列名) 将列的数据往下挪一行

lead(列名) 将列的数据往上挪一行

同比计算

在几个不同周期内，对周期内相同的时间段进行数据的比较

--使用表连接的方法进行数据同比的计算
select a.y,a.m,(a.amount-b.amount)/b.amount 增长率 from sales a join sales b on a.m=b.m and a.y=b.y+1;

--使用lag()进行数据的下移，进行同比的计算
select a.*,(amount-last_y)/last_y 增长率 from
(select sales.*,
       lag(amount) over(partition by m order by y) last_y
  from sales) a
 where last_y is not null;

环比计算

在一个时间周期内，对相邻的时间段进行数据比较

--使用表连接的方法进行数据环比的计算
select a.y,a.m,(a.amount-b.amount)/b.amount from sales a join sales b on a.m=b.m+1;

--使用lag()进行数据的下移，进行环比的计算
select a.*,(amount-last_m)/last_m from
(select sales.*,
       lag(amount) over(order by m) last_m
  from sales) a;

行列转换

行展示的数据，转换成列展示的数据

pivot()

先找出需要的数据，

再在privot函数中进行分组聚合函数的操作，

显示需要的列

select * from 
(select 需要的数据的列 from 原来的表格名)
pivot(
聚合函数(列名) 
    for 
分组的列名 
    in 
(新的列名1,新的列名2...)
);

临时表

表里面的数据不是永久存在的

很大的表格进行表连接查询的时候，
将每个表分开进行数据的筛选，
将筛选的结果保存到临时表中，
最后再对临时表的数据，进行表的连接。
为了减少数据筛选和查询时候的数据量。

会话级临时表

本次登录的过程中，数据一直存在，如果重新登录，数据就消失了

create  global  temporary  table  临时表的名字(
列名 数据类型
)on  commit  preserve rows;

事务级临时表

操作了事务之后（commit, rollback），数据就会消失

create global temporary table 临时表名字(
列名 数据类型
)on commit delete rows;

推荐阅读