oracle 高级函数

柔光的暖阳◎ 2021-06-26 16:06 454阅读 0赞

工作两年了，一直使用的`oracle`，最近经常使用不常用的`sql`语句，索性就自己整理一下，发出来 供大家参考,后续会不断添加、整理,本文章不做详细的功能解释，主要集合描述目前接触过的一些函数

### 复制表结构和数据 ###

CREATE TABLE table_name AS SELECT * FROM old_table_name;

### 只复制表结构 ###

CREATE TABLE table_name AS SELECT * FROM old_table_name WHERE 1=2

### 只复制表数据 ###

两个表结构一毛一样：

INSERT INTO  table_name select * from old_table_name;

结构不完全一致：

Insert into table_name(column1,column2...) select column1,column2 from old_table_name;

### 递归查询 ###

从根节点开始查询：

Select t.*,level  from table_name t  start with t.id=0 connect by prior t.id=t.pid order by level

从子节点开始查询：

把上面的开始节点换成开始的子节点，并且`prior` 放在子节点前面

Select t.*,level  from table_name t  start with t.id=10 connect by t.id= prior t.pid order by level

合并字符串（简单的行专列）

Select to_char(wm_concat(column1 || ‘-’ || column2))  from table group by column1

### 拆分字符串 ###

select REGEXP_SUBSTR('01#02#03#04', '[^#]+', 1, rownum) as newport from dual connect by rownum <= REGEXP_COUNT('01#02#03#04', '[^#]+');

### With as 临时表 ###

With t as (select 1 id,2 pid from dual),
    
    t2 as (select 1 id,22 pid from dual)
    
    Select t2.* from t,t2 where t.id=t2.id

### 行专列查询 ###

with t as (
    
    Select ‘桃子’ name, 300 nums ,1 jidu from dual
    
    Union
    
    Select ‘苹果’ name, 200 nums ,1 jidu from dual
    
    Union
    
    Select ‘西瓜’ name, 100 nums ,1 jidu from dual
    
    Union
    
    Select ‘桃子’ name, 111 nums ,2 jidu from dual
    
    Union
    
    Select ‘西瓜’ name, 222 nums ,2 jidu from dual
    
    Union
    
    Select ‘苹果’ name, 333 nums ,2 jidu from dual
    )
    
    Select * from t pivot(sum(nums)  for name in (‘桃子’,’西瓜’,’苹果’)) order by jidu

### 列转行查询 ###

With t as (
    
    Select 1 id,’桃子’ name,100 q1,200 q2,300 q3,400 q4 from dual
    
    Union
    
    Select 2 id,’苹果’ name,111 q1,222 q2,333 q3 444 q4 from dual
    
    Union
    
    Select 3 id,’西瓜’ name,123 q1,234 q2,345 q3,456 q4 from dual
    
    )
    
    Select * from t unpivot(nums for jidu in (q1,q2,q3,q4)) order by id

### 排序函数 ###

SELECT region_id, 
           customer_id, 
           SUM(customer_sales)                    total, 
           Rank() 
             over( 
               ORDER BY SUM(customer_sales) DESC) rank, 
           Dense_rank() 
             over( 
               ORDER BY SUM(customer_sales) DESC) dense_rank, 
           Row_number() 
             over( 
               ORDER BY SUM(customer_sales) DESC) row_number 
    FROM   user_order 
    GROUP  BY region_id, 
              customer_id;

REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
    ---------- ----------- ---------- ---------- ---------- ----------
                
             8          18                1253840         11         11         11
             5           2                 1224992         12         12         12
             9          23                1224992         12         12         13
             9          24                1224992         12         12         14
            10          30               1216858         15           13            15

请注意上面的绿色高亮部分，这里生动的演示了3种不同的排名策略：

①对于第一条相同的记录，3种函数的排名都是一样的：12

②当出现第二条相同的记录时，`Rank`和`Dense_rank`依然给出同样的排名12；而`row_number`则顺延递增为13，依次类推至第三条相同的记录

③当排名进行到下一条不同的记录时，可以看到`Rank`函数在12和15之间空出了13,14的排名，因为这2个排名实际上已经被第二、三条相同的记录占了。而`Dense_rank`则顺序递增。`row_number`函数也是顺序递增

**使用分析函数为记录进行分组排名：**

上面的排名是按订单总额来进行排列的，现在跟进一步：假如是为各个地区的订单总额进行排名呢？这意味着又多了一次分组操作：对记录按地区分组然后进行排名。幸亏Oracle也提供了这样的支持，我们所要做的仅仅是在`over`函数中`order by`的前面增加一个分组子句：`partition by region_id`。

SELECT region_id, 
           customer_id, 
           SUM(customer_sales)   total, 
           Rank() 
             over( 
               PARTITION BY region_id 
               ORDER BY SUM(customer_sales) DESC) rank, 
           Dense_rank() 
             over( 
               PARTITION BY region_id 
               ORDER BY SUM(customer_sales) DESC) dense_rank, 
           Row_number() 
             over( 
               PARTITION BY region_id 
               ORDER BY SUM(customer_sales) DESC) row_number 
    FROM   user_order 
    GROUP  BY region_id, 
              customer_id;

REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
    ---------- ----------- ---------- ---------- ---------- ----------
             5           4                1878275          1          1          1
             5           2                1224992          2          2          2
             5           5                1169926          3          3          3
             6           6                1788836          1          1          1
             6           9                1208959          2          2          2
             6          10               1196748          3          3          3

现在我们看到的排名将是基于各个地区的，而非所有区域的了！`Partition by` 子句在排列函数中的作用是将一个结果集划分成几个部分，这样排列函数就能够应用于这各个子集。

