SQL数值函数

作者：哪吒
时间：2024年12月

概述

SQL数值函数用于对数值数据进行各种数学运算和处理。这些函数可以帮助我们进行计算、格式化数字、处理精度等操作。

基本数学函数

ABS() - 绝对值

返回数值的绝对值。

SELECT ABS(-15) AS absolute_value;  -- 结果: 15
SELECT ABS(25) AS absolute_value;   -- 结果: 25
SELECT ABS(-3.14) AS absolute_value; -- 结果: 3.14

ROUND() - 四舍五入

将数值四舍五入到指定的小数位数。

-- 基本用法
SELECT ROUND(3.14159) AS rounded;        -- 结果: 3
SELECT ROUND(3.14159, 2) AS rounded;     -- 结果: 3.14
SELECT ROUND(3.14159, 4) AS rounded;     -- 结果: 3.1416

-- 负数小数位
SELECT ROUND(1234.5678, -1) AS rounded;  -- 结果: 1230
SELECT ROUND(1234.5678, -2) AS rounded;  -- 结果: 1200

CEIL() / CEILING() - 向上取整

返回大于或等于指定数值的最小整数。

SELECT CEIL(3.14) AS ceiling_value;    -- 结果: 4
SELECT CEIL(-3.14) AS ceiling_value;   -- 结果: -3
SELECT CEILING(5.01) AS ceiling_value; -- 结果: 6

FLOOR() - 向下取整

返回小于或等于指定数值的最大整数。

SELECT FLOOR(3.14) AS floor_value;  -- 结果: 3
SELECT FLOOR(-3.14) AS floor_value; -- 结果: -4
SELECT FLOOR(5.99) AS floor_value;  -- 结果: 5

TRUNCATE() - 截断

截断数值到指定的小数位数（不进行四舍五入）。

SELECT TRUNCATE(3.14159, 2) AS truncated; -- 结果: 3.14
SELECT TRUNCATE(3.19999, 2) AS truncated; -- 结果: 3.19
SELECT TRUNCATE(1234.5678, -1) AS truncated; -- 结果: 1230

幂运算和根运算

POWER() / POW() - 幂运算

计算数值的幂。

SELECT POWER(2, 3) AS power_result;  -- 结果: 8 (2的3次方)
SELECT POW(5, 2) AS power_result;    -- 结果: 25 (5的2次方)
SELECT POWER(10, -2) AS power_result; -- 结果: 0.01

SQRT() - 平方根

计算数值的平方根。

SELECT SQRT(16) AS square_root;  -- 结果: 4
SELECT SQRT(2) AS square_root;   -- 结果: 1.4142135623731
SELECT SQRT(100) AS square_root; -- 结果: 10

EXP() - 自然指数

计算e的指定次幂。

SELECT EXP(1) AS exp_result;  -- 结果: 2.718281828459 (e)
SELECT EXP(0) AS exp_result;  -- 结果: 1
SELECT EXP(2) AS exp_result;  -- 结果: 7.3890560989307

LOG() - 对数

计算自然对数或指定底数的对数。

-- 自然对数
SELECT LOG(2.718281828459) AS natural_log; -- 结果: 1
SELECT LN(10) AS natural_log;              -- 结果: 2.302585092994

-- 指定底数的对数
SELECT LOG(10, 100) AS log_result;  -- 结果: 2 (以10为底100的对数)
SELECT LOG(2, 8) AS log_result;     -- 结果: 3 (以2为底8的对数)

三角函数

SIN(), COS(), TAN() - 基本三角函数

-- 角度需要转换为弧度
SELECT SIN(PI()/2) AS sin_90;    -- 结果: 1 (sin 90°)
SELECT COS(0) AS cos_0;          -- 结果: 1 (cos 0°)
SELECT TAN(PI()/4) AS tan_45;    -- 结果: 1 (tan 45°)

-- 角度转弧度: 角度 * PI() / 180
SELECT SIN(30 * PI() / 180) AS sin_30_degrees; -- 结果: 0.5

ASIN(), ACOS(), ATAN() - 反三角函数

SELECT ASIN(0.5) AS arcsin;     -- 结果: 0.5236 (30°的弧度值)
SELECT ACOS(0.5) AS arccos;     -- 结果: 1.0472 (60°的弧度值)
SELECT ATAN(1) AS arctan;       -- 结果: 0.7854 (45°的弧度值)

-- 弧度转角度: 弧度 * 180 / PI()
SELECT ASIN(0.5) * 180 / PI() AS arcsin_degrees; -- 结果: 30

特殊数值函数

PI() - 圆周率

返回π的值。

SELECT PI() AS pi_value;  -- 结果: 3.141592653590

RAND() - 随机数

生成0到1之间的随机数。

SELECT RAND() AS random_number;           -- 结果: 0.123456789 (随机)
SELECT RAND(123) AS seeded_random;        -- 结果: 固定种子的随机数
SELECT FLOOR(RAND() * 100) AS random_int; -- 结果: 0-99的随机整数

SIGN() - 符号函数

