sql - 对于每个 ID#，FInd 记录比同一列中给出的日期早/晚 1 年

Question

我需要查找客户是否在上一年和下一年进行了订阅，以及下一年有多少订阅是新的或被取消的。

示例数据:

<表类="s-表"><头>身份证订阅年份<正文>120101201112019220112201232010

考虑这种方法:在订阅年份中减去 1 并加 1，然后查看客户 ID 是否有对应的另一行(例如，如果年份 + 1 没有行，则客户取消了下一年)。希望是这样的:

<表类="s-表"><头>身份证订阅年份SubscribedPreviousYear订阅的下一年<正文>12010FT12011TF12019FF22011FT22012TF32010FF

然后将 SubscribedPreviousYear 中的 F 计为新订阅(如果客户在前一年没有订阅，则它们被计为新订阅，即使对于现有客户也是如此)，并将 SubscribedNextYear 中的 F 计为取消订阅，得到如下内容:

<表类="s-表"><头>年新的(# F's in SubscribedPreviousYear)已取消(# F's in SubscribedPreviousYear)<正文>201021201111201201201911

我试过这段代码，从类似的 MySQL 问题修改而来，但所有行都得到“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;

Answer 1

我会在这里使用 LEAD() 和 LAG():

SELECT id, year,
       CASE WHEN LAG(year) OVER (PARTITION BY id ORDER BY year) = year - 1
            THEN 'T' ELSE 'F' END AS SubscribedPreviousYear,
       CASE WHEN LEAD(year) OVER (PARTITION BY id ORDER BY year) = year + 1
            THEN 'T' ELSE 'F' END AS SubscribedNextYear
FROM yourTable
ORDER BY id, year;

为了得到最终的结果，我们可以按年份汇总:

WITH cte AS (
    SELECT *,
        LAG(year) OVER (PARTITION BY id ORDER BY year) AS year_lag,
        LEAD(year) OVER (PARTITION BY id ORDER BY year) AS year_lead
    FROM yourTable
)

SELECT year,
       COUNT(CASE WHEN year != year_lag + 1 THEN 1 END) AS [New],
       COUNT(CASE WHEN year != year_lead - 1 THEN 1 END) AS Cancelled
FROM cte
GROUP BY year
ORDER BY year;