SQL 如何计算列的平均值然后将其包含在oracle的选择查询中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9647693/
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
How to calculate average of a column and then include it in a select query in oracle?
提问by Ashish dmc4
My table is--
我的桌子是——
create table mobile
(
id integer,
m_name varchar(20),
cost integer
)
and the values are --
这些值是——
insert into mobile values(10,'NOkia',100);
insert into mobile values(11,'Samsung',150);
insert into mobile values(12,'Sony',120);
I know how to calculate average on column cost, my code is--
我知道如何计算列成本的平均值，我的代码是——
select avg(cost) from mobile;
and the result is 123
结果是123
But i want to calculate average and then also show the difference.I was able to this but, I am not able to add avg column in the select query--
但我想计算平均值，然后也显示差异。我能够做到这一点，但是，我无法在选择查询中添加 avg 列--
My code is ---
我的代码是---
SELECT id, m_name as "Mobile Name", cost as Price,avg(cost) as Average,
cost-(select avg(cost) from mobile) as Difference FROM mobile
group by id,m_name,cost;
and the output is --
输出是——
id Mobile Name Price Average Difference
10 Nokia 100 100 -23
11 Samsung 150 150 27
12 Sony 120 120 -3
what I wants is to correct this average column.. I wants this---
我想要的是纠正这个平均列..我想要这个---
id Mobile Name Price Average Difference
10 Nokia 100 123 -23
11 Samsung 150 123 27
12 Sony 120 123 -3
please help...
请帮忙...
回答by David Faber
Since you're using Oracle, you should be able to use AVG() as an analytic (window) function:
由于您使用的是 Oracle，您应该能够使用 AVG() 作为分析（窗口）函数：
SELECT id, m_name AS "Mobile Name" cost AS Price, AVG(cost) OVER( ) AS Average
, cost - AVG(cost) OVER ( ) AS Difference
FROM mobile
No need for subqueries or GROUP BY.
不需要子查询或 GROUP BY。
回答by Justin Pihony
Your group by is what aggregates your average, and it is grouping by the whole table (I am assuming you did this to allow the select for everything) Just move your avg into another subquery, remove the overarching group by and that should solve it.
您的 group by 是聚合您的平均值的内容，它按整个表进行分组（我假设您这样做是为了允许选择所有内容）只需将您的 avg 移动到另一个子查询中，删除总体 group by 即可解决问题。
SELECT id, m_name AS "Mobile Name", cost AS Price,
(SELECT AVG(cost) FROM mobile) AS Average,
cost-(SELECT AVG(cost) FROM mobile) AS Difference
FROM mobile;
When you run the basic SELECT AVG(cost)statement it is naturally grouping by the column specified (cost in this case) as that is what you are requesting. I would suggest reading up more on GROUP BYand aggregatesto get a better grasp on the concept. That should help you more than just a simple solution.
当您运行基本SELECT AVG(cost)语句时，它自然会按指定的列（在本例中为成本）进行分组，因为这就是您所请求的。我建议阅读更多关于GROUP BY和聚合的内容，以更好地掌握这个概念。这对您的帮助不仅仅是一个简单的解决方案。
UPDATE:
更新：
The answer below is actually from David's answer. It makes use the analytical functions. Basically, what is happening is that on each AVG call, you are telling the engine what to use for the function (in this case, nothing). A decent writeup on analytical functions can be found hereand hereand more with a google on the matter.
下面的答案实际上来自大卫的答案。它利用了分析功能。基本上，发生的事情是在每个 AVG 调用中，您都在告诉引擎该函数使用什么（在这种情况下，什么都没有）。关于分析函数的一篇不错的文章可以在这里和这里找到，还有更多关于这个问题的谷歌。
SELECT id, m_name AS "Mobile Name" cost AS Price, AVG(cost) OVER( ) AS Average,
cost - AVG(cost) OVER ( ) AS Difference
FROM mobile
However, if your SQL engine allows for variables, you could just as easily do the below answer. I actually prefer this for future maintainability/readability. The reason is that a variable with a good name can be very descriptive to future readers of the code, versus an analytical function that does require a little bit more work to read (especially if you do not understand the over function).
但是，如果您的 SQL 引擎允许使用变量，则您可以轻松地执行以下答案。我实际上更喜欢这个以实现未来的可维护性/可读性。原因是具有良好名称的变量对于未来的代码读者来说非常具有描述性，而分析函数则需要更多的工作来阅读（特别是如果您不理解 over 函数）。
Also, this solution duplicates the same query twice, so it might be worth storing your average in a SQL variable. Then you ca change your statement to simply use that global average
此外，此解决方案将同一查询重复两次，因此可能值得将您的平均值存储在 SQL 变量中。然后你可以改变你的陈述来简单地使用这个全球平均值
This is variables in SQL-Server (you will have to adapt it for your own instance of SQL)
这是 SQL-Server 中的变量（您必须根据自己的 SQL 实例对其进行调整）
DECLARE @my_avg INT;
SELECT @my_avg = AVG(cost) FROM Mobile;
SELECT id, m_name AS "Mobile Name", cost AS Price,
@my_avg AS Average, cost-@my_avg AS Difference
FROM mobile;
This solution will read a lot cleaner to future readers of your SQL, too
对于 SQL 的未来读者，此解决方案也会更清晰
回答by ruakh
The simplest change is to change avg(cost) as Averageto (select avg(cost) from mobile) as Average. This also means that you won't need the GROUP BYclause anymore (since it doesn't do what you actually wanted):
最简单的更改是更改avg(cost) as Average为(select avg(cost) from mobile) as Average。这也意味着您将不再需要该GROUP BY子句（因为它没有做您真正想要的）：
SELECT id,
m_name AS "Mobile Name",
cost AS "Price",
(SELECT AVG(cost) FROM mobile) AS "Average",
cost - (SELECT AVG(cost) FROM mobile) AS "Difference"
FROM mobile
;
回答by RAJU
select pid, name, price as actualcost,
AVERAGE = (select AVG(price) from Part_Master),
price - (select AVG(price) as diff from Part_Master) AS COST_DIFF
from Part_Master
回答by Angelo Fuchs
try
尝试
SELECT id, m_name as "Mobile Name", cost as Price,(select avg(cost) from mobile) as Average),
cost-(select avg(cost) from mobile) as Difference FROM mobile
group by id,m_name,cost;
if your query is too expensive that way drop me a commend then I'll improve it.
如果您的查询太昂贵，请给我一个推荐，然后我会改进它。
回答by tawman
One of the rare times a CROSS JOINis applicable:
aCROSS JOIN适用的罕见时间之一：
WITH avgcost as (select round(avg(cost)) as Average from mobile)
SELECT id, m_name as "Mobile Name", cost as Price, Average,
cost-Average as Difference
FROM mobile cross join avgcost
Which will result in:
这将导致：
ID Mobile Name PRICE AVERAGE DIFFERENCE
10 NOkia 100 123 -23
11 Samsung 150 123 27
12 Sony 120 123 -3
