Snowflake查询：如何按表中指定列的第二大值过滤数据

你原本取week_id最大值的写法是可行的，要取第二大值对应行，有两种非常简便的原生写法，不需要复杂的多层CTE处理：

方案1：单表直接用QUALIFY+DENSE_RANK（最推荐） #

全程不需要单独计算第二大值，一次扫描表就可以拿到结果，自动处理week_id重复的场景：

SELECT *
FROM track_metrics
QUALIFY DENSE_RANK() OVER(ORDER BY "week_id" DESC) = 2;

逻辑说明：

• 按week_id降序做密集排名，所有等于最大值的行排名为1，所有等于第二大值的行排名为2
• 直接用QUALIFY过滤排名为2的行即可，不存在重复返回值的问题
• 即使存在多个相同的最大/第二大week_id，结果也完全准确

方案2：CTE取唯一值后用LIMIT+OFFSET定位 #

如果你更习惯先单独拿到第二大的week_id值再过滤，可以把你之前的第二种写法简化，避免窗口函数返回多行重复值：

WITH second_week AS (
    SELECT DISTINCT "week_id"
    FROM track_metrics
    ORDER BY "week_id" DESC
    LIMIT 1 OFFSET 1
)
SELECT * 
FROM track_metrics
WHERE "week_id" = (SELECT "week_id" FROM second_week);

逻辑说明：

• 对去重后的week_id降序排列，LIMIT 1 OFFSET 1代表跳过第1行（最大值）取1行，直接返回单个第二大的week_id值
• 外层查询直接匹配这个单值做过滤即可，不会出现多行重复值无法匹配的问题

之前两种写法的问题说明 #

• 第一种升序调用nth_value的写法：一是升序排列时第2位是全局第二小值，和第二大值的需求完全相反；二是窗口函数默认窗口范围是分区首行到当前行，没有声明全窗口范围的话，nth_value拿不到排序后所有行的值，结果必然错误。
• 第二种去重后调用nth_value的写法：nth_value是窗口函数，会为结果集的每一行都返回计算结果，所以有多少个去重后的week_id就会返回多少行重复的第二大值，无法直接作为单值过滤条件使用。

注：如果表中week_id的去重值少于2个（比如只有1种周值、或表为空），上述两种方案都会返回空结果，不会抛出语法错误。

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