如何基于订阅年限重复统计记录，生成年度订阅统计报表

要实现多年订阅客户在生效每一年的重复统计，核心是把单条订阅记录拆分成它覆盖的每一年的记录，再按年份和PUB分组统计。以下是具体实现步骤和代码：

步骤1：生成年份序列 #

用递归CTE生成需要统计的年份范围（比如从2013年到当前年份，也可根据数据的最大到期年调整），确保覆盖所有订阅涉及的年份。

步骤2：关联订阅数据与年份表 #

将年份表和原订阅表关联，筛选出年份落在订阅开始年到订阅到期年之间的组合，这样每条多年订阅就会对应多个年份记录。

步骤3：按年份和PUB分组统计 #

对拆分后的记录，按PUB_CDE和年份分组，计算客户数、总销售额、平均销售额。

修改后的SQL代码 #

-- 生成年份序列的递归CTE
WITH YearRange AS (
    SELECT 2013 AS ReportYear  -- 起始年份，和原WHERE条件一致
    UNION ALL
    SELECT ReportYear + 1
    FROM YearRange
    WHERE ReportYear + 1 <= YEAR(GETDATE())  -- 终止年份，可改为数据中最大的exp_dte年份
),
-- 拆分订阅记录到对应年份
SubscriptionsByYear AS (
    SELECT
        dtl.CTM_NBR,
        dtl.PUB_CDE,
        yr.ReportYear,
        dtl.tot_liab,
        -- 如果需要将总金额分摊到每年，取消注释下方这行
        -- dtl.tot_liab / (YEAR(dtl.exp_dte) - YEAR(dtl.strt_DTE) + 1) AS AnnualSales,
        YEAR(dtl.strt_DTE) AS StartYear,
        YEAR(dtl.exp_dte) AS ExpYear
    FROM CIRSUB_M dtl
    JOIN YearRange yr
        ON yr.ReportYear BETWEEN YEAR(dtl.strt_DTE) AND YEAR(dtl.exp_dte)
    WHERE YEAR(dtl.STRT_DTE) >= 2013
        AND dtl.PUB_CDE IN ('MEN')
        AND dtl.CRC_STS IN ('r','w','p') 
        AND dtl.TERM > 6 
        AND YEAR(dtl.CTG_DTE) > 2012
        AND dtl.CTM_NBR NOT IN (
            SELECT ord.CTM_NBR 
            FROM PROOLN_M oln
            INNER JOIN PROORD_M ord 
                ON ord.ORD_NUM = oln.ORD_NUM
                AND YEAR(ord.CTG_DTE) = YEAR(dtl.CTG_DTE)
                AND SUBSTRING(oln.PMO_CDE,2,2) = 'ME'
                AND oln.ITM_NUM <> 'DIGITAL SUB'
        )
)
-- 最终统计
SELECT
    PUB_CDE AS Pub,
    ReportYear AS 'Year',
    COUNT(DISTINCT CTM_NBR) AS 'CustomerCount',
    SUM(tot_liab) AS 'TotalSales',  -- 如果用分摊后的金额，替换为SUM(AnnualSales)
    ROUND(SUM(tot_liab) * 1.0 / COUNT(DISTINCT CTM_NBR), 2) AS 'AvgSalesPerCustomer'
FROM SubscriptionsByYear
GROUP BY PUB_CDE, ReportYear
ORDER BY PUB_CDE, ReportYear;

关键说明 #

• 年份拆分逻辑：通过YearRange CTE生成连续年份，再用BETWEEN关联订阅的起止年份，实现单条订阅拆分为多条年度记录。
• 销售额处理：原代码的tot_liab是整个订阅周期的总金额，如果需要将金额分摊到每一年，取消注释AnnualSales列，统计时用该列求和即可。
• 客户数统计：用COUNT(DISTINCT CTM_NBR)确保每个客户在同一年份只被统计一次，即使他有多个订阅。

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