查询客户年度订阅关联情况并统计新增与取消订阅量

需求说明 #

查询客户是否存在上一年及下一年的订阅记录，并统计每年的新增订阅量（客户上一年无订阅视为新增）与次年取消订阅量（客户下一年无订阅视为取消）。

示例数据 #

期望中间表 #

期望结果表 #

问题代码 #

尝试以下SQL代码时，所有行的SubscribedPreviousYear均返回'F'：

select 
    t1.Id, cast(t1.year as date), 
    IIF((select count(*) from table t2 
        where t1.Id=t2.Id and 
        datediff(y, t2.year, t1.year)=1) <1, 'T','F') 
    as SubscribedPreviousYear 
from table t;

问题分析与修正方案 #

问题原因 #

1. 日期转换逻辑错误：Subscription year是数值类型（如2010），cast(t1.year as date)会把数值解析为1900-01-01加上对应天数的日期（比如2010会变成1905-06-19），导致datediff(y, ...)计算的年份差完全不符合预期。
2. 逻辑判断颠倒：原代码中IIF(计数<1, 'T','F')的逻辑和需求相反——存在上一年订阅时应该返回'T'，但代码里是无匹配时返回'T'。
3. 表别名笔误：主表别名是t，但代码里引用的是t1，导致关联逻辑出错。

修正后的SQL代码 #

第一步：生成中间表

SELECT 
    t1.ID,
    t1.[Subscription year] AS SubscriptionYear,
    -- 判断是否存在上一年订阅
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM [table] t2 
            WHERE t2.ID = t1.ID 
              AND t2.[Subscription year] = t1.[Subscription year] - 1
        ) THEN 'T' 
        ELSE 'F' 
    END AS SubscribedPreviousYear,
    -- 判断是否存在下一年订阅
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM [table] t2 
            WHERE t2.ID = t1.ID 
              AND t2.[Subscription year] = t1.[Subscription year] + 1
        ) THEN 'T' 
        ELSE 'F' 
    END AS SubscribedNextYear
FROM [table] t1;

第二步：统计最终结果

基于中间表计算每年的新增和取消订阅量：

WITH SubscriptionStatus AS (
    SELECT 
        t1.ID,
        t1.[Subscription year] AS SubscriptionYear,
        CASE 
            WHEN EXISTS (
                SELECT 1 
                FROM [table] t2 
                WHERE t2.ID = t1.ID 
                  AND t2.[Subscription year] = t1.[Subscription year] - 1
            ) THEN 'T' 
            ELSE 'F' 
        END AS SubscribedPreviousYear,
        CASE 
            WHEN EXISTS (
                SELECT 1 
                FROM [table] t2 
                WHERE t2.ID = t1.ID 
                  AND t2.[Subscription year] = t1.[Subscription year] + 1
            ) THEN 'T' 
            ELSE 'F' 
        END AS SubscribedNextYear
    FROM [table] t1
)
SELECT 
    SubscriptionYear AS Year,
    SUM(CASE WHEN SubscribedPreviousYear = 'F' THEN 1 ELSE 0 END) AS [New (# F's in SubscribedPreviousYear)],
    SUM(CASE WHEN SubscribedNextYear = 'F' THEN 1 ELSE 0 END) AS [Canceled (# F's in SubscribedNextYear)]
FROM SubscriptionStatus
GROUP BY SubscriptionYear
ORDER BY SubscriptionYear;

修正说明 #

1. 直接用数值比较年份，避免日期转换带来的错误，因为Subscription year本身就是年份数值。
2. 使用EXISTS替代COUNT(*)，性能更优——找到匹配记录就停止查询，无需统计所有匹配项。
3. 修正逻辑判断：存在上一年订阅时返回'T'，否则返回'F'，完全符合需求定义。
4. 修正表别名笔误，确保主表与子表的关联逻辑正确。

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