mysql - 获取最新的可接受差距的日期

Question

我正在努力处理以下情况以提取日期时间。

如果没有 12 个月或更长时间的间隔，它应该返回 null(参见 id 2)。

如果存在 2 个或更多 12 个月的间隔，则应考虑最近的间隔(参见 id 1)

如果只有一个差距，那么应该考虑这一点(见 id 3)

id | datetime
1  | 2018-01-01
1  | 2018-01-02
1  | 2019-02-01
1  | 2019-02-02
1  | 2020-03-01
1  | 2020-03-02
2  | 2018-01-01
2  | 2018-01-02
3  | 2018-01-01
3  | 2018-01-02
3  | 2019-02-01

预期的结果应该是，

id | datetime
1  | 2020-03-01
2  | null
3  | 2019-02-01

结果按id分组。对于 id 1，选择 2020-03-01 是因为它最接近 id 1 中存在的最近 12 个月或更多个月的差距。(第一个差距是 2019-02-01 - 2018-01-02 >= 12，第二个差距是 2020-03-01 - 2019-02-02 选择 2020-03-01 因为它是最后一个可接受差距的高度值)

虽然我尝试使用 id 进行分组，但无法循环查看有多少空隙然后选择最新的空隙。

在 postgres 中我希望我们可以使用一个带窗口函数的 lag() 函数，这样我就可以对日期时间进行排序并减去两个相邻的日期时间，这可以用作子查询并从所有结果中获取最大值. 但我想在 mysql 中执行此操作。

lag("datetime") over (order by "datetime") - "datetime" as diff,

Answer 1

最后，我找到了使用 MySql 变量的解决方案，

SELECT
    data.id,
    MAX(data.eligible_datetime)
FROM (
    SELECT
        z.id,
        z.datetime,
        IF(TIMESTAMPDIFF(MONTH, @prevDate, z.datetime) > 11 AND @prevID = z.id, z.datetime, NULL) eligible_datetime,
        @prevDate := z.datetime,
        @prevID := z.id
    FROM tablename z,
    (
        SELECT
            @prevID := NULL,
            @prevDate := NULL
    ) vars
    ORDER BY z.id, z.datetime
) data
GROUP BY data.id