如何采用多种方式编写指定的SQL统计查询语句

原查询核心需求：统计属于指定队列（cohort_id = UUID_TO_BIN_F("38f58f93-b80d-4a54")）且在patient_score表中存在service_date_end = "2021-07-31"记录（按患者+日期去重后）的患者数量。以下是几种等价实现方式：

写法1：优化子查询（替换冗余字段） #

原查询子查询用SELECT *效率偏低，改为只保留关联所需字段：

SELECT COUNT(*) AS patient_count
FROM (
    SELECT patient_id, service_date_end
    FROM patient_score
    GROUP BY patient_id, service_date_end
) AS p
JOIN map_cohort_patient AS m 
    ON p.patient_id = m.patient_id
WHERE m.cohort_id = UUID_TO_BIN_F("38f58f93-b80d-4a54")
  AND p.service_date_end = "2021-07-31"

写法2：用DISTINCT替代GROUP BY去重 #

若仅需去重患者+日期组合，DISTINCT逻辑更直观，且可简化查询结构：

SELECT COUNT(DISTINCT p.patient_id) AS patient_count
FROM patient_score AS p
JOIN map_cohort_patient AS m 
    ON p.patient_id = m.patient_id
WHERE m.cohort_id = UUID_TO_BIN_F("38f58f93-b80d-4a54")
  AND p.service_date_end = "2021-07-31"

注：通过WHERE已筛选目标日期，直接统计DISTINCT patient_id等价于原查询的去重后关联统计。

写法3：使用EXISTS子查询（避免冗余关联） #

当关联表数据量较大时，EXISTS仅做存在性判断，性能更优：

SELECT COUNT(*) AS patient_count
FROM map_cohort_patient AS m
WHERE m.cohort_id = UUID_TO_BIN_F("38f58f93-b80d-4a54")
  AND EXISTS (
      SELECT 1
      FROM patient_score AS p
      WHERE p.patient_id = m.patient_id
        AND p.service_date_end = "2021-07-31"
  )

子查询无需GROUP BY，只要存在至少一条符合日期的记录即可满足条件。

写法4：用IN子查询筛选患者ID #

通过子查询提前获取符合日期条件的患者ID，再匹配队列：

SELECT COUNT(*) AS patient_count
FROM map_cohort_patient AS m
WHERE m.cohort_id = UUID_TO_BIN_F("38f58f93-b80d-4a54")
  AND m.patient_id IN (
      SELECT DISTINCT patient_id
      FROM patient_score
      WHERE service_date_end = "2021-07-31"
  )

写法5：窗口函数（适配扩展统计场景） #

若后续需新增聚合维度，窗口函数可同时完成去重与筛选：

SELECT COUNT(DISTINCT patient_id) AS patient_count
FROM (
    SELECT patient_id,
           ROW_NUMBER() OVER (PARTITION BY patient_id, service_date_end ORDER BY (SELECT NULL)) AS rn
    FROM patient_score
    WHERE service_date_end = "2021-07-31"
) AS p
JOIN map_cohort_patient AS m 
    ON p.patient_id = m.patient_id
WHERE m.cohort_id = UUID_TO_BIN_F("38f58f93-b80d-4a54")
  AND p.rn = 1

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