mysql - 如何在 MySQL 中对日期进行聚合计数

Question

我正在尝试在 MySQL 中进行累积聚合。

我的原始数据如下所示:(我的日期是月/日/年)

user_id    created_at    source
1          1/1/01        foo
2          1/1/01        foo
3          1/2/01        bar
4          1/2/01        foo
5          1/3/01        foo
     ....

鉴于此，我想得到如下所示的结果:

date    source    total_in_source
1/1/01  foo       2
1/2/01  foo       3
1/3/01  foo       4

其中total_in_source是第一个日期值和当前日期值之间创建的用户数的总和。

我可以使用相关子查询来做到这一点，但这有点低效:

select 
date(user.created_at) d,
user.source as user_source,
(select count(*) from users u where u.source = user_source and month(u.created_at) <= month_joined) as total_users_source,
from users user group by d, user_source

我环顾四周，似乎这样做可能是朝着正确方向迈出的一步:

select date(u1.created_at) as 'd',
u1.source as 'source', 
count(distinct u2.id) as count_users
from users u1, users u2
where
u2.created_at BETWEEN DATE(u1.created_at) AND DATE_ADD(DATE(u1.created_at), interval 1 day) and
group by d, source

但即使这是一个自连接，也会生成 n^2 行进行迭代(用户 * 用户)。是否有关于如何执行此操作的最佳实践？

提前致谢。

Answer 1

您需要“人工创建”开始日期和结束日期以在临时表中或直接内联中加入。

假设您希望开始、结束月份以及该时间范围内的每个组合按来源进行分割，并按照您的描述进行计数。你可以这样做:

select u.source, sdates.val start, edates.val end, count(*) from (select unique(date(created_at)) val from users where day(created_at) = 1) sdates join (select unique(date(date)) (created_at)) val from users where day(created_at) = 1) edates 在 sdates.val 和 edates.val 之间通过 sdates.val 和 edates.val group by sdates.val、edates.val、u.source 在 u.created_at 上加入用户 u；

基本上，内部查询“为您提供日期”，而外部查询实际上进行计算。拥有一个“日期”表会很有帮助，其中每个与项目相关的日期都有一行，这样您就可以轻松地加入它，而不必执行此巫术，但在没有该表的情况下，这似乎可行。

此外，您可能不需要开始和结束的每个组合，因此您可以使用内部查询的“where”条件或连接的“on”来满足您的需求。

好奇是否有人有更好的解决方案。