目錄

7.7 處理數(shù)字

7.7.1 計(jì)算某列的最小/大值

7.7.2 計(jì)算某列的平均值

7.7.3 計(jì)算某列的總和

7.7.4 計(jì)算表的行數(shù)

7.7.5 非NULL值的列的個(gè)數(shù)

7.7.6 NULL值的列的個(gè)數(shù)

7.7.7 含NULL值的列的聚合

7.7.8 計(jì)算累加值

7.7.9 計(jì)算累加乘

7.7.10 計(jì)算累加差

7.7.11 計(jì)算某列重復(fù)最多的值

7.7.12 計(jì)算某列的中間值

7.7.13 計(jì)算組內(nèi)總和的占比

7.7.14 計(jì)算組總和的占比

7.7.15 計(jì)算不含最大/小值的均值

7.7.16 計(jì)算標(biāo)準(zhǔn)差

---

7.7 處理數(shù)字

注:數(shù)據(jù)集和表結(jié)構(gòu)見?SQL基礎(chǔ)操作_1_檢索數(shù)據(jù)

7.7.1 計(jì)算某列的最小/大值

需求：按照部門編號(hào)分組找出EMP表中工資最低/最高值,同時(shí)返回出deptno

解決方法:通過(guò)聚合函數(shù)MIN/MAX結(jié)合GROUP BY來(lái)完成.

SQLServer、Mysql、Oracle:

SELECT DEPTNO,MIN(SAL) Min_SAL,MAX(SAL) Max_SAL FROM emp GROUP BY DEPTNO

執(zhí)行結(jié)果:

DEPTNO	Min_SAL	Max_SAL
10	1300.00	5000.00
20	800.00	3000.00
30	950.00	2850.00

延展閱讀:

如果想計(jì)算出所有emp表里工資的最低和最高,則只需要取MIN和MAX即可,不加GROUP BY.

SELECT MIN(SAL) Min_SAL,MAX(SAL) Max_SAL FROM emp

執(zhí)行結(jié)果:

Min_SAL	Max_SAL
800.00	5000.00

7.7.2 計(jì)算某列的平均值

需求：按照部門編號(hào)分組找出EMP表中工資平均值,同時(shí)返回出deptno

解決方法:通過(guò)聚合函數(shù)AVG結(jié)合GROUP BY來(lái)完成.

SQLServer、Mysql、Oracle:

SELECT DEPTNO,AVG(SAL) Avg_SAL FROM emp GROUP BY DEPTNO

執(zhí)行結(jié)果:

DEPTNO	Avg_SAL
10	2916.666667
20	2175.000000
30	1566.666667

7.7.3 計(jì)算某列的總和

需求：按照部門編號(hào)分組找出EMP表中工資總和,同時(shí)返回出deptno

解決方法:通過(guò)聚合函數(shù)SUN結(jié)合GROUP BY來(lái)完成.

SQLServer、Mysql、Oracle:

SELECT DEPTNO,SUM(SAL) Sum_SAL FROM emp GROUP BY DEPTNO

執(zhí)行結(jié)果:

DEPTNO	Sum_SAL
10	8750.00
20	10875.00
30	9400.00

7.7.4 計(jì)算表的行數(shù)

需求: 計(jì)算EMP表的總行數(shù).? ? ? ? ? ? ? ??

解決方法:通過(guò)COUNT(*)來(lái)完成.

SQLServer、Mysql、Oracle:

SELECT COUNT(*) AS rowCnt FROM emp

執(zhí)行結(jié)果:

rowCnt

14

7.7.5 非NULL值的列的個(gè)數(shù)

需求：取EMP表里COMM字段非空的個(gè)數(shù).? ? ??

解決方法:通過(guò)COUNT(comm)來(lái)完成.

Sql Server、Mysql、Oracle：

SELECT COUNT(comm) AS Comm_Cnt FROM emp

執(zhí)行結(jié)果:

Comm_Cnt

4

7.7.6 NULL值的列的個(gè)數(shù)

需求：取EMP表里COMM字段為空的個(gè)數(shù).? ? ??

解決方法:通過(guò)SUM CASEWHEN comm IS NULL … 來(lái)統(tǒng)計(jì).

Sql Server、Mysql、Oracle：

SELECT SUM(CASE WHEN comm IS NULL THEN 1 ELSE 0 END) AS NULL_Num FROM emp WHERE comm IS NULL;

執(zhí)行結(jié)果:

NULL_Num

10

7.7.7 含NULL值的列的聚合

