时间戳

首页 > 解决方案 > mysql - 获取最新可接受差距的日期

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

问题描述

我在以下场景中苦苦挣扎以提取日期时间。

如果没有 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,

标签： mysqlmysql-5.7

解决方案

最后，我找到了使用 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

推荐阅读