Oracle 高级sql篇

qq_20786701

已于 2022-05-26 19:22:20 修改

阅读量919

点赞数

文章标签： hive hadoop 数据仓库

于 2022-05-26 18:34:56 首次发布

原文链接：https://www.zhihu.com/question/384462652

版权

1 高级分组
grouping sets：集合汇总
grouping sets：集合汇总，对每个子集汇总后合并结果

select JOB,DEPTNO,sum(sal)
from emp 
group by grouping sets(JOB,DEPTNO)
order by 1,2;
JOB                      DEPTNO   SUM(SAL)
-------------------- ---------- ----------
ANALYST                               6000
CLERK                                 4150
MANAGER                               8275
PRESIDENT                             5000
SALESMAN                              5600
                             10       8750
                             20      10875
                             30       9400

rollup：向上汇总

select JOB,DEPTNO,count(1)
from emp 
group by rollup(JOB,DEPTNO)
order by 1,2
;
JOB                      DEPTNO   COUNT(1)
-------------------- ---------- ----------
ANALYST                      20          2
ANALYST                                  2
CLERK                        10          1
CLERK                        20          2
CLERK                        30          1
CLERK                                    4
MANAGER                      10          1
MANAGER                      20          1
MANAGER                      30          1
MANAGER                                  3
PRESIDENT                    10          1
PRESIDENT                                1
SALESMAN                     30          4
SALESMAN                                 4
                                        14

cube：组合汇总

select JOB,DEPTNO,count(1)
from emp 
group by cube(JOB,DEPTNO)
order by 1,2;
JOB                      DEPTNO   COUNT(1)
-------------------- ---------- ----------
ANALYST                      20          2
ANALYST                                  2
CLERK                        10          1
CLERK                        20          2
CLERK                        30          1
CLERK                                    4
MANAGER                      10          1
MANAGER                      20          1
MANAGER                      30          1
MANAGER                                  3
PRESIDENT                    10          1
PRESIDENT                                1
SALESMAN                     30          4
SALESMAN                                 4
                             10          3
                             20          5
                             30          6
                                        14

2 行转列
传统行转列

select job
      ,sum(case when deptno=10 then sal end) dept10_sum_sal
      ,sum(case when deptno=20 then sal end) dept20_sum_sal
      ,sum(case when deptno=30 then sal end) dept30_sum_sal
      ,sum(case when deptno=40 then sal end) dept40_sum_sal
from emp
group by job;

pivot 行转列

