我的问题是我通常没有那么大的数据。我使用的方法在小数据集上取得了一些成功,也许我的ram不够用。我需要在MYSQL处理一个大数据集。如下表所示,包含的数据最大可达10gb。我需要对它做一些具体的分析。
我有两个表,table1如下,id为主键,table2_id为外键
id date aid table2_id message
1 2014-03-19 16:21:02 121 1 login from xxxx
2 2014-03-20 14:00:32 123 2 decrease budget
3 2014-03-20 18:00:32 121 2 increase budget
4 2014-03-21 16:21:02 121 3 login from xxxx
5 2014-03-21 16:21:02 121 3 login from xxxx
6 2014-03-22 10:21:02 121 2 increase budget
table2是一个类型表
id type
1 login
2 change_budget
3 search
我需要分析活动change_budget和其他活动的关系。这是我到目前为止所尝试的:
我试着为每次一个援助change_budget编写SQL,计算该援助在过去24小时内的所有其他行动。如果一个援助在2014-03-14 16:21:02有一个change_budget,我需要从2014-03-13 16:21:02到2014-03-14 16:21:02在这个援助上执行的所有动作。然后,按动作分组并计算我想要的结果应该如下所示:
aid login search change_budget
121 1 0 1
123 0 0 -1
121 0 2 1
-1表示变化预算减少,1表示增加。另一个数字是在这个aid change_budget发生之前的一天内,这个aid的这个动作发生了多少次。
我的问题是我通常没有那么大的数据。如果我把它当作小数据来处理,我的内存就不够用了。所以我需要一个大数据解决方案。任何建议吗?
注意:我已经编辑了这个问题,试图更好地解释这个问题,但它仍然需要原作者的审查,以验证它正确地描述了问题。
好吧,关于这个问题有一些事情是没有意义的…然而,你所付出的努力就是我所做的。
FIRST:你想要的结果是完全不正确的。你说你想在事情发生的那一天完成,但却发布了一个完全不同的预期结果。我冒了一个险,假设你想要按日期发送,因为这是你的要求,所以考虑到这一点,这就是你应该得到的。
+-------------+---------+-----------+-----------+-----------+
|day occurred | aid | logins | searches | budget |
+-------------+---------+-----------+-----------+-----------+
|'2014-03-19' | 121 | 1 | 0 | 0 |
|'2014-03-20' | 123 | 0 | 0 | -1 |
|'2014-03-20' | 121 | 0 | 0 | 1 |
|'2014-03-21' | 121 | 0 | 2 | 0 |
|'2014-03-22' | 121 | 0 | 0 | 1 |
+-------------+---------+-----------+-----------+-----------+
这是从你提供的数据…这是每个AID每天都在发生的事情。
第二:记住这一点,您可以通过几种方式实现此查询。其中最好的方法是创建临时表来进行设置,然后对它们进行简单查询。
设置:
CREATE TABLE logs
(`id` int, `date` datetime, `aid` int, `t2_id` int, `message` varchar(55))
;
INSERT INTO logs
(`id`, `date`, `aid`, `t2_id`, `message`)
VALUES
(1, '2014-03-19 16:21:02', 121, 1, 'login from xxxx'),
(2, '2014-03-20 14:00:32', 123, 2, 'decrease budget'),
(3, '2014-03-20 18:00:32', 121, 2, 'increase budget'),
(4, '2014-03-21 16:21:02', 121, 3, 'login from xxxx'),
(5, '2014-03-21 16:21:02', 121, 3, 'login from xxxx'),
(6, '2014-03-22 10:21:02', 121, 2, 'increase budget')
;
CREATE TABLE log_type
(`id` int, `type` varchar(55))
;
INSERT INTO log_type
(`id`, `type`)
VALUES
(1, 'login'),
(2, 'change_budget'),
(3, 'search');
我称这些表为logs
和log_type
,因为这似乎是这里发生的事情。这些表记录交互。
临时表:
CREATE TEMPORARY TABLE t1
(
SELECT
l.aid,
DATE(date) AS grouping_col,
IF(
lt.type = 'change_budget',
IF(l.message LIKE '%decrease%', -1, 1),
0
) AS changed_budget
FROM logs l
JOIN log_type lt ON lt.id = l.t2_id
GROUP BY grouping_col, changed_budget, aid
);
CREATE TEMPORARY TABLE t2
( SELECT
DATE(l.date) AS grouping_col,
l.aid,
IF(lt.type = 'login', COUNT(l.id), 0) AS logged_in
FROM logs l
JOIN log_type lt ON lt.id = l.t2_id
GROUP BY grouping_col, aid
);
CREATE TEMPORARY TABLE t3
(
SELECT
DATE(l.date) AS grouping_col,
l.aid,
IF(lt.type = 'search', COUNT(l.id), 0) AS searched_for
FROM logs l
JOIN log_type lt ON lt.id = l.t2_id
GROUP BY grouping_col, aid
);
最终查询:最后你需要做的就是查询这些临时表来得到你想要的结果
SELECT
t1.grouping_col as day_occurred,
t1.aid,
logged_in,
searched_for,
changed_budget
FROM t1
JOIN t2 on t2.grouping_col = t1.grouping_col AND t2.aid = t1.aid
JOIN t3 on t3.grouping_col = t1.grouping_col AND t3.aid = t1.aid
;
结果:
我在我的本地主机数据库上设置了这个并运行它,结果是它应该是什么。IMAGE
看dplyr。它明确地允许您像处理数据框架(子集、逻辑操作等)一样处理数据库表,并允许您以sql风格的方式一个接一个地构建语句。
我对这个问题的理解如下:
- 取数据集的date/time字段,提取日期
- 根据提取日期和数据分组
- 分析文本列查找单词"increase/reduction",如果table2_id = 2
- "pivot"结果,因此分组数据在列中,而不是在行中
下面的代码应该解决部分问题,直到步骤4(可能包含语法错误,因为我没有mysql,日期/时间函数在不同的DBMS中是不同的):
select date("date"), aid, table2_id, count(message)
from table1
group by date("date"), aid, table2_id
pivot在MySQL中的结果似乎在以下帖子中得到了回答:MySQL数据透视表
的评论:
10GB并不是很大。要计算这个查询,DBMS将需要(很可能)按分组(这基本上是对前三列执行排序,并对数据再进行一次扫描以进行实际分组)。现实地假设(在本例中)一条记录占用50字节,这意味着一个包含200M记录的表=>排序成本是在10GB上进行19次扫描(这是一个相当悲观的估计),这意味着要扫描和写入190GB的数据。假设我们可以读/写50MB/s,那么整个查询应该花费3800秒(非常悲观的估计)。实际上,我会假设10GB ÷ 50MB/s = 200秒的执行时间
该查询似乎是一个相当简单的数据选择/聚合查询。我建议阅读一本关于SQL的书来了解更多。你可能只需要阅读前几十页就可以了解它
https://dba.stackexchange.com/似乎是一个更适合这些问题的地方。但是考虑到问题和答案的简单性,没有多少人会觉得它足够有趣来回答它:)
我的问题是我通常没有那么大的数据。如果我把它放在和小数据一样,我的内存也不够用。所以我需要一个大数据解决方案。任何建议吗?
dbms是智能系统。它们检查你有多少RAM,并为你的查询寻找快速的解决方案。在您的情况下,如果RAM的数量是M
和M
是远低于10GB,那么DBMS将您的数据剁成N<M
块的数据,在RAM中对这些块进行排序,将其保存到磁盘,并做排序合并之后,导致更快的解决方案