mysql - 如何编写查询来获取新捐赠者、第二年捐赠者……？

Question

我想从表格中计算每年新捐赠者、第二年捐赠者(连续 2 年)、多年捐赠者(>= 连续 3 年)、失效捐赠者和重新激活的捐赠者(今年再次捐赠的失效捐赠者)。我不确定这在一个查询中是否真的可能。但是，即使我必须为每个分割市场编写一个查询，也必须有更好的方法来运行每年的查询。

为了简单起见，我们假设表捐赠仅包含donor_id和捐赠年份

donor_id      date_year
1             2003
1             2004
2             2003
2             2005
2             2006
2             2007
3             2005

这就是我最后想知道的:

year  new_donors  second_year_donors  multi_year_donors  lapsed_donors  reactivated_donors
2003  2           0                   0                  0              0
2004  0           1                   0                  1              0
2005  1           0                   0                  1              1

我可以为新捐助者编写查询:

SELECT d.first_year AS 'year',
       COUNT(d.id) AS 'new_donors'
  FROM (SELECT donor_id AS 'id', 
               MIN(date_year) AS 'first_year'
          FROM donations
         GROUP BY donor_id) d      
 GROUP BY year
 ORDER BY year ASC;

但是，我不知道如何编写一个优雅的查询来统计所有连续两年但不超过两年捐赠的捐赠者。我非常确定一定有比每年运行此查询更好的方法。

SELECT COUNT(DISTINCT d.donor_id) AS 'second_year_donors'
  FROM donations d
 WHERE year = 2005
   AND d.donor_id IN (
                      SELECT donor_id   
                        FROM donations 
                       WHERE year = 2004
                     )
   AND d.donor_id NOT IN (
                          SELECT donor_id
                            FROM donations
                           WHERE year = 2003
                         );

Answer 1

以下是一些可帮助您入门的查询。我没有进行严格的测试，但您应该能够稍微调整这些以提出对 new_donors 和 lapsed_donors 的查询:

select this.donor_year, count(*) as second_year_donors
from donations as this
join donations as prev on this.donor_year - 1 = prev.donor_year and this.donor_id = prev.donor_id
group by this.donor_year;

select this.donor_year, count(*) as third_year_donors
from donations as this
join donations as prev on this.donor_year - 1 = prev.donor_year and this.donor_id = prev.donor_id
join donations as prev2 on this.donor_year - 2 = prev2.donor_year  and this.donor_id = prev2.donor_id
group by this.donor_year;

select this.donor_year, count(distinct this.donor_id) as reactivated_donors
from donations as this
left join donations as prev on this.donor_year - 1 = prev.donor_year and this.donor_id = prev.donor_id
join donations as past on this.donor_year - 1 > past.donor_year and this.donor_id = past.donor_id
where prev.donor_id is null
group by this.donor_year;