时间戳

首页 > 解决方案 > 分组平均值

sql-server - 分组平均值

问题描述

假设我在下面有这个查询，它选择所有部门并在日期落在WHERE子句中指定的这 3 个日期中的任何一个时获取值。

SELECT 
Sector
,Val
FROM [Consumption] upc
WHERE upc.PeriodStart = '2020-12-07 17:00:00' 
    OR upc.PeriodStart = '2021-01-07 17:30:00' 
    OR upc.PeriodStart = '2021-02-10 18:00:00'

当前结果：

Sector        Val
Retail        53.4
Retail        58.1
Retail        60.5
Commercial    58.2
Commercial    60.6
Commercial    53.5

我将如何获得按部门分组的值之间的平均值乘以 2，以便结果如下所示？

预期结果：

Sector        Val       AvTot
Retail        53.4      114.67
Retail        60.5      114.67
Retail        58.1      114.67
Commercial    58.2      114.87
Commercial    60.6      114.87
Commercial    53.5      114.87

标签： sql-servertsql

解决方案

窗口化AVG()返回预期结果：

SELECT Sector, Val, AVG(Val * 2) OVER (PARTITION BY Sector) AS AvgTotal
FROM (VALUES
   ('Retail',        53.4),
   ('Commercial',    58.2),
   ('Retail',        60.5),
   ('Commercial',    60.6),
   ('Commercial',    53.5),
   ('Retail',        58.1)
) [Consumption] (Sector, Val)
ORDER BY Sector

结果（没有四舍五入和/或铸造）：

Sector     Val  AvgTotal
--------------------------
Commercial 58.2 114.866666
Commercial 60.6 114.866666
Commercial 53.5 114.866666
Retail     60.5 114.666666
Retail     58.1 114.666666
Retail     53.4 114.666666

推荐阅读