MySQL函数：`CEILING()`和`FLOOR()`向上和向下取整的应用场景。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL函数：CEILING()和FLOOR()向上和向下取整的应用场景

大家好，今天我们来深入探讨MySQL中两个非常实用但有时容易被忽视的函数：CEILING()和FLOOR()。这两个函数分别用于向上取整和向下取整，在处理数值数据时有着广泛的应用。我们将通过具体的场景和代码示例，详细讲解它们的使用方法和应用场景。

1. CEILING()函数：向上取整

CEILING()函数返回不小于给定参数的最小整数。换句话说，它将一个数值向上舍入到最接近的整数。

语法:

CEILING(numeric_expression)

其中，numeric_expression 可以是任何可以解析为数值的表达式，例如一个数字字面量、一个列名、一个计算表达式等。

示例:

SELECT CEILING(3.14); -- 返回 4
SELECT CEILING(5);    -- 返回 5
SELECT CEILING(-2.8);  -- 返回 -2
SELECT CEILING(-5);   -- 返回 -5

应用场景:

分页处理： 在分页查询中，计算总页数时，如果总记录数除以每页显示记录数的结果不是整数，则需要向上取整。

假设我们有一个名为 products 的表，其中包含所有产品的信息。我们希望每页显示 10 个产品，并计算总页数。
```
SELECT CEILING(COUNT(*) / 10) AS total_pages FROM products;
```
在这个例子中，COUNT(*) 返回 products 表中的总记录数。将其除以 10，得到的结果可能是一个小数。CEILING() 函数确保我们得到一个整数，表示总页数。例如，如果有 105 个产品，COUNT(*) / 10 的结果是 10.5，CEILING(10.5) 的结果是 11，表示需要 11 页才能显示所有产品。
库存管理： 在库存管理系统中，如果需要保证库存数量始终为整数，并且在计算补货数量时，需要向上取整，以确保库存充足。

假设我们需要根据销售量和安全库存量来计算补货数量。销售量是 sales_quantity，安全库存量是 safety_stock，当前库存量是 current_stock。补货数量的计算公式是：补货数量 = 安全库存量 + 销售量 - 当前库存量。如果计算结果不是整数，我们需要向上取整。
```
SELECT CEILING(safety_stock + sales_quantity - current_stock) AS replenishment_quantity
FROM inventory;
```
如果 safety_stock 是 100, sales_quantity 是 50, current_stock 是 120，那么 safety_stock + sales_quantity - current_stock 的结果是 30。如果 safety_stock 是 100, sales_quantity 是 50, current_stock 是 130，那么 safety_stock + sales_quantity - current_stock 的结果是 20。

如果 safety_stock 是 100, sales_quantity 是 50, current_stock 是 150，那么 safety_stock + sales_quantity - current_stock 的结果是 0。

如果 safety_stock 是 100, sales_quantity 是 50, current_stock 是 150.5，那么 safety_stock + sales_quantity - current_stock 的结果是 -0.5。CEILING(-0.5) 的结果是 0。
费用计算： 在某些费用计算场景中，例如运费计算，如果重量或距离不是整数，则需要向上取整到最接近的整数单位，然后根据单位价格计算费用。

假设我们根据包裹重量计算运费。每公斤的运费是 5 元。如果包裹重量是 2.3 公斤，我们需要向上取整到 3 公斤，然后计算运费。
```
SELECT CEILING(weight) * 5 AS shipping_fee FROM packages;
```
如果 weight 是 2.3，那么 CEILING(2.3) 的结果是 3，CEILING(2.3) * 5 的结果是 15。
资源分配： 在资源分配系统中，如果需要将资源分配给一定数量的用户，并且每个用户分配的资源数量必须是整数，则可以使用向上取整来确保所有资源都被分配。

假设我们有 100 个计算单元，需要分配给 3 个用户。我们希望每个用户至少分配到 1 个计算单元。
```
SELECT CEILING(100 / 3) AS units_per_user; -- 返回 34
```
在这个例子中，100 / 3 的结果是 33.333…。CEILING(100 / 3) 的结果是 34，表示每个用户至少需要分配 34 个计算单元。总共分配了 34 * 3 = 102 个计算单元。我们需要从其中一个用户收回 2 个计算单元，然后将这两个计算单元分配给其他用户，确保所有用户分配到的计算单元数量尽可能接近。

