oracle查询优化改写

单表查询

给查询结果排序

操作多张表

插入、更新、删除

使用字符串

使用数字

日期运算

日期操作

范围处理

读 “oracle查询优化改写” 一书笔记。 作者：师庆栋；罗炳森

单表查询

1：将空值转换为实际值。

关键词：coalesce 如果name为空，展示age值，age还为空，展示默认值。

SELECT coalesce(name, age, ‘默认值’) FROM stu；
等价于
SELECT  nvl( nvl(name,age),'默认值' )  FROM stu

2：为列取别名 AS 或者空格

3：where条件中需要引用取别名的列，必须嵌套一层

4：字符串拼接 使用 || 或者 concat()函数

5：select 语句中使用条件逻辑

case
    when    then
    when    then
    else
end

6：返回限制的行数 where rownum>=3

7：查询语句中 select、rownum、order by 三个关键词的执行书序一次为：select、rownum、order by

给查询结果排序

1：排序关键之 order by 列名 ASC（升序,默认为ASC）/ DESC（降序）

也可将列名换成列的序号， eg：1代表 sel1列。

select sel1,sel2,sel3 from emp order by 1;

2：多列排序，函数排序

3：TRANSLATE 关键字

语法TRANSLATE(expr,from_string,to_string) . from_string与to_string 以字符为单位，对应字符，一 一替换。若to_string 为空，则返回空；若from_string对应位置在to_string 中没有替换为空。

4：按数字和字母混合字符串中的字母排序。可以使用translate 关键词将数字、空格替换掉。

5：处理排序空值。oracle默认排序空值在最后，若想把空值放最前面，可使用 NULLS FIRST。最后面 使用 NULLS LAST。 该关键词放在order by 烈面后面。

操作多张表

1：合并数据集UNION ALL：不会除重、UNION：除重（一般和唯一列一起使用）

2：IN、EXISTIS 和 INNER JOIN。 三个PLAN中，join 利用Hash join（哈希链接） 其他两种使用hash join semi（哈希半链接）

3：多表链接

4：外链接中的条件要放在on 关键后面。

插入、更新、删除

1：倘若某表中有数列存在默认值，使用INSERT 语句时，如果插入列中包含默认值列，切该列使用默认值，需要使用 DEFAULT 关键字；如果插入列中不包含默认值列，则表中实际数据会自动添加默认值。

2：复制表的定义和数据

CREATE TABLE STU2 AS SELECT * FROM STU; 
CREATE TABLE STU2 AS SELECT * FROM STU WHERE 1=2;

3：多表插入语句

--将emp中满足 in ('10','20')的数据分别插入 emp1和emp2
insert all
    into emp1(empno,ename,job) values (empno,ename,job)
    into emp1(empno,ename,deptno) values (empno,ename,deptno)
select empno,ename,job,deptno from emp where deptno in ('10','20');
--将emp中满足job in ('SALESMAN','MANAGER') 的数据存入emp1中，并将表emp中满足deptno in ('10','20')的数据存入emp2 中
delete emp1;
delete emp2;
insert all
    when job in ('SALESMAN','MANAGER') then
        into emp1(empno,ename,job) values (empno,ename,job)
    when deptno in ('10','20') then
        into emp1(empno,ename,deptno) values (empno,ename,deptno)
select empno,ename,job,deptno from emp;
--将emp中满足job in ('SALESMAN','MANAGER') 的数据存入emp1中，并将表emp中满足deptno in ('10','20')的数据存入emp2 中，如果两个条件都满足的数据，只会在前面表中存在中
delete emp1;
delete emp2;
insert first
    when job in ('SALESMAN','MANAGER') then
        into emp1(empno,ename,job) values (empno,ename,job)
    when deptno in ('10','20') then
        into emp1(empno,ename,deptno) values (empno,ename,deptno)
select empno,ename,job,deptno from emp;

4：其它表中的值更新

--若emp表中新增字段dname，需要将dept.dname更新至emp中，且只更新部门为A和B的数据
--方法一
update emp
    set emp.dname=
        (select dept.dname from dept where dept.deptno=emp.deptno and dept.dname in ('A','B'))
where exists (select dept.dname from dept where dept.deptno=emp.deptno and dept.dname in ('A','B'));
--方法二（如果ORA-01779：无法修改与非键值保存表对应的列 错误，在dept表中加上唯一索引或主键）
update (select emp.dname,dept.dname as new_dname 
        from emp
        inner join dept on dept.deptno=emp.deptno
        where dept.dname in ('A','B'))
set dname = new_dname;
--方法三
merge into emp
    using (select dname,deptno from dept where dept.dname in ('A','B')) dept
    on dept.deptno=emp.deptno
    when matched then
    update set emp.dname = new_dname;

