选读SQL经典实例笔记09_数值处理.png
1. 对于复杂的数值计算而言，SQL 并非首选工具
2. 求和
2.1. SUM函数会忽略Null，但是我们可能会遇到Null分组
2.2. sql
select deptno, comm
from emp
where deptno in (10,30)
order by 1
DEPTNO COMM
---------- ----------
10
10
10
30 300
30 500
30
30 0
30 1300
30
select sum(comm)
from emp
SUM(COMM)
----------
2100
select deptno, sum(comm)
from emp
where deptno in (10,30)
group by deptno
DEPTNO SUM(COMM)
---------- ----------
10
30 2100
3. 行数
3.1. COUNT函数会忽略Null
3.2. 使用符号*或者常量参数的时候，就会包含Null
4. 累计求和
4.1. DB2
4.2. Oracle
4.3. 使用SUM函数的窗口函数版本进行累计求和
select ename, sal,
sum(sal) over (order by sal,empno) as running_total
from emp
order by 2
ENAME SAL RUNNING_TOTAL
---------- ---------- -------------
SMITH 800 800
JAMES 950 1750
ADAMS 1100 2850
WARD 1250 4100
MARTIN 1250 5350
MILLER 1300 6650
TURNER 1500 8150
ALLEN 1600 9750
CLARK 2450 12200
BLAKE 2850 15050
JONES 2975 18025
SCOTT 3000 21025
FORD 3000 24025
KING 5000 29025
4.4. PostgreSQL
4.5. MySQL
4.6. SQL Server
4.7. 使用标量子查询来进行累计求和
select e.ename, e.sal,
(select sum(d.sal) from emp d
where d.empno ＜= e.empno) as running_total
from emp e
order by 3
ENAME SAL RUNNING_TOTAL
---------- ---------- -------------
SMITH 800 800
ALLEN 1600 2400
WARD 1250 3650
JONES 2975 6625
MARTIN 1250 7875
BLAKE 2850 10725
CLARK 2450 13175
SCOTT 3000 16175
KING 5000 21175
TURNER 1500 22675
ADAMS 1100 23775
JAMES 950 24725
FORD 3000 27725
MILLER 1300 29025
5. 累计乘积
5.1. DB2
5.2. Oracle
5.3. 使用窗口函数SUM OVER，并利用对数来模拟乘法
select empno,ename,sal,
exp(sum(ln(sal))over(order by sal,empno)) as running_prod
from emp
where deptno = 10
EMPNO ENAME SAL RUNNING_PROD
----- ---------- ---- --------------------
7934 MILLER 1300 1300
7782 CLARK 2450 3185000
7839 KING 5000 15925000000
5.4. PostgreSQL
5.5. MySQL
5.6. SQL Server
5.7. 标量子查询
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
EMPNO ENAME SAL RUNNING_PROD
----- ---------- ---- --------------------
7782 CLARK 2450 2450
7839 KING 5000 12250000
7934 MILLER 1300 15925000000
5.7.2. 对于SQL Server而言，还需要用LOG函数来替代LN函数
6. 累计差
6.1. DB2
6.2. Oracle
6.3. 使用窗口函数SUM OVER
select ename,sal,
sum(case when rn = 1 then sal else -sal end)
over(order by sal,empno) as running_diff
from (
select empno,ename,sal,
row_number() over(order by sal,empno) as rn
from emp
where deptno = 10
) x
6.4. PostgreSQL
6.5. MySQL
6.6. SQL Server
6.7. 使用标量子查询
select a.empno, a.ename, a.sal,
(select case when a.empno = min(b.empno) then sum(b.sal)
else sum(-b.sal)
end
from emp b
where b.empno ＜= a.empno
and b.deptno = a.deptno ) as rnk
from emp a
where a.deptno = 10
7. 众数
7.1. 在一组数据里出现次数最多的那个数
7.2. DB2
7.3. SQL Server
7.4. 使用窗口函数DENSE_RANK
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
7.5. Oracle
select max(sal)
keep(dense_rank first order by cnt desc) sal
from (
select sal, count(*) cnt
from emp
where deptno=20
group by sal
)
7.6. PostgreSQL
7.7. MySQL
7.8. 使用子查询
select sal
from emp
where deptno = 20
group by sal
having count(*) ＞= all ( select count(*)
from emp
where deptno = 20
group by sal )
8. 中位数
8.1. 按顺序排列的一组数据中居于中间位置的数
8.2. 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 deptno = 20
) x
where ( mod(total,2) = 0
and rn in ( mid, mid+1 )
)
or ( mod(total,2) = 1
and rn = next
)
8.2.2. DB2则使用MOD函数
8.3. SQL Server
select avg(sal)
from (
select sal,
count(*) over() total,
cast(count(*) over() as decimal)/2 mid,
ceiling(cast(count(*)over() as decimal)/2) next,
row_number() over(order by sal) rn
from emp
where deptno = 20
) x
where ( total%2 = 0
and rn in ( mid, mid+1 )
)
or ( total%2 = 1
and rn = next
)
8.3.2. SQL Server的取模运算符是%
8.4. Oracle
select median(sal)
from emp
where deptno=20
8.4.1.1. Oracle Database 10g
select percentile_cont(0.5)
within group(order by sal)
from emp
where deptno=20
8.4.2.1. Oracle 9i
8.5. PostgreSQL
8.6. MySQL
8.7. 使用自连接查询
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)))
)
9. 百分比
9.1. 某一列的值占总和的百分比
9.2. DB2
9.3. Oracle
9.4. SQL Server
9.5. sql
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
9.6. MySQL
9.7. PostgreSQL
9.8. sql
select (sum(
case when deptno = 10 then sal end)/sum(sal)
)*100 as pct
from emp
10. 聚合Null列
10.1. 使用聚合函数时一定要记住，Null值会被忽略
10.2. 一旦涉及聚合运算，就要相应地考虑如何处理Null值
select avg(coalesce(comm,0)) as avg_comm
from emp
where deptno=30
11. 计算平均值时去掉最大值和最小值
11.1. DB2
11.2. Oracle
11.3. SQL Server
11.4. 窗口函数MAX OVER和MIN OVER
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)
11.5. PostgreSQL
11.6. MySQL
11.7. 使用子查询去掉最大值和最小值
select avg(sal)
from emp
where sal not in (
(select min(sal) from emp),
(select max(sal) from emp)
)
11.7.2. 如果希望只去掉一个最大值和一个最小值，只需要把它们从合计值里先减掉，再做除法即可
select (sum(sal)-min(sal)-max(sal))/(count(*)-2)
from emp
12. 修改累计值
12.1. 示例
create view V (id,amt,trx)
as
select 1, 100, 'PR' from t1 union all
select 2, 100, 'PR' from t1 union all
select 3, 50, 'PY' from t1 union all
select 4, 100, 'PR' from t1 union all
select 5, 200, 'PY' from t1 union all
select 6, 50, 'PY' from t1
select * from V
ID AMT TRX
-- ---------- ---
1 100 PR
2 100 PR
3 50 PY
4 100 PR
5 200 PY
6 50 PY
12.2. DB2
12.3. Oracle
12.4. 使用窗口函数SUM OVER进行累计求和
select case when trx = 'PY'
then 'PAYMENT'
else 'PURCHASE'
end trx_type,
amt,
sum(
case when trx = 'PY'
then -amt else amt
end
) over (order by id,amt) as balance
from V
12.4.2. 使用CASE表达式来决定交易的类型
12.5. PostgreSQL
12.6. MySQL
12.7. SQL Server
12.8. 使用标量子查询进行累计求和
select case when v1.trx = 'PY'
then 'PAYMENT'
else 'PURCHASE'
end as trx_type,
v1.amt,
(select sum(
case when v2.trx = 'PY'
then -v2.amt else v2.amt
end
)
from V v2
where v2.id ＜= v1.id) as balance
from V v1
