 # 1. 對於複雜的數值計算而言，SQL 並非首選工具 # 2. 求和 ## 2.1. SUM函數會忽略Null ...
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
