如何获得每个值的任何 2 个连续行之间的最大时间差? [英] How to Get the MAX Time Difference Between Any 2 Consecutive Rows Per Value?

问题描述

根据以下 CTE，我如何根据 CODE 提取任意两连续行/[位置] 之间的最大 CreateDT 差异:

How could I pull the maximum CreateDT difference between any two consecutive rows/[Position], per CODE, based on the CTE below:

WITH OccDiff AS
    (
        SELECT 
            CODE
            , CreateDT
            , ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY CreateDT DESC) Position FROM Occs
    )

推荐答案

我们可以改用 LAG/LEAD 吗?

Can we use LAG/LEAD instead?

SELECT MAX(diff_sec) FROM
(
  SELECT 
    DATEDIFF(
        SECOND,  
        CreateDT,
        LEAD(CreateDT) OVER(PARTITION BY CODE ORDER BY CreateDT) --next row's createdt
    ) as diff_sec
  FROM Occs
)x

LEAD 将选择相对于当前行的下一个 CreateDT(下一个由具有相同代码的行和当前行的 CreateDT 之后的时间上第一个更大的 CreateDt"定义).DATEDIFF 获取以秒为单位的差异(选择合适的时间范围).它需要包含在子查询中(如果您愿意，也可以使用 CTE)，因为窗口函数 LEAD 不能出现在 MAX 中

LEAD will choose the next CreateDT (next defined by "the row that has the same code and the temporally first greater CreateDt after the current row's CreateDT") relative to the current row. DATEDIFF gets the difference in seconds (choose a suitable time frame). It needs wrapping up in an subquery (or CTE if you prefer) because window function LEAD cannot appear inside a MAX

当然，它可能不是特别有用......也许也添加到代码中:

It's maybe not particularly useful, of course.. Perhaps add in the code too:

SELECT x.code, MAX(x.diff_sec) FROM
(
  SELECT 
    code,
    DATEDIFF(
        SECOND,  
        CreateDT,
        LEAD(CreateDT) OVER(PARTITION BY CODE ORDER BY CreateDT) --next row's createdt
    ) as diff_sec
  FROM Occs
)x
GROUP BY x.code

这篇关于如何获得每个值的任何 2 个连续行之间的最大时间差?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！