在MySQL查询中计算百分比并四舍五入结果

在进行数据分析和统计时，百分比的计算经常会用到。而在MySQL查询语句中，计算列的百分比也是一个常见的需求。本篇文章将介绍在MySQL查询中如何计算百分比，并选择性地四舍五入结果。

阅读更多：MySQL 教程

查找基本数据

为了展示如何计算百分比，首先需要一个包含基本数据的表。我们可以使用以下的SQL语句创建一个名为“sales”的表来模拟销售数据：

CREATE TABLE `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product` varchar(50) NOT NULL,
  `quantity` int(11) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

再使用以下的语句插入一些数据：

INSERT INTO sales (product, quantity, date) VALUES
('Product1', 100, '2020-01-01'),
('Product1', 200, '2020-01-02'),
('Product1', 300, '2020-01-03'),
('Product2', 150, '2020-01-01'),
('Product2', 250, '2020-01-02'),
('Product2', 350, '2020-01-03');

这样，我们就有了一个包含两个产品在三天内的销售量的表格。

基本百分比计算

现在，我们可以在这个表格中，按照每个产品每天的销售量，计算出每天的产品销售占比。这可以使用以下的SQL语句来完成：

SELECT
  product,
  date,
  CONCAT(ROUND((SUM(quantity) / SUM(SUM(quantity)) OVER (PARTITION BY date)) * 100, 2), '%') AS percentage
FROM sales
GROUP BY product, date;

这个查询语句将会得到以下的结果：

+----------+------------+------------+
| product  |    date    | percentage |
+----------+------------+------------+
| Product1 | 2020-01-01 | 40.00%     |
| Product1 | 2020-01-02 | 44.44%     |
| Product1 | 2020-01-03 | 46.15%     |
| Product2 | 2020-01-01 | 60.00%     |
| Product2 | 2020-01-02 | 55.56%     |
| Product2 | 2020-01-03 | 53.85%     |
+----------+------------+------------+

计算百分比的核心语句是：

ROUND((SUM(quantity) / SUM(SUM(quantity)) OVER (PARTITION BY date)) * 100, 2)

这条语句将产品销售数量累加求和，然后除以每一天的销售总量，再乘以100，最后使用ROUND函数四舍五入保留两位小数。这个查询语句的结果中，每一天的产品销售量占比都在0%到100%之间。

显示0%和100%

在某些情况下，我们也会想要显示0%和100%这两种情况。比如在上述的例子中，如果有一天某一种产品没有销售，那么其占比将会为0%。在这种情况下，我们可以使用以下的语句来得到正确的结果：

SELECT
  product,
  date,
  CONCAT(ROUND(IFNULL((SUM(quantity) / SUM(SUM(quantity)) OVER (PARTITION BY date)), 0) * 100, 2), '%') AS percentage
FROM sales
GROUP BY product, date;

这个查询结果与上面的查询结果相比，在当某一种产品当天没有销售的情况下，会显示为0%：

+----------+------------+------------+
| product  |    date    | percentage |
+----------+------------+------------+
| Product1 | 2020-01-01 | 40.00%     |
| Product1 | 2020-01-02 | 44.44%     |
| Product1 | 2020-01-03 | 46.15%     |
| Product2 | 2020-01-01 | 60.00%     |
| Product2 | 2020-01-02 | 55.56%     |
| Product2 | 2020-01-03 | 53.85%     |
| Product3 | 2020-01-01 | 0.00%      |
| Product3 | 2020-01-02 | 0.00%      |
| Product3 | 2020-01-03 | 0.00%      |
+----------+------------+------------+

这条查询语句在计算占比时，使用了IFNULL函数，将某一天某种产品的销售数量如果为空，就将其设为0。这个SQL语句在计算出占比后，也使用了ROUND函数，将结果保留两位小数。

四舍五入

当我们需要按特定的规则来四舍五入百分比时，我们可以使用MySQL中的ROUND函数。ROUND函数可以将任何一个十进制数四舍五入为指定的小数位数，并提供了多种不同的舍入规则。我们可以使用以下的语句进行测试：

SELECT ROUND(1.5), ROUND(-1.5), ROUND(1.23456, 2), ROUND(-1.23456, 2);

这个查询语句可以得到以下结果：

+------------+-------------+------------------+-------------------+
| ROUND(1.5) | ROUND(-1.5) | ROUND(1.23456, 2) | ROUND(-1.23456, 2) |
+------------+-------------+------------------+-------------------+
| 2          | -2          | 1.23             | -1.23             |
+------------+-------------+------------------+-------------------+

在计算百分比时，我们也可以使用这个函数来根据需要进行四舍五入。以下的SQL语句可以将销售占比保留到整数位，并且大于等于0.5的占比向上取整：

SELECT
  product,
  date,
  CONCAT(
    IF(
      ROUND(
        IFNULL(
          (SUM(quantity) / SUM(SUM(quantity)) OVER (PARTITION BY date)),
          0
        ) * 100,
        0
      ) < 100,
      ROUND(
        IFNULL(
          (SUM(quantity) / SUM(SUM(quantity)) OVER (PARTITION BY date)),
          0
        ) * 100,
        0
      ),
      100
    ),
    '%'
  ) AS percentage
FROM sales
GROUP BY product, date;

这个查询语句在计算百分比时，使用了嵌套的IF和ROUND函数，将百分比保留到整数位，使用0.5为介数，对不足和超过这个数的占比使用不同的舍入规则。