如何在Snowflake中查询各店铺的最大日期及次大日期？

问题原因 #

你之前尝试的子查询WHERE FILE_DATE < (SELECT MAX(FILE_DATE) FROM "PRODUCTS" GROUP BY UPPER(RETAILER))无效，是因为该子查询返回每个店铺的最大日期（多行结果），而WHERE子句无法直接将单个值与多行结果做比较，导致逻辑错误。

解决方案：使用窗口函数实现 #

在Snowflake中，用窗口函数是最简洁高效的处理方式，以下提供两种可行写法：

写法一：基于排名筛选次大日期

SELECT
    SHOP,
    MAX_DATE,
    2nd_MAX_DATE
FROM (
    SELECT
        UPPER(RETAIL) AS SHOP,
        FILE_DATE AS 2nd_MAX_DATE,
        -- 直接获取当前店铺的最大日期
        MAX(FILE_DATE) OVER (PARTITION BY UPPER(RETAIL)) AS MAX_DATE,
        -- 按店铺分组，日期降序排名，1=最大日期，2=次大日期
        ROW_NUMBER() OVER (PARTITION BY UPPER(RETAIL) ORDER BY FILE_DATE DESC) AS date_rank
    FROM PRODUCTS
) ranked_dates
WHERE date_rank = 2
ORDER BY SHOP;

写法二：条件聚合直接提取结果

SELECT
    UPPER(RETAIL) AS SHOP,
    MAX(FILE_DATE) AS MAX_DATE,
    -- 筛选排名第2的日期作为次大值
    MAX(CASE WHEN date_rank = 2 THEN FILE_DATE END) AS 2nd_MAX_DATE
FROM (
    SELECT
        FILE_DATE,
        UPPER(RETAIL),
        ROW_NUMBER() OVER (PARTITION BY UPPER(RETAIL) ORDER BY FILE_DATE DESC) AS date_rank
    FROM PRODUCTS
) t
GROUP BY UPPER(RETAIL)
ORDER BY SHOP;

特殊场景适配 #

如果店铺存在多个相同的最大日期（比如同一天有多条记录），可以将ROW_NUMBER()替换为DENSE_RANK()，避免跳过排名，确保次大日期是真正的第二大值。

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