时间戳

首页 > 解决方案 > MySQL获取按日期设置的最新连续行

mysql - MySQL获取按日期设置的最新连续行

问题描述

我正在使用查询检索以下行，

SELECT id, entry_id, DATE(entry_date) 
FROM entries 
WHERE entry_id = 51 
ORDER BY entry_date DESC

+-----+----------+---------------------+
| id  | entry_id | entry_date          |
+-----+----------+---------------------+
|  84 | 51       | 2021-02-27 xx:xx:xx |<---
|  81 | 51       | 2021-02-26 xx:xx:xx |   |
|  76 | 51       | 2021-02-25 xx:xx:xx |   |-- consecutive set 
|  74 | 51       | 2021-02-25 xx:xx:xx |   |
|  73 | 51       | 2021-02-24 xx:xx:xx |<---
|  52 | 51       | 2021-02-20 xx:xx:xx |
|  44 | 51       | 2021-02-19 xx:xx:xx |
|  32 | 51       | 2021-02-18 xx:xx:xx |
|   . | ..       |    ...              |
|   . | ..       |    ...              |
+-----+----------+---------------------+

entry_date 的数据类型是时间戳。在 entry_date 中时间并不重要。我只关心没有时间的日期。

我只想获取具有“最新连续日期”或“entry_id”的最新连续集的第一个和最后一个日期的行。例如，对于 entry_id = 51，我只想要行，

+-----+----------+------------+
| id  | entry_id | entry_date |
+-----+----------+------------+
|  84 | 51       | 2021-02-27 |
|  81 | 51       | 2021-02-26 |
|  76 | 51       | 2021-02-25 |
|  74 | 51       | 2021-02-25 |
|  73 | 51       | 2021-02-24 |
+-----+----------+------------+

或者我想获取为 entry_id = 51 设置的“最新连续日期”的第一个和最后一个日期，例如。在这种情况下 entry_date 为 2021-02-24 和 2021-02-27。

我没有任何编写此类查询的经验。我可以通过 DESC 获取 entry_id = 51 的所有记录顺序，并编写一个脚本来获取最新的连续行，但是由于有数十万行，有时仅仅为了获取最新的连续行而处理效率低下。

请注意，可能有一些条目具有相同的日期（在这种情况下：2021-02-25），这些条目也会被考虑在结果中。

编辑：我使用的是 MySQL 5.6。

标签： mysqlsqldate

解决方案

这是一种间隙和孤岛问题，lead()用于确定哪里有超过一天的间隙。

select entry_id, min(entry_date), max(entry_date)
from (select e.*,
             sum(case when entry_date < next_entry_date - interval 1 day then 1 else 0 end) over (partition by entry_id order by entry_date desc) as grp
      from (select e.*,
                   lead(entry_date) over (partition by entry_id order by entry_date) as next_entry_date
            from entries e
           ) e
     ) e
where grp = 0
group by entry_id;

然后以相反的顺序完成累积和。所以最后一组的累积和为0。

这是一个 db<>fiddle。

推荐阅读