需求：驗(yàn)證行NULL的列的聚合函數(shù)的計(jì)算結(jié)果.

解決方法:通過(guò)MIN、MAX、SUM、AVG、COUNT(*)來(lái)完成.

Sql Server、Mysql、Oracle：

SELECT MIN(DATA) Min_Data,MAX(DATA) Max_Data,SUM(DATA) Sum_Data,AVG(DATA) Avg_Data,COUNT(*) AS Row_Cnt FROM ( SELECT 1 AS DATAUNION ALL SELECT NULL AS DATAUNION ALL SELECT 3 AS DATAUNION ALL SELECT NULL AS DATA )A

執(zhí)行結(jié)果:

Min_Data	Max_Data	Sum_Data	Avg_Data	Row_Cnt
1	3	4	2	4

由上面的結(jié)果可見,AVG時(shí)NULL值未參與運(yùn)算.

7.7.8 計(jì)算累加值

需求：計(jì)算EMP表里SAL字段的累加值,這里按照SAL、EMPNO排序.

解決方法:通過(guò)SUM OVER()函數(shù)來(lái)完成.

Sql Server、Oracle：

SELECT sal,empno,SUM(sal)OVER(ORDER BY sal,empno) Accu_Num FROM emp;

執(zhí)行結(jié)果：

sal	empno	accu_Num
800.00	7369	800.00
950.00	7900	1750.00
1100.00	7876	2850.00
1250.00	7521	4100.00
1250.00	7654	5350.00
1300.00	7934	6650.00
1500.00	7844	8150.00
1600.00	7499	9750.00
2450.00	7782	12200.00
2850.00	7698	15050.00
2975.00	7566	18025.00
3000.00	7788	21025.00
3000.00	7902	24025.00
5000.00	7839	29025.00

Sql Server、Oracle 、Mysql：

SELECT B.sal,B.empno,(SELECT SUM(A.sal) FROM emp A WHERE A.empno<=B.empno) AS Accu_Cnt FROM emp B ORDER BY Accu_CntSELECT B.sal,B.empno,SUM(A.sal) AS Accu_Cnt FROM emp B JOIN emp AON A.empno <=B.empno GROUP BY B.sal,B.empno ORDER BY Accu_Cnt

7.7.9 計(jì)算累加乘

需求：計(jì)算EMP表里SAL字段的累計(jì)乘積值,這里按照SAL、EMPNO排序.

解決方法:通過(guò)SUM OVER()EXP LOG LN函數(shù)來(lái)完成.

Sql Server：

SELECT sal,empno,LOG(sal) AS Log_Sal,EXP(SUM(LOG(sal))OVER(ORDER BY sal,empno)) Accu_Prod FROM emp;

執(zhí)行結(jié)果:

sal	empno	Log_Sal	Accu_Prod
800.00	7369	6.68461172766793	800
950.00	7900	6.85646198459459	759999.999999999
1100.00	7876	7.00306545878646	836000000
1250.00	7521	7.13089883029635	1045000000000
1250.00	7654	7.13089883029635	1.30625E+15
1300.00	7934	7.17011954344963	1.698125E+18
1500.00	7844	7.3132203870903	2.5471875E+21
1600.00	7499	7.37775890822787	4.07550000000002E+24
2450.00	7782	7.80384330353877	9.98497500000001E+27
2850.00	7698	7.9550742732627	2.845717875E+31
2975.00	7566	7.99799931797973	8.46601067812504E+34
3000.00	7788	8.00636756765025	2.5398032034375E+38
3000.00	7902	8.00636756765025	7.61940961031249E+41
5000.00	7839	8.51719319141624	3.80970480515626E+45

Oracle：

SELECT sal,empno,LN(sal) AS Log_Sal,EXP(SUM(LN(sal))OVER(ORDER BY sal,empno)) Accu_Prod FROM emp;

Mysql:

SELECT B.sal,B.empno,(SELECT exp(SUM(ln(A.sal))) FROM emp A WHERE A.empno<=B.empno) AS Accu_Cnt FROM emp B ORDER BY Accu_CntSELECT B.sal,B.empno,exp(SUM(ln(A.sal))) AS Accu_Cnt FROM emp B JOIN emp AON A.empno <=B.empno GROUP BY B.sal,B.empno ORDER BY Accu_Cnt

7.7.10 計(jì)算累加差

需求：計(jì)算EMP表里SAL字段的累計(jì)乘積值,這里按照SAL、EMPNO排序.

