显示 mysql 值的百分比
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11906840/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Display Percent of value mysql
提问by Chris Muench
I have the following data table and data:
我有以下数据表和数据：
mysql> describe school_data_sets_numeric_data;
+--------------+----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| data_set_nid | int(11) | NO | | NULL | |
| school_nid | int(11) | NO | | NULL | |
| year | int(11) | NO | | NULL | |
| description | varchar(255) | NO | | NULL | |
| value | decimal(18,12) | NO | | NULL | |
+--------------+----------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
+----+--------------+------------+------+-------------------+------------------+
| id | data_set_nid | school_nid | year | description | value |
+----+--------------+------------+------+-------------------+------------------+
| 54 | 19951 | 19944 | 2008 | Asian | 75.000000000000 |
| 51 | 19951 | 19944 | 2008 | White | 200.000000000000 |
| 52 | 19951 | 19944 | 2008 | African American | 100.000000000000 |
| 53 | 19951 | 19944 | 2008 | Hispanic | 50.000000000000 |
| 55 | 19951 | 19944 | 2008 | Native American | 9.000000000000 |
I want to display an average of the data instead of raw numbers. I want to do this in raw sql.
我想显示数据的平均值而不是原始数字。我想在原始 sql 中执行此操作。
Example:
例子：
+----+--------------+------------+------+-------------------+------------------+
| id | data_set_nid | school_nid | year | description | average |
+----+--------------+------------+------+-------------------+------------------+
| 54 | 19951 | 19944 | 2008 | Asian | 17.28% |
| 51 | 19951 | 19944 | 2008 | White | 46.06% |
| 52 | 19951 | 19944 | 2008 | African American | 23.04% |
| 53 | 19951 | 19944 | 2008 | Hispanic | 11.52% |
| 55 | 19951 | 19944 | 2008 | Native American | 2.07% |
What is the best way to do this? Keep in mind there will be multiple data sets and years.
做这个的最好方式是什么？请记住，会有多个数据集和年份。
回答by spencer7593
One way to do this is to write a query that gets the "total value", which will be the denominator for your average.
一种方法是编写一个查询来获取“总价值”，这将是您的平均值的分母。
SELECT SUM(value) AS total_value FROM school_data_sets_numeric_data
Join the results of that query with each row of the table, so you have the value and total available, which you use to calculate the average.
将该查询的结果与表的每一行连接起来，这样您就有了可用的值和总数，用于计算平均值。
SELECT v.id
, v.data_set_nid
, v.school_nid
, v.year
, v.description
-- , v.value
-- , t.total_value
, CONCAT(FORMAT(IF(t.total_value=0,0,(v.value*100.0)/t.total_value),2),'%')
AS `average`
FROM (SELECT SUM(value) AS total_value FROM school_data_sets_numeric_data) t
CROSS
JOIN school_data_sets_numeric_data v
The keyword CROSSis optional, but it serves as documentation to later reviewers, and lets them know that we intended to create a Cartesian product, and that we've purposefully left out (and not accidentally omitted) an ONclause for the JOIN.
关键字CROSS是可选的，但它可以作为后来审阅者的文档，并让他们知道我们打算创建笛卡尔积，并且我们故意遗漏（而不是意外省略）ON了 JOIN 子句。
The expression that returns the "average", displays the value as you show it... rounded to two decimal places with a trailing percent sign.
返回“平均值”的表达式显示您显示的值...四舍五入到两位小数并带有尾随百分号。
Calculating the "average" is straightforward... value / total_value * 100.
计算“平均”是简单的...... value / total_value * 100。
(Beware of the "integer" division if both numerator and demoninator are integer types, not an issue in your case, but we make sure numerator is decimal by multiplying by decimal literal, which insures we have decimal type value.)
（如果分子和恶魔都是整数类型，请注意“整数”除法，这在您的情况下不是问题，但我们通过乘以十进制文字来确保分子是十进制的，这确保我们有十进制类型值。）
The rest of the expression deals with avoiding possible "divide by zero" exception, rounding to two decimal places, formatting two fixed decimal places, and appending the '%'.
表达式的其余部分处理避免可能的“除以零”异常、四舍五入到两位小数、格式化两个固定小数位并附加“%”。
If you want those % signs to line up, you can pad the left side of that string with spaces, to a specific length, e.g. LPAD(expr,7,' '). (Actually, I would probably do that before the CONCAT that adds the %', but it doesn't really matter.)
如果您希望这些 % 符号对齐，您可以在该字符串的左侧用空格填充特定长度，例如LPAD(expr,7,' '). （实际上，我可能会在添加 %' 的 CONCAT 之前这样做，但这并不重要。）
If you intend to do an average on a subset, for example, for a particular year and a particular dataset, instead of the entire return set, then we get the "subtotal" for each of those groups of data.
如果您打算对一个子集进行平均，例如，针对特定年份和特定数据集，而不是整个返回集，那么我们将获得这些数据组中的每组的“小计”。
Here, we're calculating the average from a particular data_set_nidand year:
在这里，我们正在计算特定data_set_nid和的平均值year：
SELECT v.id
, v.data_set_nid
, v.school_nid
, v.year
, v.description
-- , v.value
-- , t.total_value
, CONCAT(FORMAT(IF(t.total_value=0,0,(v.value*100.0)/t.total_value),2),'%')
AS `average`
FROM (SELECT u.data_set_nid
, u.year
, SUM(u.value) AS total_value
FROM school_data_sets_numeric_data u
GROUP
BY u.data_set_nid
, u.year
) t
JOIN school_data_sets_numeric_data v
ON v.data_set_nid = t.data_set_nid
, v.year = t.year
回答by jcho360
Try this.
尝试这个。
select @total:=sum(value) from school_data_sets_numeric_data;
select id, data_set_nid, school_nid, year, description,
concat(round((100*actor_id)/@total,2),"%") as percentage
from school_data_sets_numeric_data;
Input will be something like that
输入将是这样的
回答by Florin Stingaciu
Try the following. Tested on sqlfidlle.
请尝试以下操作。在sqlfidlle 上测试。
select id, data_set_nid,school_nid, year, description, round(value/(select sum(value) from school_data_sets_numeric_data)*100,2) as average from school_data_sets_numeric_data
