MySql选择日期相差30分钟的行

问题描述

我有一个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 first visit of each visit session, where the session definition is where the CreatedAt date is 30 min or more from the previous visits.

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

So in my case, I should be returning row 2 (Id column), row 8 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.

我的解决方法如下:

    SELECT DISTINCT a.`CreatedAt`
    FROM  activities AS a
    LEFT JOIN  activities AS b
    ON (
        (UNIX_TIMESTAMP(b.`CreatedAt`)  >= (UNIX_TIMESTAMP(a.`CreatedAt`) - (30 * 60)) ) AND
        (b.`CreatedAt` < a.`CreatedAt`)
    )
    WHERE (b.`CreatedAt` IS NULL) AND (a.`VisitorId` = '26924c19-3cd1-411e-a771-5ebd6806fb27' /* or others for example */ )

它可以正常工作，但是它不返回最后一行13，我也不知道这是最好的解决方案.预先感谢.

It works alright, but it does not return the last row 13, also I'm not sure it's the best solution. Thanks in advance.

推荐答案

解决此问题的最简单方法是将所有访问与他们的较早兄弟姐妹联系起来，然后仅选择没有访问的兄弟姐妹.如果没有以后的访问(例如您的ID为13的示例)，则(更直观)握拳的另一种方法会失败，但以后会失败.

The easiest way to approach this is to relate all visits to their earlier siblings and then chose only those, that have none. The (more intuitive) other approach of taking the fist of each, that has a later sibling will fail if no later visit exists (as in your example with ID 13).

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

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