如何PIVOT和计算列平均值 [英] How to PIVOT and calculating column average
问题描述
我承认这是到目前为止我必须面对的最复杂的SQL语句之一.我有点撞到了墙上，希望有人能帮到我.
I admitted this is one of the most complex SQL statement I have to face so far. I sorta hit the wall on this one and I hope somebody can give me a hand.
我在数据库中有此表
Item ActiveTime(sec) DateTime
-------------------------------------------
1 10 2013-06-03 17:34:22 -> Monday
2 5 2013-06-04 17:34:22 -> Tuesday
1 2 2013-06-03 12:34:22 -> Monday
1 3 2013-06-04 17:33:22 -> Tuesday
我希望它在我的SQL语句后看起来像这样
I want it to look this way after my SQL Statement
Item Mon Tues Wed Thurs Fri Sat Sun Average
-----------------------------------------------------------------------------------
1 6 3 5
2 5 5
工作原理
由于(10 + 2)/2天，您可以看到星期一平均为6 周二平均值仅为3，因为它仅在周二发生一次. 项目1的平均值为5，因为(10 + 2 + 3)/3 = 5
You can see Monday average is 6 due to (10 + 2) / 2 days Tuesday average is just 3 because it occurs on Tuesday only once. Average value for item 1 is 5 because on (10 + 2 + 3)/3 = 5
它仅在星期二发生一次，因此项目2的星期二平均值为5. 平均值是5，因为它只发生一次，所以5/1 = 5.
It occurs only once on Tuesday so the average for Tuesday for Item 2 is 5. Average is 5 because it only happens once so 5/1 = 5.
到目前为止，我提出了以下SQL语句，该语句旨在显示按工作日细分的每个项目的平均ActiveTime以及每个项目的总体平均ActiveTime:
so far I came up with the following SQL Statement which aims to show the average ActiveTime of each Item broken down by weekday as well as the overall average ActiveTime for each item:
Select *,((ISNULL([Sunday],0) +ISNULL([Monday],0)+ ISNULL([Tuesday],0)+
ISNULL([Wednesday],0)+ ISNULL([Thursday],0)+ISNULL([Friday],0)+
ISNULL([Saturday],0)) /
( CASE WHEN [Sunday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Monday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Tuesday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Wednesday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Thursday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Friday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Saturday] is null
THEN 0 ELSE 1 END )) as Avg
FROM ( SELECT * FROM
(
SELECT a.ResetTime as ResetTime,a.ApartmentDescription as Apartment,
DATENAME(WEEKDAY,a.DateTime) _WEEKDAY
FROM tblECEventLog a
)
AS v1 PIVOT (AVG(ResetTime) FOR _WEEKDAY IN
([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday], [Saturday])
)
AS v2
)
AS v3
运行上面的SQL将产生以下结果:
Running the above SQL will yield the following:
Item Mon Tues Wed Thurs Fri Sat Sun Average
-----------------------------------------------------------------------------------
1 6 3 4.5
2 5 5
所以它几乎可以工作，但是注意到值4.5，通过执行(6 + 3)/2这是不正确的，我不想只求平均值. Andybody可以建议对我的SQL语句进行改进，以使每个项目的平均ActiveTime使用 实际 进行平均值计算?
So it almost works but notice the value 4.5, it got that by doing (6+3)/2 which is incorrect, I don't want to just add the average. Andybody can suggest improvement to my SQL statement to have the Average calculate using the actual average ActiveTime for each item?
推荐答案
您应该可以使用 avg() over() 以获取结果.这将允许您按每个item:
avg(ActiveTime) over(partition by item) Avg_Item
因此完整的查询将是:
SELECT item,
[Sunday],
[Monday],
[Tuesday],
[Wednesday],
[Thursday],
[Friday],
[Saturday],
Avg_Item
FROM
(
SELECT a.ActiveTime as ActiveTime,a.Item as Item,DATENAME(WEEKDAY,a.DateTime) _WEEKDAY,
avg(ActiveTime) over(partition by item) Avg_Item
FROM TableA a
) AS v1 PIVOT
(
AVG(ActiveTime)
FOR _WEEKDAY IN
(
[Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])
) AS v2;
请参见 SQL演示
这篇关于如何PIVOT和计算列平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
