MySQL:按连续天数分组并计数

Question

我有一个数据库表，其中包含每个用户在城市的签到记录。我需要知道用户在一个城市呆了多少天，然后是用户访问了一个城市多少次(一次访问包括在一个城市连续停留的天数)。

因此，假设我有下表(经过简化，仅包含 DATETIME - 相同的用户和城市):

      datetime
-------------------
2011-06-30 12:11:46
2011-07-01 13:16:34
2011-07-01 15:22:45
2011-07-01 22:35:00
2011-07-02 13:45:12
2011-08-01 00:11:45
2011-08-05 17:14:34
2011-08-05 18:11:46
2011-08-06 20:22:12

此用户到过该城市的天数为 6(30.06、01.07、02.07，01.08，05.08，06.08)。

我想过使用 SELECT COUNT(id) FROM table GROUP BY DATE(datetime)

然后，对于该用户访问该城市的次数，查询应返回 3(30.06-02.07，01.08 , 05.08-06.08).

问题是我不知道该如何构建这个查询。

非常感谢任何帮助!

Answer 1

您可以通过查找前一天没有签到的签到来找到每次访问的第一天。

select count(distinct date(start_of_visit.datetime))
from checkin start_of_visit
left join checkin previous_day
    on start_of_visit.user = previous_day.user
    and start_of_visit.city = previous_day.city
    and date(start_of_visit.datetime) - interval 1 day = date(previous_day.datetime)
where previous_day.id is null

此查询有几个重要部分。

首先，每次签到都与前一天的任何签到相结合。但由于它是外部联接，如果前一天没有 checkin ，联接的右侧将有 NULL 结果。 WHERE 过滤发生在连接之后，因此它只保留来自左侧的那些 checkin ，而没有来自右侧的 checkin 。 LEFT OUTER JOIN/WHERE IS NULL 对于查找不存在的地方非常方便。

然后它会计算不同 签到日期，以确保如果用户在访问的第一天多次签到，它不会重复计算。 (当我发现可能的错误时，我实际上在编辑时添加了那部分。)

编辑:我刚刚重新阅读了您针对第一个问题提出的查询。您的查询会得到给定日期的签到次数，而不是日期计数。我想你想要这样的东西:

select count(distinct date(datetime))
from checkin
where user='some user' and city='some city'