(6/14) oracle查询优化的改写笔记第六章使用数字

客官°小女子只卖身不卖艺 2023-02-21 04:47 11阅读 0赞

-- 6.1 常用的聚集函数
    select deptno,
    	avg(sal) as 平均值,
    	min(sal) as 最小值,
    	max(sal) as 最大值,
    	sum(sal) as 工资合计,
    	count(*) 总行数,
    	count(comm) 获得提成人数,
    	avg(comm) 错误人均提成算法,
    	avg(coalesce(comm, 0))  正确的提成算法 /*需要把空值转换为0 */
    from emp
    group by deptno;
    
    create table emp6_2 as select * from scott.emp where 1=2;
    
    select count(*) as cnt, sum(sal) as sum_sal from  emp6_2 where deptno=10;
    select count(*) as cnt, sum(sal) as sum_sal from  emp6_2 where deptno=10 group by deptno;
    
    declare 
    	v_sal emp6_2.sal%Type;
    begin
    	select sum(sal) into v_sal from emp6_2 where deptno=10;
    	dbms_output.put_line('v_sal='||v_sal);
    end;
    /
    	
    -- 6.2 列转行
    select ename,
    	empno,
    	sal,
    	deptno
    from scott.emp
    where deptno=10
    union all
    select first_name || ' ' || last_name as ename,
    	employee_id  as  empno,
    	salary,
    	department_id 
    from hr.employees
    where department_id=20;
    
    --  把同一个表中的不同列使用union all拼在一起，列转行
    select employee_id,
    	'first_name' as col,
    	first_name
    from hr.employees
    where department_id=20
    union all
    select employee_id,
    	'last_name' as col,
    	last_name
    from hr.employees
    where department_id=20
    order by 1,2;
    
    -- 6.2 行转列
    select job,sal from emp where deptno=20 order by job;
    
    select case job when 'ANALYST' then sal end as analyst,
    	case job when 'CLERK' then sal end as clerk,
    	case job  when 'MANAGER' then sal end as manager
    from emp
    where deptno=20
    order by job;
    
    -- 把数据汇总
    select sum(case job when 'ANALYST' then sal end) as analyst,
    	sum(case job when 'CLERK' then sal end) as clerk,
    	sum(case job  when 'MANAGER' then sal end) as manager
    from emp
    where deptno=20;
    
    -- 简单的条件表达式可以直接使用decode函数
    select sum(decode(job,'ANALYST',sal)) as analyst,
    sum(decode(job,'CLERK', sal)) as clerk,
    sum(decode(job,'MANAGER', sal)) as manager
    from emp
    where deptno=20;
    
    -- 6.4 生成累计和
    select empno as 编号,
    	ename as 姓名,
    	sal as 工资,
    	hiredate as 聘用日期
    from emp
    where deptno=30
    order by 聘用日期;
    
    
    select empno as 编号,
    	ename as 姓名,
    	sal as 工资,
    	hiredate as 聘用日期,
    	( select sum(b.sal) 
    		from emp b
    		where b.deptno=30
    		and b.hiredate<=a.hiredate	
    	) as 合计工资
    from emp a
    where deptno=30
    order by 聘用日期;
    
    -- 或者
    select a.empno as 编号,
    	a.ename as 姓名,
    	a.sal as 工资,
    	a.hiredate as 聘用日期,
    	sum(b.sal) as 合计工资
    from emp a,
    	emp b
    where a.deptno=30
    and b.hiredate<=a.hiredate
    and b.deptno=30
    group by a.empno, a.ename, a.sal, a.hiredate
    order by 聘用日期;
    
    -- 分析函数 sum(sal) over (order by hiredate)
    explain plan for 
    select empno as 编号,
    	ename as 姓名,
    	sal as 工资,
    	hiredate as 聘用日期,
    	sum(sal) over (order by hiredate) as 合计工资
    from emp
    where deptno=30
    order by 聘用日期;
    
    select * from table(dbms_xplan.display);
    
    
    select
    	deptno as 部门编码,
    	empno as 编号,
    	ename as 姓名,
    	sal as 工资,
    	hiredate as 聘用日期,
    	sum(sal) over (partition by deptno order by hiredate) as 合计工资
    from emp;
    
    -- 6.5 累计值与重复值
    select * 
    from ( select ename,
    	empno,
    	sal,
    	sum(sal) over(order by sal) as add_sal
    	from emp
    )
    where add_sal<=6000;
    
    select * 
    from ( select ename,
    	empno,
    	sal,
    	sum(sal) over(order by sal, ename) as add_sal
    	from emp
    )
    where add_sal<=6000; 
    
    select * 
    from ( select ename,
    	empno,
    	sal,
    	sum(sal) over(order by sal rows between unbounded preceding and current row) as add_sal
    	from emp
    )
    where add_sal<=6000; 
    
    -- 6.6 生成排名
    select deptno,
    	empno,
    	sal,
    	row_number() over (partition by deptno order by sal desc) as row_number,
    	rank() over (partition by deptno order by sal desc) as rank,
    	dense_rank() over (partition by deptno order by sal desc) as dense_rank
    from emp
    where deptno=20;
    
    select deptno, ename, empno, sal
    from (select deptno,
    			ename,
    			empno,
    			sal,
    			rank() over (partition by deptno order by sal desc) as rank
    		from emp
    )
    where rank=1;
    
    select deptno,
    		max(sal) as max_sal,
    		max(ename) keep(dense_rank last order by sal) as max_name,
    		min(ename) keep(dense_rank last order by sal) as min_name,
    		to_char(wm_concat(ename) keep(dense_rank last order by sal)) as all_name
    from emp
    group by deptno;
    
    select deptno,
    	ename,
    	empno,
    	sal,
    	max(ename) keep(dense_rank last order by sal) over (partition by deptno) as name_of_max_sal
    	from emp
    	where deptno=10;
    
    -- 6.8 求总和百分比
    -- 分组汇总
    select deptno, sum(sal) 工资合计 from emp group by deptno;
    
    --分析函数获取总合计
    select deptno, 工资合计, sum(工资合计) over() as 总合计 
    	from (select deptno, sum(sal) 工资合计 from emp group by deptno) x;
    
    -- 得到总合计后就可以计算比例
    select deptno as 部门,
    	工资合计,
    	总合计,
    	to_char(round((工资合计/总合计)*100, 2), 'fm90.09') as  工资比例
    from
    	(select deptno, 工资合计, sum(工资合计) over() as 总合计 
    	from (select deptno, sum(sal) 工资合计 from emp group by deptno) x) y
    order by 1;
    
    select to_char(round(6/3335,4)*100.00,'fm90.09')||'%'    from  dual;
    
    select deptno, round(ratio_to_report(工资合计) over()*100, 2) as 工资比例
    from (select deptno, sum(sal) 工资合计 from  emp group by deptno)
    order by 1;
    
    select deptno,
    	empno,
    	ename,
    	sal,
    	round(ratio_to_report(sal) over(partition by deptno) *100, 2) as 工资比例
    from emp
    order by 1,2;