SQL入门：数字函数实战

数字函数是 SQL 中用于处理数值型数据的工具，包括基本运算、取整、四舍五入、绝对值、幂运算等。标准 SQL 定义了一套基础数字函数，在主流数据库（MySQL、PostgreSQL、SQL Server 等）中通用性较高。以下从基础到进阶，结合实例讲解核心函数及应用场景。

一、基础算术函数：加减乘除与取余

这类函数用于基本的数学运算，是处理数值型数据的基础工具。

函数	作用	语法示例	结果
+	加法	5 + 3	8
-	减法	10 - 4	6
*	乘法	6 * 7	42
/	除法（返回浮点数）	10 / 3	3.333...
MOD(a, b) 或 a % b	取余（a 除以 b 的余数）	MOD(10, 3) 或 10 % 3	1

实例 1：计算订单金额相关指标

假设有订单表orders，包含amount（金额）、quantity（数量）字段，计算单价、折扣后金额等：

SELECT 
  order_id,
  amount,
  quantity,
  amount / quantity AS 单价, -- 除法：计算单价
  amount * 0.9 AS 九折金额, -- 乘法：计算折扣后金额
  MOD(amount, 10) AS 金额个位数 -- 取余：获取金额的个位数
FROM orders;

二、取整函数：向上取整、向下取整与四舍五入

用于将浮点数转换为整数，满足不同的精度需求（如金额保留整数、数量取整）。

函数	作用	语法示例	结果（输入 3.7）	结果（输入 3.2）
ROUND(数值, 小数位数)	四舍五入到指定小数位（默认 0 位）	ROUND(3.7)	4	3
		ROUND(3.1415, 2)	3.14	-
CEIL(数值) 或 CEILING(数值)	向上取整（返回大于等于该值的最小整数）	CEIL(3.7)	4	4
FLOOR(数值)	向下取整（返回小于等于该值的最大整数）	FLOOR(3.7)	3	3

实例 2：处理商品价格与数量

SELECT 
  product_id,
  price, -- 原价（可能带小数）
  ROUND(price) AS 整数价格, -- 四舍五入到整数
  CEIL(stock / 10) AS 箱数, -- 每10个装一箱，向上取整（如15个→2箱）
  FLOOR(discount * 10) AS 折扣等级 -- 折扣0.75→7级（0.75*10=7.5→向下取整7）
FROM products;

三、绝对值与符号函数

用于处理数值的正负特性，如计算差值的绝对值、判断数值符号。

函数	作用	语法示例	结果
ABS(数值)	返回绝对值（负数转正数，正数不变）	ABS(-5)	5
		ABS(3.14)	3.14
SIGN(数值)	返回符号（正数→1，负数→-1，0→0）	SIGN(5)	1
		SIGN(-3.2)	-1
		SIGN(0)	0

实例 3：分析销售数据波动

计算实际销量与目标销量的差异绝对值，判断是否达标：

SELECT 
  sale_date,
  target_sales, -- 目标销量
  actual_sales, -- 实际销量
  actual_sales - target_sales AS 销量差异,
  ABS(actual_sales - target_sales) AS 差异绝对值, -- 忽略正负，只看差距大小
  SIGN(actual_sales - target_sales) AS 达标情况 -- 1=超额，-1=未达标，0=持平
FROM sales_report;

四、幂运算与开方函数

用于指数运算、平方根计算等，适用于科学计算或业务中的倍率计算。

函数	作用	语法示例	结果
POWER(底数, 指数)	计算底数的指数次幂	POWER(2, 3)	8（2³）
		POWER(10, -2)	0.01（10⁻²）
SQRT(数值)	计算平方根（仅支持非负数）	SQRT(25)	5
		SQRT(2)	1.414...

实例 4：计算复利与面积

-- 计算复利：本金1000元，年利率3%，5年后的本息和（公式：本金×(1+利率)^年数）
SELECT 1000 * POWER(1 + 0.03, 5) AS 本息和;

