计算每天累计用户的正确方法是什么？

我有一个 MySQL 表，命名为 transaction ，共有5列， id(int), from(int), to(int), value(float), time(datetime) .
我需要计算累计用户数 (the number of unique "from") 对于某些特定的接收器 ("to") 每天。
例如：

+-----+------+-----+-------+----------------------------+
| id  | from | to  | value | time                       |
+-----+------+-----+-------+----------------------------+
| 1   |  1   | 223 |     1 | 2019-01-01 01:11:30.000000 |
| 2   |  1   | 224 |     2 | 2019-01-01 21:37:30.000000 |
| 3   |  2   |  25 |   0.1 | 2019-01-02 03:05:30.000000 |
| 4   |  2   | 223 |   0.2 | 2019-01-02 13:26:30.000000 |
| 5   |  3   |  26 |     3 | 2019-01-02 19:29:30.000000 |
| 6   |  3   | 227 |     4 | 2019-01-03 21:37:30.000000 |
| 7   |  1   | 224 |     5 | 2019-01-05 22:03:30.000000 |
| 8   |  4   | 224 |     1 | 2019-01-05 23:48:30.000000 |
| 9   |  5   | 223 |     2 | 2019-01-06 05:41:30.000000 |
| 10  |  6   |  28 |     2 | 2019-01-06 20:19:30.000000 |
+-----+------+-----+-------+----------------------------+

以及具体的 to 是 [223, 224, 227] 那么预期结果是：

2019-01-01: 1 # [1]
2019-01-02: 3 # [1, 2, 3]
2019-01-03: 3 # [1, 2, 3]
2019-01-04: 3 # [1, 2, 3]
2019-01-05: 4 # [1, 2, 3, 4]
2019-01-05: 5 # [1, 2, 3, 4, 5]

直接的方法是使用 SQL ```
SELECT COUNT(DISTINCT(From))
FROM transaction
FORCE INDEX (to_time_from)
WHERE time < '2019-01-0X'
AND to IN (223, 224, 227)

但问题是， `transaction` table很大（每天100万，大约2年），而且 `to` 列表是关于 `1000` . 以上 `SQL` 非常慢，尽管我已经在上创建了索引 `[to, time, from]` 并强行使用它。
此外，虽然日交易量达到100万左右，但日活跃用户只有1万左右。所以我考虑把dau列表存储在 `No-SQL` ，就像

2019-01-01: [1]
2019-01-02: [2, 3]
2019-01-03: [3]
2019-01-04: []
2019-01-05: [1, 4]
2019-01-05: [5]

当有约会的时候 `d` ，我只是在不迟于 `d` 并建立一个联合，得到累计用户。比如： `len(set([dau_list1]+[dau_list2]+[dau_list3]...))` 但我不知道是哪个 `No-SQL` 使用。 `Redis` 将所有内容加载到内存中，但我只需要在查询时使用这些数据。 `MongoDB` 似乎我需要为每个日期创建一个集合，因为我需要在每个日期上创建一个唯一的索引 `from` . 我说得对吗？
我知道我可以使用数组字段 `$addToSet` 操作。但确实如此 `O(n)` ，非常慢。
那么，正确的制作方法是什么呢？

在mysql中，可以使用（没有redis，没有mongodb）：

SELECT  DATE(`time`),
        COUNT(*),
        GROUP_CONCAT(`from`)
    FROM  tbl
    WHERE  `to` IN (...)
    GROUP BY  1;    -- shorthand for "DATE(time)"

INDEX(`to`, `from`, `time`)  -- if applying to entire table
INDEX(`to`, `time`, `from`)  -- if you have `AND time ...`

加上一些格式(这可以用一个杂乱的 CONCAT ，或留给应用程序代码。）
由于这似乎也是一个“缩放”问题，也许您需要一个“摘要表”，它每天都用前一天的条目更新，从而使查询速度更快。

CREATE TABLE Daily (
    `day` DATE NOT NULL,
    `from` ... NOT NULL,
    `to` ... NOT NULL,
    `ct` SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY(`to`, `day`, `from`)
) ENGINE=InnoDB;

查询变成

SELECT  `day`,
        SUM(ct),
        GROUP_CONCAT(DISTINCT `from`)
    FROM Daily
    WHERE  `to` IN (...)`
    GROUP BY `day`;

（它可以帮助您提供 CREATE TABLE 以及 INSERTs 从中生成测试用例。）

计算每天累计用户的正确方法是什么？

1条答案

相关问题

热门标签

最新问答