使用row_number()查询最新日期结果不稳定问题求助

我尝试用以下SQL查询id=1的最新日期数据：

select id, amount, yearmonthday
from ( 
select 
id,
amount,
yearmonthday,
row_number() OVER (PARTITION BY id ORDER BY try(date_parse(yearmonthday, '%Y-%m-%d'))) as row_num 
from "db_raw"."table" ) ranked
where row_num = 1 and id = 1

但每次执行返回的结果都不一致：
第一次得到预期的最新数据：

再次执行却拿到更早的数据：

问题原因 #

1. 日期解析失败：你的yearmonthday是YYYYMMDD格式（无横杠），但用了%Y-%m-%d的解析格式，导致date_parse执行失败，try函数返回null。当排序字段全为null时，数据库无法生成稳定的排序顺序，row_number()会随机分配序号，结果自然不稳定。
2. 缺少降序排序：即使解析成功，默认的升序排序会让最早的日期排在前面，row_num=1拿到的是旧数据，而非最新数据。

修复方案 #

方案1：修正日期解析格式并添加降序排序 #

select id, amount, yearmonthday
from ( 
select 
id,
amount,
yearmonthday,
row_number() OVER (PARTITION BY id ORDER BY try(date_parse(yearmonthday, '%Y%m%d')) DESC) as row_num 
from "db_raw"."table" ) ranked
where row_num = 1 and id = 1

方案2：直接按数字降序排序（更高效） #

因为YYYYMMDD格式的数字本身可以通过大小判断日期先后，无需转换为日期类型：

select id, amount, yearmonthday
from ( 
select 
id,
amount,
yearmonthday,
row_number() OVER (PARTITION BY id ORDER BY cast(yearmonthday as bigint) DESC) as row_num 
from "db_raw"."table" ) ranked
where row_num = 1 and id = 1

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