如何在Snowflake的CTE中按最新版本值过滤数据行

row_number()完全可以实现你的需求，问题大概率出在窗口函数的分区和排序逻辑上，下面直接给正确写法和关键说明：

模拟测试数据 #

先创建和你场景匹配的临时表：

CREATE OR REPLACE TEMP TABLE brand_data (
    id VARCHAR,
    brand VARCHAR,
    version INT,
    other_info VARCHAR
);

INSERT INTO brand_data VALUES
('id1', 'brand_a', 1, 'v1 info'),
('id1', 'brand_a', 2, 'v2 info'),
('id2', 'brand_b', 1, 'v1 info'),
('id3', 'brand_c', 1, 'v1 info');

正确CTE+row_number()写法 #

WITH ranked_brands AS (
    SELECT 
        *,
        -- 按id分区，每个id内按version降序排，最新版本行号为1
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY version DESC) AS rn
    FROM brand_data
)
-- 只保留每个id的最新版本行
SELECT * EXCLUDE rn
FROM ranked_brands
WHERE rn = 1;

关键注意事项 #

• PARTITION BY 必须指定分组维度：这里要按id分区，确保窗口函数针对每个独立的id单独计算行号，如果你误写为按brand分区，会导致同一品牌下的所有id混在一起计算，结果不符合预期。
• ORDER BY 要按版本降序：用DESC让最大的版本号排在最前面，这样row_number()会给最新版本分配1；如果用ASC，取到的会是最老版本。
• 版本号为字符串的处理：如果你的version字段是类似'v1'、'v2'的字符串，需要先转换为数字再排序，比如：

  ORDER BY TO_NUMBER(SUBSTR(version, 2)) DESC
  

常见错误排查 #

1. 分区字段错误：比如误将PARTITION BY brand当成分组条件，导致同一品牌下的不同id被合并计算行号。
2. 排序方向错误：使用ORDER BY version ASC，取到的是每个id的最老版本而非最新。
3. 版本号类型未处理：字符串类型的版本号直接排序会出现'v10'排在'v2'前面的错误，必须先转成数字。

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