### 存在则修改，不存在则新增 ###

判断表里面是否有满足条件的数据，存在则修改，不存在则新增

MERGE INTO T T1
    
    USING (SELECT '1001' AS a,2 AS b FROM dual) T2ON ( T1.a=T2.a)
    
    WHEN MATCHED THEN
    
    UPDATE SET T1.b = T2.b
    
    WHEN NOT MATCHED THEN
    
    INSERT (a,b) VALUES(T2.a,T2.b);

### 全连接 ###

select * from dept full join employee on dept.deptid=employee.deptid

### Nulls last/first ###

空值排最前面或者最后面

Select * from table_name order by id nulls first/last

取`top N`条数据

SELECT NO FROM (
    
        SELECT ROW_NUMBER() OVER (ORDER BY NO) RNO, NO FROM ROWNUM_TEST
    
    )WHERE RNO <= 5  ORDER BY NO ;

### 分组函数（`rollup,cube,grouping sets`） ###

**1 `rollup`**

假设有一个表`test`，有A、B、C、D、E5列。

如果使用`group by rollup(A,B,C)`，首先会对(A、B、C)进行`GROUP BY`，然后对(A、B)进行`GROUP BY`，然后是(A)进行`GROUP BY`，最后对全表进行`GROUP BY`操作。`roll up`的意思是“卷起”，这也可以帮助我们理解`group by rollup`就是对选择的列从右到左以一次少一列的方式进行`grouping`直到所有列都去掉后的`grouping`(也就是全表`grouping`)，对于n个参数的`rollup`，有n+1次的`grouping`。以下2个sql的结果集是一样的：

Select A,B,C,sum(E) from test group by rollup(A,B,C)

与

Select A,B,C,sum(E) from test group by A,B,C
    
    union all
    
    Select A,B,null,sum(E) from test group by A,B
    
    union all
    
    Select A,null,null,sum(E) from test group by A
    
    union all
    
    Select null,null,null,sum(E) from test

**2 `cube`**

`cube`的意思是立方，对`cube`的每个参数，都可以理解为取值为参与`grouping`和不参与`grouping`两个值的一个维度，然后所有维度取值组合的集合就是`grouping`的集合，对于n个参数的`cube`，有`2^n`次的`grouping`。如果使用`group by cube(A,B,C)`,，则首先会对(A、B、C)进行`GROUP BY`，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，©，最后对全表进行`GROUP BY`操作，一共是2^3=8次`grouping`。同`rollup`一样，也可以用基本的`group by`加上结果集的`union all`写出一个与`group by cube`结果集相同的sql：

Select A,B,C,sum(E) from test group by cube(A,B,C);

与

Select A,B,C,sum(E) from test group by A,B,C
    
    union all
    
    Select A,B,null,sum(E) from test group by A,B
    
    union all
    
    Select A,null,C,sum(E) from test group by A,C
    
    union all
    
    Select A,null,null,sum(E) from test group by A
    
    union all
    
    Select null,B,C,sum(E) from test group by B,C
    
    union all
    
    Select null,B,null,sum(E) from test group by B
    
    union all
    
    Select null,null,C,sum(E) from test group by C
    
    union all
    
    Select null,null,null,sum(E) from test;

**3 `grouping sets`**

`grouping sets`就是对参数中的每个参数做`grouping`，也就是有几个参数做几次`grouping`,例如使用`group by grouping sets(A,B,C)`，则对(A),(B),©进行`group by`，如果使用`group by grouping sets((A,B),C)`,则对(A,B),©进行`group by`。甚至`grouping by grouping set(A,A)`都是语法允许的，也就是对(A)进行2次`group by,grouping sets`的参数允许重复

### Keep ###

ID MC SL
    -------------------- -------------------- -------------------
    1 111 1
    1 222 1
    1 333 2
    1 555 3
    1 666 3
    2 111 1
    2 222 1
    2 333 2
    2 555 2
    
    9 rows selected
    
    SQL> 
    SQL> select id,mc,sl,
    2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
    3 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
    4 from test
    5 ;
    
    ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKLASTORDE
    -------------------- -------------------- ------------------- ------------------------------ ------------------------------
    1 111 1 111 666
    1 222 1 111 666
    1 333 2 111 666
    1 555 3 111 666
    1 666 3 111 666
    2 111 1 111 555
    2 222 1 111 555
    2 333 2 111 555
    2 555 2 111 555

### `ratio_to_report` 占比函数 ###

SELECT  
    
       empno,ename,ename,hiredate,sal,deptno,  
    
        ratio_to_report(sal) over () as pct1,    
    
        ratio_to_report(sal) over (partition by deptno) as pct2  
    
       FROM emp;

`Pct1` 是求`sal`在所有`sal`中的比率

`Pct2`是求sql在按`deptno`分组后 所占的比率

### `NVL2` ###

`Select nvl2(1,2,3) FROM DUAL`相当于三目运算符

如果第一个参数为`null` 返回3，否则返回2

### COALESC ###

从左到右返回第1个非`null`值，若所有的列表元素都为`null`,则返回`null`.它有短路计算功能，

Select COALESC(null,null,null,2null) FROM DUAL

### `translate`函数 ###

`translate（x, from_string, to_string）`函数在x中查找`from_string`中的字符，并将其转换成`to_string`中对应的字符。

`from_string`和`to_string`中的字符是一一对应的，然后根据这种对应关系，将原字符串进行转换；

select translate('ab123', 'abcdefg123', '3456789abc') from dual;