Snowflake SQL：如何定义不输出列并实现指定列输出与姓名匹配

需求与原SQL #

需求：仅需输出COUNT(DISTINCT PATIENT_ID)、Year、IN or Out这三列；需通过FIRST_NAME与LAST_NAME拼接成的名称匹配指定字符串，匹配则标记为IN，不匹配标记为OUT。

原SQL代码：

SELECT 
COUNT(DISTINCT PATIENT_ID),
FIRST_NAME,
LAST_NAME,
CONCAT(FIRST_NAME,' ', LAST_NAME) as Name,
(CASE WHEN (
    (Name LIKE '%First Name1%')
 OR (Name LIKE '%First Name2%')
 OR (Name LIKE '%First Name3%')
 OR (Name LIKE '%First Name4%')
) 
    
    THEN 'IN'
       ELSE 'OUT' END) as "IN or Out",
YEAR(SERVICE_DATE) Year
        
from "dataset" dx


WHERE 
 (dx.primary_specialty_desc like ('%GASTRO%')
 AND    dx.diagnosis_code in ('K7581' , 'K740' , 'K760' , 'K746') 
 AND    dx.SERVICE_DATE >= '2017-01-01') 
 or    ( dx.primary_specialty_desc like('%HEP%')
 AND    dx.diagnosis_code in ('K7581' , 'K740' , 'K760' , 'K746') 
 AND    dx.SERVICE_DATE >= '2017-01-01')
 
 GROUP BY
  Year,
  FIRST_NAME,
  LAST_NAME,
 SERVICE_DATE,
 (CASE WHEN (
    (Name LIKE '%First Name1%')
 OR (Name LIKE '%First Name2%')
 OR (Name LIKE '%First Name3%')
 OR (Name LIKE '%First Name4%')
 
) 
    
    THEN 'IN'
       ELSE 'OUT' END)

问题：如何调整SQL，使定义的Name等列不输出，仅保留目标列？

调整后的SQL #

SELECT 
    COUNT(DISTINCT PATIENT_ID),
    YEAR(SERVICE_DATE) AS Year,
    (CASE WHEN CONCAT(FIRST_NAME,' ', LAST_NAME) LIKE '%First Name1%'
            OR CONCAT(FIRST_NAME,' ', LAST_NAME) LIKE '%First Name2%'
            OR CONCAT(FIRST_NAME,' ', LAST_NAME) LIKE '%First Name3%'
            OR CONCAT(FIRST_NAME,' ', LAST_NAME) LIKE '%First Name4%'
          THEN 'IN'
          ELSE 'OUT' END) AS "IN or Out"
FROM "dataset" dx
WHERE 
    (dx.primary_specialty_desc LIKE '%GASTRO%'
     AND dx.diagnosis_code IN ('K7581' , 'K740' , 'K760' , 'K746') 
     AND dx.SERVICE_DATE >= '2017-01-01') 
    OR 
    (dx.primary_specialty_desc LIKE '%HEP%'
     AND dx.diagnosis_code IN ('K7581' , 'K740' , 'K760' , 'K746') 
     AND dx.SERVICE_DATE >= '2017-01-01')
GROUP BY
    YEAR(SERVICE_DATE),
    (CASE WHEN CONCAT(FIRST_NAME,' ', LAST_NAME) LIKE '%First Name1%'
            OR CONCAT(FIRST_NAME,' ', LAST_NAME) LIKE '%First Name2%'
            OR CONCAT(FIRST_NAME,' ', LAST_NAME) LIKE '%First Name3%'
            OR CONCAT(FIRST_NAME,' ', LAST_NAME) LIKE '%First Name4%'
          THEN 'IN'
          ELSE 'OUT' END)

关键调整点 #

• 移除冗余输出列：直接删除SELECT语句中的FIRST_NAME、LAST_NAME、Name列，只保留需要的三列。
• 替换别名引用：因为不再定义Name别名，把CASE里对Name的调用换成直接拼接FIRST_NAME和LAST_NAME的表达式。
• 精简GROUP BY子句：去掉GROUP BY里的FIRST_NAME、LAST_NAME、SERVICE_DATE，只保留SELECT中未被聚合的列（Year对应的YEAR(SERVICE_DATE)和CASE表达式），符合Snowflake的分组规则。

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