在SSMS/ADF中实现COUNTIF等效功能与患者出院状态分类

一、计算既往入院次数（Admissions Prior）与后续入院次数（Admissions Ahead） #

SQL窗口函数完全可以高效实现需求，无需依赖ADF。假设你的患者记录表包含PatientID（患者ID）、AdmissionDate（入院日期）、LOC（护理级别）等核心字段，具体实现如下：

1. 计算既往入院次数 #

通过COUNT()窗口函数按患者分组，统计当前记录之前的入院次数：

SELECT
    PatientID,
    AdmissionDate,
    LOC,
    -- 统计当前患者本次入院前的所有入院记录数
    COUNT(*) OVER (
        PARTITION BY PatientID
        ORDER BY AdmissionDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS AdmissionsPrior
FROM YourPatientTable;

• PARTITION BY PatientID：限定仅统计同一患者的记录
• ORDER BY AdmissionDate：按入院日期排序，明确记录先后顺序
• ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING：仅计算当前行之前的所有记录，排除当前行本身

2. 计算后续入院次数 #

用反向窗口范围统计当前记录之后的入院次数：

SELECT
    PatientID,
    AdmissionDate,
    LOC,
    COUNT(*) OVER (
        PARTITION BY PatientID
        ORDER BY AdmissionDate
        ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
    ) AS AdmissionsAhead
FROM YourPatientTable;

合并两个计算 #

可将两个窗口函数整合到同一查询，一次性获取结果：

SELECT
    PatientID,
    AdmissionDate,
    LOC,
    COUNT(*) OVER (
        PARTITION BY PatientID
        ORDER BY AdmissionDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS AdmissionsPrior,
    COUNT(*) OVER (
        PARTITION BY PatientID
        ORDER BY AdmissionDate
        ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
    ) AS AdmissionsAhead
FROM YourPatientTable;

二、出院状态分类（Discharge Status Typing） #

结合AdmissionsAhead与下一次入院的LOC值，通过LEAD()窗口函数获取后续入院的护理级别，再用CASE表达式实现分类逻辑：

完整查询语句 #

WITH PatientAdmissions AS (
    SELECT
        PatientID,
        AdmissionDate,
        LOC AS LOC_Old,
        COUNT(*) OVER (
            PARTITION BY PatientID
            ORDER BY AdmissionDate
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS AdmissionsPrior,
        COUNT(*) OVER (
            PARTITION BY PatientID
            ORDER BY AdmissionDate
            ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
        ) AS AdmissionsAhead,
        -- 获取当前患者下一次入院的LOC值
        LEAD(LOC) OVER (
            PARTITION BY PatientID
            ORDER BY AdmissionDate
        ) AS LOC_New
    FROM YourPatientTable
)
SELECT
    PatientID,
    AdmissionDate,
    LOC_Old,
    AdmissionsPrior,
    AdmissionsAhead,
    LOC_New,
    CASE
        WHEN AdmissionsAhead = 0 THEN 'True discharge'
        WHEN AdmissionsAhead = 1 AND LOC_New < LOC_Old THEN 'Stepdown'
        WHEN AdmissionsAhead = 1 AND LOC_New = LOC_Old THEN 'Lateral Transfer'
        WHEN AdmissionsAhead = 1 AND LOC_New > LOC_Old THEN 'Step-Up'
        -- 可根据实际需求补充AdmissionsAhead>1的分类规则
        ELSE 'Other'
    END AS DischargeStatus
FROM PatientAdmissions;

关键说明 #

• LEAD(LOC)：获取当前患者下一次入院的护理级别，用于和本次入院的LOC做对比
• CASE表达式严格遵循给定规则判断出院状态，预留了AdmissionsAhead>1的扩展空间

关于ADF的补充 #

若需批量调度或自动化执行任务，ADF可作为调度层调用上述SQL脚本，但核心统计与分类逻辑仅用SSMS的SQL窗口函数即可高效完成，无需额外依赖ADF。

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