查找过去10天在mysql中出现的记录

Question

考虑mysql表，

user_event| created_at
1          2021-12-03
2          2021-12-03
3          2021-12-03
3          2021-12-01
1          2021-11-28
2          2021-11-10
2          2021-11-01
1          2021-11-01

我正在尝试查找按created_at分组的用户数量&如果在过去10天中有一个事件，重复用户的计数

预期结果将是：

created_at | total_users | repeat_users
2021-12-03    3            2
2021-12-02    0            0
2021-12-01    1            0
.
.
2021-11-28    1            0
.
.
2021-11-10    1            1
2021-11-01    2            0

基本上，user_event 1, 2 ,3在2021~12-03有活性，所以total_users是3.2，因此在过去10天中有活性，repeat_users应该是2。同样，user_event 3在2021-12-01存在，但在最后10天没有活性，所以repeat_users是0，等等。

Answer 1

给出你的数据表

CREATE TABLE MyData
(
    user_event INT,
    created_at DATE
);

INSERT INTO MyData
VALUES
(1,'2021-12-03'),
(2,'2021-12-03'),
(3,'2021-12-03'),
(3,'2021-12-01'),
(1,'2021-11-28'),
(2,'2021-11-10'),
(2,'2021-11-01'),
(1,'2021-11-01')

您可以使用一个日历表来解决这个问题，该表可以如下所示(您可能应该在将来填充日期)

delimiter //

CREATE PROCEDURE CreateDateTable ()
BEGIN
    SET @StartDate = CAST('2021-01-01' AS DATE);
    SET @EndDate = CAST('2021-12-31' AS DATE);

    CREATE TABLE Calendar
    (
        DateValue DATE
    );

    WHILE @StartDate <= @EndDate DO
        INSERT INTO Calendar 
        (
            DateValue
        ) 
        VALUES 
        (
        @StartDate
        );
        
        SET @StartDate = DATE_ADD(@StartDate, INTERVAL 1 DAY);
    END WHILE;
END//

CALL CreateDateTable

DROP PROCEDURE CreateDateTable

一旦有了日历表，就可以选择感兴趣的日期，并使用COUNT / GROUP组合来计算当天的用户事件数，然后加入到一个子查询中，该子查询使用self join查找重复事件的数量：

SELECT  c.DateValue,
        COUNT(d.user_event) AS total_users,
        IFNULL(r.NumRepeats,0) AS repeat_users
FROM    Calendar c
        LEFT JOIN MyData d
            ON c.DateValue = d.created_at
        LEFT JOIN ( 
                        /* repeats */
                        SELECT  a.created_at,
                                COUNT(a.user_event) AS NumRepeats
                        FROM    MyData a
                                LEFT JOIN MyData b
                                    ON b.created_at > DATE_ADD(a.created_at, INTERVAL -10 DAY) AND
                                        b.created_at < a.created_at AND
                                        a.user_event = b.user_event
                        WHERE   b.user_event IS NOT NULL
                        GROUP BY a.created_at
                    ) r
            ON r.created_at = c.DateValue
WHERE   c.DateValue >= '2021-11-01' AND
        c.DateValue <= '2021-12-03'
GROUP BY c.DateValue,
        r.NumRepeats
ORDER BY c.DateValue DESC