mysql - 具有按日期分组的回溯窗口的 SQL 非重复计数

Question

试图找到运行具有按天计数的查询以及具有最近 x 天计数的列的最佳方法，但它变得比我意识到的更复杂。

我正在尝试以下方法:

SELECT
date(created_at),
count(DISTINCT(user_id)) AS daily_users,
count(
    DISTINCT (
        CASE
        WHEN date(created_at) BETWEEN date_sub(created_at, INTERVAL 2 DAY)
        AND created_at THEN
            user_id
        END
    )
) AS users_2_day_lookback
FROM
table.users
WHERE
date(created_at) BETWEEN 'yyyy-mm-dd'
AND now()
GROUP BY
1

但 2 天回顾栏没有返回任何内容。我觉得这应该通过某种可能涉及自连接的外部查询来完成，但我不确定该语法是什么样的。任何帮助将不胜感激。

Answer 1

按天查询:

select date(created_at),
       count(distinct user_id) as daily_users
from table.users u
group by date(created_at);

两天的回顾是这样的:

select dte, daily_users,
       (select count(distinct u2.user_id)
        from table.users u2
        where u2.created_at >= date_sub(dte, interval 2 days) and
              u2.created_at < date_add(dte, interval 1 day)
       ) as users_2_day_lookback
from (select date(created_at) as dte,
             count(distinct user_id) as daily_users
      from table.users u
      group by date(created_at)
      ) u;

此查询将“2 天回顾”解释为当天加上前两天。更改替代定义的 where 条件非常容易。