SQL 最大连续合格次数 最大连胜记录次数 最大连败记录次数

有这样一个问题，工厂中要统计某个供应商送货检验的情况，依照其连续合格次数，决定是否免检，不使用游标或者循环，如何写这个sql。
此情景也可以用于统计连胜记录等

先要学习一下 窗函数LAG，指的是按分组和排序，取到之前(before)行的值。

假如表是这样的：

建表语句如下：

CREATE TABLE InspectionResults (ID int NOT NULL AUTO_INCREMENT,MaterialCode varchar(50) DEFAULT NULL,InspectionTime datetime DEFAULT NULL,InspectionOutcome varchar(10) DEFAULT NULL,PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 1,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;

按照物料，统计最大的连续合格次数，结果是：

以下是sql语句

WITH RankedResults AS (  SELECT  MaterialCode,  InspectionTime,  InspectionOutcome,  CASE  WHEN InspectionOutcome = 'Y' AND   (LAG(InspectionOutcome) OVER (PARTITION BY MaterialCode ORDER BY InspectionTime) IS NULL OR   LAG(InspectionOutcome) OVER (PARTITION BY MaterialCode ORDER BY InspectionTime) <> 'Y')  THEN 1 ELSE 0  END AS StartSequence  FROM  InspectionResults  
),  
ConsecutiveGroups AS (  SELECT  MaterialCode,  InspectionTime,  InspectionOutcome,  SUM(StartSequence) OVER (PARTITION BY MaterialCode ORDER BY InspectionTime) AS SequenceGroup  FROM  RankedResults  WHERE  InspectionOutcome = 'Y'  
),  
MaxConsecutiveCounts AS (  SELECT  MaterialCode,  SequenceGroup,  COUNT(*) AS ConsecutiveCount  FROM  ConsecutiveGroups  GROUP BY  MaterialCode,  SequenceGroup  
)  
SELECT  MaterialCode,  MAX(ConsecutiveCount) AS MaxConsecutivePasses  
FROM  MaxConsecutiveCounts  
GROUP BY  MaterialCode;

关键的中间步骤，请注意观察表中的数据：

WITH RankedResults AS (  SELECT  MaterialCode,  InspectionTime,  InspectionOutcome,  CASE  WHEN InspectionOutcome = 'Y' AND   (LAG(InspectionOutcome) OVER (PARTITION BY MaterialCode ORDER BY InspectionTime) IS NULL OR   LAG(InspectionOutcome) OVER (PARTITION BY MaterialCode ORDER BY InspectionTime) <> 'Y')  THEN 1   ELSE 0  END AS StartSequence  FROM  InspectionResults  
) 
SELECT  MaterialCode,  InspectionTime,  InspectionOutcome, StartSequence, SUM(StartSequence) OVER (PARTITION BY MaterialCode ORDER BY InspectionTime) AS SequenceGroup  
FROM  RankedResults  ;