select *
from (select job,deptno,sum(sal) sal
      from emp
      group by job,deptno
      pivot (sum(sal) as sum_sal for deptno in (10 as dept10,20 as dept20,30 as dept30,40 as dept40))

;
unpivot 列转行

select *
from pivot_tab
unpivot(sum_sal for deptsal_desc in(dept10_sum_sal,dept30_sum_sal,dept30_sum_sal,dept40_sum_sal);

文本聚合函数listagg()
listagg()：文本聚合函数，类似wmsys.wm_concat()函数，listagg可以设置排序，wmsys.wm_concat无序

select DEPTNO,listagg(ENAME,',') within group(order by ENAME) 
from emp
group by DEPTNO

;

3 分析函数
分析函数结构
func() over(partition by 分组 order by 排序滑动窗口)

常用分析函数
row_number() 唯一排序

rank() 有跳号并列排名

dense_rank() 无跳号并列排名，值并列排序

lag(字段,1,默认值) 前1行

lead(字段,1,默认值) 后1行

first_value() 第一行

last_value() 最后一行

nth_value(字段,1) 任意一行

percent_rank() 百分比排名，返回0-1间

percentile_cont() 指定百分比，返回排名值，percent_rank的反函数

percentile_disc() 类似percentile_cont()，离散分布

ntile() 分组

ratio_to_report() 计算报告中值的比例：val/sum(val) over()

活动窗口
rows between unbounded preceding and unbounded following：从第一行到最后一行，无order by时默认

rows between unbounded preceding and current row：从第一行到当前行，有order by时默认

rows between 2 preceding and 2 following：前2行到后2行

range between 100 preceding and 100 following：根据order by列的值范围，相当于加条件 where expr between a and b

–计算工资最接近前后2名的均值
select DEPTNO,ENAME,SAL
,avg(SAL) over(partition by DEPTNO order by SAL rows between 2 preceding and 2 following ) sum_sal
from emp
order by DEPTNO,SAL
;
KEEP语句
–KEEP ：从并列排名最大最小值范围中做选择
select DEPTNO,ENAME,SAL,HIREDATE
,dense_rank() over(partition by a.DEPTNO order by SAL) rn
,min(HIREDATE) keep(dense_rank last order by SAL) over(partition by a.DEPTNO) keep
from emp a
order by DEPTNO,SAL,HIREDATE
;
4 递归层次查询
connect by层次查询

select level,connect_by_root|connect_by_isleaf|connect_by_iscycle

from tablename

where

connect by prior 列1=列2

start with

order siblings by

–传统connect by法

select EMPNO,ENAME,MGR
,level
,cast(sys_connect_by_path(ENAME,'-') as varchar2(200)) as path
,connect_by_root(ENAME) as top_manager
from emp
start with MGR is null
connect by prior EMPNO=MGR;
显示当月日历
select max(decode(dow,1,d,null)) Sun
 ,max(decode(dow,2,d,null)) Mon
 ,max(decode(dow,3,d,null)) Tue
 ,max(decode(dow,4,d,null)) Wen
 ,max(decode(dow,5,d,null)) Thu
 ,max(decode(dow,6,d,null)) Fri
 ,max(decode(dow,7,d,null)) Sat
from (
select rownum d --第几天
 ,trunc((rownum+to_number(to_char(trunc(sysdate,'MM'),'D'))-2)/7) p --第几周
 ,to_char(trunc(sysdate,'MM')+rownum-1,'D') dow --星期几
from dual
connect by rownum<=to_number(to_char(last_day(sysdate),'dd'))
) a
group by p
order by p

;
SUN MON TUE WEN THU FRI SAT

          1   2   3   4

5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30
with层次查询
–with子查询CTE

with 查询名([列名])

as(子查询：锚点成员 union all 递归成员)

[遍历顺序子句：深度优先、广度优先]

[循环子句：终止循环条件]

[多个子查询]

–with子查询法

with cte(EMPNO,ENAME,MGR,lev,path,top_manager) as
(select EMPNO,ENAME,MGR
,1 as lev
,ENAME as path
,ENAME as top_manager
from emp
where MGR is null
union all
select b.EMPNO,b.ENAME,b.MGR
,1+a.lev
,cast(a.path||'-'||b.ENAME as varchar2(200))
,a.top_manager
from cte a
join emp b on a.EMPNO=b.MGR
)
select * from cte
;
叶子节点
--叶子节点   connect_by_isleaf伪列
select EMPNO,ENAME,MGR
,level
,cast(sys_connect_by_path(ENAME,'-') as varchar2(200)) as path
,connect_by_root(ENAME) as top_manager
,connect_by_isleaf
from chencanhui.emp
where connect_by_isleaf=1
start with MGR is null
connect by prior EMPNO=MGR;

–叶子节点，深度优先+序列

with cte(EMPNO,ENAME,MGR,lev,path,top_manager) as
(select EMPNO,ENAME,MGR
,1 as lev
,ENAME as path
,ENAME as top_manager
from chencanhui.emp
where MGR is null
union all
select b.EMPNO,b.ENAME,b.MGR
,1+a.lev
,cast(a.path||'-'||b.ENAME as varchar2(200))
,a.top_manager
from cte a
join chencanhui.emp b on a.EMPNO=b.MGR
)
search depth first by mgr set seq   --深度优先搜索，生成序列seq
select *
from (
select a.*,case when lev < lead(lev) over(order by seq) then 0 else 1 end is_leaf
from cte a
) 
where is_leaf=1;

构造序列

select rownum rn
from dual
connect by rownum<=10;

with t(rn)as
(select 1 rn
from dual
union all
select rn+1
from t 
where rn<10
)
select * from t

;

5 model子句
model子句：单元格跨行引用，类似excel

model跨行引用虽然很灵活，但语法复杂，执行效率慢，只适合在小的汇总报表使用，本人在工作上基本没使用。

select product,
       country,
       year,
       week,
       inventory,
       sale,
       receipts
from sales_fact sf
where sf.country in ('Australia') and sf.product = 'Xtend Memory' 
 model return updated rows
 partition by(product, country) 
 dimension by(year, week)
 measures(0 inventory, sale, receipts) 
 rules automatic order(inventory [ year, week ] = 
                 nvl(inventory [ cv(year), cv(week) - 1 ], 0) 
                 - sale [ cv(year), cv(week) ] 
                 + receipts [ cv(year), cv(week) ])
 order by product, country, year, week
 ;

6 正则表达式
正则函数
regexp_substr(字符串,正则表达式,开始位置,出现次数,匹配选项)

匹配选项，i：大小写不敏感； c：大小写敏感；n：点号 . 不匹配换行符号；m：多行模式；x：扩展模式，忽略正则表达式中的空白字符。

regexp_substr()

regexp_instr()

regexp_replace()

regexp_like()

regexp_count()

Oracle特殊字符集
[[:alpha:]] 任何字母。

[[:digit:]] 任何数字。

[[:alnum:]] 任何字母和数字。

[[:upper:]] 任何大写字母。

[[:lower:]] 任何小写字母。

[[:unct:]] 任何标点符号。

[[:print:]] 任何可打印字符。

[[:space:]] 任何空白字符。

[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。

7 高级DML
insert多表插入
–同表插入多次

insert all
into parents(pid,cust_nam) values(pid,cust_nam)
into children(pid,cust_nam) values(pid,child1)
into children(pid,cust_nam) values(pid,child2)
into children(pid,cust_nam) values(pid,child3)
select pid,cust_nam,child1,child2,child3
from people;

–条件多表插入

insert all/first: all满足多个when则插入多个表，first满足第一个when后序不再插入
insert all
when sum_orders<1000 then 
into small_customers
when sum_orders>=1000 and sum_orders<10000 then 
into medium_customers
else
into large_customers
select customer_id,sum(order_total) sum_orders
from oe.orders
group by customer_id

;

–并行插入

insert /*+ append */ into small_customers
select customer_id,order_total
from oe.orders

update关联
–当关联不存在，替换成空值

UPDATE T1 
SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME)

;

–只对关联存在记录有更新

UPDATE T1 
SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME)
WHERE EXISTS(SELECT 1 FROM T2 WHERE T2.FNAME = T1.FNAME)

;

–内联视图更新

UPDATE (
select t1.fmoney  fmoney1,t2.fmoney  fmoney2 from t1,t2 where t1.fname = t2.fname
)t
set fmoney1 =fmoney2

;

–merge更新

merge into t1
using (select t2.fname,t2.fmoney from t2) t
on (t.fname = t1.fname)
when matched then 
update  set t1.fmoney = t.fmoney

;
merge语句

merge into table_name
using (subquery) on (subquery.column = table.column)
when matched then update
when not matched then insert

merge into t2
using t1 on (t1.name = t2.name)
when matched then 
update set t2.money = t2.money + t1.money
when not matched then 
insert values(t1.name,t1.money)
;

–merge条件更新、delete子句

merge into t2
using t1 on (t1.name = t2.name)
when matched then 
update set t2.money = t2.money + t1.money
where t1.name='A'   --条件过滤
delete where t1.name='B' 
when not matched then 
insert values(t1.name,t1.money)

;
8 综合实例
号段生成器
–根据号码明细生成号段分组

drop table tmp_test;
create table tmp_test as 
select 2014  year,1      num  from dual union all
select 2014  year,2      num  from dual union all
select 2014  year,3      num  from dual union all
select 2014  year,4      num  from dual union all
select 2014  year,5      num  from dual union all
select 2014  year,7      num  from dual union all
select 2014  year,8      num  from dual union all
select 2014  year,9      num  from dual union all
select 2014  year,11     num  from dual union all
select 2014  year,12     num  from dual union all
select 2013  year,120    num  from dual union all
select 2013  year,121    num  from dual union all
select 2013  year,122    num  from dual union all
select 2013  year,124    num  from dual union all
select 2013  year,125    num  from dual

;

–差异分组法

select year
      ,num-rn    group_id
      ,min(num)  num_start
      ,max(num)  num_end
from (
select year
      ,num
      ,row_number() over(partition by year order by num) rn
from tmp_test 
) a
group by year,num-rn
order by 1,2

;

–相邻比较法

select a.*
      ,a.s_end  num_start
      ,lead(s_start,1,s_max) over(partition by year order by s_start) num_end
from (
select year
      ,lag(num,1,num) over(partition by year order by num) s_start
      ,num  s_end
      ,max(num) over(partition by year) s_max
from tmp_test 
) a
where nvl(s_end-s_start,0)<>1
order by 1,2;

–根据号段分组生成号码明细

drop table tmp_test2;
create table tmp_test2 as 
select 1 group_id,1 num_start,5 num_end from dual union all
select 2 group_id,7 num_start,9 num_end from dual union all
select 3 group_id,11 num_start,12 num_end from dual
;

select a.group_id
      ,a.num_start
      ,a.num_end
      ,a.num_start + b.rn-1 num
from tmp_test2 a
    , (select rownum rn
      from (select max(num_end-num_start)+1 max_len from tmp_test2)
      connect by rownum<=max_len
      ) b 
where b.rn<=a.num_end-a.num_start+1
order by 1,2,3,4
;

9 动态SQL
绑定变量 :x

begin
  for i in 1..10
  loop 
    execute immediate 'insert into t values('||i||')';
    commit;
  end loop;
end;
/

begin
  for i in 1..10
  loop 
    execute immediate 'insert into t values( :x )' using i;
    commit;
  end loop;
end;
/

10 管道函数
管道函数table()
管道函数：能返回行集合、数组的函数

type … is table of date：创建集合类型

return … pipelined：声明返回管道函数类型

pipe row()：返回该集合的单个元素

return：以空return语句结束，表名管道函数完毕

table()：使用表函数转成数据库表格式

–创建集合类型

create or replace type data_list is table of date;
/

–创建管道函数

create or replace function pipe_date(p_start date,p_limit number)
return data_list pipelined is
begin
 for i in 0 .. p_limit-1 loop
 pipe row(p_start+i); --返回该集合的单个元素
 end loop;
 return;  --以空return语句结束，表名管道函数完毕
end;
/

–使用管道函数

select *
from table(pipe_date(trunc(sysdate),10))
;

split函数实例
–oracle没有split函数，可以自己用管道函数定义


```sql
create or replace type type_split is table of varchar2(200);
/

create or replace function split(p_list varchar2, p_sep varchar2:=',') 
return type_split pipelined is
 l_idx number;
 v_list varchar2(200):=p_list;
begin
 loop
 l_idx:=instr(v_list,p_sep);
 if l_idx>0 then
 pipe row(substr(v_list,1,l_idx-1));
 v_list:=substr(v_list,l_idx+length(p_sep));
 else
 pipe row(v_list);
 exit;
 end if;
 end loop;
 return;
end; 
/
 
select split('one,tow,three',',') from dual;
TYPE_SPLIT('one', 'tow', 'three')
 
select *
from the(select split('one,tow,three',',') from dual)
;
 
select *
from table(split('one,tow,three',','));
one
tow
three