解決方法:通過(guò)SUM OVER()函數(shù)來(lái)完成,這里SAL要取反,即SAL的負(fù)數(shù).

Sql Server、Oracle:

SELECT deptno,sal,empno,SUM(CASE WHEN RN=1 THEN SAL ELSE -sal END)OVER(PARTITION BY deptno ORDER BY sal,empno) Accu_Diff FROM ( SELECT deptno,sal,empno,ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal,empno)RN FROM emp )A

執(zhí)行結(jié)果:

deptno	sal	empno	Accu_Diff
10	1300.00	7934	1300.00
10	2450.00	7782	-1150.00
10	5000.00	7839	-6150.00
20	800.00	7369	800.00
20	1100.00	7876	-300.00
20	2975.00	7566	-3275.00
20	3000.00	7788	-6275.00
20	3000.00	7902	-9275.00
30	950.00	7900	950.00
30	1250.00	7521	-300.00
30	1250.00	7654	-1550.00
30	1500.00	7844	-3050.00
30	1600.00	7499	-4650.00
30	2850.00	7698	-7500.00

Mysql:

暫未實(shí)現(xiàn).

?

7.7.11 計(jì)算某列重復(fù)最多的值

需求：計(jì)算EMP表里SAL字段重復(fù)最多的值,以部門編號(hào)為20的為例.

解決方法:通過(guò)窗口函數(shù)或則借助聚合函數(shù)來(lái)完成.

Sql Server、Mysql:

SELECT sal FROM emp WHERE deptno=20 GROUP BY sal HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM emp WHERE deptno =20 GROUP BY SAL) -- 或者將ALL換成MAX SELECT sal FROM emp WHERE deptno=20 GROUP BY sal HAVING COUNT(*) >= (SELECT MAX(RN) FROM(SELECT COUNT(*) AS RN FROM emp WHERE deptno =20 GROUP BY SAL)A )

Oracle：

7.7.12 計(jì)算某列的中間值

需求：計(jì)算EMP表按照deptno分組，找出排在中間的SAL

解決方法:通過(guò)窗口函數(shù)集合COUNT（*）通過(guò)序號(hào)取模來(lái)判斷.

SQL Server：

SELECT B.deptno,A.sal middle_Sal,B.row_CN,CASE WHEN (B.row_CN%2=0) THEN B.row_CN/2 ELSE (row_CN+1)/2 END Middle FROM ( SELECT empno,sal,deptno,ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal) AS RN FROM emp)A JOIN(SELECT deptno,COUNT(*) AS row_CN FROM empGROUP BY deptno)B ON A.deptno = B.deptno AND A.RN = CASE WHEN (B.row_CN%2=0) THEN B.row_CN/2 ELSE (row_CN+1)/2 END

執(zhí)行結(jié)果：

deptno	middle_Sal	row_CN	Middle
10	2450.00	3	2
20	2975.00	5	3
30	1250.00	6	3

同理我們可以在mysql實(shí)現(xiàn)：

SELECT B.deptno,A.SAL,B.row_CN,ROUND(CASE WHEN (B.row_CN%2=0) THEN B.row_CN/2 ELSE (row_CN+1)/2 END) AS Middle FROM ( SELECT heyf_tmp.deptno,heyf_tmp.empno,heyf_tmp.sal,@rownum :=@rownum+1AS rn ,IF(@pdept= heyf_tmp.deptno,@rank:=@rank+1, @rank:=1) AS rank,@pdept:=heyf_tmp.deptno AS pre_deptnoFROM ( SELECT A.deptno,A.sal,A.empno FROM emp AORDER BY deptno,sal) heyf_tmp ,(SELECT @rownum :=0 , @pdept :=NULL ,@rank :=0) a )A JOIN ( SELECT deptno,COUNT(*) AS row_CN FROM empGROUP BY deptno ) B ON A.deptno = B.deptno AND A.RANK =CASE WHEN (B.row_CN%2=0) THEN B.row_CN/2 ELSE (row_CN+1)/2 END

執(zhí)行結(jié)果：

deptno	SAL	row_CN	Middle
10	2450.00	3	2
20	2975.00	5	3
30	1250.00	6	3

7.7.13 計(jì)算組內(nèi)總和的占比

需求：計(jì)算EMP表按照deptno分組，計(jì)算SAL在每組總和中的占比

解決方法:通過(guò)每行的SAL除以每組的SUM（SAL）來(lái)實(shí)現(xiàn).

SQL Server：

