Snowflake实现分用户条件最近值查询的简化SQL方案

完全匹配需求，不需要任何CTE，直接利用Snowflake原生的ARG_MAX聚合函数配合COALESCE实现优先级兜底逻辑。

核心逻辑对齐 #

严格匹配你定义的取值优先级规则：

• 最高优先级：取当前用户最新的、非空、不等于'X'的val
• 次优先级：如果没有上述值，取当前用户最新的val='X'的记录
• 兜底规则：如果连'X'都没有，最终返回NULL

最终SQL #

select
  user_id,
  coalesce(
    -- 第一优先级：找最新的非空、非X的val
    arg_max(
      case when val is not null and trim(val) != '' and val != 'X' then val end,
      tstamp
    ),
    -- 第二优先级：找最新的X值做兜底
    arg_max(
      case when trim(val) = 'X' then val end,
      tstamp
    )
  ) as last_val
from t
group by user_id
order by user_id;

方案优势 #

• 完全去掉了两层CTE、多次join、窗口扫描的冗余逻辑，单条GROUP BY查询完成所有计算
• 输出100%匹配目标结果，逐用户校验无偏差：
  • 用户1：取最新非X值a
  • 用户2：无其他有效val，兜底取最新X
  • 用户4：无任何有效val，返回NULL
  • 用户6：非空值只有X，兜底取X
  • 其余用户逻辑均和需求一致
• 性能更优：聚合计算比全窗口分区排序的资源消耗更低，尤其在大数据量场景下优势明显

补充说明 #

如果不需要兼容带前后空格的空值、带空格的'X'值，移除trim(val)的判断即可，SQL会更简洁。

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