第六章 使用数字

/*
1.生成累计和（使用4种方法）
- 分析函数
- rows开窗
- range开窗
- 标量方式
*/
select empno,
             sal,
       sum(sal) over(ORDER BY empno),
             sum(sal) over(ORDER BY empno rows BETWEEN unbounded preceding and current row),
         sum(sal) over(ORDER BY empno rang BETWEEN unbounded preceding and current row),
           (select sum(sal) from emp b where b.deptno = 30 and b.empno < a.empno)
from emp a
where deptno = 30
order by 1;

/**
2.计算累计差
**/
select seq,projectName,money,
         sum(case when seq=1 then money else -money end) over(over by seq)
from x;

/**
3.更改累加和的值
**/
select id,
             case when trx = 'py' then '取款' else '存款' end type,
             amt,
             sum(case when trx = 'py' then -amt else amt end) over(order by id)
from v order by 1;

/**
4.返回各部门工资排名前三位的员工
  使用 dense_rank()
**/
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() as dense_rank
from emp
where deptno = (20,30)
order by 1,3 desc;

/**
5.计算出现次数最多的值
**/
select deptno,sal
from (
        select deptno,sal,
                 dense_rank() over(PARTITION by deptno order by 出现次数 desc) as 次数排序
        from (select sal,deptno,count(*) as 出现次数 from emp group by deptno,sal) x
) y
where 次数排序 = 1;

/**
6.求总和的百分比
**/
-- 方式一：通过分析函数
select deptno,sum,allsum,
            round((sum/allsum)*100,2) as 工资比例
from (select deptno,sum,sum(sum) over() as allsum from 
                        (select deptno,sum(sal) sum from emp group by deptno) x) y
order by  1;

-- 方式二：通过专用比例函数
select deptno,round((ratio_to_report(sum) over() * 100),2) as 工资比例
from (select deptno,sum(sal) sum from emp group by deptno)
order by 1;