MySql选择日期相差30分钟的最后一行 [英] MySql Select last row with 30 minutes difference in date

问题描述

这是此问题的后续操作 MySql选择带有日期相差30分钟，尽管概念上相似，但所需的解决方案可能有所不同.

This is a followup to this question MySql Select rows with 30 minutes difference in date, albeit similar in concept the solution needed might be different.

我有一个MySql-8.0/MariaDb-10.4表，其中包含不同访客的站点访问列表:

I have a MySql-8.0/MariaDb-10.4 table that contains a list of site visits of different visitors:

我想创建一个查询，该查询返回每次访问会话的最后次访问，其中会话定义是指距CreatedAt日期为30分钟或更长的时间以前的访问.

I want to create a query that returns the last visit of each visit session, where the session definition is where the CreatedAt date is 30 min or more from the previous visits.

因此，在我的情况下，我应该返回第7行("Id列")，第12行和第13行.请注意，一次会话可以超过30分钟，只要每次访问都在不到30分钟的时间内成功完成了上一次访问

So in my case, I should be returning row 7 (Id column), row 12 and row 13. Note also that a session can be more than 30 minutes, as long as each visit succeeds a previous visit with less than 30min.

@EugenRieck建议的整洁解决方案如下:

The neat solution suggest by @EugenRieck was as follows:

SELECT
  late.*
FROM activities AS late
LEFT JOIN activities AS early
  ON late.VisitorId=early.VisitorId
  AND late.CreatedAt>early.CreatedAt
  AND late.CreatedAt<=DATE_ADD(early.CreatedAt, INTERVAL +30 MINUTE)
WHERE early.Id IS NULL
-- Maybe: AND late.VisitorId='26924c19-3cd1-411e-a771-5ebd6806fb27'
-- Maybe: ORDER BY late.CreatedAt

效果很好，但是可以通过在每次访问会话中返回首次访问，而不是最后一次访问来实现.我试图进行修改以按需要工作，但是没有运气.请帮忙.

It works great, but it works by returning the first visit in each visit session, not the last visit. I tried to modify to work as i wanted but with no luck. Please help.

推荐答案

这是离岛"问题的一种变体.但是您可以使用lead()处理它.只需检查下一个createdAt是否距给定行中的值超过30分钟即可.那是会话的最后一行:

This is a variant of gap-and-islands problem. But you can handle it using lead(). Just check if the next createdAt is over 30 minutes from the value in a given row. That is the last row for a session:

select a.*
from (select a.*,
             lead(createdAt) over (partition by visitorid order by createdat) as next_ca
      from activities a
     ) a
where next_ca > createdAt + interval 30 minute;

通常，在这种情况下，您也需要最后一行.您可以通过or next_ca is null来实现.

Usually, in this situation you would want the last row as well. You would get that with or next_ca is null.

这篇关于MySql选择日期相差30分钟的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！