oracle 高階函式

風靈使發表於2018-11-08
Oracle函式

工作兩年了，一直使用的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;

結果是: 3b5abc

相關文章