获取同一组中连续行之间的日期差异 [英] Getting date difference between consecutive rows in the same group

问题描述

我有一个包含以下数据的数据库:

I have a database with the following data:

Group      ID      Time
1          1       16:00:00
1          2       16:02:00
1          3       16:03:00
2          4       16:09:00
2          5       16:10:00
2          6       16:14:00

我试图找出每组中连续行之间的时间差异.使用 LAG() 和 DATEDIFF()(即https://stackoverflow.com/a/43055820)，现在我有以下结果集:

I am trying to find the difference in times between the consecutive rows within each group. Using LAG() and DATEDIFF() (ie. https://stackoverflow.com/a/43055820), right now I have the following result set:

Group      ID       Difference
    1          1    NULL
    1          2    00:02:00
    1          3    00:01:00
    2          4    00:06:00
    2          5    00:01:00
    2          6    00:04:00

但是，当到达新组时，我需要重置差异，如下所示.谁能给点建议?

However I need the difference to reset when a new group is reached, as in below. Can anyone advise?

Group      ID       Difference
    1          1    NULL
    1          2    00:02:00
    1          3    00:01:00
    2          4    NULL
    2          5    00:01:00
    2          6    00:04:00

推荐答案

代码看起来像:

select t.*,
       datediff(second, lag(time) over (partition by group order by id), time)
from t;

这会以秒数形式返回差异，但您似乎知道如何将其转换为时间表示.您似乎也知道 group 不能作为列名，因为它是 SQL 关键字.

This returns the difference as a number of seconds, but you seem to know how to convert that to a time representation. You also seem to know that group is not acceptable as a column name, because it is a SQL keyword.

根据问题，您已将 group 放在 lag() 的 order by 子句中，而不是 分区通过.

Based on the question, you have put group in the order by clause of the lag(), not the partition by.

这篇关于获取同一组中连续行之间的日期差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！