我有三列User_ID, New_Status and DATETIME。
New_Status为用户包含0(非活动)和1(活动)。每个用户都从活动状态开始，即1。随后的表格存储了它们的状态和它们被激活/去激活的datetime。
如何计算每个日期结束时的活动用户数，包括表中未生成记录的日期。
样本数据：
| ID | New_Status | DATETIME |
+----+------------+---------------------+
| 1 | 1 | 2019-01-01 21:00:00 |
| 1 | 0 | 2019-02-05 17:00:00 |
| 1 | 1 | 2019-03-06 18:00:00 |
| 2 | 1 | 2019-01-02 01:00:00 |
| 2 | 0 | 2019-02-03 13:00:00 |
将日期-时间值格式化为仅限日期的字符串并按其分组
SELECT DATE_FORMAT(DATETIME, '%Y-%m-%d') as day, COUNT(*) as active
FROM test
WHERE New_Status = 1
GROUP BY day
ORDER BY day
在MySQL 8中，您可以使用row_number()窗口函数来获取用户每天的最后状态。然后筛选指示用户当天处于活动状态的GROUP BY，并对其进行计数。
SELECT date(x.datetime),
count(*)
FROM (SELECT date(t.datetime) datetime,
t.new_status,
row_number() OVER (PARTITION BY date(t.datetime)
ORDER BY t.datetime DESC) rn
FROM elbat t) x
WHERE x.rn = 1
AND x.new_status = 1
GROUP BY x.datetime;
如果不是所有的天数都在表中，您需要创建一个包含所有天数的(可能派生的)表，并将其交叉联接。
查找每天活动更改的用户的最后一次活动状态
select User_ID, New_Status, DATE_FORMAT(DATETIME, '%Y-%m-%d')
from activity_table
where not exists
(
select 1
from activity_table at
where at.User_ID = activity_table.User_ID and
DATE_FORMAT(at.DATETIME, '%Y-%m-%d') = DATE_FORMAT(activity_table.DATETIME, '%Y-%m-%d') and
at.DATETIME > activity_table.DATETIME
)
order by DATE_FORMAT(activity_table.DATETIME, '%Y-%m-%d');
这还不是解决方案，而是解决方案之前非常有用的信息。请注意，这里还没有涵盖所有日期，这些值是单独的记录，更确切地说是每天的最后一个值，按日期排序。
让我们得到总数
使用上面的查询作为子选择并将其别名为table，您可以group by DATETIME并执行select sum(new_Status) as activity, count(*) total, DATETIME，这样您就会知道activity - (total - activity)是与前一天相比的差异。
了解结果中出现的每一天的增量
在上一节中，我们已经了解了如何计算delta。如果上一节中的整个查询都有别名，那么您可以使用左联接对其进行自联接，其中有对(前一日期、当前日期)，仍然有日期间隔，但现在还不用担心。在第一个日期的情况下，其activity是delta。对于后续记录，将前一天的delta添加到它们的delta中可以得到所需的结果。为了实现这一点，您可以使用MySQL 8支持的递归查询，或者，您可以只使用一个子查询来求和前几天的delta(如前所述，特别注意第一个日期)，并添加当前日期的delta来获得我们需要的结果。
填补空白
上一节已经很好地工作了(假设没有完整性问题)，假设每天都有活动变化，但我们不会继续假设。在这里，我们知道存在数字的每个日期的数字都是正确的，我们只需要将缺失的日期添加到结果中。如果结果的顺序正确，那么可以使用光标并循环结果。在第一个记录之后的每个记录中，我们可以确定丢失的日期。在两个或多个后续日期之间可能有0个这样的日期。我们所知道的差距是，它们的值与之前的记录完全相同，确实有数据。如果在给定日期没有活动更改，则活动用户的数量与前一天完全相同。使用一些结构，比如表格，你可以根据这里描述的知识生成你所拥有的结果。
解决可能的完整性问题
这种问题有几种可能性：
首先，一个数据项可能在引入该表之前就已经存在。该表的记录开始派生。
其次，错误或任何其他原因可能会暂停为该活动表创建记录。
第三，用户的添加不一定会产生活动变化，因为它的突然出现使其先前的活动状态不确定，并受制于人类标准，而人类标准可能会随着时间的推移而变化。
第四，用户的删除不一定会产生活动变化，因为它的突然出现会导致当前的活动状态未定义，并受人类标准的约束，而人类标准可能会随着时间的推移而变化。
第五，还有无数其他问题可能会导致数据完整性问题。
为了应对这些问题，您需要全面分析源代码和项目历史记录中的任何内容，包括数据库记录、日志和可供人类使用的信息，以检测这些异常，它们有效的时间，并找出它们的解决方案(如果存在)。
编辑
与此同时，我在考虑一个用户的可能性，他在一天开始时很活跃，但在一天结束时又被激活了。类似地，一天中不活跃的用户可能会被激活，然后在一天结束时最终被停用。对于在一天开始时有不止一次激活的用户，我们需要比较他们在一天结束时的活动状态，以找出差异。
SELECT
DATE(DATETIME),
COUNT(*)
FROM your_table
WHERE New_Status = 1
GROUP BY User_ID,
DATE(DATETIME)
对于MySQL
WITH RECURSIVE
cte AS (
SELECT MIN(DATE(DT)) dt
FROM src
UNION ALL
SELECT dt + INTERVAL 1 DAY
FROM cte
WHERE dt < ( SELECT MAX(DATE(DT)) dt
FROM src )
),
cte2 AS
(
SELECT users.id,
cte.dt,
SUM( CASE src.New_Status WHEN 1 THEN 1
WHEN 0 THEN -1
ELSE 0
END ) OVER ( PARTITION BY users.id
ORDER BY cte.dt ) status
FROM cte
CROSS JOIN ( SELECT DISTINCT id
FROM src ) users
LEFT JOIN src ON src.id = users.id
AND DATE(src.dt) = cte.dt
)
SELECT dt, SUM(status)
FROM cte2
GROUP BY dt;
小提琴
不要忘记调整最大递归深度。
我认为以下是解决您的这个问题的好方法：
SELECT SUM(New_Status) "Number of active users"
, DATE_FORMAT(DATEC, '%Y-%m-%d') "Date"
FROM TEST T1
WHERE DATE_FORMAT(DATEC,'%H:%i:%s') =
(SELECT MAX(DATE_FORMAT(T2.DATEC,'%H:%i:%s'))
FROM TEST T2
WHERE T2.ID = T1.ID
AND DATE_FORMAT(T1.DATEC, '%Y-%m-%d') = DATE_FORMAT(T2.DATEC, '%Y-%m-%d')
GROUP BY ID
, DATE_FORMAT(DATEC, '%Y-%m-%d'))
GROUP BY DATE_FORMAT(DATEC, '%Y-%m-%d');
这是演示