Redshift中如何无需回表为全局最大Version打标识

问题背景 #

需要在Amazon Redshift中无需回表关联，给version列的全局最大值所在行设置flag=1，其余行设为0，但现有SQL结果不符合预期。

错误SQL代码 #

select e.id, e.version, e.version_type, q.status,
(case when e.version  = max(e.version) over (partition by e.id) then 1 else 0 end) as flag
from data.deployment_events e
join data.deployments d  on   e.id = d.id

当前执行结果 #

期望结果 #

问题原因 #

原SQL中max(e.version) over (partition by e.id)是按id分组取每个id对应的最大version，而非全局最大version，导致每个id下等于自身最大version的行都被标记为1，不符合需求。

正确解法 #

去掉窗口函数的partition by e.id，直接计算全局最大version：

select e.id, e.version, e.version_type, d.status,
       case when e.version = max(e.version) over () then 1 else 0 end as flag
from data.deployment_events e
join data.deployments d on e.id = d.id

说明 #

• max(e.version) over ()会计算整个结果集的全局最大version，无需分组
• 仅version等于全局最大值的行被标记为1，其余为0，完全匹配需求
• 保持了无需回表关联的要求，通过窗口函数一次计算完成

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