5：删除重复数据

--表dupes(id,name) 中name列有重复数据，需要删除重复数据
--方法一：通过name相同，id不同来除重 (id列为自增)
delete from dupes a
where exists (select null from dupes b where b.name= a.name and b.id > a.id);
--方法二：通过rowid来除重
delete from dupes a
where exists (select null from dupes b where b.name= a.name and b.rowid> a.rowid);
--方法三：通过rowid  和分组来除重
delete from dupes 
where rowid in (select rid from (
                select rowid ad rid,row_number() over (partition by order by id) as seq
                from dupes)
                where seq>1);

使用字符串

1：遍历字符串

--connect by 字句  （可以配合substr配合）
select level from dual connect by level<=4;
    level
----------
    1
    2
    3
    4
4 row selected

2：regexp_count(str,’，’)函数：计算str中有几个’，’。

3：将字符和数字分离

--数据  abc123    使用如下SQL将其分开
select regexp_replace(data,'[0-9]','') adc,
       regexp_replace(data,'^[0-9]','') 123,
    from dept;

4：查询只包含字母或数字型的数据

regexp_like(data,'[ABC]'); 相当于 like '%A%'  or like '%B%' or like '%C%';
regexp_like(data,'[0-9a-zA-Z]+'); 相当于 like '%数字%'  or like '%小写字母%' or like '%大写字母%';
1：regexp_like(data,'A'); 相当于 like '%A%'  
2: regexp_like(data,'^A'); 相当于 like 'A%'
3: regexp_like(data,'A$'); 相当于 like '%A'
4: regexp_like(data,'^A$'); 相当于 like 'A'
5: regexp_like(data,'^16+'); +号前面的6至少匹配一次，相当于 相当于 like '16%'

5：根据表中的行创建一个分隔列表

--根据表中的行创建一个分隔列表
select deptno,
       listagg(ename,',') whitin group (order by ename) as enames
from  emp
group by deptno;

6：取缔n个分隔字符串

原字符串 str=sun,da,sheng   取出da
select regexp_substr(str,'[^,]+',1,2) as newStr from  V;
^:在方括号里面表示否的意思；+表示匹配1次以上；
[^,]+ 表示匹配不包含逗号的多个字符；
1：表示从第一个字符开始
2：表示第二个能匹配 '[^,]+' 的字符串。

7：分解IP地址 192.168.1.110

select regexp_substr(v.ip,'[^,]+',1,1) as a,
       regexp_substr(v.ip,'[^,]+',1,2) as b,
       regexp_substr(v.ip,'[^,]+',1,3) as c,
       regexp_substr(v.ip,'[^,]+',1,4) as d
 from  (select '192.168.1.110' as ip from dual) V;
 --------------------------------------------
a        b        c        d
192        168        1        110

使用数字

1：常用的聚合函数

select deptno,
       AVG(sal) as 平均值,
       MIN(sal) as 最小值,
       MAX(sal) as 最大值,
       SUM(sal) as 合计,
       COUNT(*) as 总行数,
       COUNT(comm) as 获取提成的人,
       AVG(comm) as 错误的人均提成算法,
       AVG(coalesce(comm,0)) as 正确的人均提成算法  --需要把空值转换为0
from emp
group by deptno;

注意：当表中没有数据时，不加group by会返回一行数据；加group by 没有数据返回。

2：生成累计和

--注：某公司需要计算用人成本，需要对员工工资进行累加，以便于更直观的看出成本变化。（成本累计=当前行以及当前行上面所有行人工成本之和）
select empno as 编号,
       ename as 姓名,
       sal   as 人工成本,
       sum(sal) over(order by empno) as 成本累计
from emp
where deptno =30
order by empno;
---------------------------------------
sum(sal) over(order by empno)   --odery by 的列 还可以加排序方式 asc desc 
--相当于
sum("sal") over(order by "empno" range between unbounded preceding and current row);
sum("sal") over(order by "empno" rows between unbounded preceding and current row)

3：计算累计差

如下步骤实现计算累计差：

1：对数据排序。

select rownum as seq, a.*
from (select 编号,项目,金额 from tb_A order by 编号) a;

2：可以将seq=1视为收入，其他视为支出，case when 将后面金额变为负数

with  x  as
    (select rownum as seq, a.*
    from (select 编号,项目,金额 from tb_A order by 编号) a)
select 编号,项目,金额,(case seq=1 when 金额 else -金额 end) as 转换后的值 from x;

3：将转换后的值进行累加处理

with  x  as
    (select rownum as seq, a.*
    from (select 编号,项目,金额 from tb_A order by 编号) a)
