OracleSQL优化一(SQL使用技术)

1、行列转换:

  decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值);

 

  select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; –取较小值

 

  sign()函数根据有个别值是0、正数还是负数,分别再次来到0、壹,-1

  例如:

  变量1=10,变量2=20

  则sign(变量1-变量2)重临-1,decode解码结果为“变量1”,达到了取较小值的目标。

 

举例:查询emp表中的每一个单位的人口?

SELECT sum(decode(deptno,10,1,0)) as 部门10,
            sum(decode(deptno,20,1,0)) as 部门20,
            sum(decode(deptno,30,1,0)) as 部门30 from emp

Oracle 1

      

      做个对照:select deptno,count(deptno) from emp group by deptno

      Oracle 2

 

2、递归查询的优化

   lpad/rpad( string, padded_length, [ pad_string ] )

   解释:字符不够的时候向左大概向右填充。

   例如:SQL> select lpad(‘abcde’,10,’x’) from dual;

      LPAD(‘ABCDE’,10,’X’)

      xxxxxabcde

 

  1. SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF   
  2. FROM T_TREE   
  3. START WITH FATHER_ID = 0   
  4. CONNECT BY PRIOR ID = FATHER_ID;  

 

解释:CONNECT_BY_ISLEAF
判断该行记录是不是为叶子节点,即使是回到1,否则再次来到0

          START WITH FATHER_ID = 0 规定哪一条记下为根节点

          CONNECT BY PRIOR ID = FATHER_ID
判断此节点的父节点是哪一条记下

 

例子:

 SELECT LPAD(MENU_NAME,

            LENGTHB(MENU_NAME) + LEVEL,

            DECODE(CONNECT_BY_ISLEAF, 1, ‘ | ‘, ‘+’))

  FROM MENU

 START WITH PID = ‘0’

 CONNECT BY PRIOR ID = PID;

 

三,利用分析函数排序和去重

http://blog.csdn.net/haiross/article/details/15336313#comments

 

剖析函数是怎么?
解析函数是Oracle专门用于缓解复杂报表统计需要的成效强大的函数,它可以在数额中展开分组然后总括基于组的某种总结值,并且每一组的每一行都得以回去1个计算值。

          

浅析函数和聚合函数的不相同之处是怎么样?
一般说来的聚合函数用group
by分组,各个分组再次来到多少个总计值,再次来到的字段名只能够是分组名。而分析函数接纳partition
by分组,并且每组每行都得以回去二个计算值,重回的字段名可以是逐个字段,因为是对应到记录的,所以没有涉嫌。

              

解析函数的花样
剖析函数带有三个开窗函数over(),包罗多少个分析子句:分组(partition by),
排序(order by), 窗口(rows) ,
她们的运用方式如下:over(partition by xxx
order by yyy rows between zzz)

 

 开窗函数over()含有多个分析子句:分组子句(partition by),
排序子句(order by), 窗口子句(rows)
      窗口就是分析函数分析时要拍卖的数量范围,就拿sum来说,它是sum窗口中的记录而不是一切分组中的记录,因而大家在想博得某些栏位的累计值时,大家必要把窗口指定到该分组中的第壹行数据到当下行,
借使你指定该窗口从该分组中的第2行到结尾一行,那么该组中的每3个sum值都会雷同,即一切组的总额。

     

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):当前组第一行到当前行的汇总

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING):当前行到最后一行的汇总

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND CURRENT ROW):当前行的上一行(rownum-1)到当前行的汇总

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING):当前行的上一行(rownum-1)到下两行(rownum+2)的汇总

 

而随便是不是省略分组子句,如下结论都以确立的:

 

 一,窗口子句不能独立出现,必须有order by子句时才能出现。

 2、当省略窗口子句时:
    a) 借使存在order by则暗许的窗口是unbounded preceding and current
row  –当前组的率先行到近年来行,即在时下组中,第1行到眼下行,那里强调一下,假设partition
by字段和order by 字段一样的话,那个order by不奏效,相当于简单了order by

    b) 若是同时省略order by则暗中同意的窗口是unbounded preceding and
unbounded following  –整个组

 

三个order by的履行时机

