【MySQL】MySQL数学函数官方文档及笔记

前言

MySQL 5.7数学函数官方文档
写SQL题经常用，遇到的在这里记录一下

1. 四舍五入 保留至N位小数

ROUND() 官方说明

ROUND(X), ROUND(X,D)
Rounds the argument X to D decimal places. The rounding algorithm depends on the data type of X. D defaults to 0 if not specified. D can be negative to cause D digits left of the decimal point of the value X to become zero. The maximum absolute value for D is 30; any digits in excess of 30 (or -30) are truncated.

The return value has the same type as the first argument (assuming that it is integer, double, or decimal). This means that for an integer argument, the result is an integer (no decimal places):

ROUND(x, d)： x是负数

• 对负数进行四舍五入会发生什么？
  绝对值四舍五入然后取负号。如例子

mysql> SELECT ROUND(-1.23);
        -> -1
mysql> SELECT ROUND(-1.58);
        -> -2
mysql> SELECT ROUND(-1.23, 1);
        -> -1.2
mysql> SELECT ROUND(-1.58, 1);
        -> -2.6        

ROUND(x, d)： x是整数

• 对整数依旧返回整数，忽略d参数

mysql> SELECT ROUND(1, 3);
        -> 1

ROUND(x, d)： d是负数

小数点签名d位变为0，并删去所有小数点

mysql> SELECT ROUND(22.222, -1);
        -> 20

2. 求加权平均值

AVG()

存在字段value 表示值，weight表示权重

select AVG(value/weight) as 加权平均值 from table 

3. 按需计数

SUM()

• condition 表示表达式，类似 weigh > 3
• 用IF函数对每一行进行condition 判断
  SUM(IF(condition, 1, 0))