Snowflake中使用WHERE筛选RANK()结果报错的技术问询

我有一张结构如下的delivery_radius_log表：

实际数据格式一致，但内容更多样。我想要在Snowflake数据库中用单条查询获取各月份按时长排名第一的配送半径，当前使用的SQL如下：

SELECT DELIVERY_AREA_ID,
       MAX(DELIVERY_RADIUS_METERS) AS default_delivery_radius,
       MONTH_YEAR,
       DELIVERY_RADIUS_METERS,
       SUM(DURATION_SECONDS) AS total_duration,
       MAX(EVENT_STARTED_TIMESTAMP) AS MAX_TIMESTAMP,
       RANK() OVER (PARTITION BY DELIVERY_AREA_ID, MONTH_YEAR
                    ORDER BY SUM(DURATION_SECONDS) DESC) AS RADIUS_RANK
FROM (
    -- Add the MONTH_YEAR column to the delivery_radius_log table
    SELECT DELIVERY_AREA_ID,
           DELIVERY_RADIUS_METERS,
           EVENT_STARTED_TIMESTAMP,
           CONCAT(MONTH(EVENT_STARTED_TIMESTAMP), '/',
                  YEAR(EVENT_STARTED_TIMESTAMP)) AS MONTH_YEAR,
           DATEADD(second, DATEDIFF(second, EVENT_STARTED_TIMESTAMP, LEAD(EVENT_STARTED_TIMESTAMP) OVER (PARTITION BY DELIVERY_AREA_ID ORDER BY EVENT_STARTED_TIMESTAMP)), EVENT_STARTED_TIMESTAMP) AS end_timestamp,
           DATEDIFF(second, EVENT_STARTED_TIMESTAMP, LEAD(EVENT_STARTED_TIMESTAMP) OVER (PARTITION BY DELIVERY_AREA_ID ORDER BY EVENT_STARTED_TIMESTAMP)) AS duration_seconds
    FROM delivery_radius_log
) t  -- added alias here
GROUP BY DELIVERY_AREA_ID, MONTH_YEAR, DELIVERY_RADIUS_METERS

当我添加WHERE RADIUS_RANK = 1来筛选排名第一的结果时，出现错误：Syntax error: unexpected 'where'. (line 21)，不知道怎么解决。

错误原因 #

窗口函数（比如RANK()）的计算逻辑是在WHERE、GROUP BY之后执行的，所以无法直接在当前查询的WHERE子句中引用窗口函数生成的列RADIUS_RANK，这是SQL执行顺序的规则限制。

修正后的SQL #

需要把现有查询嵌套为子查询或使用CTE（公共表表达式），在外层查询中完成RADIUS_RANK = 1的筛选：

方式1：使用子查询

SELECT *
FROM (
    SELECT DELIVERY_AREA_ID,
           MAX(DELIVERY_RADIUS_METERS) AS default_delivery_radius,
           MONTH_YEAR,
           DELIVERY_RADIUS_METERS,
           SUM(DURATION_SECONDS) AS total_duration,
           MAX(EVENT_STARTED_TIMESTAMP) AS MAX_TIMESTAMP,
           RANK() OVER (PARTITION BY DELIVERY_AREA_ID, MONTH_YEAR
                        ORDER BY SUM(DURATION_SECONDS) DESC) AS RADIUS_RANK
    FROM (
        -- Add the MONTH_YEAR column to the delivery_radius_log table
        SELECT DELIVERY_AREA_ID,
               DELIVERY_RADIUS_METERS,
               EVENT_STARTED_TIMESTAMP,
               CONCAT(MONTH(EVENT_STARTED_TIMESTAMP), '/',
                      YEAR(EVENT_STARTED_TIMESTAMP)) AS MONTH_YEAR,
               DATEADD(second, DATEDIFF(second, EVENT_STARTED_TIMESTAMP, LEAD(EVENT_STARTED_TIMESTAMP) OVER (PARTITION BY DELIVERY_AREA_ID ORDER BY EVENT_STARTED_TIMESTAMP)), EVENT_STARTED_TIMESTAMP) AS end_timestamp,
               DATEDIFF(second, EVENT_STARTED_TIMESTAMP, LEAD(EVENT_STARTED_TIMESTAMP) OVER (PARTITION BY DELIVERY_AREA_ID ORDER BY EVENT_STARTED_TIMESTAMP)) AS duration_seconds
        FROM delivery_radius_log
    ) t
    GROUP BY DELIVERY_AREA_ID, MONTH_YEAR, DELIVERY_RADIUS_METERS
) ranked_results
WHERE RADIUS_RANK = 1;

方式2：使用CTE（可读性更强）

WITH radius_duration AS (
    SELECT DELIVERY_AREA_ID,
           DELIVERY_RADIUS_METERS,
           EVENT_STARTED_TIMESTAMP,
           CONCAT(MONTH(EVENT_STARTED_TIMESTAMP), '/',
                  YEAR(EVENT_STARTED_TIMESTAMP)) AS MONTH_YEAR,
           DATEDIFF(second, EVENT_STARTED_TIMESTAMP, LEAD(EVENT_STARTED_TIMESTAMP) OVER (PARTITION BY DELIVERY_AREA_ID ORDER BY EVENT_STARTED_TIMESTAMP)) AS duration_seconds
    FROM delivery_radius_log
),
monthly_radius_stats AS (
    SELECT DELIVERY_AREA_ID,
           MAX(DELIVERY_RADIUS_METERS) AS default_delivery_radius,
           MONTH_YEAR,
           DELIVERY_RADIUS_METERS,
           SUM(DURATION_SECONDS) AS total_duration,
           MAX(EVENT_STARTED_TIMESTAMP) AS MAX_TIMESTAMP,
           RANK() OVER (PARTITION BY DELIVERY_AREA_ID, MONTH_YEAR
                        ORDER BY SUM(DURATION_SECONDS) DESC) AS RADIUS_RANK
    FROM radius_duration
    GROUP BY DELIVERY_AREA_ID, MONTH_YEAR, DELIVERY_RADIUS_METERS
)
SELECT *
FROM monthly_radius_stats
WHERE RADIUS_RANK = 1;

额外优化建议 #

1. 生成MONTH_YEAR可以用更简洁规范的写法：TO_CHAR(EVENT_STARTED_TIMESTAMP, 'MM/YYYY')，替代CONCAT(MONTH(...), '/', YEAR(...))，避免格式拼接错误。
2. 注意表中最后一条记录的duration_seconds会是NULL（因为没有后续记录），如果需要统计这条记录的时长，可以用COALESCE设置默认值，比如假设持续到当前时间：COALESCE(DATEDIFF(...), DATEDIFF(second, EVENT_STARTED_TIMESTAMP, CURRENT_TIMESTAMP)) AS duration_seconds。

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