2. FLOOR()函数：向下取整

FLOOR()函数返回不大于给定参数的最大整数。换句话说，它将一个数值向下舍入到最接近的整数。

语法:

FLOOR(numeric_expression)

其中，numeric_expression 可以是任何可以解析为数值的表达式。

示例:

SELECT FLOOR(3.14); -- 返回 3
SELECT FLOOR(5);    -- 返回 5
SELECT FLOOR(-2.8);  -- 返回 -3
SELECT FLOOR(-5);   -- 返回 -5

应用场景:

年龄计算： 在计算年龄时，通常只需要整数部分，可以使用 FLOOR() 函数将出生日期和当前日期之间的差值向下取整。

假设我们有一个名为 users 的表，其中包含用户的出生日期信息。
```
SELECT FLOOR(DATEDIFF(CURDATE(), birth_date) / 365) AS age FROM users;
```
在这个例子中，DATEDIFF(CURDATE(), birth_date) 返回当前日期和出生日期之间的天数差。将其除以 365，得到的结果可能是一个小数。FLOOR() 函数确保我们得到一个整数，表示用户的年龄。例如,如果用户的出生日期是 1990-01-01, 当前日期是 2024-01-01, 那么DATEDIFF(CURDATE(), birth_date) 的结果是 12418, 12418 / 365 的结果是 34.0219…, FLOOR(34.0219...) 的结果是 34。
积分计算： 在积分系统中，如果消费金额达到一定额度才能获得积分，可以使用 FLOOR() 函数将消费金额除以额度的结果向下取整，得到可以获得的积分数量。

假设每消费 100 元可以获得 1 个积分。如果消费金额是 350 元，我们可以获得多少积分？
```
SELECT FLOOR(350 / 100) AS points; -- 返回 3
```
时间段划分： 在时间序列分析中，如果需要将时间数据划分到不同的时间段，可以使用 FLOOR() 函数将时间戳除以时间段长度的结果向下取整，得到时间段的编号。

假设我们需要将一天的时间划分成若干个 1 小时的时间段。
```
SELECT FLOOR(HOUR(time_column)) AS time_period FROM time_series_data;
```
在这个例子中，HOUR(time_column) 返回时间的小时数。FLOOR(HOUR(time_column)) 的结果就是时间段的编号。例如，如果 time_column 的值是 ‘2024-01-01 15:30:00’, 那么 HOUR(time_column) 的结果是 15, FLOOR(15) 的结果是 15。
数据分桶： 在数据分析中，如果需要将数据按照一定的范围划分到不同的桶中，可以使用 FLOOR() 函数将数据除以桶的大小的结果向下取整，得到桶的编号。

假设我们需要将年龄数据划分到以下几个桶中：
- 0-10
- 11-20
- 21-30
- 31-40
- 41-50
- 51-60
- 61-70
- 71-80
- 81-90
- 91-100
```
SELECT FLOOR(age / 10) AS age_bucket FROM users;
```
在这个例子中，age / 10 的结果可能是一个小数。FLOOR(age / 10) 的结果就是桶的编号。例如，如果 age 是 25, 那么 age / 10 的结果是 2.5, FLOOR(2.5) 的结果是 2，表示年龄属于 21-30 桶。

3. 实际案例：电商平台商品价格显示

在电商平台中，商品价格通常需要显示为整数，并且需要根据不同的促销活动进行调整。我们可以结合 CEILING() 和 FLOOR() 函数来实现不同的价格显示策略。

假设我们有一个名为 products 的表，其中包含商品的价格信息，以及一个名为 promotions 的表，其中包含促销活动的信息。

products 表的结构如下：

列名	数据类型	说明
product_id	INT	商品ID
price	DECIMAL(10,2)	商品原价

promotions 表的结构如下：

列名	数据类型	说明
promotion_id	INT	促销活动ID
discount_rate	DECIMAL(3,2)	折扣率
start_date	DATE	促销开始日期
end_date	DATE	促销结束日期

