sql - 使用 RANK OVER PARTITION 比较前一行结果

Question

我正在处理一个包含(在其他列中)用户 ID 和开始日期的数据集。目标是有一个新列“isRehire”，将他们的开始日期与以前的开始日期进行比较。

如果 startDates 之间的差异在 1 年以内，则 isRehire = Y。

当用户的开始日期超过 2 个时，困难和我的问题就会出现。如果第三个开始日期和第一个开始日期之间的差异超过一年，则第三个开始日期将是重新雇用的新“基准日期”。

<表类="s-表"><头>用户ID开始日期重新雇用<正文>1232019-07-24N12302/04/20是12308/25/20N12312/20/20是12321-06-15是12308/20/21是12308/30/21N

在上面的示例中，您可以看到问题的可视化。第一个 startDate 07/24/19，用户不是 Rehire。第二个 startDate 02/04/20，他们是 Rehire。 第三个​​ startDate 08/25/20 用户不是被重新雇用，因为距离他们最初的 startDate 已经超过 1 年了。这是新的“锚定”日期。

接下来的 3 个实例都是 Y，因为它们都在新“锚定”日期 08/25/20 的 1 年内。最终开始日期 08/30/21 超过 08/25/20 一年多，表示“N”，“周期”再次重置，08/30/21 作为新的“锚定”日期。

我的目标是利用 RANK OVER PARTITION 来完成这个，因为从我的测试来看，我相信必须有一种方法可以为日期分配等级，然后可以将其包装在 select 语句中，以便 CASE 表达式成为书面。尽管我完全有可能找错树了。

下面您可以看到我尝试使用的一些代码来完成此操作，尽管到目前为止还没有取得太大成功。

select TestRank,
startDate,
userID,
CASE WHEN TestRank = TestRank THEN (TestRank - 1
                                            ) ELSE '' END AS TestRank2
from
(

select userID,
startDate
RANK() OVER (PARTITION BY userID
            ORDER BY startDate desc)
            as TestRank
from [MyTable] a
WHERE a.userID = [int]

) b 

Answer 1

这是复杂的逻辑，窗口函数是不够的。要解决这个问题，您需要迭代——或者在 SQL 中，递归 CTE:

with t as (
      select t.*, row_number() over (partition by id order by startdate) as seqnum
      from mytable t
     ),
     cte as (
      select t.id, t.startdate, t.seqnum, 'N' as isrehire, t.startdate as anchordate
      from t
      where seqnum = 1
      union all
      select t.id, t.startdate, t.seqnum,
             (case when t.startdate > dateadd(year, 1, cte.anchordate) then 'N' else 'Y' end),
             (case when t.startdate > dateadd(year, 1, cte.anchordate) then t.startdate else cte.anchordate end)
      from cte join
           t
           on t.seqnum = cte.seqnum + 1
     )
select *
from cte
order by id, startdate;

Here是一个数据库<> fiddle 。