mysql - 如何根据上个月的付款情况获取本月停止付款的客户列表？

Question

我有一个具有以下结构的表:

payments
 - id
 - created
 - amount
 - customer_id

该表包含如下值:

'2016-12-03', 100, 1,
'2016-12-10',  50, 2,
'2016-12-23', 130, 3,

'2017-01-03', 100, 1,
'2017-01-11',  25, 4,
'2017-01-12',  50, 2,
'2017-01-24',  10, 3,
'2017-01-24',  25, 4,

'2017-02-03', 100, 1,
'2017-02-03',  25, 4,

'2017-03-03', 100, 1

(为了更好地查看数据，我按月间隔排列了数据)

考虑到这些数据，我正在尝试构建一个 MySQL 查询，该查询将返回每月仅支付一次(又称订阅)(至少两个月)但上个月未付款的客户(截至今天 2017 年 2 月 3 日，但 MySQL 中的 (UTC_DATE() - INTERVAL 1 MONTH)(因为 3 月尚未结束)。

使用上述数据，它将返回 customer_id 2 和 3，但我不知道如何做到这一点。

请注意，我还尝试在滚动月份的基础上进行此操作(三月包括到今天)

如果可能有帮助的话，全局的想法是吸引那些已经停止每月定期付款的客户。

我做了一个SQLFiddle来帮忙。

Answer 1

好的，我将从部分答案开始...前几个月每月至少付款 1 次的客户...

SELECT customer_id
  FROM payments 
 WHERE created < DATE_FORMAT(NOW() ,'%Y-%m-01')
 GROUP
    BY customer_id
HAVING COUNT(DISTINCT DATE_FORMAT(created,'%Y-%m')) > 1;

...通过这个核心查询，我们可以将上个月的付款外部联接到该查询上，如下所示...

SELECT a.*
  FROM 
     ( SELECT customer_id
         FROM payments 
        WHERE created < DATE_FORMAT(NOW() ,'%Y-%m-01')
        GROUP
           BY customer_id
       HAVING COUNT(DISTINCT DATE_FORMAT(created,'%Y-%m')) > 1
     ) a
  LEFT
  JOIN payments b
    ON b.customer_id = a.customer_id
   AND b.created BETWEEN DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 1 MONTH AND DATE_FORMAT(NOW(),'%Y-%m-01')
 WHERE b.id IS NULL;

顺便说一句，复杂的日期比较是经过深思熟虑的。以这种方式编写它们允许 MySQL 使用 created 上的索引(如果提供了)