MS SQL查询优化：排除-1值的季度打印机页数最值统计

当前需从MS SQL数据库按季度统计打印机的总页数、单色页数、彩色页数的首尾有效值，但原查询在遇到打印机返回的无效值-1时，会错误将-1统计为最小值，导致结果异常。

原SQL查询 #

SELECT 
  p_printerName AS Printer
  , DATEPART(q,  pmh_pollDate) AS Quartal
  , DATEPART(yy, pmh_pollDate) AS Year
  , MIN(pmh_pageCount) AS first
  , MAX(pmh_pageCount) AS last
  , MIN(pmh_pageCountMono) AS first_Mono
  , MAX(pmh_pageCountMono) AS last_Mono
  , MIN(pmh_pageCountColor) AS first_Color
  , MAX(pmh_pageCountColor) AS last_Color
FROM [group] INNER JOIN printerGroup ON g_groupID = pg_groupID 
             INNER JOIN printer ON pg_printerID = p_printerID 
             INNER JOIN printerMeterHistory ON p_printerID = pmh_printerID
WHERE
  (p_printerName LIKE 'WBMP015')   
GROUP BY 
  g_groupName
  , p_printerName
  , DATEPART(yy, pmh_pollDate)
  , DATEPART(q, pmh_pollDate)

问题场景 #

当打印机数据中出现无效值-1时（如下例），原查询会将-1当作单色页数的最小值，导致统计结果错误：

p_printerName   pmh_pollDate    pmh_pageCount   pmh_pageCountMono   pmh_pageCountColor
printer1    20.10.2022 12:32    12276   -1  4450

修改后的SQL查询 #

SELECT 
  p_printerName AS Printer
  , DATEPART(q, pmh_pollDate) AS Quartal
  , DATEPART(yy, pmh_pollDate) AS Year
  -- 总页数：若存在-1可添加CASE过滤，当前假设总页数无无效值
  , MIN(pmh_pageCount) AS first
  , MAX(pmh_pageCount) AS last
  -- 单色页数：排除-1后统计首尾有效数值
  , MIN(CASE WHEN pmh_pageCountMono != -1 THEN pmh_pageCountMono END) AS first_Mono
  , MAX(CASE WHEN pmh_pageCountMono != -1 THEN pmh_pageCountMono END) AS last_Mono
  -- 彩色页数：同理排除-1后统计
  , MIN(CASE WHEN pmh_pageCountColor != -1 THEN pmh_pageCountColor END) AS first_Color
  , MAX(CASE WHEN pmh_pageCountColor != -1 THEN pmh_pageCountColor END) AS last_Color
FROM [group] 
INNER JOIN printerGroup ON g_groupID = pg_groupID 
INNER JOIN printer ON pg_printerID = p_printerID 
INNER JOIN printerMeterHistory ON p_printerID = pmh_printerID
WHERE
  p_printerName LIKE 'WBMP015'   
GROUP BY 
  g_groupName
  , p_printerName
  , DATEPART(yy, pmh_pollDate)
  , DATEPART(q, pmh_pollDate)

逻辑说明 #

• 通过CASE WHEN在聚合函数内部过滤掉-1值，让MIN和MAX仅计算有效数据
• 若总页数也可能出现-1，可对pmh_pageCount添加相同的CASE过滤逻辑
• 保留原查询的分组关联逻辑，仅针对无效值做针对性排除，不影响正常数据统计

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