需求：

如果商品没有参加促销活动，则显示原价的整数部分。
如果商品参加了促销活动，则显示折扣后的价格，并向上取整。

SQL语句：

SELECT
    p.product_id,
    CASE
        WHEN pr.promotion_id IS NULL THEN FLOOR(p.price)
        ELSE CEILING(p.price * (1 - pr.discount_rate))
    END AS displayed_price
FROM
    products p
LEFT JOIN
    promotions pr ON p.product_id = pr.product_id
WHERE
    pr.start_date <= CURDATE() AND pr.end_date >= CURDATE()
    OR pr.promotion_id IS NULL;

代码解释：

LEFT JOIN：将 products 表和 promotions 表进行左连接，以便获取商品的促销活动信息。
WHERE：筛选出当前正在进行的促销活动，或者没有参加任何促销活动的商品。
CASE WHEN：根据商品是否参加促销活动，选择不同的价格显示策略。
- 如果商品没有参加促销活动（pr.promotion_id IS NULL），则使用 FLOOR(p.price) 将商品原价向下取整，显示整数部分。
- 如果商品参加了促销活动，则使用 CEILING(p.price * (1 - pr.discount_rate)) 计算折扣后的价格，并向上取整。

示例数据：

products 表：

product_id	price
1	19.99
2	29.50
3	39.75

promotions 表：

promotion_id	product_id	discount_rate	start_date	end_date
1	1	0.10	2024-01-01	2024-01-31
2	3	0.20	2024-01-15	2024-02-15

查询结果：

product_id	displayed_price
1	18
2	29
3	32

在这个例子中，商品 1 参加了促销活动，折扣率为 10%，折扣后的价格是 19.99 (1 – 0.10) = 17.991，向上取整后显示为 18。商品 2 没有参加促销活动，显示原价的整数部分 29。商品 3 参加了促销活动，折扣率为 20%，折扣后的价格是 39.75 (1 – 0.20) = 31.8，向上取整后显示为 32。

4. CEILING() 和 FLOOR() 与 ROUND() 的区别

CEILING() 和 FLOOR() 函数分别向上和向下取整，而 ROUND() 函数则根据指定的小数位数进行四舍五入。

函数	说明
CEILING()	向上取整，返回不小于给定参数的最小整数。
FLOOR()	向下取整，返回不大于给定参数的最大整数。
ROUND()	四舍五入到指定的小数位数。如果没有指定小数位数，则四舍五入到最接近的整数。

示例：

SELECT CEILING(3.14);   -- 返回 4
SELECT FLOOR(3.14);     -- 返回 3
SELECT ROUND(3.14);     -- 返回 3
SELECT ROUND(3.5);      -- 返回 4
SELECT ROUND(3.14, 1);  -- 返回 3.1
SELECT ROUND(3.15, 1);  -- 返回 3.2

选择哪个函数取决于具体的业务需求。如果需要始终向上取整，则使用 CEILING() 函数；如果需要始终向下取整，则使用 FLOOR() 函数；如果需要根据四舍五入规则进行取整，则使用 ROUND() 函数。

5. 注意事项

CEILING() 和 FLOOR() 函数都返回整数。
如果参数为 NULL，则返回 NULL。
这两个函数可以应用于任何可以解析为数值的表达式。
在进行数值计算时，需要注意数据类型，避免数据溢出。

6. 总结

CEILING() 和 FLOOR() 函数是在MySQL中进行向上和向下取整的强大工具。它们在分页、库存管理、费用计算和数据分析等领域都有广泛的应用，能够帮助我们更精确地处理数值数据。理解这两个函数的功能和应用场景，能够提高SQL查询的效率和准确性。

7. 掌握取整函数，提升数据处理能力

通过本文的讲解，我们深入了解了 MySQL 中 CEILING() 和 FLOOR() 函数的用法和应用场景。这两个函数虽然简单，但在实际开发中却非常实用，能够帮助我们更好地处理数值数据，满足不同的业务需求。熟练掌握这两个函数，可以提升我们在数据处理方面的能力，编写出更高效、更准确的 SQL 查询。