Presto如何为1秒内重试成功的数据条目分配对应标识标签

我需要根据1秒内是否重试成功为数据中的每条记录分配标记，样本数据如下：

date       id  event            url  code  event_ts
2021-08-20 11  1629515037.0682  xyz  503   2021-08-20 20:03:57.068
2021-08-20 11  1629515037.1073  xyz  200   2021-08-20 20:03:57.107  -- 1秒内重试成功
2021-08-20 12  1629515037.1866  abc  503   2021-08-20 20:03:57.187
2021-08-20 12  1629515037.1942  abc  503   2021-08-20 20:03:57.194
2021-08-20 12  1629515037.2037  abc  503   2021-08-20 20:03:57.204
2021-08-20 12  1629515037.2249  abc  503   2021-08-20 20:03:57.225
2021-08-20 12  1629515064.2427  abc  200   2021-08-20 20:04:24.243  -- 1秒内重试成功

新增名为retry的列规则如下：

• 当code = 503且1秒内重试成功，标记为successful_retry
• 当code = 503且1秒后才重试成功，标记为successful_retry_after_1_sec
• 当code = 503且始终没有重试成功，标记为no_successful_retry

我平时主要使用Python/Pandas处理数据，但本次需要使用Presto实现该逻辑。我曾尝试使用LEAD()函数，但无法实现可变偏移的计算，希望得到相关指导。

以下是基于Gordon Linoff回答调整的参考代码：

SELECT
    date,
    id,
    url,
    event,
    FROM_UNIXTIME(event) AS event_ts,
    code,
    (
        CASE
            WHEN code <= 399 THEN 'successful_response'
            WHEN MIN(CASE WHEN code <= 399 THEN FROM_UNIXTIME(event) END) OVER (
                PARTITION BY
                    date,
                    id,
                    url
                ORDER BY
                    date,
                    id,
                    url,
                    event rows BETWEEN CURRENT ROW AND UNBOUNDED following
            ) <= FROM_UNIXTIME(event) + INTERVAL '1' SECOND THEN 'success_retry_within_1_sec'
            WHEN MIN(CASE WHEN code <= 399 THEN FROM_UNIXTIME(event) END) OVER (
                PARTITION BY
                    date,
                    id,
                    url
                ORDER BY
                    date,
                    id,
                    url,
                    event rows BETWEEN CURRENT ROW AND UNBOUNDED following
            ) > FROM_UNIXTIME(event) + INTERVAL '1' SECOND THEN 'success_retry_after_1_sec'
            ELSE 'No_successful_retry'
        END
    ) AS successful_retry_flag
FROM t

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