我有一个名为事务的MySQL表,该表具有5列,即id(int),from(int),to(int),value(float),time(datetime).
而且,我每天需要计算某个特定接收者(“至”)的累积用户(唯一的“来自”的数量).
例如:
+-----+------+-----+-------+----------------------------+
| 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 |
+-----+------+-----+-------+----------------------------+
特定于[223,224,227]
那么预期结果是:
2019-01-01: 1 # [1]
2019-01-02: 3 # [1,2,3]
2019-01-03: 3 # [1,3]
2019-01-04: 3 # [1,3]
2019-01-05: 4 # [1,3,4]
2019-01-05: 5 # [1,4,5]
直接的方法是使用SQL
SELECT COUNT(DISTINCT(`From`))
FROM `transaction`
FORCE INDEX (to_time_from)
WHERE `time` < '2019-01-0X'
AND `to` IN (223,227)
但是问题是,事务表很大(每天100万个,大约2年),列出的数据大约是1000个.即使我创建了[to,time,from]和强制使用它.
此外,尽管每日交易量达到约100万,但每日活动用户仅为10,000.所以我正在考虑将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检索所有DAU列表并进行联合以获取累积用户.像这样的东西:len(set([dau_list1] [dau_list2] [dau_list3] …))
但是我不知道要使用哪个No-SQL.
> Redis会将所有内容加载到内存中,但是查询时我只需要这些数据. > MongoDB
>似乎我需要为每个日期创建一个集合,因为我需要在from上创建一个唯一索引.我对吗? >我知道我可以使用数组字段和$addToSet操作.但这是O(n),非常慢.
那么,什么是正确的方法呢?
最佳答案
在MySQL中,使用类似(no redis,no 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和INSERT来构建测试用例.)
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|