SQL实例 - 数值计算

1.计算平均值

select deptno,avg(sal) as avg_sal from emp group by deptno

2.求某列中的最小值最大值

select deptno, min(sal) as min_sal, max(sal) as max_sal from emp group by deptno

3.对某列的值求和

select sum(sal) from emp group by deptno

4.求一个表的行数

select count(*) fom emp group by deptno

5.求某列值的个数，非null值的个数

select count(comm) from emp group by deptno

6.生成累计和

mysql,postgreSQL,sqlserver - 自链接子查询

select e.ename,e.sal,
(select sum(d.sal) from emp d where d.empno<=e.deptno) as running_total 
from emp e order by running_total

db2,oracle - 窗口函数

select ename,sal, sum(sal) over (order by sal,empno) as running_total from emp order by sal

7.生成累乘积

db2,oracle - 窗口函数

select empno,ename,sal,exp(sum(ln(sal)) over (order by sal,empno)) as running_prod 
from emp where deptno=10;

mysql,postgreSQL,sqlserver - 自链接子查询

select e.empno,e.ename,e.sal,
(select exp(sum(ln(d.sal))) 
from emp d where d.empno<=e.empno and e.deptno=d.deptno) as running_prod
from emp e where e.deptno=10

Note：sqlserver 要使用log代替ln

 

8.计算累计差
略，少见

 

9.计算模式
模式：对给定数据集出现最频繁的元素

db2,oracle - 窗口函数

select sal from ( select sal,dense_rank() over (order by cnt desc) as rnk
from (select sal,count(*) as cnt from emp where deptno=20
group by sal) x ) y where rnk=1;

mysql,postgreSQL,sqlserver - 自链接子查询

select sal from emp where deptno=20 group by sal
having count(*)>= all(select count(*) from emp where deptno=20 group by sal);

 

row_number 排序值从小到大,依次排列

dense_rank 相同数据,并列保存,不存在断值(一直连续)

rank 相同数据并列保存,下一个值跳值(断续)

 

 

 

10.计算中间值

db2 - 窗口函数

select avg(sal) from 
　　(select sal,count(*) over() total,cast(count(*) over() as decimal)/2 mid,
　　ceil(cast(count(*) over as decimal)/2) next,
　　row_number() over (order by sal) rn from emp where dept=20)x 
where (mod(total,2)=0 and rn in(mid,mid+1)) or(mod(total,2)=1 and rn=next);

sqlserver - 窗口函数(细节不同）

select avg(sal) from 
　　(select sal,count(*) over() total,cast(count(*) over() as decimal)/2 mid,
　　ceil(cast(count(*) over as decimal)/2) next,
　　row_number() over (order by sal) rn from emp where dept=20)x 
where (total%2=0 and rn in(mid,mid+1)) or(total%2=1 and rn=next);

mysql,postgreSQL - 自链接子查询

select avg(sal) from (select e.sal from emp e,emp d
where e.deptno=d.deptno and e.deptno=20 group by e.sal
having sum(case when e.sal=d.sal then 1 else 0 end)>=abs(sum(sign(e.sal-d.sal))));

oracle - median语法

select median(sal) from emp where deptno=20;

11.求总和的百分比

db2,oracle,sqlserver - 窗口函数

select distinct (d10/total)*100 as pct from
　　(select deptno,sum(sal) over() total,sum(sal) over (partition by deptno) d10 from emp ) x 
where deptno=10;

mysql,postgreSQL - 自链接子查询

select (sum(case when deptno=10 then sal end)/sum(sal))* 100 as pct from emp;

 

 

12.对可空列做聚集

select avg(coalesce(comm,0)) as avg_comm from emp where deptno=30;

 

13.计算不包含最大值和最小值的均值

db2,oracle,sqlserver - 窗口函数

select avg(sal) from (
select sal,min(sal) over() min_sal,max(sal) over max_sal from emp
) x where sal not in (min_sal,max_sal);

mysql,postgreSQL,sqlserver - 并列的标量子查询

select avg(sal) from emp where sal not in
　　(select min(sal) from emp,select max(sal) from emp);

14.将字母数字转换为数值

db2,oracle

select cast(replace(translate(ename,repeat('#',26),'qwertyuioplkjhgfdsazxcvbnm'),'#','') as integer) as num from emp;
oracle,postgresql : select cast(replace(translate(ename,'qwertyuioplkjhgfdsazxcvbnm',rpad('#',26,'#')),'#','') as integer) as num from emp;

mysql和sqlserver暂时不支持translate函数

 

15.更改累积和中的值
虽然表中数据均是正值，但是在某种类型下是以负值累计的

较少见，略

Note:使用数字统计的时候，要进行合理的检查，特别是在多条件以及表联接查询的时候

整理自SQL Cookbook 系列 - 使用数字_且行路莫问蹇的博客-CSDN博客等