分组平均值

Question

假设我有下面这个查询，它选择所有扇区并获取日期落在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

Answer 1

窗口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