需要重写此查询而不使用相关子查询

Question

我知道correlated subqueries是如何工作的，通常都会避开他们。。但是对于我试图做的事情，我找不到其他方法来编写这个查询。我可以使用它，因为我使用的表中没有一堆记录。。但是现在我需要对一些表执行相同的操作，这些表中有超过100000条记录，这将使此查询花费很长时间。所以我需要一些帮助来重写这个查询。你知道吗

初始查询：

SELECT
DATE(m.Created_At) AS m_date,
COUNT(m.id) AS daily_count,
(
    SELECT
        COUNT(m1.id)
    FROM members_joined m1
    WHERE m1.has_verified = 1 and DATE(m1.Created_At) <= m_date
) AS member_totals
FROM members_joined AS m
WHERE m.has_verified = 1 and m.Created_At BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
GROUP BY m_date;

此查询所做的是获取30天前的注册成员总数。。在过去的30天里，每天都要重新计算总数

m_date      daily_count member_totals
2014-05-27      4           619
2014-05-29      1           620
2014-05-30      3           623
2014-06-02      4           627
2014-06-03      7           634
2014-06-04      10          644
2014-06-05      12          656
2014-06-06      4           660
2014-06-07      3           663
2014-06-08      3           666
2014-06-09      3           669
2014-06-10      5           674
2014-06-11      3           677

如果您注意到它正在按每天的计数递增。你知道吗

这个查询本身只需要0.036秒就可以运行，这不是什么大问题

但在一些更大的桌子上，它已经需要12秒，而且它们只会变得更大。你知道吗

注意：在不使用相关子查询的情况下是否可以执行此操作？假设您不能使用用户定义的变量。你知道吗

如果这不能在mysql中完成，我可以在python中对数据库执行两次操作，但是我希望在mysql中找到一个解决方案，而不是多次对数据库执行操作。你知道吗

谢谢你的指点/帮助！你知道吗

Answer 1

这很难用纯SQL高效地完成，但是您可以使用MySQL特定的用户定义变量。您只需要计算每天的计数，并累积每行的计数。你知道吗

主要的技巧是强制MySQL按正确的顺序更新变量。你知道吗

查询应如下所示（不确定是否100%正确）：

SELECT m_date, daily_count, @count:=(daily_count + @count) as member_totals
FROM
(SELECT DATE(Created_At) as m_date, COUNT(*) as daily_count
 FROM members_joined
 WHERE DATE(Created_At) >= CURDATE() - INTERVAL 30 DAY AND has_verified = 1
 GROUP BY m_date
) as days,
(SELECT @count:=COUNT(*) as cnt0
 FROM members_joined
 WHERE DATE(Created_At) < CURDATE() - INTERVAL 30 DAY 
   AND has_verified = 1) as init
ORDER BY m_date;

Answer 2

编辑：修复查询中的错误

EDIT2：python方法的提出

你可以用一个显式的自动连接重写它，但我不认为它会更快

SELECT
DATE(m.Created_At) AS m_date,
COUNT(m.id) AS daily_count,
COUNT(m1.id) AS member_totals
FROM members_joined AS m
JOIN members_joined m1 ON m1.has_verified = 1 and DATE(m1.Created_At) <= DATE(m.Created_At) 
WHERE m.has_verified = 1 and m.Created_At BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
GROUP BY m_date;

您要求数据库获得二次行数。你知道吗

由于autojoin不能提高性能，我认为应该从数据库中进行两个简单的查询，然后用Python进行求和。你知道吗

SELECT
DATE(m.Created_At) AS m_date,
COUNT(m.id) AS daily_count,
FROM members_joined AS m
WHERE m.has_verified = 1 and m.Created_At BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
GROUP BY m_date;

获取过去30天内每个日期的每日\u计数，以及

SELECT
CURDATE() - INTERVAL 30 DAY AS m_date,
COUNT(m.id) AS member_totals,
FROM members_joined AS m
WHERE m.has_verified = 1 and m.Created_At < CURDATE() - INTERVAL 30 DAY;

获取第一个查询前一天的累积成员总数

Python伪代码，比如说第二个查询初始化了member_total，然后第一个查询初始化了行（可以是一个游标，也可以是由fetchall获得的列表或元组）

for row in rows:
    dat, daily_count = row
    member_totals += daily_count
    # use dat, daily_count, member_totals