假設我在下面有這個查詢，它選擇所有扇區并在日期落在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
uj5u.com熱心網友回復：
一個視窗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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/313282.html
標籤：sql-server 查询语句
上一篇：如何選擇具有相同值的記錄？