解析函数(以及与其合营的开窗函数over())是在漫天sql查询停止后(sql语句中的order
by的推行相比尤其)再展开的操作, 也等于说sql语句中的order
by也会潜移默化分析函数的执行结果:

 

    a) 两者一致:尽管sql语句中的order
by满意与分析函数合营的开窗函数over()分析时讲求的排序,即sql语句中的order
by子句里的始末和开窗函数over()中的order
by子句里的始末千篇一律,那么sql语句中的排序将先实施,分析函数在条分缕析时就无须再排序;
    b) 两者不同:假诺sql语句中的order
by不满足与分析函数合营的开窗函数over()分析时需要的排序,即sql语句中的order
by子句里的故事情节和开窗函数over()中的order
by子句里的始末不等同,那么sql语句中的排序将最终在分析函数分析停止后执行排序。

 

 

常用的辨析函数:

  1、row_number() over(partition by … order by …) 
为每一条记下重回二个唯一的值。当遭逢相同数量时,排行按照记录集中记录的依次依次递增,现实情状为:个人在分组内的名次
  2、rank() over(partition by … order by …) 
拿到每条记下在多少中的排行,名次不跳跃
  3、dense_rank() over(partition by … order by …)
得到每条记下在数额中的名次,排行跳跃
  肆,count() over(partition by … order by …)
每种分组中,有些字段的计算
  5、max() over(partition by … order by …)
  6、min() over(partition by … order by …)
  7、sum() over(partition by … order by …)
  8、avg() over(partition by … order by …)
  9、first_value() over(partition by … order by …)
得到第二个记录值
  10、last_value() over(partition by … order by …)
得到最后多个记录值
  11、lag() over(partition by … order by …)
lag函数可以在一回查询中取出同一字段的前n行的多寡

  12、lead() over(partition by … order by …)
lead函数可以在一回询问中取出同一字段的后n行的值

 

 

lag(arg1,arg2,arg3)
先是个参数是列名,
其次个参数是偏移的offset,
其多个参数是领先记录窗口时的暗许值。

select id,name,lag(name,1,0) over(order by id) from kkk; 

 

 例子:select ename,deptno,sal,sum(sal) over(partition by deptno order
by ename) as A from emp

Oracle 3

 

去重:

  一,利用rowid的唯一性查询或删除重复数据

  
select ROWNUM,ROWID,d1.* from dept2 d1 where d1.rowid=(select min(d2.rowid) from dept2 d2 where d2.deptno=d1.deptno);

  2、给定重复行序号并去重

SELECT FWZL

    FROM (SELECT FWZL,

                 ROW_NUMBER() OVER(PARTITION BY FWZL ORDER BY ID DESC)
RN

            FROM T_FWXX) F

   WHERE F.RN = 1

 

 四,求占比、小计和一起

      分析函数RATIO_TO_REPOENCORET
用来计量当前记录的目标expr占开窗函数over中蕴藏记录的兼具同一目的的百分比.
那里如若开窗函数的总括结果为null大概为0,就是说占用比率的被除数为0或许为null,
则赢得的结果也为0. 开窗条件query_partition_clause决定被除数的值,
借使用户忽略了那个条件, 则总计查询结果中兼有记录的集中值.
用户无法采纳别的分析函数或许ratio_to_report作为分析函数ratio_to_report的参数expr,
也等于说那个函数

 百分比(求这几个字段值占整组的比重):select deptno,ename,empno,round(RATIO_TO_REPORT(sal) OVER(PARTITION BY deptno)*100,1) 百分比 from emp

Oracle 4

 

rollup()与cube():排列组合分组 

1)、group by rollup(a, b, c):
先是会对(a、b、c)举行group by,
接下来再对(a、b)举办group by,
从此今后再对(a)进行group by,
末段对全表举行集中操作。

 

2)、group by cube(a, b, c):
则率先会对(a、b、c)举办group by,
下一场依次是(a、b),(a、c),(a),(b、c),(b),(c),
最终对全表进行集中操作。

 

 

ROLLUP,是GROUP
BY子句的一种伸张,能够为各类分组重返小计记录以及为拥有分组重返计算记录。

CUBE,也是GROUP
BY子句的一种扩展,可以回来每1个列组合的小计记录,同时在最后加上总括记录。

 

