关于数字

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