如何通过单条SQL查询实现带条件去重计数及Severe类细分统计

当然可以用单条SQL完成所有统计需求，核心思路是用CASE语句给每条记录打上对应的分类标签，再通过分组统计去重患者数。以下是两种常见场景的实现方案：

方案1：统计所有符合条件的记录对应的患者（同一患者可能出现在多个子分类） #

如果只要患者有符合某分类/子分类的记录，就将其计入该分类的统计，可使用如下SQL：

SELECT
  patient_category,
  COUNT(DISTINCT SOURCE_PATIENT_ID) AS unique_patient_count
FROM (
  SELECT
    SOURCE_PATIENT_ID,
    CASE
      -- 处理基础分类
      WHEN RESULT_CATEGORY = 'Normal' THEN 'Normal'
      WHEN RESULT_CATEGORY = 'Mild' THEN 'Mild'
      WHEN RESULT_CATEGORY = 'Moderate' THEN 'Moderate'
      -- 处理Severe子分类，同时过滤不符合单位的记录
      WHEN RESULT_CATEGORY = 'Severe' AND RESULT_UNIT <> 'MG/DL' THEN
        CASE
          WHEN CAST(RESULT_VALUE_STANDARDIZED AS DECIMAL) BETWEEN 500 AND 599 THEN 'Severe_500_to_599'
          WHEN CAST(RESULT_VALUE_STANDARDIZED AS DECIMAL) BETWEEN 600 AND 699 THEN 'Severe_600_to_699'
          WHEN CAST(RESULT_VALUE_STANDARDIZED AS DECIMAL) BETWEEN 700 AND 799 THEN 'Severe_700_to_799'
          WHEN CAST(RESULT_VALUE_STANDARDIZED AS DECIMAL) >= 800 THEN 'Severe_>800'
          ELSE NULL -- 不在指定区间的Severe记录不统计
        END
      ELSE NULL -- 其他未定义分类的记录不统计
    END AS patient_category
  FROM Table1
) AS categorized_data
WHERE patient_category IS NOT NULL -- 过滤掉未分类的记录
GROUP BY patient_category
ORDER BY patient_category;

方案2：每个患者仅归属到最高级的子分类（避免重复统计） #

如果同一个患者有多个Severe区间的记录，希望仅将其计入结果值最大的子分类，可先按患者分组取最大结果值，再进行分类：

WITH patient_max_results AS (
  SELECT
    SOURCE_PATIENT_ID,
    RESULT_CATEGORY,
    MAX(CAST(RESULT_VALUE_STANDARDIZED AS DECIMAL)) AS max_result_value,
    RESULT_UNIT
  FROM Table1
  WHERE RESULT_UNIT <> 'MG/DL' -- 统一过滤不符合单位的记录
  GROUP BY SOURCE_PATIENT_ID, RESULT_CATEGORY, RESULT_UNIT
)
SELECT
  patient_category,
  COUNT(DISTINCT SOURCE_PATIENT_ID) AS unique_patient_count
FROM (
  SELECT
    SOURCE_PATIENT_ID,
    CASE
      WHEN RESULT_CATEGORY = 'Normal' THEN 'Normal'
      WHEN RESULT_CATEGORY = 'Mild' THEN 'Mild'
      WHEN RESULT_CATEGORY = 'Moderate' THEN 'Moderate'
      WHEN RESULT_CATEGORY = 'Severe' THEN
        CASE
          WHEN max_result_value BETWEEN 500 AND 599 THEN 'Severe_500_to_599'
          WHEN max_result_value BETWEEN 600 AND 699 THEN 'Severe_600_to_699'
          WHEN max_result_value BETWEEN 700 AND 799 THEN 'Severe_700_to_799'
          WHEN max_result_value >= 800 THEN 'Severe_>800'
          ELSE NULL
        END
      ELSE NULL
    END AS patient_category
  FROM patient_max_results
) AS categorized_data
WHERE patient_category IS NOT NULL
GROUP BY patient_category
ORDER BY patient_category;

注意事项 #

• 若RESULT_VALUE_STANDARDIZED存在非数字格式的内容，可改用TRY_CAST（支持SQL Server、PostgreSQL等）替代CAST，避免转换报错，不符合的记录会被归为NULL从而不参与统计。
• 可根据实际需求调整WHERE子句的过滤条件，比如如果所有分类都需要排除MG/DL单位，可将过滤条件移到最外层的FROM子句中。

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