我如何使用SQL Server获得此结果(avg，median) [英] How can i get this result(avg,median) using sql server

问题描述

这是我的桌子.

This is my table.

select * from table1

-------------------------------------------------- ------------------------------
名称v1 v2 v3
-------------------------------------------------- ------------------------------
a 1.2 1.2 2.0
b 2.1 2.4 3.4
c 1.0 8.5 0.56
d 2.7 2.5 2.6
-------------------------------------------------- ------------------------------
我需要这个结果.
-------------------------------------------------- ------------------------------
名称v1 v2 v3
-------------------------------------------------- ------------------------------
平均1.75 3.65 2.14(此新行)
中位数1.65 2.45 2.3(此新行)
a 1.2 1.2 2.0
b 2.1 2.4 3.4
c 1.0 8.5 0.56
d 2.7 2.5 2.6
-------------------------------------------------- ------------------------------
行Avg为v1值(1.2、2.1、1.0、2.7)为1.75
v2值(1.2,2.4,8.5,2.5)为3.65
v3值(2.0,3.4,0.56,2.6)为2.14

使用avg()动态添加平均行.

中位数"行是值的一半.它是动态添加的
v1(1.2,2.1,1.0,2.7)中位数为1.65.

请任何人帮助我.非常感谢.

--------------------------------------------------------------------------------
name v1 v2 v3
--------------------------------------------------------------------------------
a 1.2 1.2 2.0
b 2.1 2.4 3.4
c 1.0 8.5 0.56
d 2.7 2.5 2.6
--------------------------------------------------------------------------------
I need this result.
--------------------------------------------------------------------------------
name v1 v2 v3
--------------------------------------------------------------------------------
avg 1.75 3.65 2.14(this new row)
median 1.65 2.45 2.3 (THIS new row)
a 1.2 1.2 2.0
b 2.1 2.4 3.4
c 1.0 8.5 0.56
d 2.7 2.5 2.6
--------------------------------------------------------------------------------
The row Avg is v1 values(1.2,2.1,1.0,2.7) is 1.75
v2 values (1.2,2.4,8.5,2.5) is 3.65
v3 values (2.0,3.4,0.56,2.6) is 2.14

The row avg is added dynamically using avg().

The row Median is half of the value. It is dynamically added
v1(1.2,2.1,1.0,2.7) median is 1.65.

Please anyone help me. Thanks a lot.

推荐答案

此处的一个示例: http://www.sqlmag.com /article/tsql3/calculating-the-median-gets-simpler-in-sql-server-2005 [ SQL Server中的自定义聚合 [ ^ ]

One example here: http://weblogs.sqlteam.com/jeffs/archive/2010/08/30/sql-server-calculate-median.aspx[^]

You can also use CTE which would be performing more well. An example: http://www.sqlmag.com/article/tsql3/calculating-the-median-gets-simpler-in-sql-server-2005[^]

And also if you want, you can create your own aggregate, an example: Custom Aggregates in SQL Server[^]

继续Mika Wendelius的回答..，
参见下面的链接..
此处 [

Continue to Mika Wendelius answer..,
see below link also..
Here[^]

这篇关于我如何使用SQL Server获得此结果(avg，median)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！