Snowflake窗口函数：同步修复关联列的最后有效值问题

需求说明 #

当NETWORK_NAME为特定值（如'FALLBACK_CASE'）时，需通过窗口函数获取**最近的有效NETWORK_NAME**生成NETWORK_NAME_FIX，同时同步获取对应有效行的CAMPAIGN_NAME、CREATIVE_NAME、ADGROUP_NAME三列值完成修复。

原代码问题 #

原SQL中CAMPAIGN_NAME_FIX的写法无法实现需求，因为无法关联到NETWORK_NAME_FIX对应的有效行，取不到匹配的有效CAMPAIGN_NAME值。原代码如下：

SELECT coalesce(NULLIF(NETWORK_NAME,'FALLBACK_CASE'), LAG(NULLIF(NETWORK_NAME,'FALLBACK_CASE')) IGNORE NULLS OVER (partition by adid order by created_at asc)) NETWORK_NAME_FIX,
NETWORK_NAME,
CASE WHEN NETWORK_NAME_FIX != NETWORK_NAME THEN LAST_VALUE(CAMPAIGN_NAME) OVER (partition by adid, network_name_fix order by created_at asc) ELSE CAMPAIGN_NAME END CAMPAIGN_NAME_FIX,
CAMPAIGN_NAME, CREATIVE_NAME, ADGROUP_NAME, MATCH_TYPE,
CREATED_AT, DATE_MONTH, DATE_WEEK
FROM b 

期望输出示例（以network_name≠'x'为有效条件） #

解决方案SQL #

通过生成分组ID，将连续的无效行与最近的有效行归为同一组，再用FIRST_VALUE提取组内有效行的字段值：

WITH grouped_data AS (
  SELECT 
    *,
    -- 生成分组ID：每遇到有效NETWORK_NAME就递增，后续无效行归入同一组
    COUNT(CASE WHEN NETWORK_NAME != 'FALLBACK_CASE' THEN 1 END) OVER (PARTITION BY adid ORDER BY created_at ASC) AS group_id
  FROM b
)
SELECT 
  -- 提取分组内第一个有效NETWORK_NAME作为修复值
  FIRST_VALUE(CASE WHEN NETWORK_NAME != 'FALLBACK_CASE' THEN NETWORK_NAME END) OVER (PARTITION BY adid, group_id ORDER BY created_at ASC) AS NETWORK_NAME_FIX,
  NETWORK_NAME,
  -- 提取分组内第一个有效CAMPAIGN_NAME作为修复值
  FIRST_VALUE(CASE WHEN NETWORK_NAME != 'FALLBACK_CASE' THEN CAMPAIGN_NAME END) OVER (PARTITION BY adid, group_id ORDER BY created_at ASC) AS CAMPAIGN_NAME_FIX,
  -- 同理修复另外两列
  FIRST_VALUE(CASE WHEN NETWORK_NAME != 'FALLBACK_CASE' THEN CREATIVE_NAME END) OVER (PARTITION BY adid, group_id ORDER BY created_at ASC) AS CREATIVE_NAME_FIX,
  FIRST_VALUE(CASE WHEN NETWORK_NAME != 'FALLBACK_CASE' THEN ADGROUP_NAME END) OVER (PARTITION BY adid, group_id ORDER BY created_at ASC) AS ADGROUP_NAME_FIX,
  CAMPAIGN_NAME,
  CREATIVE_NAME,
  ADGROUP_NAME,
  MATCH_TYPE,
  CREATED_AT,
  DATE_MONTH,
  DATE_WEEK
FROM grouped_data
ORDER BY adid, created_at ASC;

逻辑说明 #

1. 分组ID生成：用COUNT窗口函数，每遇到有效NETWORK_NAME（非'FALLBACK_CASE'）就计数+1，让连续无效行和最近的有效行共享同一个group_id。
2. 提取修复值：通过FIRST_VALUE在每个分组内提取第一个有效行的对应字段，实现“获取最近有效行对应值”的需求。

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