sql - 根据连续日期计算排名(如果日期之间有间隔天数，则返回 1)

Question

我正在尝试获取此表的排名列。

<表类="s-表"><头>键日期 1姓名1地点1<正文>202021-12-30安站点A232021-12-31安站点A262022-01-03安站点A282022-01-04安站点A292022-01-05安站点A322022-01-06安站点A622022-01-08安站点A632022-01-10安站点A642022-01-11安站点A652022-01-12安站点A662022-01-13安站点A

期望的输出:

<表类="s-表"><头>键日期 1姓名1地点1Rank1<正文>202021-12-30安站点A1232021-12-31安站点A2262022-01-03安站点A1282022-01-04安站点A2292022-01-05安站点A3322022-01-06安站点A4622022-01-08安站点A1632022-01-10安站点A1642022-01-11安站点A2652022-01-12安站点A3662022-01-13安站点A4

我尝试使用从另一篇文章中获得的脚本，但我仍然无法获得我想要的排名列输出

select
    t.Date1,
    t.Name1,
    t.Location1,
    row_number() over (partition by Name1, Location1, grp order by KEY1) as Rank1
from
(
select 
t.*,
sum(case when gap > 1 then 1 else 0 end) over (partition by Name1, Location1, order by KEY1) as grp
from
    (select
     t.*, 
     isnull(datediff(day, Date1, lag(Date1) over (partition by Name1, Location1, order by KEY1)), 1) as gap
     from Table1 t
    ) t
) t;

谢谢。

Answer 1

对我来说，我发现按以下顺序处理最简单:

• 突出显示前一行早于一天的所有情况
• 对于间隔超过一天的每一行，输出一个 1，而不是零
• 从那里，将所有前面的值加起来为每个间隙创建一个组 - 因为每个间隙都会有一个 1，每个新的间隙都会使累积和增加 1，但每个后续的 0 不会，所以它会将所有连续的日子都放在一个“孤岛”中
• 最后，使用分区依据对每个组应用新的行号序列。

结果查询是:

;WITH FindTheGaps AS 
(
  -- highlight all cases where previous row is more than a day earlier
  SELECT *, gap = CASE WHEN Date1 > 
    DATEADD(DAY, 1, LAG(Date1,1) OVER 
    (ORDER BY Date1))
    THEN 1 ELSE 0 END FROM dbo.Table1
),
MarkTheGaps AS
(
  -- add up all the preceding values to create a group for each gap
  SELECT *, grp = SUM(gap) OVER
    (ORDER BY Date1 
      ROWS UNBOUNDED PRECEDING) FROM FindTheGaps
)
SELECT Key1, Date1, Name1, Location1, 
  Rank1 = ROW_NUMBER() OVER 
    (PARTITION BY grp ORDER BY Date1)
  FROM MarkTheGaps ORDER BY Name1, Location1, Date1;

输出:

<表类="s-表"><头>Key1日期 1名称 1位置 1等级 1<正文>202021-12-30安站点 A1232021-12-31安站点 A2262022-01-03安站点 A1282022-01-04安站点 A2292022-01-05安站点 A3322022-01-06安站点 A4622022-01-08安站点 A1632022-01-10安站点 A1642022-01-11安站点 A2652022-01-12安站点 A3662022-01-13安站点 A4

• 示例 db<>fiddle

您的示例数据完全相同 Name1和 Location1值，但是如果您需要在日期有差距时重新开始排名，以及在名称或位置发生变化时，逻辑并没有什么不同，您可以将它们添加到所有OVER()子句，例如:

;WITH FindTheGaps AS 
(
  -- highlight all cases where previous row is more than a day earlier
  SELECT *, gap = CASE WHEN Date1 > 
    DATEADD(DAY, 1, LAG(Date1,1) OVER 
    (PARTITION BY Name1, Location1 ORDER BY Date1))
    THEN 1 ELSE 0 END FROM dbo.Table1
),
MarkTheGaps AS
(
  -- add up all the preceding values to create a group for each gap
  SELECT *, grp = SUM(gap) OVER
    (PARTITION BY Name1, Location1 ORDER BY Date1 
      ROWS UNBOUNDED PRECEDING) FROM FindTheGaps
)
SELECT Key1, Date1, Name1, Location1, 
  Rank1 = ROW_NUMBER() OVER 
    (PARTITION BY Name1, Location1, grp ORDER BY Date1)
  FROM MarkTheGaps ORDER BY Name1, Location1, Date1;

使用此源数据，它提供相同的输出。