求各种分组的小计和一起:select deptno,sal,sum(sal) from emp group by
rollup(deptno,sal)

Oracle 5

 

求每二个列的咬合的小计记录:select deptno,sum(sal) from emp group by
cube(deptno,sal)

Oracle 6

 

聚合函数(within group语法):

 

select rank(1500) within group (order by sal desc) “rank of 1500” from
emp;

 

   
解释:借使存在一条记下,那条记下的salary字段值为1500。那么将该条记录插入emp表中后,依照sal字段降序排列后,该条记录的序号为多少?

   可以运用within
group关键字的函数有rank,dense_rank,PERCENT_RANK,PERCENTILE_CONT,PERCENTILE_DISC等

*   Oracle 7*

 

 

 5、单条记录插入多表

      原理:利用一个insert all 语法:insert all when .. then

     

INSERT ALL WHEN LOCALE = 1 THEN INTO EMPLOYEE1(ID, LOCALE, NAME, AGE,
GENDER, CODE) VALUES (ID, LOCALE, NAME, AGE, GENDER, CODE)

                  WHEN LOCALE = 32 THEN INTO EMPLOYEE2(ID, LOCALE, NAME,
AGE, GENDER, CODE) VALUES(ID, LOCALE, NAME, AGE, GENDER, CODE)

 

 

insert all into table values()

              into  table values()

 

 

 6、Merge的使用

 

 解释:DML语句,适用于批量处理

   MERGE INTO table_name alias1 
  USING (table|view|sub_query) alias2 
  ON (join condition) 
  WHEN MATCHED THEN 
  UPDATE table_name
  SET col1 = col_val1, 
  col2 = col2_val where 条件
  WHEN NOT MATCHED THEN 
  INSERT (column_list) VALUES (column_values) where 条件;

 

MERGE INTO EMPLOYEE E

USING (SELECT * FROM EMPLOYEE1) E1

ON (E.NAME = E1.NAME)

WHEN MATCHED THEN

    UPDATE SET E.CODE = E1.CODE, E.AGE = E1.AGE

WHEN NOT MATCHED THEN

   INSERT  (E.ID, E.LOCALE, E.NAME, E.AGE, E.GENDER, E.CODE)
VALUES(E1.ID, E1.LOCALE, E1.NAME, E1.AGE, E1.GENDER, E1.CODE);

 

备考:对两张表的三个字段相匹配,假设协作上了就做立异操作,否则就做插入操作。

 

 7、KEEP的使用

    
keep是Oracle下的另2个解析函数,他的用法差异于通过over关键字指定的剖析函数,可以用来那样一种场面下:取同七个分组下以有些字段排序后,对点名字段取最小或最大的不得了值。

   

  一般写法是 MIN [ MAX ] (A) KEEP(DENSE_RANK FIRST [ LAST ] OLX570DER
BY B),那里引用别人说的明的解释一下:

DENSE_RANK

功能描述:根据O翼虎DER
BY子句中表明式的值,从询问重回的每一行,计算它们与其他行的相对地方。组内的数量按O逍客DER
BY子句排序,然后给每一行赋3个号,从而形成一个连串,该连串从1发端,以往添加。每回O卡宴DER
BY表明式的值暴发变化时,该体系也随后扩充。有一致值的行得到同样的数字序号(认为null时相当的)。密集的队列再次回到的时未尝间隔的数。

 

FIRST

职能描述:从DENSE_RANK再次回到的汇聚中取出排在最前方的多少个值的行(或许多行,因为值恐怕十分),由此总体的语法须要在先河处加上三个集合函数以从中取出记录。

LAST

职能描述:从DENSE_RANK再次回到的见面中取出排在最终面的1个值的行(或许多行,因为值大概也等于),因而总体的语法需要在上马处加上三个集合函数以从中取出记录。

就此暗许排序下,FI路虎极光ST能够精晓是取小值,LAST取大值。而目前的MIN可能MAX则是在KEEP的结果集中取某一字段的最大值或纤维值。

 

 
keep和普通分析函数的区分:普通的辨析函数只是列出分组后的笔录,而对每二个组的记录进行统计分析。

                            keep对分组内的函数通过order
