sql——数值处理

1、计算平均值

select avg(sal) as avf_sal
avg()//忽略空值
coalesce()//解释：返回参数中的第一个非空表达式（从左向右依次类推）； 使用示例：a,b,c三个变量。select coalesce(null,2,3); // Return 2

遇到空值怎么办？
1、忽略空值元组不做计算。
直接select avg(sal) as avf_sal
2、将空值记为0

swlect avg(coalesce(sal,0)) from t2;

group by
只能把group by 中有的列放在select中（不放也行），但是group by中没有的列一定不可以放

2、计算最大值最小值

min()
max()

3、求和

sum()

4、计算行数

count(*) //计算行数，空值行也记录在内
count(comm)//计算comm列不为空的行数

5、累计求和

保留中间的过程

//标量查询
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
      

mysql 8以上或者oracle可以使用窗口函数

select ename,sal,sum(sal) over(order by sal,empno) as running_total
from emp
order by 2 

over(partition by columnname1 order by columnname2)
//按照columnname1字段进行分组
//over不能单独使用要和聚合函数一起使用

辨析rank()、dense_rank()、row_number()

rank()//排序，大小并列的结果 1 1 3 3 5 6
dense_rank()//连续排序，大小并列的结果 1 1 2 2 3 4
row_number()//行号，1 2 3 4 5 6 

注意：

如果这里面over中没有添加empno，那么认为WARD和MARTIN并列，这时候running_total对应的结果是 5350 5350

6、计算众数

思路：按照该列进行分组，子查询返回每个分组的大小，
父查询中分组的大小 如果大于等于所有子查询返回的每个分组的大小即为众数

select sal 
from emp 
group by sal 
having count(*)>=all(select count(*) from emp d group by sal);

7、计算中位数

oracle提供了median 提供中位数
mysql

SIGN(X) //这个函数返回X的符号(负数，零或正)对应-1，0或1

//步骤1 求笛卡尔积，并分组
select e.sal from emp e,emp  d group by e.sal
//步骤2 在每个分组里，如果 e.sal和d.sal相等的个数大于等于（d.sal中比e.sal大的个数-比它小的个数） 则这个数是中位数
//求e.sal和d.sal相等的个数
sum(case when e.sal=d.sal then 1 else 0 end)
//求d.sal中比e.sal大的个数-比它小的个数
abs(sum(sign(e.sal-d.sal)))

//完整代码
select avg(sal)
from(
  select e.sal from emp e,emp d group by e.sal
  having sum(case when e.sal=d.sal then 1 else 0 end)>=
         abs(sum(sign(e.sal-d.sal)))
)

8、计算百分比

计算满足条件的个数/总个数

select (sum (case when deptno=10 then sal end) /sum(sal))*100 as pct
from emp

9、计算平均值去掉最大值和最小值

select avg(sal) from emp where sal not in(
(select min(sal) from emp),(select max(sal) from emp))

10、修改累计值

思考题

5是进行累加，怎么做累计乘法，和累计减法。

1）累加

SELECT
    T.*,
    SUM(T.QUALITY) OVER (PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RUNNING_QUALITY
FROM
    TEST T;

2）累乘

SELECT
    T.*,
    ROUND(EXP(SUM(LN(T.QUALITY)) OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID)),0) RUNNING_PROD
FROM
    TEST T;

ln() //取对数
exp(n)// e^n
round(num,n)//四舍五入，保留n位小数
if(exp,a,b)//如果exp为真，结果为a，否则为b

例如 5*3=e^(ln 5+ln 3)

3)累减

SELECT
    T.PARENT_ID,
    T.PART_ID,
    T.QUALITY,
    SUM(if(T.RN=1,T.QUALITY,-T.QUALITY)) OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RUNNING_PROD
FROM
    (
        SELECT
            T.*,
            ROW_NUMBER() OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RN
        FROM
            TEST T) T;

4）累除

SELECT
    T.PARENT_ID,
    T.PART_ID,
    T.QUALITY,
    EXP(SUM(if(RN=1,LN(T.QUALITY),-LN(T.QUALITY))) OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID)) RUNNING_PROD
FROM
    (
        SELECT
            T.*,
            ROW_NUMBER() OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RN
        FROM
            TEST T)T;