加入数据并优化性能 [英] Join data and optimize performance
问题描述
我需要加入2个表中的数据，我需要优化性能。
我想通过工作步骤加入订单。
Hi,
I need to join data from 2 table and I need to optimize the performance.
I want to join orders with working steps.
Table 1 - Orders
Barcode, OrderData, etc
184512, 20160412, ...
184752, 20160412, ...
184764, 20160413, ...
Table 2 - Working Steps (idWorkingStep from 1 to 5)
Barcode, Data, idWorkingStep
184512, 20160413, 1
184512, 20160413, 2
184512, 20160413, 4
184512, 20160413, 5
184764, 20160413, 2
184764, 20160414, 3
184752, 20160414, 4
需要的结果
Needed result
Barcode, count(idWorkingStep(1)) ws1, count(idWorkingStep(2)) ws2, count(idWorkingStep(3)) ws3, count(idWorkingStep(4)) ws4, count(idWorkingStep(5)) ws5
184512, 1, 1, 1, 1, 1
184764, 1, 1, 1, 0, 0
184752, 1, 1, 1, 1, 0
所有WorkingSteps都是必需的，因此如果扫描仪没有读取某个步骤而没有出现在表2中'出于同样的原因，我需要填写所有遗漏的信息。
正如您在结果表中看到的那样对于条形码184752我填充'1'从条形码的'表2'到最大（idWorkingStep）的所有步骤。
有时我使用它（脚本ex.2）：
All WorkingSteps are required, so if a step is not readed by the scanner and don't appears in the 'table 2' for same reason, I need to fill all the missing information.
As you can see in the result table for then Barcode 184752 i fill with '1' all the steps up to the max(idWorkingStep) from 'Table 2' for that Barcode.
Sometime I use this (script ex.2):
SELECT BarCode
, CASE ws1
WHEN 0
THEN
CASE ws2
WHEN 0
THEN
CASE ws3
WHEN 0
THEN
CASE ws4
WHEN 0 THEN ''
ELSE 1
END
ELSE 1
END
ELSE 1
END
ELSE
CASE ws1
WHEN 0 THEN ''
ELSE 1
END
END ws1
, CASE ws2
WHEN 0
THEN
CASE ws3
WHEN 0
THEN
CASE ws4
WHEN 0 THEN ''
ELSE 1
END
ELSE 1
END
ELSE 1
END ws2
, CASE ws3
WHEN 0
THEN
CASE ws4
WHEN 0 THEN ''
ELSE 1
END
ELSE 1
END ws3
, CASE ws4
WHEN 0 THEN ''
ELSE 1
END ws4
Tnx的回复
我尝试了什么：
我实际上使用视图对数据进行分组并使用：
SELECT Max（v） FROM（VALUES（ws1），（ws2），（ws3），（ws4），（ws5））AS value（v））WHERE Barcode ='184512'
所有这些代码都是为了获得ws1
对于ws2，我使用：
SELECT Max（v）FROM（VALUES（ws2），（ws3），（ws4），（ws5））AS值（v） ）WHERE Barcode ='184512'
可能不是最佳解决方案
Tnx for replies
What I have tried:
I actualy use views to group the data and I use:
SELECT Max(v) FROM (VALUES (ws1), (ws2), (ws3), (ws4), (ws5)) AS value(v)) WHERE Barcode = '184512'
All this code is to obtain ws1
For ws2 I use:
SELECT Max(v) FROM (VALUES (ws2), (ws3), (ws4), (ws5)) AS value(v)) WHERE Barcode = '184512'
Probably not the best solution
推荐答案
第一步（原谅pun）是为每个条形码生成所有可能步骤的列表。此查询将生成一个Table变量，其中包含示例中每个条形码的步骤1到5：
First step (excuse the pun) is to generate a list of all the possible steps for each barcode. This query will generate a Table variable containing steps 1 to 5 for each of the barcodes in your sample:
DECLARE @AllSteps TABLE (step int, barcode bigint)
;with CTE as
(
select 1 as num, barcode from Working_Steps
UNION ALL
select CTE.num + 1 , Barcode
from CTE where num < 5
)
INSERT INTO @AllSteps
select DISTINCT * from CTE
你然后可以使用该表变量LEFT OUTER JOIN到表Working_Steps，就像这样
You can then use that table variable to LEFT OUTER JOIN to table Working_Steps like this
SELECT O.Barcode, A.step, WS.idWorkingStep
FROM Orders O
INNER JOIN @AllSteps A ON O.Barcode = A.BarCode
LEFT OUTER JOIN Working_Steps WS ON WS.Barcode = O.Barcode AND WS.idWorkingStep=A.step
group by O.Barcode, A.step, WS.idWorkingStep获取以下结果（根据您在问题中的样本）
to get the following results (based on your sample in the question)
barcode step idWorkingStep
184512 1 1
184512 2 2
184512 3 NULL
184512 4 4
184512 5 5
184752 1 NULL
184752 2 NULL
184752 3 NULL
184752 4 4
184752 5 NULL
184764 1 NULL
184764 2 2
184764 3 3
184764 4 NULL
184764 5 NULL
然后你可以用一个简单的PIVOT来获取将数据转换为您想要的格式
You can then use a simple PIVOT to get the data into the format you want
SELECT * FROM
(
SELECT O.Barcode, A.step, WS.idWorkingStep
FROM Orders O
INNER JOIN @AllSteps A ON O.Barcode = A.BarCode
LEFT OUTER JOIN Working_Steps WS ON WS.Barcode = O.Barcode AND WS.idWorkingStep=A.step
group by O.Barcode, A.step, WS.idWorkingStep
) AS psource
PIVOT
(
COUNT(idWorkingStep) FOR step in ([1],[2],[3],[4],[5])
) AS pvt这给了我以下结果
Which gave me the following results
barcode 1 2 3 4 5
184512 1 1 0 1 1
184752 0 0 0 1 0
184764 0 1 1 0 0
[EDI T]
OP已经澄清了要求 - 可以假设在完成的最大步骤之前的序列中的任何间隙都已完成。对查询的这种调整将实现这一点（也包括@ RichardDeeming的建议）
OP has clarified the requirement - any gaps in the sequence earlier than the maximum step achieved can be assumed to have been done. This adjustment to the query will achieve that (incorporating @RichardDeeming's suggestion too)
DECLARE @AllSteps TABLE (step int, barcode bigint)
INSERT INTO @AllSteps
SELECT T.num, S.barcode FROM Working_Steps As S
CROSS APPLY (VALUES (1), (2), (3), (4), (5)) As T (num)
SELECT Barcode, [1],[2],[3],[4],[5] FROM
(
SELECT O.Barcode, A.step, T.MaxPer,
CASE WHEN A.step < T.MaxPer THEN A.step
ELSE NULL END AS idWorkingStep
FROM Orders O
INNER JOIN @AllSteps A ON O.Barcode = A.BarCode
INNER JOIN (SELECT MAX(idWorkingStep) as MaxPer, Barcode FROM Working_Steps GROUP BY Barcode) T ON O.Barcode=T.Barcode
LEFT OUTER JOIN Working_Steps WS ON WS.Barcode = O.Barcode AND WS.idWorkingStep=A.step
group by O.Barcode, A.step, t.MaxPer, WS.idWorkingStep
) AS psource
PIVOT
(
COUNT(idWorkingStep) FOR step in ([1],[2],[3],[4],[5])
) AS pvt
ORDER BY Barcode
产生结果
Which yields the results
barcode 1 2 3 4 5
184512 1 1 1 1 1
184752 1 1 1 1 0
184764 1 1 1 0 0
鉴于所有记录到最大记录的步骤都可以假定已经采取行动，那么我们真正感兴趣的是每个条形码记录的最大步数。因此根本不需要PIVOT - 可以通过以下方式生成相同的结果：
Given that all steps up to the maximum recorded can be assumed to have been actioned then all we are really interested in is the maximum step recorded per barcode. So there is no need for the PIVOT at all - the same results can be generated by the following:
DECLARE @MaxSteps TABLE(MaxPer int, barcode bigint)
INSERT INTO @MaxSteps
SELECT MAX(idWorkingStep) as MaxPer, Barcode FROM Working_Steps GROUP BY Barcode
SELECT O.Barcode,
[1] = CASE WHEN M.MaxPer >= 1 THEN 1 ELSE 0 END,
[2] = CASE WHEN M.MaxPer >= 2 THEN 1 ELSE 0 END,
[3] = CASE WHEN M.MaxPer >= 3 THEN 1 ELSE 0 END,
[4] = CASE WHEN M.MaxPer >= 4 THEN 1 ELSE 0 END,
[5] = CASE WHEN M.MaxPer >=5 THEN 1 ELSE 0 END
FROM Orders O
LEFT OUTER JOIN @MaxSteps M ON O.Barcode = M.Barcode
这篇关于加入数据并优化性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