by和max(),min()采取有些字段的值。可以精晓成那里的keep就是sum() groud by
deptno 前的sum()那样

 

  实例:取出各种部门薪金最高的职工编号

 
1、select deptno,empno,sal,max(empno) keep(dense_rank first order by sal desc) over(partition by deptno) from emp2

  2、select deptno,max(empno) keep(dense_rank first order by sal desc) from emp2 group by deptno 

        解释:按deptno分组,再对分组中的sal降序,取出第2个sal的职工号

Oracle 8

 

 

八,SQL查询正则表明式的运用

 

  ORACLE中的扶助正则表明式的函数主要有上边八个:
1,REGEXP_LIKE :与LIKE的效能相似

    select * from emp where regexp_like(empno,’7[0-9]{2}9′)
2,REGEXP_INSTHighlander :与INST索罗德的职能相似

 

REGEXP_INSTR

6个参数

第多个是输入的字符串

第二个是正则表明式

其多个是标识从第多少个字符初叶正则表达式匹配。(暗中认可为1)

第多个是标识第多少个匹配组。(暗中认同为1)

第10、个是指定重回值的系列,即使该参数为0,则重回值为匹配地方的第二个字符,如果该值为非0则赶回匹配值的最后一个职位。

首个是是取值范围:

i:大小写不灵活;

c:大小写敏感;

n:点号 . 不匹配换行符号;

m:多行形式;

x:增添形式,忽略正则表明式中的空白字符。

    SELECT REGEXP_INSTR(a,'[0-9]+’) AS A FROM test_reg_substr;

 

3,REGEXP_SUBST途胜 :与SUBST库罗德的职能相似

   

REGEXP_SUBST奥迪Q3函数格式如下:

   function REGEXP_SUBSTR(String, pattern, position, occurrence,
modifier)
__srcstr     :要求展开正则处理的字符串
__pattern    :进行匹配的正则表明式
__position   :发轫地点,从第多少个字符初叶正则表明式匹配(暗中同意为1)
__occurrence :标识第多少个匹配组,暗许为1
__modifier  
:格局(’i’不区分轻重缓急写举办检索;’c’区分轻重缓急写举办搜索。暗中认同为’c’。)

  1. –壹,查询利用正则分割后的第二个值,也等于34  
  2. SELECT REGEXP_SUBSTR(‘34,56,-23′,'[^,]+’,1,1,’i’) AS STR FROM DUAL;  
  3. –结果是:34   
  4. –2、查询利用正则分割后的尾声1个值,约等于-23  
  5. SELECT REGEXP_SUBSTR(‘34,56,-23′,'[^,]+’,1,3,’i’) AS STR FROM DUAL;  
  6. –结果是:-23  

4,REGEXP_REPLACE :与REPLACE的意义相似

 

 

 

 

    

九,常见函数

TRUNC:截取函数

EXTRACT:用于从3个date只怕interval类型中截取到一定的局地

NVL

DECODE

length:字符长度

lengthb:字节长度

ASCII

INITCAP:首字母大写

 

SOUNDEX:重临由多个字符组成的代码 (SOUNDEX) 以评估多少个字符串的相似性

MONTHS_BETWEEN

ADD_MONTHS

NEXT_DAY

LAST_DAY

ROUND:函数用于把数值字段舍入为指定的小数位数

 

拾,分页函数

 

–普通写法

 

SELECT AA.FWZL, AA.FWTYBH

  FROM (SELECT A.FWZL, A.FWTYBH, ROWNUM RN

          FROM (SELECT F.FWZL, F.FWTYBH FROM FW F ORDER BY F.FWTYBH
DESC) A

         WHERE ROWNUM <= 120020) AA

 WHERE AA.RN > 120000;

 

–rowid写法

SELECT /*+ ROWID(FW) */ FW.FWZL, FW.FWTYBH

   FROM FW FW,

        (SELECT AA.RID, AA.RN

           FROM (SELECT A.RID, ROWNUM RN

                  FROM (SELECT /*+ index(F IDX_FW_FWTYBH) */

                         ROWID RID

                          FROM FW F

                          ORDER BY F.FWTYBH DESC) A

                 WHERE ROWNUM <= 120020) AA

          WHERE AA.RN > 120000) B

  WHERE FW.ROWID = B.RID;

相关文章