SQL查询求助：统计从当前日期起的连续捐赠年数

问题背景 #

我有一张按donor_account_id记录donation_date的捐赠数据表，需要统计每位捐赠者的连续捐赠年数。现有SQL仅能统计当年有捐赠记录的用户（如ID123），但当前年份尚未结束，还需纳入上一年有捐赠记录的用户（如ID456），然而现有代码无法查询到ID456。

测试数据集 #

WITH DONATIONS AS 
(
    SELECT 123 AS donor_account_id, DATE '2022-10-10' AS donation_date        
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2021-10-10' AS donation_date    
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2020-09-10' AS donation_date    
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2020-10-10' AS donation_date    
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2019-10-10' AS donation_date    
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2017-10-10' AS donation_date    
    UNION ALL  -- 不纳入连续统计
    SELECT 456 AS donor_account_id, DATE '2021-10-10' AS donation_date    
    UNION ALL
    SELECT 456 AS donor_account_id, DATE '2020-10-10' AS donation_date    
    UNION ALL
    SELECT 456 AS donor_account_id, DATE '2020-01-10' AS donation_date    
    UNION ALL
    SELECT 456 AS donor_account_id, DATE '2019-10-10' AS donation_date    
    UNION ALL
    SELECT 456 AS donor_account_id, DATE '2019-01-10' AS donation_date    
    UNION ALL
    SELECT 789 AS donor_account_id, DATE '2020-10-10' AS donation_date    
    UNION ALL -- 不纳入连续统计
    SELECT 789 AS donor_account_id, DATE '2019-10-10' AS donation_date                
    -- 不纳入连续统计
)

现有代码（存在问题） #

SELECT 
    CY.ID AS id, COUNT(CY.YEAR) AS cont_years
FROM 
    (SELECT 
         DY.ID, DY.YEAR, 
         RANK() OVER (PARTITION BY DY.ID ORDER BY DY.YEAR DESC) AS CT,  
         (YEAR + RANK() OVER (PARTITION BY DY.ID ORDER BY DY.YEAR DESC)) AS FLAG
     FROM 
         (SELECT DISTINCT 
              D.donor_account_id ID, 
              EXTRACT(YEAR FROM D.donation_date) YEAR
          FROM 
              DONATIONS D
          GROUP BY 
              1, 2) DY
     ORDER BY 
         1, 2 DESC) CY
  WHERE 
      CY.FLAG = EXTRACT(YEAR FROM CURRENT_DATE) + 1
  GROUP BY 
      1

问题分析 #

现有代码的过滤条件CY.FLAG = EXTRACT(YEAR FROM CURRENT_DATE) + 1仅匹配当年有捐赠的用户：假设当前年份是2022，只有ID123的FLAG会等于2023（2022+1）。而ID456的最近捐赠年份是2021，它的FLAG最大值是2021+1=2022，无法满足等于2023的条件，因此被过滤掉。

我们需要调整逻辑，让连续统计的终点可以是当前年份或者上一年，同时确保年份是连续的。

修正后的代码 #

WITH DONATIONS AS 
(
    -- 测试数据集，实际使用时替换为你的数据表
    SELECT 123 AS donor_account_id, DATE '2022-10-10' AS donation_date        
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2021-10-10' AS donation_date    
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2020-09-10' AS donation_date    
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2020-10-10' AS donation_date    
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2019-10-10' AS donation_date    
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2017-10-10' AS donation_date    
    UNION ALL  -- 不纳入连续统计
    SELECT 456 AS donor_account_id, DATE '2021-10-10' AS donation_date    
    UNION ALL
    SELECT 456 AS donor_account_id, DATE '2020-10-10' AS donation_date    
    UNION ALL
    SELECT 456 AS donor_account_id, DATE '2020-01-10' AS donation_date    
    UNION ALL
    SELECT 456 AS donor_account_id, DATE '2019-10-10' AS donation_date    
    UNION ALL
    SELECT 456 AS donor_account_id, DATE '2019-01-10' AS donation_date    
    UNION ALL
    SELECT 789 AS donor_account_id, DATE '2020-10-10' AS donation_date    
    UNION ALL -- 不纳入连续统计
    SELECT 789 AS donor_account_id, DATE '2019-10-10' AS donation_date                
    -- 不纳入连续统计
),
-- 步骤1：去重，得到每个捐赠者的唯一捐赠年份
donor_years AS (
    SELECT DISTINCT
        donor_account_id AS id,
        EXTRACT(YEAR FROM donation_date) AS year
    FROM DONATIONS
),
-- 步骤2：生成连续年份的分组标识（连续年份的group_id相同）
year_groups AS (
    SELECT
        id,
        year,
        year - ROW_NUMBER() OVER (PARTITION BY id ORDER BY year ASC) AS group_id
    FROM donor_years
),
-- 步骤3：筛选最新的连续分组并统计年数
latest_continuous AS (
    SELECT
        id,
        COUNT(*) AS cont_years
    FROM year_groups
    WHERE 
        -- 仅保留每个用户最新的连续分组
        group_id = (SELECT MAX(group_id) FROM year_groups y WHERE y.id = year_groups.id)
        -- 保留连续到当前年或上一年的用户
        AND year >= EXTRACT(YEAR FROM CURRENT_DATE) - 1
    GROUP BY id
)
SELECT id, cont_years FROM latest_continuous;

结果验证 #

执行修正后的代码，会得到期望结果：

逻辑说明 #

1. donor_years：去重处理，避免同一年多次捐赠重复计数。
2. year_groups：通过年份 - 升序行号生成分组标识，连续的年份会得到相同的group_id，以此区分非连续的年份段。
3. latest_continuous：筛选每个用户最新的连续年份段，同时过滤出连续到当前年或上一年的用户，最后统计连续年数。

内容的提问来源于stack exchange，提问作者Alen Giliana