select 编号,项目,金额,sum(case seq=1 when 金额 else -金额 end) over(order by seq) as 余额 from x;

4：更改累计和的值。

前面介绍累加或者累计减，再介绍一种混合场景。银行存取款余额展示。存款为加，取款为减。 将取款变为负数，进行累计，展示余额。

注意：计算出现次数最多的值；返回最值所在的行数据；计算百分比等，不常用，不一一赘述。

日期运算

1：加减日、月、年

Date类型可以直接加减天数；加减月份用add_months函数。

2：加减时、分、秒

Date类型可以直接加减天数，那么1/24就是一个小时，分钟和秒的加减类同。

3：日期间隔之时、分、秒

两个日期减法，得到的天数，乘以24就是小时，再乘以60 就是分钟，再乘以60就是秒

4：日期间隔之日、月、年

加减月份用add_months；计算月份间隔用函数months_between。

5：计算一年中周内各日期的次数

步骤：1：获取年度信息

2：计算一年有多少天

3：生成日期列表

4：转换成对应的日期

5：汇总统计

步骤：1：获取年度信息

2：计算一年有多少天

3：生成日期列表

4：转换成对应的日期

5：汇总统计

with x as
    (select to_date('2019-01-01','yyyy-mm-dd') as 年初) from dual),
    x1 as
    (select 年初,add_months(年初,12)) as 下年初 from x),
    x2 as
    (select 年初，下年初，下年初-年初 as 天数 from x1),
    x3 as
    (select 年初+(level-1) as 日期) from x2 connect by level<=天数),
    x4 as 
    (select 日期,to_char(日期,'DY') as 星期) from x3)
select 星期,count(1) as 天数 from x4 group by 星期;

6：确定当前记录和下一条记录之间相差的天数

需要用到lead() over() 分析函数； lead对应的就是lag

select deptno,
       ename,
       hiredate,
       lead(hiredate) over(order by hiredate) next_hd
from emp;

日期操作

1：sysdate 能得到的信息

2：EXTRACT 也可以取时间字段（systimestamp）中的年月日时分秒，返回值为number类型。不能去Date中的时分秒。

3：创建本月日历

枚举指定月份所有日期，并转换为对应的周信息，再按周做一次“行转列”即可

with x1 as  /*1:指定日期 如 sysdate*/
    (select to_date(sysdate,'yyyy-mm-dd') as cur_date from dual),
    x2 as /*2：取月初*/
    (select trunc(cur_date,'mm') as 月初, add_months(trunc(cur_date,'mm'),1) as 下月初 from x1),
    x3 as /*3:枚举当月所有天*/
    (select 月初+(level-1) as 日 from x2 connect by level<=(下月初-月初)),
    x4 as /*4:提取周信息*/
    (select to_char(日,'iw') as 所在周, to_char(日,'dd') as 日期, to_number(to_char(日,'d')) from x3)
select MAX( case 周几 when 2 then 日期 end)  周一,
      MAX( case 周几 when 3 then 日期 end)  周二,
      MAX( case 周几 when 4 then 日期 end)  周三,
      MAX( case 周几 when 5 then 日期 end)  周四,
      MAX( case 周几 when 6 then 日期 end)  周五,
      MAX( case 周几 when 7 then 日期 end)  周六,
      MAX( case 周几 when 1 then 日期 end)  周日
from x4
group by 所在周
order by 所在周;

3：全年日历，类似创建本月日历，有兴趣可以翻阅书籍。

4：确定指定年份季度的开始日期和结束日期

select sn as 季度
       (sn-1)*3+1 as 开始月份,
       add_months(年初,(sn-1)*3) as 开始日期,
       add_months(年初,sn*3)-1 as 结束日期
from (select trunc(to_date(年,'yyyy'),'yyyy') as 年初,sn
     from (select '2019' as 年,level as sn from dual connect by <=4) a 
)  b;

5：补充范围内丢失的值

with  x as
(select 开始年份 + (level-1) as 年份
 from (select extract(YEAR from MIN(hiredate)) as 开始年份, 
              extract(YEAR from MAN(hiredate)) as 结束年份
       from scott.emp) connect by level <=(结束年份-开始年份+1)
select x.年份,count(e.empno) 聘用人数
from x
left join scoot.emp e on (extract(YEAR from e.hiredate) = x.年份)
group by x.年份
order by 1;

范围处理

1：定位连续值得范围（可以使用表自关联或者 lead() over() ）

select proj_id as 工程号,
       proj_start as 开始日期,
       proj_end as 结束日期,
       lead(proj_start) over (order by proj_id) 下个工程开始日期
from v

把上面sql作为一个视图，然后可以加上相关条件；也可用于列之间的计算，例如：计算用户前后两次登陆时间。