SQL练习---------数值处理

1.1 计算平均值

从emp表查询全部员工得平均工资，同时也查询每个部门得平均工资

计算所有员工的平均工资，只需要针对SAL列调用AVG函数即可。AVG函数会计算所有非NULL的值。

SELECT AVG(sal) AS avg_sal
FROM emp

SELECT deptno,AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno

计算每个部门的平均工资

 

1.2 查找最小值和最大值

查找EMP表全体员工的最低工资和最高工资

SELECT MIN(sal) AS min_sal,MAX(sal) AS max_sal
FROM emp

查找部门里的最低工资和最高工资（有些部门只有一个人，所以最大值和最小值一样）

SELECT MIN(sal) AS min_sal,MAX(sal) AS max_sal
FROM emp
GROUP BY deptno

MIN函数和MAX函数会忽略null,而查询过程可能会遇到null分组，也可能在一个分组里遇到null值。比如下面查询的某些分组会烦返回null值

SELECT deptno,comm
FROM emp
ORDER BY 1

SELECT MIN(comm),MAX(comm)
FROM emp

SELECT deptno,MIN(comm),MAX(comm)
FROM emp
GROUP BY deptno

 

 

1.3 求和

计算EMP表全体员工的工资总和

SELECT SUM(sal)
FROM emp

如歌把数据分为多组，就需要使用SUM函数和GROUP BY 子句。

按照部门员工计算的工资总额

SELECT deptno,SUM(sal) AS total_dept
FROM emp
GROUP BY deptno

 

当计算各部门的工资总额时，其实是在怼数据进行分组。对于每个部门而言，所有员工的工资会被相加，并得出总和。SUM函数会忽略NULL，但是在查询过程可能会遇到NULL分组。举例计算COMM的总和会烦恼null

SELECT deptno,comm
FROM emp
ORDER BY 1

SELECT SUM(comm)
FROM emp

SELECT deptno,SUM(comm)
FROM emp
GROUP BY deptno

 

1.4 计算行数

得出EMP表员工总数

SELECT COUNT(*)
FROM emp

各部门的员工数量

SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno

 

1.5 计算非null值得个数

查询有多少名员工获得提成(即COMM列不为空)

SELECT COUNT(comm)
FROM emp

当执行COUNT(*)操作时，实际上是在统计行数，而不会去管实际得值是上面，这就是为什么NULL值和非NULL值都会被计入总数。但是，如果只是针对某一列执行COUNT操作，得到得结果是该列非NULL的个数。

 

1.6 累计求和

计算全体员工工资的累计额。

SELECT e.`ENAME`,e.`SAL`,
	(SELECT SUM(d.`SAL`) FROM emp d
	  WHERE d.empno <= e.`EMPNO`) AS runing_total
FROM emp e

使用标量子查询来进行累计求和。必须通过一个有唯一值的列做连接查询，否则，如果有两个员工的工资相同，那么得到到的累计和就是错误的。对于上面而言，关键在于把D.EMPNO和E.EMPNO连接起来，并且针对每个小于或者等于E.EMPNO的D.EMPNO计算对应的D.SAL。如果把标量子查询改写成一个EMP表全体数据与部分数据之间做的连接查询，会更加容易理解一点。

SELECT e.`ENAME` AS ename1,e.`EMPNO` AS empno1,e.`SAL` AS sal1,
	d.`ENAME` AS ename2,d.`EMPNO` AS empno2,d.`SAL` AS sal2
FROM emp e,emp d
WHERE d.`EMPNO` <= e.`EMPNO`
AND e.`EMPNO`  = 1255

EMPNO2列中的每一个值都会和EMPNO1列中对应的值进行比较。如果EMPNO2列中的值小于或者等于EMPNO1列中对应的值，则其中对应SAL2列的值会被计入总和。对于上述查询。员工马化腾，农大炮，马云，曾小贤，杨幂的EMPNO值会和杨幂的EMPNO值相比较。由于5个员工的EMPNO的值都满足小于或者等于杨幂的EMPNO的值这一条件，因此他们的工资都会被计入总和。反之，任何EMPNO值大于杨幂的EMPNO值得员工，其工资都不会被计入总和。

 

1.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` = 2

实现步骤：

计算它们各自得自然对数，把自然对数得计算结果累加起来，把累加结果作为指数，以数学常量e为底数，进行幂运算(使用EXP函数)

该做法不适用于零和负数的累计，因为SQL的对函数不支持小于或者等于零的值。

 

1.8 计算累计差

计算某个数值列的累计差。

计算DEPTNO等于2的部门里员工工资的累计差

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` = 2

 

