SQL Server中的聚合问题

Question

我的查询进行了两次pivot，以便获得每列中的停机代码之和和每列中的停机代码计数。

有两个内连接，用于引入宽度和数量组件，以执行SQM生产和LM生产的计算。

我的问题是，我的查询得到重复的记录，我觉得我已经尝试了一切来解决它;我需要一个记录每个日期，机器，LM(生产)，SQM(生产)和停机代码的每个计数(NUM_)和总和(H_)。

数据输出示例:https://docs.google.com/spreadsheets/d/1lnQrtP5GO-F_zB7zR2GBvLe8RIa61rA8/edit?usp=sharing& ouid = 115258646474729421097, rtpof = true& sd = true

SELECT 
Pivot_DownTime_Code.Date, 
Pivot_DownTime_Code.Mach AS MACHINE, 
FORMAT(
ISNULL(
ROUND(
(
SUM(Table2.qty) / 3.2808399
), 
4
), 
0
), 
'F', 
'it-IT'
) AS LM_CY, 
FORMAT(
ROUND(
SUM(
(
(
ROUND(
(Table2.qty / 12), 
4
) * (
CONVERT(FLOAT, Table3.Width)
)
) / 10.7639104
) -1
), 
4
), 
'F', 
'it-IT'
) AS SQM_CY, 
Pivot_DownTime_Code.[Reason1] AS H_Reason1, 
Pivot_DownTime_Code.[Reason2] AS H_Reason2, 
Pivot_DownTime_Code.[Reason3] AS H_Reason3, 
Pivot_DownTime_Code.[Reason4] AS H_Reason4, 
Pivot_DownTime_Code.[Reason5] AS H_Reason5, 
Pivot_DownTime_Code.[Reason6] AS H_Reason6, 
Pivot_DownTime_Code.[Reason7] AS H_Reason7, 
Pivot_DownTime_Code.[Reason8] AS H_Reason8, 
Pivot_DownTime_Code.[Reason9] AS H_Reason9, 
Pivot_DownTime_Code.[Reason10] AS H_Reason10, 
Pivot_DownTime_Code.[Reason11] AS H_Reason11, 
Pivot_DownTime_Code.[Reason12] AS H_Reason12, 
Pivot_DownTime_Code.[Reason13] AS H_Reason13, 
Pivot_DownTime_Code.[Reason14] AS H_Reason14, 
NUM_Reason1, 
NUM_Reason2, 
NUM_Reason3, 
NUM_Reason4, 
NUM_Reason5, 
NUM_Reason6, 
NUM_Reason7, 
NUM_Reason8, 
NUM_Reason9, 
NUM_Reason10, 
NUM_Reason11, 
NUM_Reason12, 
NUM_Reason13, 
NUM_Reason14 
FROM 
(
SELECT 
Date, 
Mach, 
DownMinutes, 
ISNULL(_Code, 'Reason14') AS _Code, 
-- Here downtime codes are being counted
SUM(
CASE WHEN _Code = 'Reason1' THEN 1 ELSE 0 END
) AS NUM_Reason1, 
SUM(
CASE WHEN _Code = 'Reason2' THEN 1 ELSE 0 END
) AS NUM_Reason2, 
SUM(
CASE WHEN _Code = 'Reason3' THEN 1 ELSE 0 END
) AS NUM_Reason3, 
SUM(
CASE WHEN _Code = 'Reason4' THEN 1 ELSE 0 END
) AS NUM_Reason4, 
SUM(
CASE WHEN _Code = 'Reason5' THEN 1 ELSE 0 END
) AS NUM_Reason5, 
SUM(
CASE WHEN _Code = 'Reason6' THEN 1 ELSE 0 END
) AS NUM_Reason6, 
SUM(
CASE WHEN _Code = 'Reason7' THEN 1 ELSE 0 END
) AS NUM_Reason7, 
SUM(
CASE WHEN _Code = 'Reason8' THEN 1 ELSE 0 END
) AS NUM_Reason8, 
SUM(
CASE WHEN _Code = 'Start-up/Shutdown' THEN 1 ELSE 0 END
) AS NUM_Reason9, 
SUM(
CASE WHEN _Code = 'Reason10' THEN 1 ELSE 0 END
) AS NUM_Reason10, 
SUM(
CASE WHEN _Code = 'Reason11' THEN 1 ELSE 0 END
) AS NUM_Reason11, 
SUM(
CASE WHEN _Code = 'Reason12' THEN 1 ELSE 0 END
) AS NUM_Reason12, 
SUM(
CASE WHEN _Code = 'Reason13' THEN 1 ELSE 0 END
) AS NUM_Reason13, 
SUM(
CASE WHEN _Code = 'Reason14' THEN 1 ELSE 0 END
) AS NUM_Reason14 
FROM 
Database1.dbo.Table1
GROUP BY 
Date, 
Mach, 
DownMinutes, 
_Code
) AS DownTimeTotals PIVOT(

--This has to be pivoted for the desired layout
SUM([DownMinutes]) FOR [_Code] IN(
[Reason1], [Reason2], 
[Reason3], [Reason4], 
[Reason5], [Reason6], 
[Reason7], [Reason8], 
[Reason9], [Reason10], 
[Reason11], [Reason12], 
[Reason13], [Reason14]
)
) AS Pivot_DownTime_Code 
INNER JOIN [Server1].[Database1].dbo.Table2 ON Pivot_DownTime_Code.Mach = Table2.coater_desc 
AND CONVERT(
VARCHAR, Pivot_DownTime_Code.Date, 
1
) = CONVERT(VARCHAR, Table2.picked_date, 1) 
INNER JOIN [Server1].[Database1].dbo.Table3 ON Pivot_DownTime_Code.Mach = Table3.Machine 
AND Table2.[MO Number] = Table3.MO 
AND CONVERT(
VARCHAR, Pivot_DownTime_Code.Date, 
1
) = CONVERT(VARCHAR, Table3.Date, 1) 
GROUP BY 
Pivot_DownTime_Code.Date, 
Pivot_DownTime_Code.Mach, 
Table3.Width, 
Pivot_DownTime_Code.[Reason1], 
Pivot_DownTime_Code.[Reason2], 
Pivot_DownTime_Code.[Reason3], 
Pivot_DownTime_Code.[Reason4], 
Pivot_DownTime_Code.[Reason5], 
Pivot_DownTime_Code.[Reason6], 
Pivot_DownTime_Code.[Reason7], 
Pivot_DownTime_Code.[Reason8], 
Pivot_DownTime_Code.[Reason9], 
Pivot_DownTime_Code.[Reason10], 
Pivot_DownTime_Code.[Reason11], 
Pivot_DownTime_Code.[Reason12], 
Pivot_DownTime_Code.[Reason13], 
Pivot_DownTime_Code.[Reason14], 
NUM_Reason1, 
NUM_Reason2, 
NUM_Reason3, 
NUM_Reason4, 
NUM_Reason5, 
NUM_Reason6, 
NUM_Reason7, 
NUM_Reason8, 
NUM_Reason9, 
NUM_Reason10, 
NUM_Reason11, 
NUM_Reason12, 
NUM_Reason13, 
NUM_Reason14 
ORDER BY 
Date;

Answer 1

感谢大家的帮助。看起来都是不错的解释。我结束了:

• 将日期、机器、LM、SQM的输出插入到临时表
• 在停机时间总和的第一个枢轴上加入输出
• 在下一个pivot查询
上连接该临时表

对于从搜索中看到这个问题的其他人:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL 
DROP 
TABLE #TempTable
GO IF OBJECT_ID('tempdb..#TempTable2') IS NOT NULL 
DROP 
TABLE #TempTable2
GO 
SELECT 
...INTO #TempTable
FROM 
...
JOIN...(if needed) GO 
SELECT 
...
FROM 
...(PIVOT Statement) GO 
SELECT 
#Temptable2.FieldNames...
SUM (
CASE WHEN _CODE = 'Reason1' then 1 else 0 end
) as Num_REASON1...
FROM 
TABLE1 
INNER JOIN #TempTable2 etc