如何为SQL表推导IND列？附需求示例及尝试代码

需求说明 #

需要为SQL表推导IND列，以下是示例参考表（实际场景中无IND列）：

ID.     ACCT_ID     GEO     IND     Timestamp
abcd    XEMXOAA5    IN      Y       2023-05-04 10:08:51.000

efgh    XEPPKAAX    IN      Y       2023-05-04 10:08:51.000
ijkl    XEPPKAAX    CA      N       2023-05-04 10:08:51.000
mnop    XEPPKAAX    ALAC    N       2023-05-04 10:08:51.000

qrst    XEMXOAB5    CA      Y       2023-05-04 10:08:51.000

uvwx    XEMXOAD7    EMEIA   Y       2023-05-04 10:10:43.000
yzab    XEMXOAD7    APAC    N       2023-05-04 10:08:51.000

IND列推导规则 #

• 当GEO为IN时，IND取值Y；
• 同一ACCT_ID下存在不同GEO时，IN对应的IND为Y，其余为N；
• 若无IN条目时，IND取值Y；
• 若无IN条目且同一ACCT_ID有多条记录时，最新Timestamp的记录IND为Y，其余为N。

错误代码分析 #

你尝试的代码存在语法错误和逻辑顺序问题：

select
ID,
ACCT_ID,
GEO,
case when geo = 'IN' then 'Y' 
            when geo <> 'IN' then 'N'
            when geo not in ('IN')  row_number() over(partition by acct_id order by Timestamp desc) = 1 then 'Y'
            else 'N'
END AS IND,
Timestamp
from test_table

问题点：

1. 语法错误：第三个WHEN条件缺少逻辑运算符，geo not in ('IN')与row_number()...=1之间需添加AND；
2. 逻辑顺序错误：第二个WHEN geo <> 'IN' then 'N'会直接拦截所有非IN记录，导致后续判断无法执行；
3. 未统计同一ACCT_ID下是否存在IN记录的全局状态，无法覆盖规则2和规则4的场景。

正确实现方案 #

我们需要先通过窗口函数计算两个关键辅助值，再进行IND列的判断：

WITH base_data AS (
    SELECT 
        ID,
        ACCT_ID,
        GEO,
        Timestamp,
        -- 标记当前ACCT_ID是否存在IN的记录
        MAX(CASE WHEN GEO = 'IN' THEN 1 ELSE 0 END) OVER (PARTITION BY ACCT_ID) AS has_in,
        -- 按时间倒序生成行号，用于无IN时取最新记录
        ROW_NUMBER() OVER (PARTITION BY ACCT_ID ORDER BY Timestamp DESC) AS rn
    FROM test_table
)
SELECT 
    ID,
    ACCT_ID,
    GEO,
    CASE
        -- 规则1：GEO为IN时直接返回Y
        WHEN GEO = 'IN' THEN 'Y'
        -- 规则2：存在IN记录且当前不是IN，返回N
        WHEN has_in = 1 THEN 'N'
        -- 规则3&4：无IN记录时，最新的返回Y，其余返回N
        WHEN rn = 1 THEN 'Y'
        ELSE 'N'
    END AS IND,
    Timestamp
FROM base_data
ORDER BY ACCT_ID, Timestamp DESC;

逻辑解释 #

1. CTE预处理：先计算每个ACCT_ID的has_in（是否包含IN记录）和rn（时间倒序的行号），为后续判断提供基础数据；
2. CASE判断顺序：
   • 优先匹配GEO='IN'的场景，直接返回Y；
   • 如果当前ACCT_ID存在IN记录，所有非IN记录返回N；
   • 无IN记录时，行号为1的最新记录返回Y，其余返回N，同时覆盖单条记录的场景（此时rn=1，直接返回Y）。

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