SELECT A.deptno,SAL,CAST(ROUND(SAL/sum_num,2) AS DECIMAL(10, 2)) AS percent FROM emp A JOIN ( SELECT deptno,SUM(SAL) AS sum_num FROM emp GROUP BY deptno )B ON A.deptno = B.deptno

執(zhí)行結(jié)果：

deptno	SAL	percent
10	2450.00	0.28
10	5000.00	0.57
10	1300.00	0.15
20	3000.00	0.28
20	1100.00	0.10
20	3000.00	0.28
20	800.00	0.07
20	2975.00	0.27
30	1250.00	0.13
30	2850.00	0.30
30	1600.00	0.17
30	1250.00	0.13
30	1500.00	0.16
30	950.00	0.10

Mysql：

SELECT A.deptno,SAL,ROUND(SAL/sum_num,2) AS percent FROM emp A JOIN ( SELECT deptno,SUM(SAL) AS sum_num FROM emp GROUP BY deptno )B ON A.deptno = B.deptno

7.7.14 計(jì)算組總和的占比

需求：計(jì)算EMP表按照deptno分組，計(jì)算每組的SAL總和與所以記錄總和中的占比

解決方法:通過(guò)每組SUM（SAL）窗口函數(shù)再除以記錄總和來(lái)實(shí)現(xiàn).

Sql Server、Oracle：

SELECT DISTINCT deptno,SUM(SAL)OVER(PARTITION BY deptno) AS sum_num,SUM(SAL) OVER() AS sum_total, CAST(ROUND(SUM(SAL)OVER(PARTITION BY deptno)/SUM(SAL)OVER(),2)AS DECIMAL(10, 2)) AS group_percent FROM emp

執(zhí)行結(jié)果：

deptno	sum_num	sum_total	group_percent
10	8750.00	29025.00	0.30
20	10875.00	29025.00	0.37
30	9400.00	29025.00	0.32

Sql Server：

SELECT deptno,CAST(SUM(SAL)/(SELECT SUM(SAL) FROM EMP)AS DECIMAL(10, 2)) AS group_percent FROM EMP GROUP BY deptno

Mysql：

SELECT deptno,ROUND(SUM(SAL)/(SELECT SUM(SAL) FROM EMP), 2) AS group_percent FROM EMP GROUP BY deptno

執(zhí)行結(jié)果:

deptno	group_percent
10	0.30
20	0.37
30	0.32

?

7.7.15 計(jì)算不含最大/小值的均值

需求：計(jì)算EMP表按照deptno分組，計(jì)算每組的SAL均值,這里不包含最大和最小值.

解決方法:通過(guò)每組踢除最大/小值再AVG的方式來(lái)實(shí)現(xiàn).

Sql Server、Mysql、Oracle:

SELECT A.deptno,AVG(A.SAL) AS avg_SAL FROM EMP A LEFT JOIN ( SELECT deptno,MIN(SAL) AS SAL FROM EMPGROUP BY deptno UNIONSELECT deptno,MAX(SAL) AS SAL FROM EMPGROUP BY deptno )B ON A.deptno = B.deptno AND A.SAL = B.SAL WHERE B.deptno IS NULL GROUP BY A.deptno

執(zhí)行結(jié)果:

deptno	avg_SAL
10	2450.000000
20	2037.500000
30	1400.000000

7.7.16 計(jì)算標(biāo)準(zhǔn)差

需求：計(jì)算EMP表按照deptno分組，計(jì)算每組的SAL的標(biāo)準(zhǔn)差.

解決方法:通過(guò)每組踢出最大/小值再AVG的方式來(lái)實(shí)現(xiàn).

SQL Server:

SELECT DEPTNO,STDEVP(SAL) AS Stdevp_Value FROMEMP GROUP BY DEPTNO

執(zhí)行結(jié)果:

DEPTNO	Stdevp_Value
10	1546.14215244122
20	1004.73877201987
30	610.100173924104

注:

1)? 標(biāo)準(zhǔn)差的公式見下:

2) STDEVP和STDEV的區(qū)別是STDEVP除以的是N,而STDEV除以的是N-1

Mysql:

SELECT A.DEPTNO,SQRT(SUM(POWER((A.SAL-avg_SAL),2))/cnt) Stdevp_Value FROM EMP A JOIN ( SELECT DEPTNO,AVG(SAL) AS avg_SAL,COUNT(*) AS cnt FROM EMP GROUP BY DEPTNO )B ON A.deptno = B.deptno GROUP BY A.deptno

結(jié)果同上.

總結(jié)

以上是生活随笔為你收集整理的SQL基础操作_6_处理数字的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。