MySQL:连续数日分组和计数组

Question

我有一个数据库表，用于保存城市中每个用户的签入。我需要知道一个用户在一个城市中已经访问了多少天，然后，一个用户对一个城市进行了多少次访问(一次访问包括在一个城市中连续的几天)。

因此，请考虑下面的表(简化，只包含DATETIMEs -相同的用户和城市)：

      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

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

首先，每个签入都加入到前一天的任何签入。但是由于它是一个外部连接，如果前一天没有签入，那么join的右侧将得到NULL结果。WHERE过滤是在联接之后进行的，因此它只保留左侧的签入，而右边没有检查。LEFT OUTER JOIN/WHERE IS NULL非常方便地找到不是的地方。

然后，它对不同的签入日期进行计数，以确保用户在访问的第一天多次报到时不会重复计数。(实际上，当我发现可能的错误时，我在编辑中添加了这个部分。)

编辑:我只是重新阅读了你对第一个问题的建议。查询将获得给定日期上的签入数，而不是日期计数。我觉得你想要这样的东西

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

Answer 2

试着把这个代码应用到你的任务中-

CREATE TABLE visits(
  user_id INT(11) NOT NULL,
  dt DATETIME DEFAULT NULL
);

INSERT INTO visits VALUES 
  (1, '2011-06-30 12:11:46'),
  (1, '2011-07-01 13:16:34'),
  (1, '2011-07-01 15:22:45'),
  (1, '2011-07-01 22:35:00'),
  (1, '2011-07-02 13:45:12'),
  (1, '2011-08-01 00:11:45'),
  (1, '2011-08-05 17:14:34'),
  (1, '2011-08-05 18:11:46'),
  (1, '2011-08-06 20:22:12'),
  (2, '2011-08-30 16:13:34'),
  (2, '2011-08-31 16:13:41');


SET @i = 0;
SET @last_dt = NULL;
SET @last_user = NULL;

SELECT v.user_id,
  COUNT(DISTINCT(DATE(dt))) number_of_days,
  MAX(days) number_of_visits
FROM
  (SELECT user_id, dt
        @i := IF(@last_user IS NULL OR @last_user <> user_id, 1, IF(@last_dt IS NULL OR (DATE(dt) - INTERVAL 1 DAY) > DATE(@last_dt), @i + 1, @i)) AS days,
        @last_dt := DATE(dt),
        @last_user := user_id
   FROM
     visits
   ORDER BY
     user_id, dt
  ) v
GROUP BY
  v.user_id;

----------------
Output:

+---------+----------------+------------------+
| user_id | number_of_days | number_of_visits |
+---------+----------------+------------------+
|       1 |              6 |                3 |
|       2 |              2 |                1 |
+---------+----------------+------------------+

解释：

为了理解它是如何工作的，让我们检查子查询，如下所示。

SET @i = 0;
SET @last_dt = NULL;
SET @last_user = NULL;


SELECT user_id, dt,
        @i := IF(@last_user IS NULL OR @last_user <> user_id, 1, IF(@last_dt IS NULL OR (DATE(dt) - INTERVAL 1 DAY) > DATE(@last_dt), @i + 1, @i)) AS 

days,
        @last_dt := DATE(dt) lt,
        @last_user := user_id lu
FROM
  visits
ORDER BY
  user_id, dt;

如您所见，查询返回所有行，并对访问次数进行排序。这是基于变量的已知排序方法，请注意，行是按用户字段和日期字段排序的。该查询计算用户访问，并输出下一个数据集，其中days列为访问次数提供了排名-

+---------+---------------------+------+------------+----+
| user_id | dt                  | days | lt         | lu |
+---------+---------------------+------+------------+----+
|       1 | 2011-06-30 12:11:46 |    1 | 2011-06-30 |  1 |
|       1 | 2011-07-01 13:16:34 |    1 | 2011-07-01 |  1 |
|       1 | 2011-07-01 15:22:45 |    1 | 2011-07-01 |  1 |
|       1 | 2011-07-01 22:35:00 |    1 | 2011-07-01 |  1 |
|       1 | 2011-07-02 13:45:12 |    1 | 2011-07-02 |  1 |
|       1 | 2011-08-01 00:11:45 |    2 | 2011-08-01 |  1 |
|       1 | 2011-08-05 17:14:34 |    3 | 2011-08-05 |  1 |
|       1 | 2011-08-05 18:11:46 |    3 | 2011-08-05 |  1 |
|       1 | 2011-08-06 20:22:12 |    3 | 2011-08-06 |  1 |
|       2 | 2011-08-30 16:13:34 |    1 | 2011-08-30 |  2 |
|       2 | 2011-08-31 16:13:41 |    1 | 2011-08-31 |  2 |
+---------+---------------------+------+------------+----+

然后我们根据用户对这些数据进行分组，并使用聚合函数：‘COUNT(DATE(Dt))’--计数'MAX( days )‘的天数--访问的次数，这是我们子查询中days字段的最大值。

仅此而已；)

Answer 3

作为Devart提供的数据示例，内部"PreQuery“可以使用sql变量。通过将@LUser默认为a-1(可能不存在用户ID)，IF()测试检查最后一个用户与当前用户之间的任何差异。一旦一个新用户，它的值为1.此外，如果最后一个日期是从新的签入日期起超过1天，它将得到值1。然后，随后的列将@LUser和@LDate重置为刚刚根据下一个周期测试的传入记录的值。然后，外部查询只对它们进行汇总，并根据Devart数据集对它们进行计数以获得最终的正确结果。

User ID    Distinct Visits   Total Days
1           3                 9
2           1                 2

select PreQuery.User_ID,
       sum( PreQuery.NextVisit ) as DistinctVisits,
       count(*) as TotalDays
   from
      (  select v.user_id,
               if( @LUser <> v.User_ID OR @LDate < ( date( v.dt ) - Interval 1 day ), 1, 0 ) as NextVisit,
               @LUser := v.user_id,
               @LDate := date( v.dt )
            from 
               Visits v,
               ( select @LUser := -1, @LDate := date(now()) ) AtVars 
            order by
               v.user_id,
               v.dt  ) PreQuery
    group by 
       PreQuery.User_ID