基于Microsoft SQL Server提取PanelSubID首次实例的SQL统计查询需求

需要针对SQL Server数据库中的PanelSubID，提取其首次出现的实例并进行基础统计分析。现有两种尝试：

• 第一种查询会汇总该ID的所有实例数据，无法仅基于首次实例计算；
• 第二种CTE查询能得到单个PanelSubID的正确结果，但无法批量处理所有ID。

原错误查询代码 #

WITH FirstInstance AS (
    SELECT PanelSubID, 
           MIN(column1) AS Min_Column1,
           MAX(column1) AS Max_Column1,
           MIN(column2) AS Min_Column2,
           MAX(column2) AS Max_Column2,
           COUNT(column1) AS Count_Column1,
           COUNT(column2) AS Count_Column2,
           SUM(CASE WHEN column1 = 0 THEN 1 ELSE 0 END) AS Count_Zero_Column1,
           SUM(CASE WHEN column2 = 0 THEN 1 ELSE 0 END) AS Count_Zero_Column2
    FROM myTable
    WHERE BatchID = 'testBatch'
    GROUP BY PanelSubID
)
SELECT UP.PanelSubID,
       FI.Min_Column1,
       FI.Max_Column1,
       FI.Min_Column2,
       FI.Max_Column2,
       FI.Count_Column1,
       FI.Count_Column2,
       FI.Count_Zero_Column1,
       FI.Count_Zero_Column2
FROM (
    SELECT DISTINCT PanelSubID
    FROM myTable
    WHERE BatchID = 'testBatch'
) UP
JOIN FirstInstance FI ON UP.PanelSubID = FI.PanelSubID
GROUP BY UP.PanelSubID
ORDER BY UP.PanelSubID;

问题：该查询直接按PanelSubID分组汇总所有关联数据，未筛选首次实例，不符合需求。

原单次处理正确代码 #

WITH CTE AS(
 SELECT *
 , RN = ROW_NUMBER()OVER(PARTITION BY someColumn ORDER BY PanelSubID)
 FROM myTable
 WHERE BatchID = 'testBatch'
 AND PanelSubID = '1234'
)
Select
       MIN(column1) AS Min_Column1,
       MAX(column1) AS Max_Column1,
       MIN(column2) AS Min_Column2,
       MAX(column2) AS Max_Column2,
       COUNT(column1) AS Count_Column1,
       COUNT(column2) AS Count_Column2,
       SUM(CASE WHEN column1 = 0 THEN 1 ELSE 0 END) AS Count_Zero_Column1,
       SUM(CASE WHEN column2 = 0 THEN 1 ELSE 0 END) AS Count_Zero_Column2
FROM CTE
WHERE RN = 1

问题：添加了PanelSubID = '1234'的过滤条件，仅能处理单个ID；且PARTITION BY someColumn应为PARTITION BY PanelSubID，否则无法按目标ID分组编号。

批量处理的正确SQL写法 #

WITH PanelFirstInstances AS (
    SELECT 
        PanelSubID,
        column1,
        column2,
        -- 按PanelSubID分区，按你定义的"首次"规则排序（比如插入时间、自增ID等）
        RN = ROW_NUMBER() OVER(PARTITION BY PanelSubID ORDER BY CreateTime ASC) -- 替换CreateTime为实际排序字段
    FROM myTable
    WHERE BatchID = 'testBatch'
)
SELECT 
    PanelSubID,
    MIN(column1) AS Min_Column1,
    MAX(column1) AS Max_Column1,
    MIN(column2) AS Min_Column2,
    MAX(column2) AS Max_Column2,
    COUNT(column1) AS Count_Column1,
    COUNT(column2) AS Count_Column2,
    SUM(CASE WHEN column1 = 0 THEN 1 ELSE 0 END) AS Count_Zero_Column1,
    SUM(CASE WHEN column2 = 0 THEN 1 ELSE 0 END) AS Count_Zero_Column2
FROM PanelFirstInstances
WHERE RN = 1 -- 仅保留每个PanelSubID的首次实例
GROUP BY PanelSubID
ORDER BY PanelSubID;

关键改进说明 #

1. 分区与编号：通过PARTITION BY PanelSubID对每个ID的记录分组，使用ROW_NUMBER()按指定顺序（如创建时间）为每组记录编号，RN=1即为该ID的首次实例。
2. 批量筛选：移除单个PanelSubID的过滤条件，自动处理所有符合BatchID='testBatch'的ID。
3. 统计逻辑：基于筛选后的首次实例数据进行统计，确保结果仅来自每个ID的第一条记录。

内容的提问来源于stack exchange，提问作者AaronS