MySQL - 每天计算唯一用户数，考虑所有先前的日期

Question

我希望统计数据库每天获得的新用户数量，包括所有记录的日期。 每天不会有任何重复的 ID，但是在多个日期上可能存在重复。

如果我的表格长这样：

ID | DATE
---------
1  | 2022-05-21

1  | 2022-05-22
2  | 2022-05-22

1  | 2022-05-23
2  | 2022-05-23

1  | 2022-05-24
2  | 2022-05-24
3  | 2022-05-24

我希望结果看起来像这样：

DATE       | NEW UNIQUE IDs
---------------------------
2022-05-21 | 1
2022-05-22 | 1
2022-05-23 | 0
2022-05-24 | 1

例如以下查询：

SELECT `date` , COUNT( DISTINCT id)
  FROM tbl
  GROUP BY DATE( `date` ) 

将每天的计数返回，并不考虑前几天的数量。

任何援助将不胜感激。

编辑：使用MySQL 8

- Fuzz

---

你使用的是哪个版本？ - Asgar

我正在使用MySQL 8版本。 - Fuzz

Answer 1

从子查询开始，显示每个id出现的最早日期。

            SELECT MIN(`date`) `firstdate`, id
              FROM tbl
             GROUP BY id

然后在子查询中进行计数。 这里。

SELECT firstdate, COUNT(*)
  FROM (
            SELECT MIN(`date`) `firstdate`, id
              FROM tbl
             GROUP BY id
       ) m
 GROUP BY firstdate

这可以帮助你得到想要的内容。

但它没有包含那些没有新用户id首次出现的日期的行。

- O. Jones

Answer 2

只计算（并求和）左连接失败的行：

SELECT 
    m1.`DATE` , 
    sum(CASE WHEN m2.id is null THEN 1 ELSE 0 END) as C
FROM mytable m1
LEFT JOIN mytable m2 ON m2.`DATE`<m1.`DATE` AND m2.ID=m1.ID
GROUP BY m1.`DATE` 

请查看：DBFIDDLE

- Luuk

---

可能在巨大的表格上死掉... - Akina

也许我过于专注于“考虑所有以前的日子”，但在死之前它会变慢...请参见：DBFIDDLE。 - Luuk

是的，但我们都忽略了“每天不会有重复的ID”，因此应该在表定义中添加PRIMARY KEY x(ID,DATE)。 - Luuk

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1f7fb43fcbff47a66b7d06ba8b3a3849 - Akina

MySQL 要死之前需要一些时间，即使将计数增加到 50000，仍然可以在一秒内产生结果，并且 只有 () 慢了六倍 (https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2bb30b9813553f8f96caf8c13ba31399)。 - Luuk

Answer 3

当日期为该用户最小日期时，该用户就是新用户。

因此，您需要进行类似以下的操作。

SELECT date, COUNT(new_users.id)
FROM calendar
LEFT JOIN ( SELECT id, MIN(date) date
            FROM test 
            GROUP BY id ) new_users USING (date)
GROUP BY date

calendar 是静态或动态生成的表格，其中包含所需日期列表。它还可以是SELECT DISTINCT date FROM test 子查询。

- Akina