Oracle SQL实现90天滚动日期窗口筛选符合间隔要求的记录

• 这类依赖上一次触发节点的滚动判断逻辑，适合用Oracle支持的递归CTE（公用表表达式）实现，核心逻辑是逐行迭代校验与上一次告警日期的差值，动态更新告警标记和基准日期。
• 步骤拆解：
  1. 先对所有交易日期按升序排序，生成唯一行号，方便后续按顺序遍历
  2. 递归起始节点：取排序后的第一行，默认标记Alert为Yes，上次告警日期为当前行日期，距上次告警天数为0
  3. 递归迭代节点：依次遍历后续行，用当前行日期减去上一轮记录的上次告警日期，判断差值是否大于90：
     • 差值>90：Alert标记为Yes，更新上次告警日期为当前行日期，天数差为计算得到的差值
     • 差值<=90：Alert标记为No，上次告警日期沿用上个节点的记录，天数差为计算得到的差值
  4. 最终如果仅需返回告警记录，加WHERE Alert = 'Yes'过滤即可。

WITH src_data AS (
    -- 测试数据源
    SELECT date'2021-05-06' AS tran_dt FROM dual UNION
    SELECT date'2021-05-24' FROM dual UNION
    SELECT date'2021-06-25' FROM dual UNION
    SELECT date'2021-07-02' FROM dual UNION
    SELECT date'2021-07-27' FROM dual UNION
    SELECT date'2021-08-16' FROM dual UNION
    SELECT date'2021-08-23' FROM dual UNION
    SELECT date'2021-10-01' FROM dual UNION
    SELECT date'2021-12-31' FROM dual
),
sorted_data AS (
    -- 日期排序加行号
    SELECT tran_dt, ROW_NUMBER() OVER(ORDER BY tran_dt) rn
    FROM src_data
),
recursive_calc(rn, tran_dt, last_alert_dt, alert, days_since_last_alert) AS (
    -- 递归起始节点：第一行默认告警
    SELECT rn,
           tran_dt,
           tran_dt AS last_alert_dt,
           'Yes' AS alert,
           0 AS days_since_last_alert
    FROM sorted_data
    WHERE rn = 1
    UNION ALL
    -- 递归迭代：逐行判断
    SELECT s.rn,
           s.tran_dt,
           CASE WHEN s.tran_dt - r.last_alert_dt > 90 THEN s.tran_dt ELSE r.last_alert_dt END AS last_alert_dt,
           CASE WHEN s.tran_dt - r.last_alert_dt > 90 THEN 'Yes' ELSE 'No' END AS alert,
           s.tran_dt - r.last_alert_dt AS days_since_last_alert
    FROM recursive_calc r
    JOIN sorted_data s ON s.rn = r.rn + 1
)
-- 输出结果，格式化日期和预期一致
SELECT TO_CHAR(tran_dt, 'dd/mm/yyyy') AS TRAN_DT,
       ALERT,
       DAYS_SINCE_LAST_ALERT AS "Days Since Last Alert"
FROM recursive_calc
-- 如果只需要返回Alert为Yes的记录，打开下面注释即可
-- WHERE ALERT = 'Yes'
ORDER BY rn;

执行上述SQL后输出结果和预期完全一致：

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