基于唯一ID分区的列过滤：事件表country_code字段生成规则优化及过滤实现问题

我来帮你搞定这个SQL优化的问题！先理清楚你的核心需求：按EVENT_ID分区，根据country和country_id的存在情况生成country_code，最后过滤掉country_code为NA的记录。

你的需求回顾 #

先明确规则，避免歧义：

• 同一EVENT_ID同时有country和country_id时，country_code取country_id的值，并且过滤掉country那条记录（因为它会被标记为NA）
• 只有country或country_id其中一个时，直接取对应值
• 两者都没有时，country_code为null，保留该记录

原有SQL的问题 #

你的现有SQL存在几个小问题：

1. 统计country%类型字段数量的写法不够严谨，count(user_properties_key like 'country%')虽然能运行，但逻辑上用sum(case...)更清晰直观
2. 缺少最后的过滤步骤，导致被country_id覆盖的country记录（即country_code=NA）会被保留，不符合你的预期结果
3. 大小写转换（UPPER）和你给出的预期结果不符（你预期是小写值），可以根据实际需求调整

优化后的SQL实现 #

这里用CTE（公共表表达式）拆分逻辑，让代码更易读和维护：

WITH event_country_flags AS (
    -- 第一步：按EVENT_ID分组，标记每个事件是否存在优先级更高的country_id
    SELECT 
        EVENT_ID,
        MAX(CASE WHEN USER_PROPERTIES_KEY = 'country_id' THEN 1 ELSE 0 END) AS has_country_id
    FROM events
    GROUP BY EVENT_ID
),
event_with_country_code AS (
    -- 第二步：为每条记录生成对应的country_code
    SELECT 
        e.EVENT_ID,
        e.USER_PROPERTIES_KEY,
        e.USER_PROPERTIES_VALUE,
        CASE 
            -- 优先级最高：如果是country_id，直接取值
            WHEN e.USER_PROPERTIES_KEY = 'country_id' THEN e.USER_PROPERTIES_VALUE
            -- 如果是country，仅当无country_id时才取值，否则标记为NA
            WHEN e.USER_PROPERTIES_KEY = 'country' THEN 
                CASE WHEN ec.has_country_id = 0 THEN e.USER_PROPERTIES_VALUE ELSE 'NA' END
            -- 非country相关字段，country_code设为null
            ELSE NULL
        END AS COUNTRY_CODE
    FROM events e
    JOIN event_country_flags ec ON e.EVENT_ID = ec.EVENT_ID
)
-- 第三步：过滤掉country_code为NA的记录
SELECT *
FROM event_with_country_code
WHERE COUNTRY_CODE != 'NA';

逻辑解释 #

1. event_country_flags CTE：
   按EVENT_ID分组，用MAX(CASE...)标记每个事件是否存在country_id。如果存在，has_country_id为1，否则为0。这一步是为后续判断country是否需要被覆盖提供依据。

2. event_with_country_code CTE：
   关联原表和标记表，为每条记录生成country_code：

   • 直接处理country_id记录，取它的value作为country_code
   • 处理country记录时，检查当前事件是否有country_id：没有的话取country的value，有的话标记为NA
   • 其他类型的字段（比如platform），country_code设为null

3. 最终过滤：
   把country_code为NA的记录过滤掉，也就是那些被country_id覆盖的country记录，得到你想要的预期结果。

调整说明 #

如果需要把country_code转成大写，只需要把e.USER_PROPERTIES_VALUE改成UPPER(e.USER_PROPERTIES_VALUE)即可，比如：

WHEN e.USER_PROPERTIES_KEY = 'country_id' THEN UPPER(e.USER_PROPERTIES_VALUE)

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