1.生成累乘积
select empno,ename,sal,exp(sum(ln(sal)) over(order by sal,empno)) as running_prod from emp where deptno = 10
2.计算累计差
select ename,sal,sum(case when rn = 1 then sal else -sal end) over(order by sal) from (
select ename,sal,row_number() over(order by sal) rn from emp where deptno = 10
)
3.计算模式(查找列中出现次数最多的值)
①select sal,kn from(
select sal,row_number() over(order by rn desc) kn from(
select deptno,sal,count(sal) rn from emp where deptno = 20 group by deptno,sal
)
) where kn = 1
② select max(sal) keep(dense_rank first order by rn desc) sal from(
select sal,count(sal) rn from emp where deptno = 20 group by sal
)
4.取中间值
select median(sal) from emp
5.求总和的百分比
select (s20/total)*100 pct from(
select distinct deptno,sum(sal) over() total,sum(sal) over(partition by deptno order by deptno) s20 from emp
) where deptno = 10
6.对可空列做聚集
select avg(coalesce(comm,0)) avg_comm from emp
7.求不包含最大值和最小值的均值
select avg(sal) from (
select sal,max(sal) over() maxvalue,min(sal) over() minvalue from emp where deptno = 20
) where sal not in (maxvalue,minvalue)
8.更改累计和中的值
with temp as(
select 1 id,100 amt,'PR' trx from dual
union all
select 2 id,100 amt,'PR' trx from dual
union all
select 3 id,50 amt,'PY' trx from dual
union all
select 4 id,100 amt,'PR' trx from dual
union all
select 5 id,200 amt,'PY' trx from dual
union all
select 6 id,50 amt,'PY' trx from dual
)
select case when trx = 'PR' then 'PURCHASE' else 'PAYMENT' end trx_type,
amt,
sum(case when trx = 'PY' then -amt else amt end) over(order by id) balance
from temp