SQL基础操作_6_处理数字

原创

Shen Liang 2023-02-21 10:05:30 博主文章分类：SQL案例 ©著作权

文章标签 处理数字 mysql 数据库 sql mariadb 文章分类 Redis 数据库

©著作权归作者所有：来自51CTO博客作者Shen Liang的原创作品，请联系作者获取转载授权，否则将追究法律责任

7.7 处理数字

7.7.1 计算某列的最小/大值

7.7.2 计算某列的平均值

7.7.3 计算某列的总和

7.7.4 计算表的行数

7.7.5 非NULL值的列的个数

7.7.6 NULL值的列的个数

7.7.7 含NULL值的列的聚合

7.7.8 计算累加值

7.7.9 计算累加乘

7.7.10 计算累加差

7.7.11 计算某列重复最多的值

7.7.12 计算某列的中间值

7.7.13 计算组内总和的占比

7.7.14 计算组总和的占比

7.7.15 计算不含最大/小值的均值

7.7.16 计算标准差

7.7 处理数字

注:数据集和表结构见 SQL基础操作_1_检索数据

7.7.1 计算某列的最小/大值

需求：按照部门编号分组找出EMP表中工资最低/最高值,同时返回出deptno

解决方法:通过聚合函数MIN/MAX结合GROUP BY来完成.

SQLServer、Mysql、Oracle:

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

执行结果:

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

延展阅读:

如果想计算出所有emp表里工资的最低和最高,则只需要取MIN和MAX即可,不加GROUP BY.

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

执行结果:

Min_SAL	Max_SAL
800.00	5000.00

7.7.2 计算某列的平均值

需求：按照部门编号分组找出EMP表中工资平均值,同时返回出deptno

解决方法:通过聚合函数AVG结合GROUP BY来完成.

SQLServer、Mysql、Oracle:

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

执行结果:

DEPTNO	Avg_SAL
10	2916.666667
20	2175.000000
30	1566.666667

7.7.3 计算某列的总和

需求：按照部门编号分组找出EMP表中工资总和,同时返回出deptno

解决方法:通过聚合函数SUN结合GROUP BY来完成.

SQLServer、Mysql、Oracle:

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

执行结果:

DEPTNO	Sum_SAL
10	8750.00
20	10875.00
30	9400.00

7.7.4 计算表的行数

需求: 计算EMP表的总行数.

解决方法:通过COUNT(*)来完成.

SQLServer、Mysql、Oracle:

SELECT COUNT(*) AS rowCnt
FROM emp

执行结果:

rowCnt
14

7.7.5 非NULL值的列的个数

需求：取EMP表里COMM字段非空的个数.

解决方法:通过COUNT(comm)来完成.

Sql Server、Mysql、Oracle：

SELECT COUNT(comm) AS Comm_Cnt FROM emp

执行结果:

Comm_Cnt
4

7.7.6 NULL值的列的个数

需求：取EMP表里COMM字段为空的个数.

解决方法:通过SUM CASEWHEN comm IS NULL … 来统计.

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;

执行结果:

NULL_Num
10

7.7.7 含NULL值的列的聚合

需求：验证行NULL的列的聚合函数的计算结果.

解决方法:通过MIN、MAX、SUM、AVG、COUNT(*)来完成.

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 DATA
   UNION ALL
SELECT NULL AS DATA
   UNION ALL
SELECT 3 AS DATA
   UNION ALL
SELECT NULL AS DATA
)A

执行结果:

Min_Data	Max_Data	Sum_Data	Avg_Data	Row_Cnt
1	3	4	2	4

由上面的结果可见,AVG时NULL值未参与运算.

7.7.8 计算累加值

需求：计算EMP表里SAL字段的累加值,这里按照SAL、EMPNO排序.

解决方法:通过SUM OVER()函数来完成.

Sql Server、Oracle：

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

执行结果：

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_Cnt

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

7.7.9 计算累加乘

需求：计算EMP表里SAL字段的累计乘积值,这里按照SAL、EMPNO排序.

解决方法:通过SUM OVER()EXP LOG LN函数来完成.

Sql Server：

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

执行结果:

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_Cnt

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

7.7.10 计算累加差

需求：计算EMP表里SAL字段的累计乘积值,这里按照SAL、EMPNO排序.

解决方法:通过SUM OVER()函数来完成,这里SAL要取反,即SAL的负数.

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

执行结果:

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:

暂未实现.

7.7.11 计算某列重复最多的值

需求：计算EMP表里SAL字段重复最多的值,以部门编号为20的为例.

解决方法:通过窗口函数或则借助聚合函数来完成.

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 计算某列的中间值

需求：计算EMP表按照deptno分组，找出排在中间的SAL

解决方法:通过窗口函数集合COUNT（*）通过序号取模来判断.

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 emp
   GROUP 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

执行结果：

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

同理我们可以在mysql实现：

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_deptno
        FROM ( 
        SELECT A.deptno,A.sal,A.empno FROM emp A
        ORDER BY deptno,sal
        ) heyf_tmp ,
        (SELECT @rownum :=0 , @pdept :=NULL ,@rank :=0) a
)A
JOIN
(
SELECT deptno,COUNT(*) AS row_CN FROM emp
   GROUP 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

执行结果：

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

7.7.13 计算组内总和的占比

需求：计算EMP表按照deptno分组，计算SAL在每组总和中的占比

解决方法:通过每行的SAL除以每组的SUM（SAL）来实现.

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

执行结果：

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 计算组总和的占比

需求：计算EMP表按照deptno分组，计算每组的SAL总和与所以记录总和中的占比

解决方法:通过每组SUM（SAL）窗口函数再除以记录总和来实现.

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

执行结果：

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

执行结果:

deptno	group_percent
10	0.30
20	0.37
30	0.32

7.7.15 计算不含最大/小值的均值

需求：计算EMP表按照deptno分组，计算每组的SAL均值,这里不包含最大和最小值.

解决方法:通过每组踢除最大/小值再AVG的方式来实现.

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 EMP
   GROUP BY deptno 
UNION
   SELECT deptno,MAX(SAL) AS SAL FROM EMP
   GROUP BY deptno
)B
ON A.deptno = B.deptno AND A.SAL = B.SAL
WHERE B.deptno IS NULL
GROUP BY A.deptno

执行结果:

deptno	avg_SAL
10	2450.000000
20	2037.500000
30	1400.000000

7.7.16 计算标准差

需求：计算EMP表按照deptno分组，计算每组的SAL的标准差.

解决方法:通过每组踢出最大/小值再AVG的方式来实现.

SQL Server:

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

执行结果:

DEPTNO	Stdevp_Value
10	1546.14215244122
20	1004.73877201987
30	610.100173924104

注:

1) 标准差的公式见下:

SQL基础操作_6_处理数字_处理数字

2) STDEVP和STDEV的区别是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

结果同上.