php - 在已售订阅计数期间计算续订

Question

我有一个表格，其中写下了我的杂志的每笔订阅销售，如下所示:

USERID | DATE | 

31 | 2011-09-22 | 
54 | 2011-09-22 | 
59 | 2011-09-23 | 
11 | 2011-10-02 | 
88 | 2011-10-05 |
31 | 2011-10-06 | 
17 | 2011-10-12 | 
54 | 2011-10-15 |
31 | 2011-11-05 |
54 | 2011-11-12 |

现在，出于统计目的，我需要得到一个结果，其中我可以看到每个月我已售出多少订阅以及有多少用户已经购买过一次。

例如，如果我们查看顶部的数据，我应该得到如下结果:

DATE | SOLD | RENEWAL 
 09  | 3    | 0
 10  | 5    | 1
 11  | 2    | 2

我可以对每月销售的订阅进行分组，但无法添加“续订”信息。

  SELECT COUNT( * ) AS  sold, MONTH(date) FROM table_sold WHERE date >= CURDATE() - INTERVAL 13 MONTH GROUP BY YEAR( date ) , MONTH( date ) ORDER BY date ASC LIMIT 1,15

这样我只能得到这样的结果:

 DATE | SOLD
  09  | 3    
  10  | 5
  11  | 2

我尝试了几种选项，包括子查询、联合等，但没有成功。需要考虑的是，该表有 70.000 个条目，使用硬扫描的查询可能会出现问题。

您认为 mysql 有没有办法，或者我必须采取这个想法在 php 中实现它？

Answer 1

这是一个想法。查看每个用户的第一个日期。然后计算除第一个日期之外的每个月的续订:

select year(date), month(date), count(*) as sold,
       sum(case when date <> firstdate then 1 else 0 end) as renewals
from subscriptions s join
     (select userid, min(date) as firstdate
      from subscriptions s
      group by userid
     ) su
     on s.userid = su.userid
group by year(date), month(date)
order by year(date), month(date);