MySQL 每月活跃用户数

Question

有人可以帮我为活跃用户创建一份 mysql 月度报告吗？用户在特定日期去看医生。该日期记录在访问日期。然后他们会立即收到返回的预约日期。用户活跃的月份是从访问月份一直到约会日期月份的月份，加上 90 天的宽限期。如果他们不在约会日期上投球，他们将获得 90 天的宽限期，在此期间他们'您仍将被视为活跃用户。之后他们将不再被视为活跃。

Users Table
+------------+------------+------------+
|  UserID    |  visit     |Appointment |  
+------------+------------+------------+
| 10001      | 01-01-2010 | 01-02-2010 | 
| 10001      | 05-02-2010 | 01-03-2010 | 
| 10002      | 20-07-2010 | 15-10-2010 | 
| 10003      | 01-11-2010 | 10-11-2010| 
+------------+------------+------------+

期望的结果是

Monthly Report
+------------+------------+------------+
|  Month     |  active    |            |  
+------------+------------+------------+
| 2010-01    | 1          |            | 
| 2010-02    | 1          |            | 
| 2010-03    | 1          |            | 
| 2010-04    | 1          |            | 
| 2010-05    | 1          |            | 
| 2010-07    | 1          |            |
| 2010-08    | 1          |            | 
| 2010-09    | 1          |            | 
| 2010-10    | 1          |            | 
| 2010-11    | 2          |            | 
| 2010-12    | 2          |            | 
| 2011-01    | 1          |            | 
+------------+------------+------------+

这是sql代码

  CREATE TABLE `visits` (
   `id` int(10) NOT NULL,
   `userid` int(10) NOT NULL,
   `visit` date NOT NULL,
   `appointment` date NOT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;



INSERT INTO `visits` (`id`, `userid`, `visit`, `appointment`) VALUES
(1, 10001, '2010-01-01', '2010-02-01'),
(2, 10001, '2010-02-05', '2010-03-01'),
(3, 10002, '2010-07-20', '2010-10-15'),
(4, 10003, '2010-11-01', '2010-11-10');


ALTER TABLE `visits`
ADD PRIMARY KEY (`id`);

ALTER TABLE `visits`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10007;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Answer 1

对于那些可能需要这个的人。

SELECT
date_format(c.dt,'%Y-%m') AS "month",
c.dt AS date,
a.visit,
a.fu,
COUNT(DISTINCT userid) AS Active
FROM calendar_table c
LEFT JOIN visits v ON c.dt BETWEEN date_format(v.visit,'%Y-%m') AND 
DATE_SUB(date_add(v.appointment, INTERVAL 90 day), INTERVAL 
date_format((LAST_DAY(date_add(v.appointment, INTERVAL 90 day))),'%d')-1 DAY)
WHERE c.d = 1
AND c.y IN (2010,2011)
GROUP BY c.dt