数据库语法总结（7）——数字处理

秃头小白菜

已于 2023-04-17 13:43:17 修改

阅读量337

点赞数

分类专栏：数据库文章标签： sql sqlserver oracle mysql 数据库

于 2023-04-14 17:49:20 首次发布

本文链接：https://blog.csdn.net/m0_61746796/article/details/130146201

版权

数据库专栏收录该内容

14 篇文章 2 订阅

订阅专栏

本文总结了数据库查询中涉及的数字处理方法，包括计算平均值、最大值最小值、总值、行数以及处理NULL值。还介绍了移动计数，如移动总计、移动总积和加权移动平均值的计算。此外，讨论了众数、中位数的求解以及百分比的计算，并提供了不同数据库系统中的实现方式。最后，提到了使用本福特法则检测异常数据的概念。

摘要由CSDN通过智能技术生成

4本文总结一些数字方面的用法，这块比较好理解，也是对于前面文章数据库语法总结（1）——简单用法的扩展和运用，所以好理解的部分就大概说一下函数，不举例子了

1、简单的计算方式

计算平均值，nums三个数10,20,null。请牢记聚合函数会自动忽略值为null的值

avg(nums) --15;

若想要聚合值为null的列：avg(coalesce(nums,0)) --10;

分组计算平均值

avg() ……group by

最大值最小值

min(nums),max(nums)

分组查找最大最小

min(nums),max(nums)……group by

计算总值

sum(nums)；分组计算sum(nums)……group by

计算总行数

count(*)；分组计算count(*)……group by

计算非null列行数

count(nums) --count(字段名)

2、移动计数方式

（1）生成移动总计

根据某些字段分组排序后进行总值计算

sum(nums) over (order by id,name……)

（2）生成移动总积

