MySQL筛选相邻时间戳间隔≤30天的数据直至首次超期

我来帮你梳理下这个需求的解决步骤，咱们一步步拆解：

核心需求明确 #

首先得把你的需求拆清楚：

• 数据要按timestamp降序排列（最新的记录在前）
• 从第一条记录开始，依次检查相邻两行的时间间隔，只要间隔≤30天就保留；一旦遇到间隔>30天的行，就停止筛选
• 最终结果必须包含id=65404844的条目（也就是说，即使这条目之后的间隔超过30天，也要保留到它为止）

分步实现方案 #

1. 先排序并计算相邻行的时间差 #

首先，我们需要把数据按时间戳降序排列，同时计算每一行和前一行（更旧的那条）的间隔天数。这里可以用MySQL的窗口函数LAG()来获取上一行的时间戳，再用TIMESTAMPDIFF()计算天数差：

SELECT 
    id,
    timestamp,
    -- 计算当前行与上一行的时间间隔（天数）
    TIMESTAMPDIFF(DAY, LAG(timestamp) OVER (ORDER BY timestamp DESC), timestamp) AS days_between
FROM your_table
ORDER BY timestamp DESC;

运行这个查询，你能直观看到每一行和前一行的间隔，方便后续验证逻辑。

2. 用递归CTE筛选连续符合条件的记录 #

因为我们需要逐行检查并保留连续满足间隔≤30天的记录，同时确保包含目标id，递归CTE是最合适的工具。它能从最新记录开始，逐步往下（时间更旧的方向）检查，直到遇到间隔>30天的行，或者覆盖到目标id：

WITH RECURSIVE sorted_records AS (
    -- 第一步：给排序后的记录标记行号，同时计算相邻间隔
    SELECT 
        id,
        timestamp,
        TIMESTAMPDIFF(DAY, LAG(timestamp) OVER (ORDER BY timestamp DESC), timestamp) AS days_between,
        ROW_NUMBER() OVER (ORDER BY timestamp DESC) AS row_num
    FROM your_table
),
continuous_chain AS (
    -- 起始点：最新的那条记录
    SELECT id, timestamp, row_num
    FROM sorted_records
    WHERE row_num = 1
    
    UNION ALL
    
    -- 递归逻辑：只要当前行和上一行的间隔≤30天，或者当前行是目标id，就加入结果
    SELECT sr.id, sr.timestamp, sr.row_num
    FROM sorted_records sr
    JOIN continuous_chain cc ON sr.row_num = cc.row_num + 1
    WHERE (sr.days_between <= 30 OR sr.id = 65404844)
)
-- 最终输出结果，按时间降序排列
SELECT id, timestamp
FROM continuous_chain
ORDER BY timestamp DESC;

3. 特殊情况的调整 #

如果id=65404844的条目刚好是在间隔>30天的前一行，递归会自动停在它这里；如果它在连续间隔的中间，递归会继续直到遇到第一个间隔超标的行。

要是你发现目标id不在初始的连续链里（比如它和上一行的间隔超过30天），可以调整递归的终止条件，优先确保包含目标id：比如先找到目标id的时间戳，然后从最新记录开始，保留所有时间戳≥(目标id时间戳 - 30天)的记录？不过这和你“相邻行间隔不超过30天”的核心需求有冲突，所以还是优先以相邻间隔为判断标准，同时强制包含目标id。

验证方法 #

你可以先运行第一步的排序+间隔查询，找到id=65404844的位置，看看它和前后行的间隔，再调整递归CTE的条件，确保结果符合预期。

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