SQL中检测'N'标识时生成递增唯一计数的实现方案

问题描述 #

现有数据表包含ID和prev_appt_flag（取值为'Y'或'N'）两列，原始数据如下：

需求是生成final_goal列：仅当prev_appt_flag为'N'时计数递增，其余行沿用当前计数，预期结果如下：

解决方案 #

使用窗口函数SUM()结合CASE语句即可实现，SQL代码如下：

SELECT
    ID,
    prev_appt_flag,
    SUM(CASE WHEN prev_appt_flag = 'N' THEN 1 ELSE 0 END) OVER (ORDER BY ID) AS final_goal
FROM
    your_table_name;

逻辑说明 #

1. CASE WHEN prev_appt_flag = 'N' THEN 1 ELSE 0 END：将每一行的标识转换为1（当为'N'时）或0（当为'Y'时）
2. SUM(...) OVER (ORDER BY ID)：按ID的顺序累计求和，每遇到一个'N'就累加1，'Y'则保持当前累计值，正好符合需求的计数规则

该方案兼容MySQL 8.0+、PostgreSQL、SQL Server、Oracle等支持窗口函数的主流数据库。

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