返回数值的符号（-1, 0, 1）。

SELECT SIGN(-15) AS sign_value;  -- 结果: -1
SELECT SIGN(0) AS sign_value;    -- 结果: 0
SELECT SIGN(25) AS sign_value;   -- 结果: 1

数值格式化函数

FORMAT() - 数值格式化

将数值格式化为指定格式的字符串。

SELECT FORMAT(1234.5678, 2) AS formatted;     -- 结果: '1,234.57'
SELECT FORMAT(1234567.89, 0) AS formatted;    -- 结果: '1,234,568'
SELECT FORMAT(123.456, 4) AS formatted;       -- 结果: '123.4560'

CONV() - 进制转换

在不同进制之间转换数值。

SELECT CONV('255', 10, 16) AS hex_value;    -- 结果: 'FF' (十进制转十六进制)
SELECT CONV('FF', 16, 10) AS decimal_value; -- 结果: '255' (十六进制转十进制)
SELECT CONV('1010', 2, 10) AS binary_to_dec; -- 结果: '10' (二进制转十进制)

实战示例

示例1：计算商品价格统计

-- 创建测试数据
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    discount_rate DECIMAL(3,2)
);

INSERT INTO products VALUES
(1, '笔记本电脑', 5999.99, 0.15),
(2, '手机', 2999.50, 0.10),
(3, '平板', 1899.00, 0.20);

-- 计算折后价格和统计信息
SELECT 
    name,
    price AS original_price,
    ROUND(price * (1 - discount_rate), 2) AS discounted_price,
    ROUND(price * discount_rate, 2) AS discount_amount,
    CEIL(price * (1 - discount_rate)) AS rounded_up_price,
    FORMAT(price, 2) AS formatted_price
FROM products;

示例2：数学计算应用

-- 计算圆的面积和周长
SELECT 
    5 AS radius,
    ROUND(PI() * POWER(5, 2), 2) AS area,
    ROUND(2 * PI() * 5, 2) AS circumference;

-- 计算三角形边长（勾股定理）
SELECT 
    3 AS side_a,
    4 AS side_b,
    ROUND(SQRT(POWER(3, 2) + POWER(4, 2)), 2) AS hypotenuse;

示例3：随机数据生成

-- 生成随机测试数据
SELECT 
    FLOOR(RAND() * 1000) + 1 AS random_id,
    ROUND(RAND() * 100, 2) AS random_score,
    FLOOR(RAND() * 50) + 18 AS random_age,
    CASE 
        WHEN RAND() > 0.5 THEN '男' 
        ELSE '女' 
    END AS random_gender;

性能优化建议

1. 避免在WHERE子句中使用函数

-- 不推荐：在WHERE中使用函数
SELECT * FROM orders WHERE ROUND(amount, 0) = 100;

-- 推荐：使用范围查询
SELECT * FROM orders WHERE amount >= 99.5 AND amount < 100.5;

2. 预计算常用值

-- 不推荐：每次都计算
SELECT *, price * 0.8 AS discounted_price FROM products;

-- 推荐：添加计算列或在应用层计算
ALTER TABLE products ADD COLUMN discounted_price DECIMAL(10,2) 
GENERATED ALWAYS AS (price * 0.8) STORED;

3. 合理使用精度

-- 根据业务需求选择合适的精度
SELECT 
    ROUND(price, 2) AS price_display,  -- 显示用，2位小数
    TRUNCATE(price, 4) AS price_calc   -- 计算用，4位小数
FROM products;

常见错误和解决方案

1. 除零错误

-- 错误：可能导致除零
SELECT 10 / 0;  -- 错误

-- 正确：使用条件判断
SELECT 
    CASE 
        WHEN denominator = 0 THEN NULL
        ELSE numerator / denominator
    END AS safe_division
FROM calculations;

2. 精度丢失

-- 注意：浮点数精度问题
SELECT 0.1 + 0.2 = 0.3;  -- 可能返回 false

-- 解决：使用DECIMAL类型或适当的精度比较
SELECT ABS((0.1 + 0.2) - 0.3) < 0.0001 AS is_equal;

3. 负数开方

-- 错误：负数开方
SELECT SQRT(-4);  -- 错误

-- 正确：先检查符号
SELECT 
    CASE 
        WHEN value >= 0 THEN SQRT(value)
        ELSE NULL
    END AS safe_sqrt
FROM numbers;

最佳实践

1. 选择合适的数据类型：根据精度要求选择DECIMAL、FLOAT或DOUBLE
2. 避免过度精度：不要使用超过业务需求的精度
3. 处理边界情况：考虑NULL值、零值、负值等特殊情况
4. 性能考虑：在大数据量时避免复杂的数学运算
5. 文档化计算逻辑：为复杂的数学计算添加注释

总结

SQL数值函数提供了强大的数学计算能力，合理使用这些函数可以：

• 简化复杂的数学运算
• 提高数据处理效率
• 确保计算结果的准确性
• 满足各种业务场景的需求

在使用时要注意精度、性能和边界情况的处理，选择最适合业务需求的函数和方法。