mysql - 一个我不知道怎么写的SQL查询(aMember wordpress插件)

Question

我目前正在开发一个使用 aMember 来管理 WordPress 网站付款的网站，以实现付费专区。我不是设置它的管理员，我仍在学习他们的数据库的方法。另外，我的 SQL fu 很弱；我不是一名受过培训的工程师，但我是组织中唯一完全了解 SQL 的人。

我的首席财务官想要一份报告，详细说明两年前的每日新注册数量。据我所知，aMember 对此没有简单的分割。它所拥有的是我们收到的每笔付款的完整记录。

我可以轻松地按日期分割此付款记录，以查看在给定日期我们收到了多少笔付款。问题是，其中许多付款来自现有用户。此外，我们还有已订阅、取消然后重新订阅的用户。我们的阈值是 30 天，因为我们认为这是“完全失效”。因此，我们希望忽略付款在 [日期] 后 30 天内过期的用户在 [日期] 进行的任何付款。

示例:John Smith 于 2012 年 12 月 1 日订阅。 John Smith 的最后一次付款已于 2013 年 1 月 1 日到期。约翰的帐户没有续订。 John 于 2013 年 1 月 5 日重新订阅。 John 的付款应在 2012 年 12 月 1 日算作"new"付款(因为他之前没有付款记录)，但在 2013 年 1 月 5 日则不然(因为他的付款在 1 月 1 日到期，即 30 天内到期)。 1/5 付款)。

架构如下。当前的查询不会过滤掉现有的成员(member)续订，而只是收到的付款计数，如下所示。如果需要任何其他信息，或者有人对成员(member)的了解超出了我的了解，请让我知道我还可以提供什么来帮助回答。我已尽力而为!

SELECT begin_date, COUNT( payment_id ) 
FROM  [foo] 
WHERE amount >0
GROUP BY begin_date
ORDER BY begin_date DESC 

payment_id
member_id
product_id
begin_date
expire_date
paysys_id
receipt_id
amount
completed
remote_addr
data 
time

Answer 1

我想这样就可以了。它可能需要一些调整。我不确定 amount > 0 的具体用途，但它也包含在子查询中。

针对一些良好的测试数据运行它以确保它产生正确的结果可能是一个好主意。

SELECT begin_date, COUNT( payment_id ) 
FROM  [foo] f1
WHERE amount > 0
AND 
-- don't count it if there's another subscription from this member that expires between the 30 days previous to this one and the end of this one.
(select count(expire_date) from [foo] f2 where f1.member_id = f2.member_id and 
    f2.expire_date BETWEEN DATE_ADD(f1.begin_date, INTERVAL -30 day) and f1.expire_date 
    and f1.payment_id <> f2.payment_id and amount > 0) = 0
GROUP BY begin_date
ORDER BY begin_date DESC