sql - 使用 T-SQL 滚动 DAU、MAU

Question

情况:

我有一个登录表，其中包含电子邮件和登录时间列。我想计算每日登录时间(DAU)和每月滚动登录时间(MAU)。 DAU 和 MAU 必须计算不同的用户。即，如果某人在过去 30 天 (MAU) 内登录 20 次，那么他仅被计入一次。 DAU 也遵循同样的逻辑。 MAU 的范围是 30 天。

DAU:通过每天不同的电子邮件登录来计算。

MAU:通过连续 30 天的不同电子邮件登录来计算。

期望的结果: 参见下面的 fiddle

Date         MAU     DAU     
2019-04-01   4       2  
2019-04-02   3       2  
2019-04-03   4       2   

计算 DAU 相当简单，但获取滚动 MAU 却不确定如何。

fiddle :

create table #t1 (email varchar(max), login_time datetime)
insert into #t1 values 
('aa@gmail.com', '2019-03-15 00:00:00.000'),
('aa@gmail.com', '2019-04-01 00:00:00.000'),
('aa@gmail.com', '2019-04-02 00:00:00.000'),
('aa@gmail.com', '2019-04-03 00:00:00.000'),

('bb@gmail.com', '2019-03-19 00:00:00.000'),
('bb@gmail.com', '2019-04-01 00:00:00.000'),
('bb@gmail.com', '2019-04-02 00:00:00.000'),
('bb@gmail.com', '2019-04-02 00:00:00.000'),

('cc@gmail.com', '2019-03-02 00:00:00.000'),
('cc@gmail.com', '2019-04-03 00:00:00.000'),

('dd@gmail.com', '2019-03-06 00:00:00.000')

Answer 1

这是一种方法。

SELECT login_time, 
    m.MAU,
    COUNT(DISTINCT email) AS DAU
FROM #t1 d
CROSS APPLY (SELECT COUNT( DISTINCT email) AS MAU
            FROM #t1 m
            WHERE m.login_time BETWEEN DATEADD( dd, -30, d.login_time) AND d.login_time) m
GROUP BY login_time, m.MAU
ORDER BY login_time;