exp(sum(ln(nums)) over (order by id,name……)

注意：ln()函数用于返回给定输入数字的自然对数。数字的自然对数是该数字与底数e的对数，其中e是近似等于2.718的数学常数。这使用lnx表示法，有时也用logex表示。其中值必须大于0。

sum() over()计算两个对数的和

exp()函数是计算数学常数e的n次幂，其中n为前面两个对数的和。

（3）加权移动平均值

为了更好的查看数据移动趋势，我们可以计算移动平均来查看，方法就是将n值与前n-1个值相加，再除以n。加权就是通过修改系数和分母的比例

这时就用到我们在第一章中提到的偏移函数lag

移动平均值（nums+(lag(nums,1) over(order by id))+（lag(nums,2) over(order by id)））/3

加权（3*nums+2*(lag(nums,1) over(order by id))+1*（lag(nums,2) over(order by id)））/6

3、众数、中位数、百分比

（1）众数：想要找到一组数据中的众数（出现次数最多的数值），以T_DEMP_3表的nums列为例

dbms通用的开窗函数dense_rank() ove()自动排序函数

SELECT nums FROM (
	SELECT nums,DENSE_RANK()OVER (ORDER BY cnt DESC) AS rnk
	FROM (
	SELECT nums,count(*) AS cnt FROM T_DEMP_3 GROUP BY nums 
	)
	)
	WHERE rnk=1;

在Oracle中可以将SELECT nums,DENSE_RANK()OVER (ORDER BY cnt DESC) AS rnk，修改成SELECT MAX(nums) keep(DENSE_RANK FIRST ORDER BY cnt DESC) nums

但是使用max() keep()出现多个众数的话只能返回一组众数，所以最好是要dense_rank() ove()函数

扩展：dense_rank() over() 的排序函数还有一种排序函数rank() over()，区别是rank() over()是不连续的，众数会占位，例如，有两名同学都考了100分，那么考99分的同学就是第三名。结果是1,1,3

dense_rank() over()是连续结果的那么上面三个同学的排名就是1,1,2

如果想要深入了解，大家可以自行百度一下

（2）中位数：中位数是指数值排序之后处于中间位置的值

Oracle数据库有两种方式实现：median()或percentile_cont(0.5) within group(order by...)

SELECT median(nums) FROM T_DEMP_3;
SELECT percentile_cont(0.5) WITHIN group(ORDER BY nums) FROM T_DEMP_3 ;

DB2和PostgreSQL中同样使用窗口函数percentile_cont(0.5) within group(order by...)

Sqlserver也使用上述的函数，区别是必须包含over()子句，例如：percentile_cont(0.5) within group(order by...) over()

Mysql中没有percentile_cont()这样的函数,我们可以采用cume_dist(自动排序函数)和CTE(with as用法)结合使用查找中位数，举例：

with rank_tab(nums,rank_num) as
(select nums,cume_dist()over (order by nums)
    from T_DEMP_3
),
inter as
(
select nums,rank_num from rank_tab
where rank_num>=0.5
union
select nums,rank_num from rank_tab
where rank_num<=0.5
)

select avg(nums) as med from inter;

cume_dist()over (order by ...) 分组内小于、等于当前rank值的行数 / 分组内总行数,可以计算出当前数值所占比例

（3）计算百分比

计算T_DEMP_3中ID为1的nums占总数的白分比

DB2、Oracle、Sqlserver

SELECT DISTINCT (d1/total)*100 AS pct
FROM (
	SELECT id,sum(nums) over() total,
	sum(nums) over(PARTITION BY id) d1
	FROM T_DEMP_3 
)
WHERE id=1;

保留小数方法：直接截断trunc(66.667,2)；四舍五入round(66.667,2)；不管有没有小数，始终保留两位小数to_char(round(0.8,2),'fm99999990.00')

Mysql、PostgreSQL

select (sum(
    case when id=1 then nums end)/sum(nums)
)*100 as pct
from T_DEMP_3;

（4）剔除最高值和最低值后计算平均值

DB2、Oracle、Sqlserver

select avg(nums)from (
    select nums,min(nums) over() min_sal,max(nums) over() max_sal
    from T_DEMP_3
)where nums not in (min_sal,max_sal);

Mysql、PostgreSQL

select avg(nums) from T_DEMP_3 where nums not in ((select min(nums) from T_DEMP_3),(select max(nums) from T_DEMP_3));

4、修改移动总计中的值

现在我们有一个视图V,三个字段id,amt,trx。id是唯一标识列，amt是金额，trx是交易类型：PY支出，PR收入。现在我们想计算每次操作之后剩余金额显示，类似于银行交易明细。

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;

5、使用绝对中位值找出异常值

思路：首先根据上面提到的找到数据的中位值，然后计算这个中位值与各个值的绝对偏差的中值，即中位差，然后，查询每个值对于中位值的偏差与绝对中位差的比值，确认偏差时绝对中位值的3倍以上，即为异常值。

同样，我们需要借助CTE方法展示各种Oracle的用法（主要大家知道思路和逻辑是什么，没有比较复杂的函数）

Oracle

with Deviation(Deviation)
as
(select abs(nums-median(nums) over ()) from T_DEMP_3),
MAD(MAD) as
(select median(Deviation) from Deviation)

select abs(nums-median(nums) OVER())/MAD,nums,id
from MAD join T_DEMP_3 ON 1=1

Sqlserver、DB2、PostgreSQL中都需要上面计算中位数提到的percentile_cont（注意三者使用区别）Mysql有点不同，它没有计算中位数的函数，但是上面我们已经提到过这方面要如何解决，基本思路是一致的，这里就不多说了

扩展：abs(number)取数值的绝对值函数

6、使用本福特法查找反常数据

本福特定律，也称为本福特法则，说明一堆从实际生活得出的数据中，以1为首位数字的数的出现概率约为总数的三成，接近直觉得出之期望值1/9的3倍。推广来说，越大的数，以它为首几位的数出现的概率就越低。它可用于检查各种数据是否有造假。

用公式表达的话大概是这样的

$log_{10}\begin{pmatrix}d+1 & \\ - & \\ d & \end{pmatrix}$

我们借助用CTE展现Oracle的写法

WITH firstdigits(firstdigit)
AS
(
SELECT SUBSTR(CAST(nums AS varchar2(100)),0,1) AS firstdigit
FROM T_DEMP_3 
),
totalcount (total)
AS 
(SELECT count(*) FROM T_DEMP_3),
expectedbenford(digit,expected)
as
(
SELECT rownum,(LOG(10,rownum+1)-LOG(10,rownum)) AS expected  FROM all_tables WHERE rownum<7
)


SELECT count(firstdigit),digit,coalesce(count(*)/total,0) AS actual,expected
FROM firstdigits f
	JOIN totalcount t ON 1=1
	RIGHT JOIN expectedbenford e
	ON f.firstdigit=e.digit
GROUP BY digit,expected,total
order BY digit,expected

今天就更新到这了，我们将会持续更新章节，喜欢的小伙伴可以收藏关注！