python - 需要在不使用相关子查询的情况下重写此查询

Question

我知道 correlated subqueries 是如何工作的，并且通常会避开它们。但是对于我想要做的事情，我找不到另一种方法来编写这个查询。我可以使用它，因为我在使用的表中没有一堆记录..但是现在我需要在一些表上做同样的事情，其中​​有超过 100,000 条记录，这将使这个查询花费很长时间时间。所以我需要一些帮助来重写这个查询。

初始查询:

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 天前的注册成员(member)总数.. 然后在过去 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

如果您注意到它正在增加计数每个每日计数。

这个查询本身只需要 .036 秒来运行，这没什么大不了的

但在一些更大的 table 上，它已经需要 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;