1.9 计算众数

找出SAL的众数，由此看出众数是8800

SELECT sal
FROM emp
GROUP BY sal
HAVING COUNT(*) >= ALL(SELECT COUNT(*)
			FROM emp
			GROUP BY sal)

 

子查询返回每一个SAL值的出现次数。外层查询返回出现次数大于或者等于子查询全部的返回值SAL值，即外层查询DEPTNO等于2的部门里出现次数最多的工资值

 

1.10 计算百分比

计算deptno等于1的工资总额除以全体员工工资总额

SELECT(SUM(
	CASE WHEN deptno = 1 THEN sal END)/SUM(sal)
	)*100 AS pct
FROM emp

 

CASE语句很容易筛选出DEPTNO等于1的工资值，把这些数加起来就可以得到工资和，然后除以工资总和。因为聚合函数忽略NULL，所以不需要再CASE语句的后面加ELSE子句。为了清除地看到除数和被除数，先去掉除法运算。

SELECT SUM(CASE WHEN deptno = 1 THEN sal END) AS D1,
	SUM(sal)
FROM emp

 

1.11 聚合NULL列

如果想针对某列做聚合运算，但是该列的值为NULL。想保持聚合运算结果的准确性，又担心聚合函数会忽略NULL值。这时候可以使用COALESCE函数把NULL转换0，这样聚合函数就能处理了。

比如现在想知道EMP表中员工的平均业务提成，但是部分员工实际上没有获得提成(这些人的COMM列是NULL)。

SELECT AVG(COALESCE(comm,0)) AS avg_comm
FROM emp

 

如果不使用COALESCE函数输出 结果会出错

SELECT AVG(comm)
FROM emp

SELECT ename,comm
FROM emp
ORDER BY comm DESC

以上结果集显示，在7个员工中，只有5个能领取业务提成。获取提成总额是4000，因而平均值应该是4000/7，而不是4000/5.如果不使用COALESCE函数，答案就会出现差异。

 

1.12 计算平均值时去掉最大值和最小值

SELECT AVG(sal)
FROM emp
WHERE sal NOT IN(
	(SELECT MIN(sal) FROM emp),
	(SELECT MAX(sal) FROM emp)
)

子查询能找到最高和对子的工资值。针对它们使用NOT IN之后，在计算平均值时就能去掉这些值。如果最大值和最小值有重复值(最高或者最低工资对应的员工不止一个人)，那么所有重复值将会被过滤掉。如果只希望去掉一个最大值和最小值，只需要把它们从合计值里先去掉，再做除法即可。

SELECT(SUM(sal)-MIN(sal)-MAX(sal))/(COUNT(*)-2)
FROM emp

 

1.13 修改累计值

显示一个信用卡账户的交易历史，并显示每一笔交易完成后的余额。

创建一个信用卡视图

CREATE VIEW v(ID,ATM,TRX)
AS
SELECT 1,100,'PR' FROM t1 UNION ALL
SELECT 5,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 

ID列能唯一地标示每一笔交易。ATM列代表每一笔交易涉及的金额。TRX列定义交易的类型：还款是PY,购物是PR.如果TRX的值是PY，就从累计值里减去当前的ATM值。日后TRX的值是PR，就从累计值加上当前的ATM值。

SELECT CASE WHEN v1.trx = 'PY'
	    THEN 'PAYMENT'
	    ELSE 'PURCHASE'
	 END AS trx_type,
	 v1.atm,
	 (SELECT SUM(
		  CASE WHEN v2.trx = 'PY'
		       THEN -v2.atm ELSE v2.atm
		   END
	 )
	 FROM v v2
	 WHERE v2.id <= v1.id) AS balance
FROM v v1

CASE表达式用于决定是把当前的ATM值加到累计值中，还是从累计值中减去当前的ATM值。如果交易类型是还款，ATM值会变成负数，因而相应的累计值会减少。

SELECT CASE WHEN trx = 'PY'
	    THEN 'PAYMENT'
	    ELSE 'PURCHASE'
	 END AS trx_type,
	 CASE WHEN trx = 'PY'
	     THEN -atm ELSE atm
END AS ATM
FROM v

依据交易类型的评估结果，ATM值会被加入累计值，或者从累计值减去。