计算AVG& STDEV用于sql查询中的名称值 [英] Calculate AVG & STDEV for name's values in sql query

问题描述

我目前有查询，后面调用output1。我想在查询中添加额外的列，例如AVG和STDEV，它们可以为每个名称的标记计算AVG和STDEV，如所需的输出2所示。



output1

I currently have query, which calls following output1. I would like to add extra column to the query such as AVG and STDEV, which can calculate AVG and STDEV for each name's tag, such as shown in desired output2.

output1

name	Tag	        Value
AIM	00900AAC2	96.76
AIM	00900AAC2	96
AIM	00900AAC2	97
ARE	NULL	        98.5
ARE	NULL	        97

Select [Name], Tag, [Decimal price] from (
select [Name], Tag, [Decimal price], count([Decimal price]) Over(Partition by Name) cnt
from [dbo].[database_data]
where [UploadDate] = '2014-08-18'
) t
WHERE cnt>=3

期望的输出2

Desired Output2

name	 Tag	 Value	         AVG	STD
AIM	00900AAC2	96.76	96.59	0.43
AIM	00900AAC2	96		
AIM	00900AAC2	97		
ARE	NULL	        98.5	97.75	0.75
ARE	NULL	        97		

非常感谢任何帮助。

非常感谢

Any help would be very much appreciated.
Many thanks

推荐答案

假设您的sql引擎（您没有告诉我们您使用的引擎）具有AVG和STD聚合功能，你对这个问题有点不合适。您将sql查询与报告混淆。查询返回一个集合，因此它按定义无序。所以假设你得到了计算结果，你可以得到这样的结果：

Assuming that your sql engine (you haven't told us which engine you use) has AVG and STD aggregation functions, you are a little bit out of the line with this question. You confuse an sql query with a report. A query returns a set, thus it is unordered by definition. So assuming that you get the calculation, you can end up with a result like this:

name	 Tag	 Value	         AVG	STD
AIM	00900AAC2	96		
AIM	00900AAC2	96.76	96.59	0.43
AIM	00900AAC2	97		
ARE	NULL	        97
ARE	NULL	        98.5	97.75	0.75

现在让我们解决另一个问题，如果你想要坚持你发布的结果：你可以轻松生成如下结果：

Now let's take the other problem, if you want to stick to the "result" you posted: You can easily generate a result like this:

name	 Tag	 Value	         AVG	STD
AIM	00900AAC2	96	96.59	0.43	
AIM	00900AAC2	96.76	96.59	0.43
AIM	00900AAC2	97	96.59	0.43	
ARE	NULL	        97      97.75	0.75
ARE	NULL	        98.5	97.75	0.75

by添加子查询（未测试 - 只是为了得到这个想法）：

by adding subquery (not tested - and only to get the idea):

select 
[Name], 
[Tag], 
[Value], 
(select AVG([value]) from [dbo].[database_data] t2 where t2.name=t2.name) as AVG,
(select STDEV([value]) from [dbo].[database_data] t2 where t2.name=t2.name) as STD
from [dbo].[database_data] t1

但是很难将聚合结果仅显示在每个组的第一行 - 并且让其他地方说空值。 SQL查询并不意味着这样做。你真的应该在表示层上处理这个，无论是什么。

But it is really hard to have the aggregations result shown only for the first row of every group - and let's say nulls elsewhere. An sql query is just not meant to do this. You really should handle this on the presentation layer, whatever that is.

选择列并使用Group By

select the columns and Use Group By

尝试以下查询以获得粗略的想法获得所需的输出。

Try following query to get rough idea to get desired output.

SELECT	t.[Name], 
		t.Tag, 
		t.[Decimal price],
		(CASE WHEN id = 1 THEN (SELECT Avg([Decimal price]) FROM [database_data] WHERE NAME = t.NAME) ELSE NULL END) [Avg],
		(CASE WHEN id = 1 THEN (SELECT STDEV([Decimal price]) FROM [database_data] WHERE NAME = t.NAME) ELSE NULL END) [STDEV]
FROM	(
			SELECT [Name], 
					Tag,
					[Decimal price], 
					row_number() Over(partition by Name order by Name) id
			FROM	[database_data]
		) t

对条件进行更改你想要的。

Make changes in its conditions whatever you want.

这篇关于计算AVG& STDEV用于sql查询中的名称值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！