找到一种方法来创建多个列，在同一个SQL查询中按不同分组平均数据

Question

此查询为我们提供了各种操作的时间。现在它按照名称，日期和铲子模型为每个事件分配时间。不过，我想以某种方式将一名船员的所有名字的平均值加入一个新的专栏。作为SQL游戏的新手，拥有可以提供各种平均定义并将它们返回到同一个表的策略将是非常好的。输出图片在这个链接之后。

https://i.stack.imgur.com/6V5XB.png

DECLARE @start AS DATETIME = '2017-08-15' 

DECLARE @end AS DATETIME = '2017-08-20' 

SELECT * 

FROM ( 

SELECT 

[Name], 
[Date], 
MAX([Crew]) as [Crew], 
MAX([Shovel_ID]) as [Shovel Model], 
CONVERT(DECIMAL(10,1) ,AVG([empty])) as [Empty], 
CONVERT(DECIMAL(10,1) ,AVG([HAUL])) AS [Hauling], 
CONVERT(DECIMAL(10,0) ,AVG([Wait At Dump]*60)) AS [Wait at Dump], 
CONVERT(DECIMAL(10,0) ,AVG([Dumping]*60)) as [Dumping], 
CONVERT(DECIMAL(10,0) ,AVG([Spot at LU]*60)) as [Spot at LU], 
CONVERT(DECIMAL(10,0) ,AVG([Load Time]*60)) AS [Truck Loading], 
CONVERT(DECIMAL(10,0) ,AVG([QUEUE]*60)) as [Wait at Shovel], 
CONVERT(DECIMAL(10,0) ,AVG([Hot Seat]*60)) as [Hot Seat], 
CONVERT(DECIMAL(10,0) ,AVG([Empty Hauling]*60)) as [Empty Stopped], 
CONVERT(DECIMAL(10,0) ,AVG([HAULING STOPPED]*60)) AS [Hauling Stopped] 


FROM (

Select 
haul_cycle, 
max([name]) as [Name], 
max([shovel_id]) as [Shovel_ID], 
max([date]) as [Date], 
max([crew]) as [Crew], 
sum(dumping) as [Dumping], 
sum([spot at lu]) as [Spot at LU], 
sum([wait at dump]) as [Wait at Dump], 
sum([empty]) as [Empty], 
sum([hauling]) as [Haul], 
SUM([Truck Loading]) as [Load Time], 
SUM([Queue at LU]) as [QUEUE], 
SUM([Hot Change]) as [Hot Seat], 
SUM([EMPTY sTOPPED]) AS [Empty Hauling], 
SUM([HAULing STOPPED]) AS [Hauling Stopped], 

SUM([HAULing STOPPED]+[EMPTY STOPPED]+[Queue at LU]+[Truck Loading]+ 
[Hauling]+[Empty]+[Spot at LU]+[wait at dump]+[dumping]) as [Cycle Time], 
COUNT([HAUL_CYCLE]) AS [Number of Cycles] 

FROM (

SELECT 
HAUL_CYCLE_TRANS.HAUL_CYCLE_REC_IDENT as [HAUL_CYCLE], 
CREW_IDENT as [CREW], 
badge.LAST_NAME + ' , ' + badge.FIRST_NAME as [Name], 
DUMP_END_SHIFT_DATE as date , 
DUMP_END_SHIFT_IDENT, 
CAST(EQUIPMENT_STATUS_TRANS.END_TIMESTAMP- 
EQUIPMENT_STATUS_TRANS.START_TIMESTAMP AS FLOAT)*24*60 AS DURATION, 
COALESCE(SUB_STATUS_DESC, STATUS_DESC) AS [Status], 

CASE 
    WHEN HAUL_CYCLE_TRANS.LOADING_UNIT_IDENT IN (4001, 4002, 4005, 4006) 
THEN '6060' 
    ELSE '7495' 
END AS SHOVEL_ID 

FROM HAUL_CYCLE_TRANS 

LEFT JOIN HAUL_UNIT_STATUS_TRANS_COL on 
HAUL_UNIT_STATUS_TRANS_COL.HAUL_CYCLE_REC_IDENT = 
HAUL_CYCLE_TRANS.HAUL_CYCLE_REC_IDENT 

LEFT JOIN EQUIPMENT_STATUS_TRANS on 
EQUIPMENT_STATUS_TRANS.EQUIP_STATUS_REC_IDENT = 
HAUL_UNIT_STATUS_TRANS_COL.EQUIP_STATUS_REC_IDENT 

LEFT JOIN badge on badge.BADGE_IDENT = 
haul_cycle_trans.HAULING_UNIT_BADGE_IDENT 

LEFT JOIN EQUIP_STATUS_CODE on EQUIP_STATUS_CODE.STATUS_CODE = 
EQUIPMENT_STATUS_TRANS.STATUS_CODE 


LEFT JOIN EQUIP_SUB_STATUS_CODE on EQUIP_SUB_STATUS_CODE.SUB_STATUS_CODE = 
EQUIPMENT_STATUS_TRANS.SUB_STATUS_CODE 

WHERE dump_end_shift_date >= @start AND First_Name <> '') raw_data 

PIVOT(SUM(duration) FOR [Status] IN ([Dumping], [Spot at LU],[Wait at Dump], 
[Empty], [Empty Stopped], [Hauling Stopped], [Hauling], [Queue at LU], 
[Truck Loading], [Hot Change])) DATAFIELD 

GROUP BY haul_cycle) SUM_DATA_FOR_EACH_REC 
GROUP BY [name], [date], Shovel_ID 
) AVG_OPERATOR_STATUS 

ORDER BY [NAME],[DATE] 

Answer 1

可以使用OVER (PARTITION BY)功能。

通过这种方式，您可以使用不同的方式对数据集进行分区，从而使用您希望平均具有的任何分割。

您可以阅读更多关于OVER条款here。

例子：

SELECT 
    DateField 
    ,YourName1 
    ,YourId 
    ,SUM(Value1) 
    ,AVG(Value1) OVER (PARTITION BY DateField) AS DateFieldAverage 
FROM 
    YourTable 
GROUP BY 
    DateField 
    ,YourName1 
    ,YourId 

假设你正在使用SQL Server（基于你的问题顶部的语法）。