<p>编辑:修复查询中的错误</p>
<p>EDIT2:python方法的提出</p>
<p>你可以用一个显式的自动连接重写它,但我不认为它会更快</p>
<pre><code>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;
</code></pre>
<p>您要求数据库获得二次行数。你知道吗</p>
<p>由于autojoin不能提高性能,我认为应该从数据库中进行两个简单的查询,然后用Python进行求和。你知道吗</p>
<pre><code>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;
</code></pre>
<p>获取过去30天内每个日期的每日\u计数,以及</p>
<pre><code>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;
</code></pre>
<p>获取第一个查询前一天的累积成员总数</p>
<p>Python伪代码,比如说第二个查询初始化了<code>member_total</code>,然后第一个查询初始化了行(可以是一个游标,也可以是由<code>fetchall</code>获得的列表或元组)</p>
<pre><code>for row in rows:
dat, daily_count = row
member_totals += daily_count
# use dat, daily_count, member_totals
</code></pre>