将PostgreSQL含ORDER BY的移动SUM窗口函数迁移至Redshift遇帧子句问题

要还原PostgreSQL中的累计计算逻辑，你需要明确指定RANGE帧而非ROWS帧，因为PostgreSQL在窗口函数带有ORDER BY时的默认行为是使用RANGE UNBOUNDED PRECEDING AND CURRENT ROW，而Redshift要求显式声明帧子句。

修改后的Redshift代码 #

select *
from
(   select distinct
      a.id
      , sum(case when a.is_batch_empty then 1 else 0 end) 
           over (partition by a.client_id order by a.id 
                 range between unbounded preceding and current row) as empty_count
    from my_temp_table a
) a
where a.id = 111

为什么之前的尝试无效 #

1. 去掉ORDER BY：此时sum会计算整个client_id分组内的总空批次数量，而非按id顺序的累计值，完全偏离原逻辑。
2. 使用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：ROWS帧是基于物理行位置的累计，若my_temp_table中存在同一id的多行数据，它只会累计到当前物理行，而PostgreSQL默认的RANGE帧会包含所有id值小于等于当前行id的行，两者结果会有差异。
3. 使用ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：这会计算整个分组的总和，和去掉ORDER BY的效果一致，并非按id的累计计算。

额外优化（可选） #

如果id在my_temp_table中是唯一值，子查询中的SELECT DISTINCT可以省略，因为每个id只会对应一行数据，代码会更高效：

select id, empty_count
from
(   select
      a.id
      , sum(case when a.is_batch_empty then 1 else 0 end) 
           over (partition by a.client_id order by a.id 
                 range between unbounded preceding and current row) as empty_count
    from my_temp_table a
) a
where a.id = 111

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