SQL需求：基于ProductionTime统计DMC的合格零件及FTT-Yield指标

我有一张存储测量数据的表[dbo].[Results]，结构如下：

SELECT [Plant] --工厂名称
       ,[Machine] --设备名称
       ,[Material] --物料名称
       ,[Batch] --批次名称
       ,[DMC] --每个零件唯一的Data Matrix Code
       ,[ProductionTime] --UTC时间戳
       ,[Result] --0代表不合格(NOK)，1代表合格(OK)
FROM [dbo].[Results]

每行对应一次测量结果，每个零件对应唯一DMC，但零件可多次测试，因此表中存在重复DMC（对应不同Result）。

示例数据 #

需要新增的统计列 #

为计算报废率和首次通过率，需在现有统计结果中添加以下列：

• #OKParts：按每个DMC的最新ProductionTime，统计Result=1的数量
• #NOKParts：按每个DMC的最新ProductionTime，统计Result=0的数量
• #PartsFirstTestOK：按每个DMC的最早ProductionTime，统计Result=1的数量
• #PartsFirstTestNOK：按每个DMC的最早ProductionTime，统计Result=0的数量

现有查询语句 #

目前我使用的查询语句如下（已添加过滤条件，处理大数据量）：

SELECT [Plant]
      ,[Machine]
      ,[Material]
      ,[Batch]
      ,Count([Result]) as '#Tests' --总测试次数
      ,Count(Distinct [DMC]) as '#Parts' --总零件数
      ,COUNT(CASE when [Result] = 1 THEN 1 END) as '#OKTests' --合格测试次数
      ,COUNT(CASE when [Result] = 0 THEN 1 END) as '#NOKTests' --不合格测试次数
  FROM [dbo].[Results]
  where [Plant] = 'A' and [ProductionTime] > DATEADD(DAY, -365,GETDATE())
  group by [Plant],[Material],[Batch],[Machine]

（也可使用Sum(Cast([Result] as INT))和Count([Result])-Sum(Cast([Result] as INT))替代CASE函数）

现有查询结果 #

我尝试过子查询、FIRST_VALUE及OVER函数，但都未成功，希望能得到解决方案。

可以使用**公共表表达式(CTE)**先预处理每个DMC的首次和末次测试结果，再和原统计数据关联，既保证效率又逻辑清晰。

完整查询语句 #

WITH FilteredData AS (
    -- 先过滤数据，减少后续处理量
    SELECT 
        [Plant], [Machine], [Material], [Batch], [DMC], [ProductionTime], [Result],
        -- 标记每个DMC的首次测试（时间升序第一条）
        ROW_NUMBER() OVER (PARTITION BY [DMC] ORDER BY [ProductionTime] ASC) AS rn_asc,
        -- 标记每个DMC的末次测试（时间降序第一条）
        ROW_NUMBER() OVER (PARTITION BY [DMC] ORDER BY [ProductionTime] DESC) AS rn_desc
    FROM [dbo].[Results]
    WHERE [Plant] = 'A' AND [ProductionTime] > DATEADD(DAY, -365, GETDATE())
),
FirstTests AS (
    -- 提取每个DMC的首次测试结果
    SELECT 
        [Plant], [Machine], [Material], [Batch], [DMC], [Result] AS FirstResult
    FROM FilteredData
    WHERE rn_asc = 1
),
LastTests AS (
    -- 提取每个DMC的末次测试结果
    SELECT 
        [Plant], [Machine], [Material], [Batch], [DMC], [Result] AS LastResult
    FROM FilteredData
    WHERE rn_desc = 1
)
-- 合并所有统计指标
SELECT 
    t.[Plant],
    t.[Machine],
    t.[Material],
    t.[Batch],
    t.[#Tests],
    t.[#Parts],
    t.[#OKTests],
    t.[#NOKTests],
    SUM(CASE WHEN lt.LastResult = 1 THEN 1 ELSE 0 END) AS '#OKParts',
    SUM(CASE WHEN lt.LastResult = 0 THEN 1 ELSE 0 END) AS '#NOKParts',
    SUM(CASE WHEN ft.FirstResult = 1 THEN 1 ELSE 0 END) AS '#PartsFirstTestOK',
    SUM(CASE WHEN ft.FirstResult = 0 THEN 1 ELSE 0 END) AS '#PartsFirstTestNOK'
FROM (
    -- 原统计逻辑
    SELECT 
        [Plant],
        [Machine],
        [Material],
        [Batch],
        Count([Result]) as '#Tests',
        Count(Distinct [DMC]) as '#Parts',
        COUNT(CASE when [Result] = 1 THEN 1 END) as '#OKTests',
        COUNT(CASE when [Result] = 0 THEN 1 END) as '#NOKTests'
    FROM FilteredData
    GROUP BY [Plant],[Material],[Batch],[Machine]
) t
LEFT JOIN FirstTests ft 
    ON t.[Plant] = ft.[Plant] 
    AND t.[Machine] = ft.[Machine] 
    AND t.[Material] = ft.[Material] 
    AND t.[Batch] = ft.[Batch]
LEFT JOIN LastTests lt 
    ON t.[Plant] = lt.[Plant] 
    AND t.[Machine] = lt.[Machine] 
    AND t.[Material] = lt.[Material] 
    AND t.[Batch] = lt.[Batch]
GROUP BY 
    t.[Plant], t.[Machine], t.[Material], t.[Batch],
    t.[#Tests], t.[#Parts], t.[#OKTests], t.[#NOKTests]
ORDER BY 
    t.[Plant], t.[Machine], t.[Material], t.[Batch];

关键说明 #

1. 效率优先：先通过FilteredData过滤出符合条件的数据集，后续所有操作都基于这个子集，避免全表扫描。
2. 精准标记：用ROW_NUMBER()窗口函数为每个DMC的测试记录排序，确保只取首次和末次各一条数据，避免重复统计。
3. 逻辑拆分：将首次、末次测试的提取与最终统计分开，结构清晰，新手易调试修改。

内容的提问来源于stack exchange，提问作者Felix W.