-- 计算圆形面积：半径r，面积=π×r²（π可用PI()函数获取）
SELECT product_id, 
       PI() * POWER(radius, 2) AS 圆形面积 
FROM round_products;

五、其他常用数字函数

1. 生成随机数：RAND()

返回 0 到 1 之间的随机浮点数（不同数据库实现略有差异）：

-- 生成0-1之间的随机数
SELECT RAND();

-- 生成1-100之间的随机整数
SELECT FLOOR(RAND() * 100) + 1 AS 随机数;

2. 圆周率：PI()

返回圆周率 π（约等于 3.1415926535...）：

-- 计算圆的周长：2×π×半径
SELECT 2 * PI() * radius AS 周长 FROM round_products;

3. 取模运算扩展：MOD()

除了基本取余，还可用于判断奇偶性、周期性数据分组：

-- 判断订单ID的奇偶性（偶数→0，奇数→1）
SELECT order_id, MOD(order_id, 2) AS 是否为奇数 FROM orders;

-- 按周几分组统计订单（假设日期转数字：1=周一，7=周日）
SELECT MOD(day_of_week, 7) AS 周几, COUNT(*) AS 订单数 
FROM orders GROUP BY 周几;

六、主流数据库差异（避坑重点）

函数功能	标准 SQL/MySQL	PostgreSQL	SQL Server
向上取整	CEIL() 或 CEILING()	CEIL() 或 CEILING()	CEILING()
随机数	RAND()（无参数）	RANDOM()（返回 0-1）	RAND()（支持种子参数）
自然对数	LN()	LN()	LOG()（单参数时为自然对数）
以 10 为底的对数	LOG10()	LOG(10, x)	LOG10()

七、实战场景：综合运用数字函数

场景 1：商品价格分级

将商品价格分为 5 个等级（1-5 级），价格越高等级越高：

SELECT 
  product_id,
  price,
  -- 价格范围0-1000，分为5级（每级200元）
  CEIL(price / 200) AS 价格等级 
FROM products
WHERE price > 0;

场景 2：计算用户消费等级

根据用户总消费金额，用平方根归一化计算等级（消费越高，等级增长越慢）：

SELECT 
  user_id,
  total_spending,
  -- 总消费开平方后四舍五入，作为等级（非线性增长）
  ROUND(SQRT(total_spending / 100)) AS 消费等级 
FROM user_total;

场景 3：生成随机优惠券码后 4 位

生成 4 位随机数字作为优惠券码的后四位：

SELECT 
  CONCAT('COUPON-', FLOOR(RAND() * 10000)) AS 优惠券码 
FROM generate_series(1, 10); -- 生成10个优惠券码（PostgreSQL语法）

八、常见误区与避坑指南

1. 误区 1：整数除法结果为整数标准 SQL 中，5 / 2 结果为 2（整数除法），而非 2.5。如需浮点数结果，需将其中一个数转为小数：5.0 / 2 或 CAST(5 AS FLOAT) / 2。

2. 误区 2：ROUND 函数的四舍五入方向不同数据库对ROUND(2.5)的处理可能不同（有的为 2，有的为 3），建议明确小数位数，如ROUND(2.5, 0)。

3. 误区 3：MOD 函数对负数的处理负数取余结果的符号与被除数一致，如MOD(-10, 3) = -1，MOD(10, -3) = 1，需注意业务逻辑是否兼容。

4. 误区 4：随机数函数的重复性同一次查询中，RAND() 可能返回相同值（如SELECT RAND(), RAND()可能两列相同），如需不同随机数，可结合其他字段（如RAND() * id）。

九、总结

标准 SQL 的数字函数覆盖了从基础算术到复杂运算的需求，核心包括：

• 基础运算：+、-、*、/、MOD；
• 取整函数：ROUND、CEIL、FLOOR；
• 特殊运算：ABS、SIGN、POWER、SQRT；
• 辅助函数：RAND、PI。

实际开发中，需根据业务场景（如金额计算、数据分组、科学计算）选择合适的函数，并注意不同数据库的语法差异，避免因整数除法、四舍五入规则等细节导致错误。