sql - 如何进行数据透视并计算列平均值

Question

我承认这是迄今为止我必须面对的最复杂的 SQL 语句之一。我在这件事上有点碰壁，我希望有人能帮我一把。

我在数据库中有这个表

 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 语句之后看起来像这样

 Item     Mon     Tues    Wed    Thurs   Fri  Sat   Sun    Average
 -----------------------------------------------------------------------------------
 1        6       3                                        5
 2                5                                        5

它是如何工作的

对于第 1 项:

由于 (10 + 2)/2 天，您可以看到周一平均值为 6周二平均值仅为 3，因为周二只发生一次。第 1 项的平均值为 5，因为 (10 + 2 + 3)/3 = 5

对于第 2 项:

它在星期二仅发生一次，因此项目 2 星期二的平均值为 5。平均值为 5，因为它只发生一次，所以 5/1 = 5。

到目前为止，我想出了以下 SQL 语句，旨在显示按工作日分割的每个项目的平均 ActiveTime 以及每个项目的总体平均 ActiveTime:

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 将产生以下结果:

 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 来计算平均值吗？

Answer 1

您应该能够使用avg() over()得到结果。这将允许您按每个项目对数据进行分区:

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 Demo