如何跟踪SQL中值的迭代

Question

我试图使用SSMS 2017在制造过程中跟踪刀片的使用。刀片已加载并在产品上使用，直到看到它变钝，然后将其带出来磨刀刀片更换。我们有30个叶片可用于使用和锐化。

使用提供产品批号(顺序(和刀片名称的表，我想将刀片名称的每个批次使用分为组。

我的SQL技能非常基本，因此我一直在尝试Row_number，等级以及一些尝试利用铅/滞后功能的尝试。到目前为止，这仅使我能够根据刀片名称将每个产品分解为订单，并确定对叶片更换的产品。我觉得这可能很有用，但是我很难确切地弄清楚如何做。

我希望能够通过刀片a a a识别数字分配每个制造的产品。例如：

LotNo   BladeID Iteration
418211  BH40    1
418212  BH40    1
418213  BH40    1
418214  ES11    2
418215  ES11    2
418216  BH40    3

我目前能够产生这些不正确的结果：

使用：

SELECT b.LotNo,
       b.BladeID,
       ROW_NUMBER() OVER (PARTITION BY b.BladeID ORDER BY b.BladeID)
FROM blades AS b
ORDER BY b.LotNo ASC;

我得到：

LotNo   BladeID Iteration
418211  BH40    1
418212  BH40    2
418213  BH40    3
418214  ES11    1
418215  ES11    2
418216  BH40    4

Answer 1

这是解决问题的可能解决方案。它首先创建了一系列组，以识别何时必须更改数字。然后，它将获得一个订单，将正确的值分配给每个组。最后，它为迭代分配了值。我以消耗的方式包括了示例数据，以便任何人都可以将其用于测试目的。

CREATE TABLE #Sample(
    LotNo     int,
    BladeID   varchar(10),
    Iteration int
);
INSERT INTO #Sample
VALUES
(418211, 'BH40', 1),
(418212, 'BH40', 1),
(418213, 'BH40', 1),
(418214, 'ES11', 2),
(418215, 'ES11', 2),
(418216, 'BH40', 3);
GO
WITH cteGroups AS(
    SELECT *,
        ROW_NUMBER() OVER(ORDER BY LotNo) - ROW_NUMBER() OVER(PARTITION BY BladeID ORDER BY LotNo) AS island
    FROM #Sample
),
cteOrdering AS(
    SELECT *, MIN( LotNo) OVER( PARTITION BY island, BladeID) AS OrderCol
    FROM cteGroups
)
SELECT LotNo,
    BladeID,
    Iteration,
    DENSE_RANK() OVER( ORDER BY OrderCol) AS IterationCalc
FROM cteOrdering;

Answer 2

您可以使用lag()和累积总和：

select s.*,
       sum(case when prev_BladeID = BladeId then 0 else 1 end) over (order by LotNo) as Iteration
from (select s.*,
             lag(s.BladeID) over (order by s.LotNo) as prev_BladeID
      from #sample s
     ) s;

除了与行号的差异更简单的代码外，我认为这在概念上也更简单。这只是计算BladeID从一个批次变为下一个的次数。