MySQL：连续数日组合 [英] MySQL: group by consecutive days and count groups

问题描述

我有一个数据库表，它保存每个用户在城市中的签到。我需要知道用户在一个城市中有多少天，然后用户对一个城市进行了多少次访问（访问包括在城市中花费的连续几天）。

所以，考虑我有以下表格（简化，只包含 DATETIME s - 同一个用户和城市）：

  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 <我想到这样做是使用 SELECT（）， COUNT（id）FROM table GROUP BY DATE（datetime）

然后，对于这个用户对这个城市的访问次数，t他的查询应该返回 3 （ 30.06-02.07 ， 01.08 ， 05.08-06.08 ）

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

任何帮助将非常感谢！

解决方案

你可以找到每天访问的第一天，通过查找前一天没有签到的签到。

  select count（distinct date（start_of_visit。 datetime））
从checkin start_of_visit 
 left join checkin previous_day 
 on start_of_visit.user = previous_day.user 
和start_of_visit.city = previous_day.city 
和date（start_of_visit .datetime） - 间隔1天=日期（previous_day.datetime）
其中previous_day.id为空
  

这个查询有几个重要的部分。

首先，每个签到都加入到前一天的任何签到。但是，由于它是外部连接，如果前一天没有签入，连接的右侧将具有 NULL 结果。 WHERE 过滤发生在连接之后，所以它只保留左侧没有右侧的那些签入。 LEFT OUTER JOIN / WHERE IS NULL 非常适合查找不在的地方。

然后，它计数不同的签到日期，以确保在用户访问的第一天多次登录用户时不会重复计算。 （实际上，当我发现可能的错误时，我实际上添加了该部分。）

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

  select count（distinct date（datetime））
 from checkin 
其中user ='some user'and city ='some city'
  

I have a database table which holds each user's checkins in cities. I need to know how many days a user has been in a city, and then, how many visits a user has made to a city (a visit consists of consecutive days spent in a city).

So, consider I have the following table (simplified, containing only the DATETIMEs - same user and city):

      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

The number of days this user has been to this city would be 6 (30.06, 01.07, 02.07, 01.08, 05.08, 06.08).

I thought of doing this using SELECT COUNT(id) FROM table GROUP BY DATE(datetime)

Then, for the number of visits this user has made to this city, the query should return 3 (30.06-02.07, 01.08, 05.08-06.08).

The problem is that I have no idea how shall I build this query.

Any help would be highly appreciated!

解决方案

You can find the first day of each visit by finding checkins where there was no checkin the day before.

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

There are several important parts to this query.

First, each checkin is joined to any checkin from the previous day. But since it's an outer join, if there was no checkin the previous day the right side of the join will have NULL results. The WHERE filtering happens after the join, so it keeps only those checkins from the left side where there are none from the right side. LEFT OUTER JOIN/WHERE IS NULL is really handy for finding where things aren't.

Then it counts distinct checkin dates to make sure it doesn't double-count if the user checked in multiple times on the first day of the visit. (I actually added that part on edit, when I spotted the possible error.)

Edit: I just re-read your proposed query for the first question. Your query would get you the number of checkins on a given date, instead of a count of dates. I think you want something like this instead:

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

这篇关于MySQL：连续数日组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！