Redshift使用ROW_NUMBER()查询时如何保留原有行顺序

• 关系型数据库的表本质是无序集合，不存在"固有行顺序"的概念。你在CTE中用UNION拼接写入的行，Redshift不会按照你书写的顺序存储或返回，分布式架构下的并行扫描、分片存储机制都会导致行返回顺序随机。
• ROW_NUMBER() OVER ()窗口函数如果不指定ORDER BY子句，数据库不会对窗口内的行做任何排序承诺，编号分配完全取决于执行时的行扫描顺序，结果不稳定。你在CTE或外层查询写的ORDER BY row_number是在编号生成完成后才执行的排序，无法反过来影响编号的分配逻辑，自然得不到和rowid对齐的结果。

要让生成的序号和rowid按预期一一对应，必须在窗口函数的OVER子句中明确指定排序规则，不要依赖不存在的"默认顺序"。
修正后的SQL代码如下：

with 
    testset (rowid) as (
    select '1' union
    select '2' union
    select '3' union
    select '4' union
    select '5' union
    select '6' union
    select '7' union
    select '8' union
    select '9' union
    select '10'
)
select
   row_number() over (order by cast(rowid as integer)) as row_number,
   rowid
from testset
order by row_number

补充说明 #

• 示例中对rowid做整数类型转换，是因为测试集里的rowid是字符串类型，直接按字符串排序会出现'10'排在'2'前面的字典序问题，转成数值后排序才符合自然数顺序预期。
• 所有场景下使用ROW_NUMBER、RANK等排名类窗口函数时，只要对编号顺序有明确要求，就必须在OVER()内写明ORDER BY规则，留空的写法在所有主流SQL引擎中都无法保证结果稳定可复现。

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